Re: postgresql 9.5 has ocuuered OOM

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