[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: WARNING: oldest xmin is far in the past
From: Vijaykumar Jain <vijaykumarjain.github () gmail ! com>
Date: 2021-05-28 15:56:58
Message-ID: CAM+6J960c4AQ9di3MfoRiM40ZnbhNfFsG+MHoX0Gu9vXLSsp4g () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@apollotyres.com>
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@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 #5 (text/html)]
<div dir="auto">If the replication slot is still inactive,<div dir="auto">It will \
prevent vacuum to do the cleanup no matter how much vacuum is run manually.</div><div \
dir="auto"><br></div><div dir="auto">did the slot show as active after the restart of \
the collector ?</div><div dir="auto"><br></div><div dir="auto">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.</div><div \
dir="auto"><br></div><div dir="auto"><a \
href="https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/" \
target="_blank" rel="noreferrer">https://www.cybertec-postgresql.com/en/reasons-why-vacuum-wont-remove-dead-rows/</a><br></div><div \
dir="auto"><a href="https://www.cybertec-postgresql.com/en/tuning-autovacuum-postgresq \
l/">https://www.cybertec-postgresql.com/en/tuning-autovacuum-postgresql/</a><br></div><div \
dir="auto"><br></div></div><br><div class="gmail_quote"><div dir="ltr" \
class="gmail_attr">On Fri, May 28, 2021, 8:57 PM Alban Hertroys <<a \
href="mailto:alban.hertroys@apollotyres.com" rel="noreferrer noreferrer" \
target="_blank">alban.hertroys@apollotyres.com</a>> wrote:<br></div><blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex">
<div>
<font face="Helvetica, Arial, sans-serif">Good day,<br>
<br>
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:<br>
<br>
2021-05-28 15:01:54.094 CEST [20164] WARNING: oldest xmin is
far in the past<br>
2021-05-28 15:01:54.094 CEST [20164] HINT: Close open
transactions soon to avoid wraparound problems.<br>
You might also need to commit or roll back old prepared
transactions, or drop stale replication slots.<br>
<br>
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.<br>
<br>
The current situation around our xid's is this:<br>
<br>
avbv=# select slot_name, slot_type, database, active,
catalog_xmin, restart_lsn, confirmed_flush_lsn from
pg_replication_slots ;<br>
slot_name | slot_type | database | active | catalog_xmin |
restart_lsn | confirmed_flush_lsn<br>
---------------+-----------+----------+--------+--------------+---------------+---------------------<br>
debezium_prod | logical | avbv | t | 616648922 |
1166/C45B5140 | 1167/65C7AA0<br>
(1 row)<br>
<br>
avbv=# select datname, datfrozenxid from pg_database ;<br>
datname | datfrozenxid<br>
---------------+--------------<br>
postgres | 610128180<br>
speeltuin | 610128180<br>
template1 | 610128180<br>
template0 | 591773830<br>
reportinfo | 610128180<br>
avbv_20190314 | 610128180<br>
avbv | 610128180<br>
ensfocus-tst | 610128180<br>
ensfocus | 610128180<br>
ensfocuswf8 | 610128180<br>
portal_prd | 610128180<br>
portal_tst | 610128180<br>
(12 rows)<br>
<br>
Clearly, the gap between the higher frozen xid's (</font><font \
face="Helvetica, Arial, sans-serif"><font face="Helvetica, Arial,
sans-serif">610128180</font>) and the replication slots xmin (</font><font \
face="Helvetica, Arial, sans-serif"><font face="Helvetica, Arial, \
sans-serif">616648922 </font>) is rather small; a mere 650k xid's apart.<br>
<br>
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.<br>
<br>
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...<br>
<br>
I did already take several actions in attempts to solve the issue,
so far to little avail:<br>
<br>
* I restarted the database, closing any idle in transaction
sessions that might have gone unnoticed otherwise<br>
* I ran vacuum -a -U postgres, which printed a number of
repetitions of the same error message on the console<br>
* I ran vacuum -a -F -U postgres<br>
* I added a heartbeat interval of 10000ms (10s) to the Debezium
connector, although I didn't think that was necessary<br>
<br>
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.<br>
Or is there some action I should take?<br>
<br>
<br>
<br>
For the record:<br>
<br>
avbv=# select version();<br>
\
version<br>
----------------------------------------------------------------------------------------------------------<br>
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<br>
(1 row)<br>
<br>
<br>
Regards,<br>
<br>
Alban Hertroys<br>
<br>
P.S. Sorry about below company disclaimer, there is nothing I can
do about that.<br>
<br>
<br>
<br>
</font>
<p><span style="font-size:10pt"><span \
style="font-family:calibri,verdana,geneva,sans-serif"><strong>Alban Hertroys \
</strong><br>
D: 8776 |M: |T: +31 (0)53 4888 888 | E: <a \
href="mailto:alban.hertroys@apollotyres.com" rel="noreferrer noreferrer noreferrer" \
target="_blank">alban.hertroys@apollotyres.com</a><br> Apollo Vredestein B.V.| Ir. \
E.L.C. Schiffstraat 370, 7547 RD Enschede, The Netherlands<br> Chamber of Commerce \
number: 34223268</span></span></p>
<p> </p>
<table style="border-collapse:collapse" width="100%">
<tbody>
<tr>
<td bgcolor="white" style="border-style:none none none \
none;border-color:#000000;border-width:0px 0px 0px 0px;padding:0px 0px" \
width="13%"><img src="cid:4XcDPEBPU2ZBfewYhipAwApolloTyres-GoTheDistance_jpg" \
style="float:left"></td> <td bgcolor="white" style="border-style:none none none \
none;border-color:#000000;border-width:0px 0px 0px 0px;padding:0px 0px" width="71%"> \
</td> <td bgcolor="white" style="border-style:none none none \
none;border-color:#000000;border-width:0px 0px 0px 0px;padding:0px 0px" \
width="14%"><img src="cid:cceDd5XazEeiOC5N3M0uBQVredestein_jpg" \
style="float:right"></td> </tr>
</tbody>
</table>
<p><span style="font-size:10pt"><span \
style="font-family:calibri,verdana,geneva,sans-serif"><span \
style="color:#808080"><em>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.</em></span></span></span></p> <table border="0" cellpadding="1" \
cellspacing="1" style="height:8px;width:500px"> <tbody>
<tr>
<td><img src="cid:e5jDYMe1bEqaWJsLE7Aenvironment_gif" \
style="margin-top:10px;margin-bottom:0px"></td> <td><span \
style="font-size:10pt"><span \
style="font-family:calibri,verdana,geneva,sans-serif"><span \
style="color:#008000">Please consider the environment before printing this \
e-mail</span></span></span></td> </tr>
</tbody>
</table>
<p><img src="cid:qZRKxX9DrEGLjLFZwXkKqwVR-experience_jpg"></p>
</div>
<table><tr><td bgcolor="#ffffff"><font color="#000000"><pre>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</pre></font></td></tr></table> </blockquote></div>
--000000000000d139f205c365c2c8--
["noname" (image/jpeg)]
["noname" (image/jpeg)]
["noname" (image/gif)]
["noname" (image/jpeg)]
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic