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@xxxxxxxxxxxxxxx Baltimore, MD
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
