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

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