Thread: Bug #579: Auto-VACUUM doesnt work

Bug #579: Auto-VACUUM doesnt work

From
pgsql-bugs@postgresql.org
Date:
Paul Mamin (magamos@mail.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

Re: Bug #579: Auto-VACUUM doesnt work

From
Tom Lane
Date:
pgsql-bugs@postgresql.org writes:
> Auto-VACUUM doesnt work

Auto-VACUUM doesn't exist (yet).  Where did you get the impression it does?

            regards, tom lane

Re: Bug #579: Auto-VACUUM doesnt work

From
Peter Eisentraut
Date:
> Paul Mamin (magamos@mail.ru) reports a bug with a severity of 4
> The lower the number the more severe it is.
>
> Short Description
> Auto-VACUUM doesnt work

You didn't say what you expected VACUUM to do for you.  There isn't
anything obviously wrong with the results you showed, so you're going to
have to supply more detail.

--
Peter Eisentraut   peter_e@gmx.net