WARNING: oldest xmin is far in the past - Mailing list pgsql-general
Good day,
We have a PG 11.11 instance here that serves as a data-warehouse for us. This morning I was investigating an issue with our ETL's and discovered this error in the logs, that keeps repeating:
2021-05-28 15:01:54.094 CEST [20164] WARNING: oldest xmin is far in the past
2021-05-28 15:01:54.094 CEST [20164] HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
We don't have any idle in transaction sessions, but we do have a replication slot that turns out to have been inactive for an unknown while.
The current situation around our xid's is this:
avbv=# select slot_name, slot_type, database, active, catalog_xmin, restart_lsn, confirmed_flush_lsn from pg_replication_slots ;
slot_name | slot_type | database | active | catalog_xmin | restart_lsn | confirmed_flush_lsn
---------------+-----------+----------+--------+--------------+---------------+---------------------
debezium_prod | logical | avbv | t | 616648922 | 1166/C45B5140 | 1167/65C7AA0
(1 row)
avbv=# select datname, datfrozenxid from pg_database ;
datname | datfrozenxid
---------------+--------------
postgres | 610128180
speeltuin | 610128180
template1 | 610128180
template0 | 591773830
reportinfo | 610128180
avbv_20190314 | 610128180
avbv | 610128180
ensfocus-tst | 610128180
ensfocus | 610128180
ensfocuswf8 | 610128180
portal_prd | 610128180
portal_tst | 610128180
(12 rows)
Clearly, the gap between the higher frozen xid's (610128180) and the replication slots xmin (616648922 ) is rather small; a mere 650k xid's apart.
We have that single logical replication slot that Debezium subscribes to, to push committed records for some tables to Kafka. Those are tables that get frequent inserts, a batch of new records arrives about every 15 minutes, 24/7.
As mentioned, initially when I detected this problem, the Debezium connector (the subscriber) had failed to attach. Restarting it fixed that (that's a known issue that was recently discovered in the current version 1.4.0). I had hopes the xmin issue would be gone once it caught up, but it did catch up earlier today and the issue remains...
I did already take several actions in attempts to solve the issue, so far to little avail:
* I restarted the database, closing any idle in transaction sessions that might have gone unnoticed otherwise
* I ran vacuum -a -U postgres, which printed a number of repetitions of the same error message on the console
* I ran vacuum -a -F -U postgres
* I added a heartbeat interval of 10000ms (10s) to the Debezium connector, although I didn't think that was necessary
Should I just wait for the replication slot xmin to increase into a safe area? It is slowly increasing, while the frozen xid's have remained the same while monitoring this issue.
Or is there some action I should take?
For the record:
avbv=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 11.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
Regards,
Alban Hertroys
P.S. Sorry about below company disclaimer, there is nothing I can do about that.
We have a PG 11.11 instance here that serves as a data-warehouse for us. This morning I was investigating an issue with our ETL's and discovered this error in the logs, that keeps repeating:
2021-05-28 15:01:54.094 CEST [20164] WARNING: oldest xmin is far in the past
2021-05-28 15:01:54.094 CEST [20164] HINT: Close open transactions soon to avoid wraparound problems.
You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
We don't have any idle in transaction sessions, but we do have a replication slot that turns out to have been inactive for an unknown while.
The current situation around our xid's is this:
avbv=# select slot_name, slot_type, database, active, catalog_xmin, restart_lsn, confirmed_flush_lsn from pg_replication_slots ;
slot_name | slot_type | database | active | catalog_xmin | restart_lsn | confirmed_flush_lsn
---------------+-----------+----------+--------+--------------+---------------+---------------------
debezium_prod | logical | avbv | t | 616648922 | 1166/C45B5140 | 1167/65C7AA0
(1 row)
avbv=# select datname, datfrozenxid from pg_database ;
datname | datfrozenxid
---------------+--------------
postgres | 610128180
speeltuin | 610128180
template1 | 610128180
template0 | 591773830
reportinfo | 610128180
avbv_20190314 | 610128180
avbv | 610128180
ensfocus-tst | 610128180
ensfocus | 610128180
ensfocuswf8 | 610128180
portal_prd | 610128180
portal_tst | 610128180
(12 rows)
Clearly, the gap between the higher frozen xid's (610128180) and the replication slots xmin (616648922 ) is rather small; a mere 650k xid's apart.
We have that single logical replication slot that Debezium subscribes to, to push committed records for some tables to Kafka. Those are tables that get frequent inserts, a batch of new records arrives about every 15 minutes, 24/7.
As mentioned, initially when I detected this problem, the Debezium connector (the subscriber) had failed to attach. Restarting it fixed that (that's a known issue that was recently discovered in the current version 1.4.0). I had hopes the xmin issue would be gone once it caught up, but it did catch up earlier today and the issue remains...
I did already take several actions in attempts to solve the issue, so far to little avail:
* I restarted the database, closing any idle in transaction sessions that might have gone unnoticed otherwise
* I ran vacuum -a -U postgres, which printed a number of repetitions of the same error message on the console
* I ran vacuum -a -F -U postgres
* I added a heartbeat interval of 10000ms (10s) to the Debezium connector, although I didn't think that was necessary
Should I just wait for the replication slot xmin to increase into a safe area? It is slowly increasing, while the frozen xid's have remained the same while monitoring this issue.
Or is there some action I should take?
For the record:
avbv=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 11.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
Regards,
Alban Hertroys
P.S. Sorry about below company disclaimer, there is nothing I can do about that.
Alban Hertroys
D: 8776 |M: |T: +31 (0)53 4888 888 | E: alban.hertroys@apollotyres.com
Apollo Vredestein B.V.| Ir. E.L.C. Schiffstraat 370, 7547 RD Enschede, The Netherlands
Chamber of Commerce number: 34223268
 |  |
The information contained in this e-mail is intended solely for the use of the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or action in relation to the contents of this information is strictly prohibited and may be unlawful and request you to delete this message and any attachments and advise the sender by return e-mail. The confidentiality of this message is not warranted. Apollo Vredestein and its subsidiaries rule out any and every liability resulting from this or any other electronic transmission.
 | Please consider the environment before printing this e-mail |
CIN: L25111KL1972PLC002449 Registered Office: Apollo Tyres Ltd, 3rd Floor, Areekal Mansion, Panampilly Nagar, Kochi 682036, India Disclaimer: The information contained in this e-mail is intended solely for the use of the individual or entity to whom it is addressed. If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or action in relation to the contents of this information is strictly prohibited and may be unlawful and request you to delete this message and any attachments and advise the sender by return e-mail. The confidentiality of this message is not warranted. Apollo Tyres and its subsidiaries rule out any and every liability resulting from this or any other electronic transmiss |
Attachment
pgsql-general by date: