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@2ndQuadrant.com    Baltimore, MD