Hey,
I'm trying to help a guy that is using pg9.6 but I'm not so familiar with
the error message :
ERROR: found xmin 16804535 from before relfrozenxid 90126924
CONTEXT: automatic vacuum of table db1.public.table_1"
It seems that the error has started appearing two weeks ago. Data that I
collected :
-all the autovacuum params are set to default
-SELECT relname, age(relfrozenxid) as xid_age,
pg_size_pretty(pg_table_size(oid)) as table_size
FROM pg_class
WHERE relkind = 'r' and pg_table_size(oid) > 1073741824
ORDER BY age(relfrozenxid) DESC LIMIT 4;
relname | xid_age | table_size
-------------------------------+-----------+------------
table_1 | 180850538 | 10 GB
table_2 | 163557812 | 10 GB
table_3 | 143732477 | 1270 MB
table_4 | 70464685 | 3376 MB
pg_controldata :
Latest checkpoint's NextXID: 0:270977386
Latest checkpoint's NextOID: 25567991
Latest checkpoint's NextMultiXactId: 1079168
Latest checkpoint's NextMultiOffset: 68355
Latest checkpoint's oldestXID: 77980003
Latest checkpoint's oldestXID's DB: 16403
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1047846
Latest checkpoint's oldestMulti's DB: 16403
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
It seems that the autovacuum cant vacuum table_1 and it has alot of
dead_tuples. Moreover, it seems that the indexes are bloated.
schemaname relname n_tup_upd n_tup_del n_tup_hot_upd n_live_tup n_dead_tup
n_mod_since_analyze last_vacuum last_autovacuum last_analyze
public table_1 0 5422370 0 382222 109582923 10760701
I tried to vacuum the table (full,freeze) but it didnt help.
I read about the wrap that can happen but to be honest I'm not sure that I
understood id.
What can I do to vacuum the table ? Can some one explain the logic behind
the error message ?
Thanks.
