Search Postgresql Archives

Re: Service outage: each postgres process use the exact amount of the configured work_mem

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Fri, Apr 14, 2017 at 2:28 PM, Moreno Andreo <moreno.andreo@xxxxxxxxxx> 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.andreo@xxxxxxxxxx> 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.andreo@xxxxxxxxxx> 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
"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/


--
Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux