Re: PostgreSQL 8.4 performance tuning questions

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.