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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD