Re: WARNING: oldest xmin is far in the past

[Vijaykumar Jain <vijaykumarjain.github@xxxxxxxxx>]

If the replication slot is still inactive,

It will prevent vacuum to do the cleanup no matter how much vacuum is run manually.

did the slot show as active after the restart of the collector ?

If it is active then may be increase maintenance_work_mem to a aggresive value and lower nap time for auto vacuum to ensure it gets priority to get the cleanup done quickly.

https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/

https://www.cybertec-postgresql.com/en/tuning-autovacuum-postgresql/

On Fri, May 28, 2021, 8:57 PM Alban Hertroys <alban.hertroys@xxxxxxxxxxxxxxx> wrote:

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.

Alban Hertroys    
D: 8776 |M:  |T: +31 (0)53 4888 888 | E: alban.hertroys@xxxxxxxxxxxxxxx
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: noname
Description: JPEG image

Attachment: noname
Description: JPEG image

Attachment: noname
Description: GIF image

Attachment: noname
Description: JPEG image