[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] to many locks held
From:       Michael Paquier <michael.paquier () gmail ! com>
Date:       2013-07-31 5:18:21
Message-ID: CAB7nPqQaZ9TaMn6cSMc9GwCWDNcBS=pv=6=r715+vN9YPNO+xw () mail ! gmail ! com
[Download RAW message or body]

On Tue, Jul 30, 2013 at 11:48 PM, bricklen <bricklen@gmail.com> wrote:

> On Tue, Jul 30, 2013 at 3:52 AM, Jeison Bedoya <jeisonb@audifarma.com.co>wrote:
>
 memory ram: 128 GB
>> cores: 32
>>
>> max_connections: 900
>>
>
> I would say you might be better off using a connection pooler if you need
> this many connections.
>
Yeah that's a lot. pgbouncer might be a good option in your case.

work_mem = 1024MB
>>
>
> work_mem is pretty high. It would make sense in a data warehouse-type
> environment, but with a max of 900 connections, that can get used up in a
> hurry. Do you find your queries regularly spilling sorts to disk (something
> like "External merge Disk" in your EXPLAIN ANALYZE plans)?
>
work_mem is a per-operation setting for sort/hash operations. So in your
case you might finish with a maximum of 900GB of memory allocated based on
the maximum number of sessions that can run in parallel on your server.
Simply reduce the value of work_mem to something your server can manage and
you should be able to solve your problems of OOM.
-- 
Michael

[Attachment #3 (text/html)]

<div dir="ltr"><br><div class="gmail_extra"><br><br><div class="gmail_quote">On Tue, \
Jul 30, 2013 at 11:48 PM, bricklen <span dir="ltr">&lt;<a \
href="mailto:bricklen@gmail.com" target="_blank">bricklen@gmail.com</a>&gt;</span> \
wrote:<br> <blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px \
#ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><div \
class="gmail_quote"><div class="im">On Tue, Jul 30, 2013 at 3:52 AM, Jeison Bedoya \
<span dir="ltr">&lt;<a href="mailto:jeisonb@audifarma.com.co" \
target="_blank">jeisonb@audifarma.com.co</a>&gt;</span> wrote:</div> \
</div></div></div></blockquote><blockquote class="gmail_quote" style="margin:0 0 0 \
.8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div \
class="gmail_extra"><div class="gmail_quote"><div> </div><div class="im"><blockquote \
class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"> memory ram: 128 GB<br>
cores: 32<br>
<br>
max_connections: 900<br></blockquote><div><br></div></div><div>I would say you might \
be better off using a connection pooler if you need this many \
connections.<br></div></div></div></div></blockquote><div>Yeah that&#39;s a lot. \
pgbouncer might be a good option in your case. <br> <br></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div dir="ltr"><div class="gmail_extra"><div \
class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">


work_mem = 1024MB<br></blockquote><div><br></div><div>work_mem is pretty high. It \
would make sense in a data warehouse-type environment, but with a max of 900 \
connections, that can get used up in a hurry. Do you find your queries regularly \
spilling sorts to disk (something like &quot;External merge Disk&quot; in your \
EXPLAIN ANALYZE plans)?<br> </div></div></div></div></blockquote><div>work_mem is a \
per-operation setting for sort/hash operations. So in your case you might finish with \
a maximum of 900GB of memory allocated based on the maximum number of sessions that \
can run in parallel on your server. Simply reduce the value of work_mem to something \
                your server can manage and you should be able to solve your problems \
                of OOM.<br>
-- <br></div></div>Michael<br>
</div></div>



[prev in list] [next in list] [prev in thread] [next in thread]