Thank you. I'll set work_mem back to 16MB and see what happens from there.
-Midge
----- Original Message -----
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 <midgems@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.