On Mon, May 20, 2013 at 3:21 PM, Armand du Plessis <a...@bank.io> wrote:
> On Mon, May 20, 2013 at 3:11 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > >> Armand du Plessis <a...@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. Kind regards, Armand
