From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Brian Fehrle <brianf@consistentstate.com>
Cc: Kevin Kempter <kevink@consistentstate.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: background writer being lazy?
Date: Tue, 01 Nov 2011 14:47:44 -0500
Message-ID: <4EB0069002000025000428F9@gw.wicourts.gov> (raw)
In-Reply-To: <4EB04533.4080905@consistentstate.com>
References: <4EAF2F20.7080205@consistentstate.com>
<4EAFC2CA02000025000428D2@gw.wicourts.gov>
<4EB03821.6090408@consistentstate.com>
<4EAFF7C502000025000428F2@gw.wicourts.gov>
<4EB04533.4080905@consistentstate.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Kevin.Grittner@wicourts.gov, brianf@consistentstate.com, kevink@consistentstate.com
Subject: Re: background writer being lazy?
In-Reply-To: <4EB0069002000025000428F9@gw.wicourts.gov>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox