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.
Attachment:
pglog.tgz
Description: Binary data
Attachment:
datalog.tgz
Description: Binary data
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
