It seems like pgsql-general would be the right list for this so I am
going to post it there rather, sorry for the noise...
It appears (and I am open to correction) that autovacuum is not
operating correctly in 8.3.0. I have a vanilla installation where
autovacuum is enabled, and is running with all the default settings.
I have a table which is continually having rows added to it (~50/sec).
For the sake of this example I am limiting it to 20000 rows, which
means that I am continually having to remove rows (100 at a time) as I
get to 20000.
When I get to 20000 rows for the first time the table disk size (using
pg_total_relation_size) is around 5MB. Since the autovacuum only kicks
in after a while I would expect it to get a little bigger (maybe
6-7MB) and then level out as I am cycling through recovered rows.
However the table disk size continues increasing basically linearly
and when I stopped it it was approaching 40MB and heading up. During
that time I was running ANALYZE VERBOSE periodically and I could see
the dead rows increase and then drop down as the autovacuum kicked in
- the autovacuum worker process was running. It didn't seem to free
any space though. In fact a VACUUM FULL at this point didn't help a
whole lot either.
I ran the same test but using manual VACUUMs every 60 seconds and the
table size leveled out at 6.6MB so it appears like a normal vacuum is
working. I changed the normal VACUUM to have the same delay parameters
(20ms) as the autovacuum and it still worked.
So it appears to me like the autovacuum is not freeing up dead rows
correctly.
I turned on logging for autovacuum and ran the same test and saw the
following messages:
LOG: automatic vacuum of table "metadb.test.transactions": index
scans: 1
pages: 0 removed, 254 remain
tuples: 4082 removed, 19957 remain
system usage: CPU 0.02s/0.02u sec elapsed 1.11 sec
LOG: automatic vacuum of table "metadb.test.transactions": index
scans: 1
pages: 0 removed, 271 remain
tuples: 5045 removed, 19954 remain
system usage: CPU 0.03s/0.03u sec elapsed 1.54 sec
ERROR: canceling autovacuum task
CONTEXT: automatic vacuum of table "metadb.test.transactions"
At this point I had deleted 32800 rows as can be seen from the query
below, although the logs only indicated that around 10000 rows had
been freed up.
select min(transaction_key),max(transaction_key) from test.transactions;
min | max
-------+-------
32801 | 52750
Is there anything I have missed as far as setting this up is
concerned, anything I could try? I would really rather use autovacuum
than manage the vacuums of a whole lot of tables by hand...
Thanks
Stuart
PS. Running on NetBSD 3
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
