Re:Re: postgresql 9.5 has ocuuered OOM

Hi Tomas,Uwe,David G
Thanks for your reply.

>So, did a single process use 70% of memory, or all postgres processes
>combined?

all postgres processes use over 70% of memory.
>If just a single process, it might be a poor plan choice (e.g. hash
>aggregate may easily cause that).

because delete clause used a lot of memory .

delete clause is below:

DELETE FROM test WHERE testid in (select r_id from test1 where p_id_id in ( select re_id from ap_en where link = $1))

delete from test where test1_id = $1 AND id = $2

because delete clause is using select condition. maybe It make memory useage high.

if I decrease the work_mem size,It will affect delete clause execution efficiency,

I want the session unit to set work_mem size.

the OS level (cgconfig) to set all postgres processes memory usage.

How about this setting ?

Regards,

Mark

At 2017-12-21 00:24:35, "Tomas Vondra" <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>On 12/20/2017 04:08 PM, mark wrote:
>> I have set shared_buffers is 1/4 of memory.
>> work_mem is 2% of memory.
>> max_connections is 50.
>
>That means if you have all 50 connections active, they may easily
>consume 100% of memory, because 50 * 2 is 100. It's even easier if the
>connections are executing complex queries, because each query may use
>multiple work_mem buffers. So 2% seems a bit too high.
>
>> momery size is 16GB.
>> postgresql process used over 70% of memory and occuered OOM.
>
>So, did a single process use 70% of memory, or all postgres processes
>combined?
>
>If just a single process, it might be a poor plan choice (e.g. hash
>aggregate may easily cause that).
>
>If all processes combined, then perhaps it's due to work_mem being too high.
>
>> what should I do to deal with this problem?
>>
>
>Hard to say, until you provide enough information.
>
>regards
>
>--
>Tomas Vondra http://www.2ndQuadrant.com
>PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services