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

On Fri, Apr 14, 2017 at 2:28 PM, Moreno Andreo <moreno(dot)andreo(at)evolu-s(dot)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(dot)andreo(at)evolu-s(dot)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(dot)andreo(at)evolu-s(dot)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.