From: Marinos Yannikos <mjy@geizhals.at>
To: pgsql-performance@postgresql.org
Subject: Background writer underemphasized ...
Date: Wed, 16 Apr 2008 17:22:50 +0200
Message-ID: <480619CA.2080904@geizhals.at> (raw)
Hi,
to save some people a headache or two: I believe we just solved our
performance problem in the following scenario:
- Linux 2.6.24.4
- lots of RAM (32GB)
- enough CPU power (4 cores)
- disks with relatively slow random writes (SATA RAID-5 / 7 disks, 128K
stripe, ext2)
Our database is around 86GB, the busy parts being 20-30GB. Typical load
is regular reads of all sizes (large joins, sequential scans on a 8GB
table, many small selects with few rows) interspersed with writes of
several 1000s rows on the busier tables by several clients.
After many tests and research revolving around the Linux I/O-Schedulers
(which still have some issues one should be wary about:
http://lwn.net/Articles/216853/) because we saw problems when occasional
(intensive) writes completely starved all I/O, we discovered that
changing the default settings for the background writer seems to have
solved all these problems. Performance is much better now with fsync on
than it was with fsync off previously, no other configuration options
had a noticeable effect on performance (or these problems rather).
This helped with our configuration:
bgwriter_delay = 10000ms # 10-10000ms between rounds
bgwriter_lru_maxpages = 1000 # 0-1000 max buffers written/round
Previously, our typical writes resulted in around 5-10MB/s going to disk
and some reads stalling, now we are seeing typical disk I/O in the
30-60MB/s range with write load present and no noticeable problems with
reads except when autovacuum's "analyze" is running. Other options we
have tried/used were shared_buffers between 200MB and 20GB, wal_buffers
= 256MB, wal_writer_delay=5000ms ...
So, using this is highly recommended and I would say that the
documentation does not do it justice... (and yes, I could have figured
it out earlier)
-mjy
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-performance@postgresql.org
Cc: mjy@geizhals.at
Subject: Re: Background writer underemphasized ...
In-Reply-To: <480619CA.2080904@geizhals.at>
* 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