Re: New features for pgbench - Mailing list pgsql-patches

On Mon, 12 Feb 2007, NikhilS wrote:

> From your patch I see that it augments the -l flag. IMHO it does not
> make sense to add another flag. We can save the "if" check and log the
> extended contents as part of -l itself.

I wanted something people could apply to 8.2 without breaking existing
scripts (regardless of whether it was accepted into 8.2).  And I expected
some concern over whether this change effects results.  By putting in a
switch, it's possible to test both ways, with only the if being added to
the default case.

> If I read it correctly, are you suggesting that the same database with a
> prior pgbench run be used for further pgbench runs? How is it useful?
> How can one guarantee consistency of observed tps values with this in
> place?

Right now when you run pgbench, the results vary considerably from run to
run even if you completely rebuild the database every time.  I've found
that a lot of that variation comes from two things:

1) If your run is so small that it usually doesn't generate a checkpoint,
the runs that do encounter one will be slower--possibly a lot slower if
you have a large buffer cache.  Similarly, runs that are just long enough
to normally encounter one checkpoint will take longer if they happen to
run into two, and so on.  There are games you can play with improving
pgbench performance by using more checkpoint_segments and a bigger
shared_buffer cache that look like they dramatically improve results.
But what you're mainly doing is just making checkpoints less frequent,
reducing the odds that you'll run into one during the pgbench test itself.

2) The standard pgbench test does 3 UPDATEs per transaction.  That leaves
behind a lot of dead rows that need to be vacuumed.  The amount of
autovacuum that occurs during the run will vary.  This means that some
runs finish with more dead space left behind than others.  It really isn't
fair that a pgbench run that involves cleaning up more of its own mess
during the test will get a lower TPS result than one that just generates a
bunch of dead tuples and leaves the mess hanging around.  Right now,
optimal pgbench results are generated with the autovacuum turned
completely off; that's just not realistic.

In order to get a completely fair comparison, I've adopted a policy that
says the run isn't over until the database has been cleaned up such that
it's in a similar state to how it started:  all tables are vacuumed, and
all updates have been written to disk.  The new -X behavior forces this
cleanup to be considered part of the test.  Whether or not you choose to
use it for your regular tests, I suggest trying it out.  You may be as
surprised as I was at exactly how much vacuuming work is leftover after a
long pgbench run, and how dramatically it lowers TPS results if you
consider that cleanup essential to the test.

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