Re: limiting performance impact of wal archiving.

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(at)2ndQuadrant(dot)com Baltimore, MD