From: Greg Smith <greg@2ndquadrant.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Laurent Laborde <kerdezixe@gmail.com>
Cc: Ivan Voras <ivoras@freebsd.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: limiting performance impact of wal archiving.
Date: Tue, 10 Nov 2009 12:48:46 -0500
Message-ID: <4AF9A77E.9070406@2ndquadrant.com> (raw)
In-Reply-To: <dcc563d10911100901i3af595dr71920fe99c1f9f70@mail.gmail.com>
References: <8a1bfe660911100355l34a557ffj8244079c1fb689b5@mail.gmail.com>
<hdbrv8$p35$1@ger.gmane.org>
<8a1bfe660911100700l2a44f638m97ea6136b65deb21@mail.gmail.com>
<hdbvqt$7v8$1@ger.gmane.org>
<8a1bfe660911100729q42ac732cga9bbadd52e2533dd@mail.gmail.com>
<4AF99666.5090705@2ndquadrant.com>
<8a1bfe660911100852l5573ab30ne91c6d8937dc836b@mail.gmail.com>
<dcc563d10911100901i3af595dr71920fe99c1f9f70@mail.gmail.com>
Scott Marlowe wrote:
> On some busy systems with lots of small transactions large
> shared_buffer can cause it to run slower rather than faster due to
> background writer overhead.
>
This is only really true in 8.2 and earlier, where background writer
computations are done as a percentage of shared_buffers. The rewrite I
did in 8.3 changes that to where it's proportional to overall system
activity (specifically, buffer allocations) and you shouldn't see this
there. However, large values for shared_buffers do increase the
potential for longer checkpoints though, which is similar background
overhead starting in 8.3. That's why I mention it hand in hand with
decreasing the checkpoint frequency, you really need to do that before
large shared_buffers values are viable.
This is actually a topic I meant to mention to Laurent: if you're not
running at least PG8.3, you really should be considering what it would
take to upgrade to 8.4. It's hard to justify the 8.3->8.4 upgrade just
based on that version's new performance features (unless you delete
things a lot), but the changes from 8.1 to 8.2 to 8.3 make the database
faster at a lot of common tasks.
> Note that if you've got a slow IO subsystem, a large number of
> checkpoint segments can result in REALLY long restart times after a
> crash, as well as really long waits for shutdown and / or bgwriter
> once you've filled them all up.
>
The setup here, with a decent number of disks and a 3ware controller,
shouldn't be that bad here. Ultimately you have to ask yourself whether
it's OK to suffer from the rare recovery issue this introduces if it
improves things a lot all of the rest of the time, which increasing
checkpoint_segments does.
> Note that XFS gets a LOT of testing, especially under linux. That
> said it's still probably only 1/10th as many dbs (or fewer) as those
> running on ext3 on linux. I've used it before and it's a little
> faster than ext3 at some stuff, especially deleting large files (or in
> pg's case lots of 1G files) which can make ext3 crawl.
>
While true, you have to consider whether the things it's better at
really happen during a regular day. The whole "faster at deleting large
files" thing doesn't matter to me on a production DB server at all, so
that slam-dunk win for XFS doesn't even factor into my filesystem
ranking computations in that context.
--
Greg Smith greg@2ndQuadrant.com Baltimore, MD
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, scott.marlowe@gmail.com, kerdezixe@gmail.com, ivoras@freebsd.org
Subject: Re: limiting performance impact of wal archiving.
In-Reply-To: <4AF9A77E.9070406@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