I am experiencing a strange problem where autovacuum appears to be
vacuuming 1 table in preference to another even through they have very
similar usage patterns.
For this test case I have 2 tables, 'transactions' and 'lineitems', and
the ratio of writes is approx 1:3. I am filling these tables as fast as
possible and once I reach approx 1'000'000 transactions (3mil lineitems)
they are both periodically trimmed to keep their sizes constant.
The transactions table gets autovacuumed periodically, roughly when I
would expect, but the autovacuum only seems to run on the lineitems
table well after the trigger point (I am using the autovacuum defaults
in postgresql.conf). According to pg_stat_user_tables there were 1.7m
dead rows and 3.1m live rows when it decided to run the vacuum.
I have been logging data every minute from pg_stat_user_tables
(n_tup_ins,n_live_tup,n_dead_tup,pg_total_relation_size()) for each of
the tables and it makes interesting reading. For one thing, the number
of dead tuples drops every now and again without the vacuum being run
(is it possible that a vacuum is starting and then being terminated
before completing?) and also the size of the lineitems table continues
increasing where the transactions table levels off as expected.
I was wondering if there is any way I can get more logging information
about the autovacuum decision making to find out exactly what is
happening? I also read that the stats are not always accurate under
high-load and was wondering if this could be affecting the vacuum.
Thanks
Stuart
PS. Running 8.3.1 on NetBSD 3.
PS2. I have attached the postgresql log and the data log (tab-separated).
PS3. I am not (to my knowledge) doing anything other than inserting rows
into the database and periodically (every minute) pulling stats from
pg_stat_user_tables. I am not running vacuum or analyze manually.
