Thank you. I'll set work_mem back to 16MB and see what happens from there. -Midge ----- Original Message ----- From: Scott Marlowe To: Midge Brown Cc: pgsql-performance@postgresql.org Sent: Saturday, August 20, 2011 9:01 PM Subject: Re: [PERFORM] settings input for upgrade
On Thu, Aug 18, 2011 at 3:55 PM, Midge Brown <midg...@sbcglobal.net> wrote: > Here are the changes I made to postgres.conf. The only differences between > the conf file for DB1 and those for DB2 & 3 are the port and > effective_cache_size (which I made slightly smaller -- 8 GB instead of 10 -- > for the 2 write-heavy DBs). The 600 max connections are often idle and don't > get explicitly closed in the application. I'm looking at connection pooling > as well. > work_mem = 128MB I'd lower this unless you are certain that something like 16MB just isn't gonna get similar performance. Even with mostly connections idle, 128M is a rather large work_mem. Remember it's per sort, per connection. It can quickly cause the kernel to dump file cache that keeps the machine running fast if a couple dozen connections run a handful of large sorts at once. What happens is that while things run smooth when there's low to medium load, under high load the machine will start thrashing trying to allocate too much work_mem and then just slow to a crawl.
