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
>
>