Re: Some ideas about Vacuum

On Wed, 16 Jan 2008, Kevin Grittner wrote:

> I haven't seen any benchmarks on the list or in our environment
> where the separate spindles gave more than a 1% increase in
> performance when using a good-quality BBC controller.

Well, even 1% isn't nothing, which is the main point I was making--it
doesn't completely remove the gain, just reduce it a lot. If you wanted
to see a bigger difference you could simulate a workload with lots of
clients doing short transactions.

The biggest gain in having a separate WAL isn't as obvious in gross
performance measurements. It's what happens to worst-case performance for
the transactions just after a checkpoint, when there is a burst of more
full page writes (they normally settle down as the most popular pages get
written). That's the spot where you're most likely to run into a WAL
bottleneck that just having a BBC doesn't completely eliminate.

> Do you have results that show more of a difference? Can you share them?

I wasn't trying to quantify this particular number and it would take a bit
just to figure out what I could and couldn't share. Expanding on the
above, though, if you look some of the recent public benchmarks like
http://www.kaltenbrunner.cc/blog/index.php?/archives/21-8.3-vs.-8.2-a-simple-benchmark.html
you'll see Stefan was able to hit around 4000 TPS on that test system.
Now, if you had a typical 256MB BBC (it's 512MB there) and full pages
writes are dumping 8K each, that means you can fit 32768 of them before
you blow your cache and the disks really have to keep up--and the WAL
doesn't get the whole cache to itself. The first 10-20 seconds after a
checkpoint on such a system are kind of interesting to zoom in on. If the
WAL has to fight for seek time with database reads during that period (DB
writes will still be mostly cached by the OS just after a checkpoint) it
can be messy compared to what you get with a dedicated WAL. But that will
average out to a minimal effect on TPS over the course of the test.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD