Is it a bad thing that txid_exhaustion_oldest_txid_num is constantly increasing?

Question

We recently setup netdata to monitor a self-hosted PostgreSQL server.

Is it a bad thing that txid_exhaustion_oldest_txid_num is constantly increasing?

What we did

PostgreSQL is running in v13
We've run a full-vacuum (vacuumdb --full --all --jobs=1 --verbose --analyze --username=XXXX --password) on all logical databases and only got these errors:

ERROR: relation "pg_temp_31.temp_lig_plo" ERROR: relation "pg_temp_31.lst_lig_plo"
We've dropped the schema "pg_temp_31" but still had a similar issue.
max_prepared_transactions is 0
SELECT * FROM pg_prepared_xacts is empty on all databases
We've restarted the DB (so all idle transactions were disconnected) and still observe this increase

Can you please describe how "txid_exhaustion_oldest_txid_num" is measured? — Laurenz Albe
– Laurenz Albe, Commented Jan 18, 2024 at 14:56
Offtopic: Version 11 is EOL since November 2023. You'd better upgrade to a newer version — Frank Heikens
– Frank Heikens, Commented Jan 18, 2024 at 15:23
@FrankHeikens sorry my bad, it's PG 13 and we cannot upgrade (if only...) because a proprietary software depends on it and does not support past PG 13 for now — John Smith
– John Smith, Commented Jan 18, 2024 at 16:07
@LaurenzAlbe netdata is based on prometheus postgresql exporter. We've found the SQL query for this metric here: github.com/prometheus-community/postgres_exporter/blob/master/… — John Smith
– John Smith, Commented Jan 18, 2024 at 16:17

Laurenz Albe · Accepted Answer · 2024-01-19 09:04:01Z

0

According to your comment, this graphic seems to be about transaction ID wraparound.

Check if any of your databases have a problem with that:

SELECT datname, age(datfrozenxid) AS xact_age
FROM pg_database
ORDER BY xact_age DESC;

If that number is not much above 200 million for all databases, you don't need to worry. If the number is significantly higher, connect to that database and see which tables have a problem:

SELECT oid::regclass, age(relfrozenxid) AS xact_age
FROM pg_class
ORDER BY xact_age DESC;

If there are any numbers far above 200 million (or whatever else autovacuum_freeze_max_age is set to), either autovacuum doesn't start on that table, or it is taking too long, or something is blocking its progress.

Try processing those tables manually with

VACUUM (VERBOSE) table_name;

The output should tell you what is going on and, if possible, fix the problem.

edited Jan 19, 2024 at 9:04

answered Jan 18, 2024 at 16:31

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Smith Over a year ago

Finally a nice explanation about "how it should be", amazing, let me check that and come back at you!

Collectives™ on Stack Overflow

Is it a bad thing that txid_exhaustion_oldest_txid_num is constantly increasing?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related