From: Greg Smith <greg@2ndquadrant.com>
To: Richard Yen <richyen@iparadigms.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: [Slony1-general] WAL partition overloaded--by autovacuum?
Date: Sat, 10 Jul 2010 00:23:53 +0100
Message-ID: <4C37AF89.1070103@2ndquadrant.com> (raw)
In-Reply-To: <78E79F0B-5C6C-4BDA-B69C-C0FBF69ABBC7@iparadigms.com>
References: <03D2C5F0-F222-4088-B5E4-8DA688565B70@iparadigms.com>
<AANLkTik1oR2fMBp9pzpeYUgrANXKpK0B23scXONQEK1z@mail.gmail.com>
<78E79F0B-5C6C-4BDA-B69C-C0FBF69ABBC7@iparadigms.com>
Richard Yen wrote:
> I figured that pg_xlog and data/base could both be on the FusionIO drive, since there would be no latency when there are no spindles.
>
(Rolls eyes) Please be careful about how much SSD Kool-Aid you drink,
and be skeptical of vendor claims. They don't just make latency go away,
particularly on heavy write workloads where the technology is at its
weakest.
Also, random note, I'm seeing way too many FusionIO drive setups where
people don't have any redundancy to cope with a drive failure, because
the individual drives are so expensive they don't have more than one.
Make sure that if you lose one of the drives, you won't have a massive
data loss. Replication might help with that, if you can stand a little
bit of data loss when the SSD dies. Not if--when. Even if you have a
good one they don't last forever.
> This means my pg_xlog partition should be (2 + checkpoint_completion_target) * checkpoint_segments + 1 = 41 files, or 656MB. Then, if there are more than 49 files, unneeded segment files will be deleted, but in this case all segment files are needed, so they never got deleted. Perhaps we should add in the docs that pg_xlog should be the size of the DB or larger?
>
Excessive write volume beyond the capacity of the hardware can end up
delaying the normal checkpoint that would have cleaned up all the xlog
files. There's a nasty spiral that can get into I've seen a couple of
times in similar form to what you reported. The pg_xlog should never
exceed the size computed by that formula for very long, but it can burst
above its normal size limits for a little bit. This is already mentioned
as possibility in the manual: "If, due to a short-term peak of log
output rate, there are more than 3 * checkpoint_segments + 1 segment
files, the unneeded segment files will be deleted instead of recycled
until the system gets back under this limit." Autovacuum is an easy way
to get the sort of activity needed to cause this problem, but I don't
know if it's a necessary component to see the problem. You have to be in
an unusual situation before the sum of the xlog files is anywhere close
to the size of the database though.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us
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: greg@2ndquadrant.com, richyen@iparadigms.com, scott.marlowe@gmail.com
Subject: Re: [Slony1-general] WAL partition overloaded--by autovacuum?
In-Reply-To: <4C37AF89.1070103@2ndquadrant.com>
* 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