Skip site navigation (1) Skip section navigation (2)

Bug #579: Auto-VACUUM doesnt work

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #579: Auto-VACUUM doesnt work
Date: 2002-02-11 07:47:04
Message-ID: 200202110747.g1B7l4k56137@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Paul Mamin (magamos(at)mail(dot)ru) reports a bug with a severity of 4
The lower the number the more severe it is.

Short Description
Auto-VACUUM doesnt work

Long Description
I have PIII 800MHz, 128MB, HDD IDE Quantum Fibeball AS Plus 20 Gb, Slackware Linux 8.0, kernel 2.4.17, PostgreSQL 7.2b3, standard settings with fsync enabled.

I run my example twice:
1) with
#        $conn->exec("VACUUM test");
line commented;
2) with the same line uncommented.

Results from 1):
10	 1 wallclock secs ( 0.04 usr +  0.01 sys =  0.05 CPU)
20	 1 wallclock secs ( 0.09 usr +  0.05 sys =  0.14 CPU)
30	 1 wallclock secs ( 0.11 usr +  0.03 sys =  0.14 CPU)
40	 1 wallclock secs ( 0.09 usr +  0.00 sys =  0.09 CPU)
50	 1 wallclock secs ( 0.13 usr +  0.02 sys =  0.15 CPU)
60	 1 wallclock secs ( 0.06 usr +  0.04 sys =  0.10 CPU)
70	 1 wallclock secs ( 0.12 usr +  0.06 sys =  0.18 CPU)
80	 1 wallclock secs ( 0.05 usr +  0.05 sys =  0.10 CPU)
90	 2 wallclock secs ( 0.08 usr +  0.03 sys =  0.11 CPU)
100	 1 wallclock secs ( 0.06 usr +  0.01 sys =  0.07 CPU)
110	 3 wallclock secs ( 0.07 usr +  0.09 sys =  0.16 CPU)
120	 8 wallclock secs ( 0.16 usr +  0.05 sys =  0.21 CPU)
130	12 wallclock secs ( 0.15 usr +  0.03 sys =  0.18 CPU)
140	12 wallclock secs ( 0.20 usr +  0.05 sys =  0.25 CPU)
150	14 wallclock secs ( 0.22 usr +  0.09 sys =  0.31 CPU)
160	14 wallclock secs ( 0.27 usr +  0.03 sys =  0.30 CPU)
170	15 wallclock secs ( 0.13 usr +  0.03 sys =  0.16 CPU)
180	16 wallclock secs ( 0.19 usr +  0.07 sys =  0.26 CPU)
190	17 wallclock secs ( 0.17 usr +  0.08 sys =  0.25 CPU)
200	17 wallclock secs ( 0.20 usr +  0.02 sys =  0.22 CPU)
210	19 wallclock secs ( 0.17 usr +  0.08 sys =  0.25 CPU)
220	19 wallclock secs ( 0.22 usr +  0.04 sys =  0.26 CPU)
230	20 wallclock secs ( 0.16 usr +  0.03 sys =  0.19 CPU)
240	21 wallclock secs ( 0.18 usr +  0.05 sys =  0.23 CPU)
250	21 wallclock secs ( 0.20 usr +  0.06 sys =  0.26 CPU)
260	23 wallclock secs ( 0.17 usr +  0.07 sys =  0.24 CPU)
270	23 wallclock secs ( 0.19 usr +  0.01 sys =  0.20 CPU)
280	24 wallclock secs ( 0.18 usr +  0.08 sys =  0.26 CPU)
290	25 wallclock secs ( 0.21 usr +  0.05 sys =  0.26 CPU)
300	26 wallclock secs ( 0.21 usr +  0.08 sys =  0.29 CPU)
...

And results from 2):
10	 0 wallclock secs ( 0.06 usr +  0.02 sys =  0.08 CPU)
20	 1 wallclock secs ( 0.08 usr +  0.04 sys =  0.12 CPU)
...
1000	 1 wallclock secs ( 0.10 usr +  0.01 sys =  0.11 CPU)

It seems that auto-VACUUM doesnt work properly... :(

Sample Code
#!/usr/bin/perl

use Pg;
use Benchmark;

$conn = Pg::connectdb("dbname=test");
die $conn->errorMessage unless PGRES_CONNECTION_OK eq $conn->status;

$conn->exec("BEGIN");
$result = $conn->exec("CREATE TABLE test (a int primary key, b int)");
for ($i = 0; $i < 100; $i++) {
    $conn->exec("INSERT INTO test VALUES ($i, $i)");
}
$conn->exec("COMMIT");

$conn->exec("BEGIN");
$t0 = new Benchmark;
for ($i = 0; $i < 300; $i++) {
    for ($j = 0; $j < 100; $j++) {
        $conn->exec("UPDATE test SET b=b+1 WHERE a=$j");
    }
    if ($i % 10 == 9) {
	$conn->exec("COMMIT");
#        $conn->exec("VACUUM test");
	$conn->exec("BEGIN");
	$t1 = new Benchmark;
	print $i+1 ."\t" . timestr(timediff($t1, $t0)) . "\n";
	$t0 = $t1;
    }
}
$conn->exec("COMMIT");


No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-02-11 16:07:42
Subject: Re: Bug #579: Auto-VACUUM doesnt work
Previous:From: Tom LaneDate: 2002-02-10 23:48:28
Subject: Re: Bug #578: pg_dumpall from 7.1.3 can not be imported in 7.2.0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group