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
