Re: settings input for upgrade - Mailing list pgsql-performance

From Midge Brown
Subject Re: settings input for upgrade
Date
Msg-id E8FE04D126994AC9A7AE0299DE241439@BERNICE
Whole thread Raw
In response to settings input for upgrade  ("Midge Brown" <midgems@sbcglobal.net>)
List pgsql-performance
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.

pgsql-performance by date:

Previous
From: hyelluas
Date:
Subject: Re: tunning strategy needed
Next
From: "Midge Brown"
Date:
Subject: Re: settings input for upgrade