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.marlowe@xxxxxxxxx>: > On Thu, Jul 30, 2009 at 10:10 PM, Greg Smith<gsmith@xxxxxxxxxxxxx> 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. > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
