Hi Tomas,Uwe,David G
Thanks for your reply.
>So, did a single process use 70% of memory, or all postgres processes
>combined?
>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).
>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.vondra@xxxxxxxxxxxxxxx> 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
