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

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*
B. shared_memory
C. work_memory
D. max_connections
E. How many users were connected when the problem occurred?

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