Re: 8.2 is 30% better in pgbench than 8.3

On Sun, 22 Jul 2007, Tom Lane wrote:

> There seems to be also some of the "good average but bad worst case"
> behavior that Josh and others have pointed out in bigger benchmarks.
> I've always assumed this was due to checkpointing

At lot of variation is from checkpoints, some comes from the index 
fullness/fragmentation, some from vacuuming, a bit is added based on 
what's in the buffer cache from previous runs, and there's a touch of 
randomness thrown on top even if you control all those things.  My tests 
suggest the "bad worst case" results in pgbench tests are almost always 
from checkpoints that happen when the buffer cache is filled with almost 
exclusively dirty buffers (which can happen very easily with pgbench).

I am in the process of squashing these issues when running pgbench against 
8.3.  A before/after look at pg_stat_bgwriter lets you measure the 
checkpoint variation.  The ability to directly play with the index 
fillfactor lets you test/control the impact of that.  Wrap all that into a 
scripting framework that runs the tests many times for you in a consistant 
fashion and then summarizes the results, and pgbench becomes a rough but 
completely servicable tool.  Expect to see a pgbench-tools project that 
does all that from me soon, my copy works but it's not ready for public 
consumption yet.

I've also got a working backport of all the pg_stat_bgwriter instrumenting 
that applies to 8.2, so people can run all this against the current 
version as well if they're in a position where they can hack a custom 
build.  The hold-up on that is that the code that measures buffers 
allocated and those written by back-ends for 8.3 is wrapped into the 
"Automatic adjustment of bgwriter_lru_maxpages" patch, and I'm not going 
to finalize my unofficial backport until that gets applied.  Once HOT 
wraps up that loose end should get snipped easily enough.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD