Обсуждение: Turn off vacuum in pgbench?

Hi,

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.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

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 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