Just one thing to add, this is not a table that's gone for long periods without a vacuum, it's been autovacuumed regularly and still is without affecting the transaction id. Also double checked for storage parameters on the table that might affect it and nothing there.
On Tue, May 21, 2013 at 10:37 AM, Armand du Plessis <adp@bank.io> wrote:
On Mon, May 20, 2013 at 3:21 PM, Armand du Plessis <adp@bank.io> wrote:
On Mon, May 20, 2013 at 3:11 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Armand du Plessis <adp@bank.io> writes: > The autovacuum completed (after many hours) however it didn't seem to have > frozen any old pages as it just kicks off again right away with the same > reason (VACUUM ANALYZE public.messages (to prevent wraparound))
I suspect it's unable to move the table's min XID forward at all because there is some old open transaction preventing cleanup of very old dead tuples. Look for old xact_start times in pg_stat_activity. Also look for old entries in pg_prepared_xacts. If you find any, get rid of them.
This now had a full vacuum on the table but the table's xid is still increasing.
"messages";1061103361;"62 GB"
It's gone up from 105xxx yesterday. I've confirmed there's no old transactions in pg_stat_activity or pg_prepared_xacts and everything else seems fine. I can see the completion in the log and see it reflected as last_vacuum in pg_stat_user_tables.
2013-05-21 08:01:44.910 UTC,"postgres","datase",6921,"[local]",519a9497.1b09,3,"VACUUM",2013-05-20 21:24:39 UTC,51/0,0,LOG,00000,"duration: 38210856.820 ms statement: vacuum messages;",,,,,,,,,"psql"
I've bumped up the autovacuum_freeze_max_age yesterday so this was a normal vacuum unlike the wrap-around autovacuums from the original post.
Settings :
autovacuum_freeze_max_age : 1500000000
vacuum_freeze_min_age : 25000000
vacuum_freeze_table_age: 150000000
It's Postgres 9.2.3.
I might just be missing something but it's worrying me that it's just increasing even after the vacuum.
