Re: PostgreSQL 8.4 performance tuning questions

[Rauan Maemirov <rauan(at)maemirov(dot)com>]

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.
>