ERROR: found xmin from before relfrozenxid

From: Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: ERROR: found xmin from before relfrozenxid
Date: 2019-01-23 15:25:33
Message-ID: CA+t6e1kBb0-h_B_hZpab0uOgiE2Go_VLnayd9RkHaX26ybdUmw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jan Nielsen 2019-01-23 17:28:52 Re: SELECT performance drop
Previous Message Jim Finnerty 2019-01-23 13:50:52 Re: SELECT performance drop