On Fri, 12 Sep 2008, Guillaume Cottenceau wrote:
> Out of the blue, is it just because when postgresql fsync's after a
> write, on a normal system the write has to really happen on disk and
> waiting for it to be complete, whereas with BBU cache the fsync is
> almost immediate because the write cache actually replaces the "really
> on disk" write?
That's the main thing, and nothing else you can do will accelerate that.
Without a useful write cache (which usually means RAM with a BBU), you'll
at best get about 100-200 write transactions per second for any one
client, and something like 500/second even with lots of clients (queued up
transaction fsyncs do get combined). Those numbers increase to several
thousand per second the minute there's a good caching controller in the
mix.
You might say "but I don't write that heavily, so what?" Even if the
write volume is low enough that the disk can keep up, there's still
latency. A person who is writing transactions is going to be delayed a
few milliseconds after each commit, which drags some types of data loading
to a crawl. Also, without a cache in places mixes of fsync'd writes and
reads can behave badly, with readers getting stuck behind writers much
more often than in the cached situation.
The final factor is that additional layers of cache usually help improve
physical grouping of blocks into ordered sections to lower seek overhead.
The OS is supposed to be doing that for you, but a cache closer to the
drives themselves helps smooth things out when the OS dumps a large block
of data out for some reason. The classic example in PostgreSQL land,
particularly before 8.3, was when a checkpoint happens.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD