Skip site navigation (1) Skip section navigation (2)

Re: PostgreSQL 8.4 performance tuning questions

From: Rauan Maemirov <rauan(at)maemirov(dot)com>
To: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
Cc: Greg Smith <gsmith(at)gregsmith(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: PostgreSQL 8.4 performance tuning questions
Date: 2009-07-31 06:40:36
Message-ID: c78bcec0907302340v1fc0dbbdxb93dcfb1a7b479a8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
That's true. I tried to lower work_mem from 192 to 64, and it caused
total slowdown.
By the way, is there any performance tips for tuning joins? I noticed,
that my joins on 8.4 slowed down, on 8.3 it was faster a bit.

2009/7/31 Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>:
> On Thu, Jul 30, 2009 at 10:10 PM, Greg Smith<gsmith(at)gregsmith(dot)com> wrote:
>> On Thu, 30 Jul 2009, Rauan Maemirov wrote:
>>
>>> maintenance_work_mem = 1GB
>>> work_mem = 192MB
>>> shared_buffers = 7680MB
>>> max_connections = 80
>>> My box is Nehalem 2xQuad 2.8 with RAM 32Gb
>>
>> While it looks like you sorted out your issue downthread, I wanted to point
>> out that your setting for work_mem could be dangerously high here and
>> contribute to problems
>
> The real danger here is that you can set up your pg server to fail
> ONLY under heavy load, when it runs out of memory and goes into a swap
> storm.  So, without proper load testing and profiling, you may not
> know you're headed for danger until your server goes unresponsive
> midday at the most critical of times.  And restarting it will just
> lead to the same failure again as the clients all reconnect and pummel
> your server.
>
> Meanwhile, going from 192 to 16MB might result in a total slowdown
> measured in a fraction of a percentage overall, and prevent this kind
> of failure.
>
> If there's one single request you can justify big work_mem for then
> set it for just that one query.  It's not uncommon to have a reporting
> user limited to a few connections and with "alter user reportinguser
> set work_mem='512MB';" so that it can run fast but not deplete your
> server's resources on accident during heavy load.
>

In response to

pgsql-performance by date

Next:From: ChrisDate: 2009-07-31 06:50:05
Subject: load / stress testing
Previous:From: Stefan KaltenbrunnerDate: 2009-07-31 06:02:58
Subject: Re: PostgreSQL 8.4 performance tuning questions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group