Turn off vacuum in pgbench?

Tatsuo Ishii <ishii@postgresql.org> writes:

Now that PostgreSQL 8.3 enables autovacuum by default, I think pgbench
should stop issuing vacuum in pgbench -i since an ordinary vacuum will
take very long time under autovacuum running. If there's no objection,
I will remove vacuum from pgbench.

I'd be inclined to leave it there, simply because you'll be changing
the conditions of the benchmark if you take it out. I have not noticed
any particular problems with it...

regards, tom lane

On 9/25/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I'd be inclined to leave it there, simply because you'll be changing
the conditions of the benchmark if you take it out. I have not noticed
any particular problems with it...

The only problem I faced is while executing custom scripts using pgbench.
You either need to do the initialization (pgbench -i) or run with -n option
(no vacuum). But I don't have any strong preference here.

Thanks,
Pavan

--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

On Tue, 25 Sep 2007, Tatsuo Ishii wrote:

Now that PostgreSQL 8.3 enables autovacuum by default, I think pgbench
should stop issuing vacuum in pgbench -i since an ordinary vacuum will
take very long time under autovacuum running. If there's no objection,
I will remove vacuum from pgbench.

Just removing the vacuum might cause backwards test repeatability issues
for some people, and fixing all the issues in this area will take a bigger
change than you're suggesting. There are already the -n and -v parameters
to adjust what gets vacuumed and what doesn't, and if you just read the
documentation on those it's really confusing. I had to look at the source
code to fully understand what they did.

I think you're right that something should be done to pgbench to address
that a default auto-vacuum configuration is going to change how some
people want to run the tool, but I don't think it's as simple as "remove
vacuum from pgbench"; in order to have a smooth transition, I'd want to
see the old behavior still available. If the default changes, that would
require inverting the command line switches, and now you're talking about
a much messier change.

Because of that, I personally wouldn't touch anything in the code at this
late point in the 8.3 cycle. An update to the documentation to clarify
how you might change the use of -n/-v based on whether autovaccum is
turned on, and hopefully improving the description of both those switches
in the process, would be a welcome improvement, and I could help out with
that.

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

On Tue, 2007-09-25 at 22:16 +0530, Pavan Deolasee wrote:

The only problem I faced is while executing custom scripts using
pgbench.
You either need to do the initialization (pgbench -i) or run with -n
option
(no vacuum). But I don't have any strong preference here.

Yes, I had that problem too.

pgbench allows you to run a script file, but there is no way to run an
initialiser script for a custom workload. Worse, if you don't do -n when
you run a custom script, it tells you that the branches table doesn't
exist, which is strange if you aren't using that in your scripts.

Proposed enhancement to pgbench:

-g filename

runs filename prior to start of main test transactions. Only used in
conjunction with the -f option. Normal cleaning/vacuuming of TPC-B
tables does not occur if -g option set.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com

Tom Lane wrote:

Tatsuo Ishii <ishii@postgresql.org> writes:

Now that PostgreSQL 8.3 enables autovacuum by default, I think pgbench
should stop issuing vacuum in pgbench -i since an ordinary vacuum will
take very long time under autovacuum running. If there's no objection,
I will remove vacuum from pgbench.

I'd be inclined to leave it there, simply because you'll be changing
the conditions of the benchmark if you take it out. I have not noticed
any particular problems with it...

I wonder if autovacuum itself is going to add more variability to the
test (like we don't have enough already).

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian <bruce@momjian.us> writes:

Tom Lane wrote:

I'd be inclined to leave it there, simply because you'll be changing
the conditions of the benchmark if you take it out. I have not noticed
any particular problems with it...

I wonder if autovacuum itself is going to add more variability to the
test (like we don't have enough already).

Of course it will, which means that people will likely turn off autovac
when trying to obtain repeatable pgbench numbers, which is another
reason not to take out the built-in vacuum step.

regards, tom lane