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.