On Fri, Apr 14, 2017 at 2:28 PM, Moreno Andreo <moreno.and...@evolu-s.it> wrote:
> Melvin, > Sorry for top-posting. > I'm going ahead in troubleshooting. As Jeff said, there's probably nothing > wrong with my values (at the end of the message you can find minimal info > you requested). > I tried running some queries against psql server and response times are > good, so I'm moving my attentions to Windows server, which hosts a WCF > service, that is the one that actually server customers. > > Thanks for now > Moreno > > > Il 14/04/2017 20:01, Melvin Davidson ha scritto: > > > On Fri, Apr 14, 2017 at 1:50 PM, Moreno Andreo <moreno.and...@evolu-s.it> > wrote: > >> Sorry, >> my mistake (I'm a bit nervous...) >> >> that's not work_mem, but shared_buffers >> >> Thanks >> >> >> Il 14/04/2017 19:33, Melvin Davidson ha scritto: >> >> >> >> On Fri, Apr 14, 2017 at 1:12 PM, Moreno Andreo <moreno.and...@evolu-s.it> >> wrote: >> >>> Hi all, >>> About 2 hours and half ago, suddenly (and on the late afternoon of the >>> Easter Friday), customers reported failing connections to our server, or >>> even very slow. >>> After a bit of checking (that also involved server reboot) I noticed >>> (using top) that every process regarding postgres is using exactly the >>> amout I configured as work_mem (3 GB). And AFAIK it's not good. >>> >>> 30085 postgres 20 0 *3370048* 156656 153876 S 6.7 0.3 0:00.20 postgres >>> 29833 postgres 20 0 *3370000* 65260 62416 S 1.7 0.1 0:00.17 postgres >>> 29632 postgres 20 0 *3372468* 11712 6028 S 0.7 0.0 0:00.60 postgres >>> >>> What can be happened? >>> Nothing has been touched.... >>> postgresql 9.5.6 on debian 8 just apt-get upgrade'd >>> >>> Any help would be appreciated. >>> Moreno. >>> >> >> >> >> *>using exactly the amout I configured as work_mem (3 GB). * >> >> >> *You are right, that is bad, but that is your own fault. 3GB of work_mem >> is very bad, Try lowing in to something more reasonable, like 20GB. * >> >> *https://www.postgresql.org/docs/9.5/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY >> <https://www.postgresql.org/docs/9.5/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-MEMORY> >> "several running sessions could be doing such operations concurrently. >> Therefore, the total memory used could be many times the value of work_mem; >> it is necessary to keep this fact in mind when choosing the value."* >> >> -- >> *Melvin Davidson* >> I reserve the right to fantasize. Whether or not you >> wish to share my fantasy is entirely up to you. >> >> >> > Moreno, > we are working with minimal information here. > Perhaps if you provided us with the following information it would be more > useful, > > A. Total *SYSTEM MEMORY* > > 52 GB > > B. shared_memory > > 3 GB (was 13 GB) > > C. work_memory > > default > > D. max_connections > > 1000 > > E. How many users were connected when the problem occurred? > > About 350 connections > > Thanks > > > -- > *Melvin Davidson* > I reserve the right to fantasize. Whether or not you > wish to share my fantasy is entirely up to you. > > > *Moreno, * *I understand you will troubleshoot on your own, but based on total system memory, you should increase * *shared_memory to 40GB. General philosphy is to allocate 80% of system memory to shared_memory. Of course you will have to do a PostgreSQL restart * *for that to take effect.* *Also, with 1000 max_connections, you would be much better off with a connection balancer like PgBouncer https://pgbouncer.github.io/downloads/ <https://pgbouncer.github.io/downloads/> * -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
