vacuuming not working? - Mailing list pgsql-general

Me again,

I have a problem with VACUUM. I searched the list and don't seem to see
anyone else reporting this behavior so I'm wondering if i'm doing something
wrong

Summary: Vacuuming does not seem to mark the space as "available for reuse"
as the documentation says.

Platform: Postgres 7.2.1 on RedHat 7.0

I have a table that I use to keep aggregate statistics for a website ...
This table is cleared and repopulated (DELETE FROM followed by a INSERT INTO
.. SELECT) periodically ... The table has ~500 records in it ...

The problem is that the file on disk is over 500 megs! and it appears to be
growing pretty much without bound ... I do a VACUUM every night (not a
VACUUM FULL, just a straight VACUUM) and it still grows .... Needless to
say, this is killing the performance of my application since the query to
delete the 500 rows is now taking over 2 minutes ...

I setup a sample script (with a bogus table with one field) to demonstrate
the problem ... It goes through and inserts 500 rows and then deletes the
500 rows ... every 10th iteration, it vacuums the table ... to run this
script, you'll need to have a table in your DB with over 500 rows
(BIG_TABLE) in it and you'll need to modify the RELFILENAME to get the
on-disk file size ...

Also, even issuing a VACUUM FULL on the table doesn't seem to reduce the
size of the file on disk (this worked in 7.1.3 for sure)

CREATE TABLE test_high_turnover(test_string VARCHAR(1000) NOT NULL);

Listing of test_high_turnover.sh
#!/bin/sh

PG_HOME=/usr/local/pgsql/bin
BIG_TABLE=<name of a big table in your database>
DB_NAME=<name of your database>
RELFILENAME=/data/pgdata/base/16566/3179174

I=0

while(( $I < $1 ))
do
    let I+=+1
    echo "Iteration #$I -- Relation file size: " `ls -s --format=s $RELFILENAME
| awk {'print $1'}`"KB"
    $PG_HOME/psql -c "INSERT INTO test_high_turnover SELECT current_timestamp
|| ' ' || VERSION() FROM $BIG_TABLE LIMIT 500;" $DB_NAME
    $PG_HOME/psql -c "DELETE FROM test_high_turnover; " $DB_NAME

    if ((($I%10) == 0)) ; then
        echo "Vacuuming ... "
        $PG_HOME/psql -c "VACUUM VERBOSE ANALYZE test_high_turnover;" $DB_NAME
    fi


        sleep 1
done





Here's a sample run from my database



[postgres@TIMDA1 postgres]$ ./test_high_turnover.sh 500
Iteration #1 -- Relation file size:  4988KB
INSERT 0 500
DELETE 500
Iteration #2 -- Relation file size:  5052KB
INSERT 0 500
DELETE 500
Iteration #3 -- Relation file size:  5116KB
INSERT 0 500
DELETE 500
Iteration #4 -- Relation file size:  5180KB
INSERT 0 500
DELETE 500
Iteration #5 -- Relation file size:  5244KB
INSERT 0 500
DELETE 500
Iteration #6 -- Relation file size:  5316KB
INSERT 0 500
DELETE 500
Iteration #7 -- Relation file size:  5380KB
INSERT 0 500
DELETE 500
Iteration #8 -- Relation file size:  5444KB
INSERT 0 500
DELETE 500
Iteration #9 -- Relation file size:  5508KB
INSERT 0 500

[postgres@TIMDA1 postgres]$ psql patronmail
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

patronmail=# drop table test_high_turnover ;
DROP
patronmail=# CREATE TABLE test_high_turnover(test_string VARCHAR(1000) NOT
NULL);
CREATE
patronmail=# \q
[postgres@TIMDA1 postgres]$ pico test_high_turnover.sh
[postgres@TIMDA1 postgres]$ ./test_high_turnover.sh 500
Iteration #1 -- Relation file size:  0KB
INSERT 0 500
DELETE 500
Iteration #2 -- Relation file size:  76KB
INSERT 0 500
DELETE 500
Iteration #3 -- Relation file size:  140KB
INSERT 0 500
DELETE 500
Iteration #4 -- Relation file size:  204KB
INSERT 0 500
DELETE 500
Iteration #5 -- Relation file size:  268KB
INSERT 0 500
DELETE 500
Iteration #6 -- Relation file size:  332KB
INSERT 0 500
DELETE 500
Iteration #7 -- Relation file size:  404KB
INSERT 0 500
DELETE 500
Iteration #8 -- Relation file size:  468KB
INSERT 0 500
DELETE 500
Iteration #9 -- Relation file size:  532KB
INSERT 0 500
DELETE 500
Iteration #10 -- Relation file size:  596KB
INSERT 0 500
DELETE 500
Vacuuming ...
NOTICE:  --Relation test_high_turnover--
NOTICE:  Pages 82: Changed 9, Empty 0; Tup 5000: Vac 0, Keep 5000, UnUsed 0.
        Total CPU 0.00s/0.01u sec elapsed 0.00 sec.
NOTICE:  Analyzing test_high_turnover
VACUUM
Iteration #11 -- Relation file size:  660KB
INSERT 0 500
DELETE 500
Iteration #12 -- Relation file size:  732KB
INSERT 0 500
DELETE 500
Iteration #13 -- Relation file size:  796KB
INSERT 0 500
DELETE 500
Iteration #14 -- Relation file size:  860KB
INSERT 0 500
DELETE 500
Iteration #15 -- Relation file size:  924KB
INSERT 0 500
DELETE 500
Iteration #16 -- Relation file size:  988KB
INSERT 0 500
DELETE 500
Iteration #17 -- Relation file size:  1052KB
INSERT 0 500
DELETE 500
Iteration #18 -- Relation file size:  1124KB
INSERT 0 500
DELETE 500
Iteration #19 -- Relation file size:  1188KB
INSERT 0 500
DELETE 500
Iteration #20 -- Relation file size:  1252KB
INSERT 0 500
DELETE 500
Vacuuming ...
NOTICE:  --Relation test_high_turnover--
NOTICE:  Pages 164: Changed 9, Empty 0; Tup 10000: Vac 0, Keep 10000, UnUsed
0.
        Total CPU 0.00s/0.01u sec elapsed 0.00 sec.
NOTICE:  Analyzing test_high_turnover
VACUUM
Iteration #21 -- Relation file size:  1316KB
INSERT 0 500
DELETE 500
Iteration #22 -- Relation file size:  1380KB
INSERT 0 500
DELETE 500
Iteration #23 -- Relation file size:  1452KB
INSERT 0 500
DELETE 500

<SNIP ... you get the idea ... >

Iteration #74 -- Relation file size:  4788KB
INSERT 0 500
DELETE 500
Iteration #75 -- Relation file size:  4860KB
INSERT 0 500
DELETE 500
Iteration #76 -- Relation file size:  4924KB
INSERT 0 500
DELETE 500
Iteration #77 -- Relation file size:  4988KB
INSERT 0 500
DELETE 500
Iteration #78 -- Relation file size:  5052KB
INSERT 0 500
DELETE 500
Iteration #79 -- Relation file size:  5116KB
INSERT 0 500
DELETE 500
Iteration #80 -- Relation file size:  5188KB
INSERT 0 500
DELETE 500
Vacuuming ...
NOTICE:  --Relation test_high_turnover--
NOTICE:  Pages 655: Changed 9, Empty 0; Tup 40000: Vac 0, Keep 40000, UnUsed
0.
        Total CPU 0.03s/0.03u sec elapsed 0.06 sec.
NOTICE:  Analyzing test_high_turnover
VACUUM
Iteration #81 -- Relation file size:  5252KB
INSERT 0 500
DELETE 500
Iteration #82 -- Relation file size:  5316KB
INSERT 0 500
DELETE 500
Iteration #83 -- Relation file size:  5380KB
INSERT 0 500
DELETE 500
Iteration #84 -- Relation file size:  5444KB
INSERT 0 500
DELETE 500
Iteration #85 -- Relation file size:  5516KB
INSERT 0 500
DELETE 500
Iteration #86 -- Relation file size:  5580KB
INSERT 0 500
DELETE 500


What else should I be trying here?

-Dave