Message-Id: <4EB0069002000025000428F9@gw.wicourts.gov>
Date: Tue, 01 Nov 2011 14:47:44 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Brian Fehrle" <brianf@consistentstate.com>
Cc: "Kevin Kempter" <kevink@consistentstate.com>, <pgsql-admin@postgresql.org>
Subject: Re: background writer being lazy?
References: <4EAF2F20.7080205@consistentstate.com>
	<4EAFC2CA02000025000428D2@gw.wicourts.gov>
	<4EB03821.6090408@consistentstate.com>
	<4EAFF7C502000025000428F2@gw.wicourts.gov>
	<4EB04533.4080905@consistentstate.com>
In-Reply-To: <4EB04533.4080905@consistentstate.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Brian Fehrle <brianf@consistentstate.com> wrote:
 
> PostgreSQL 8.4.1 on x86_64-unknown-linux-gnu
 
Please upgrade to the latest bug fix release of PostgreSQL:
 
http://www.postgresql.org/support/versioning
 
To see what bug and security fixes you're missing, look at release
notes for 8.4.2 to 8.4.9 here:
 
http://www.postgresql.org/docs/8.4/static/release.html
 
There have been improvements in your areas of concern in 9.0 and
9.1, so you might want to start planning a major release upgrade. 
That's not as painful as it used to be, with pg_upgrade.
 
>   bgwriter_lru_maxpages   | 500
 
FWIW, we do set this to 1000.
 
>   max_connections | 2000
 
This is probably your biggest problem.  Unless you've got 1000 CPUs
on this box, you should use a connection pooler which is
transaction-oriented, limits the number of database connections, and
queues requests for a new transaction when all connections are in
use.  This will almost certainly improve throughput and limit
latency problems.  You do not need 2000 connections to support 2000
cnocurrent users; such a setting will make it harder to provide 2000
concurrent users with decent and consistent performance.
 
>   effective_cache_size   | 16GB
 
Given your other settings, this seems likely to be low.  I normally
add the cache space reported by the OS to the shared_buffers
setting.
 
>   shared_buffers | 16GB
 
This is probably at least twice what it should be.  If you are
having problems with backends writing too many buffers and problems
with clusters of I/O congestion, you might want to drop it to the
0.5 to 2.0 GB range.
 
>   wal_buffers                   | 8MB
 
Might as well go to 16MB.
 
>   work_mem                      | 64MB
 
Each of your connections can allocate this much space, potentially
several times, at the same moment.  Unless you really have a monster
machine, 64MB * 2000 connections is just asking for out of memory
failures at unpredictable peak load times.
 
> One main thing is trying to lower 'spikey' disk IO so that
> performance is more consistent at any given time.
 
The advice above should help with that.
 
-Kevin