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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD
