Downloads

Blog

Overcoming VACUUM WRAPAROUND

April 13, 2022

Author

Share this Post:

Transaction ID Wraparound occurs when the VACUUM process cannot keep up with database activity and the PostgreSQL service is forced to shut down.

In more technical parlance: Transaction ID Wraparound occurs when the semantics of Multi-Version Concurrency Control (MVCC) fail and when the number of unique transaction ids reaches its maximum which numbers about two billion.

What leads up to this situation is when the VACUUM process managed by either the autovacuum workers or user-interaction (manual) does not keep up with the DML operations.

Transaction ID Wraparound can be caused by a combination of one or more of the following circumstances:

Autovacuum is turned off

Long-lived transactions

Database logical dumps (on a REPLICA using streaming replication)

Many session connections with locks extending across large swaths of the data cluster

Intense DML operations forcing the cancellation of autovacuum worker processes

Transaction WRAPAROUND can cause a spontaneous shutdown of the Postgres database server in order to protect the integrity of the data.

PostgreSQL at any one time has a number of transactions that are tracked by a unique ID. Every so often that number reaches the upper limit that can be registered, for example, 200 million transactions which is the default and is then renumbered. But if the number of unique transaction IDs goes to its maximum transactions limit, known as TXID Wraparound, Postgres will force a shutdown in order to protect the data.

Here’s how it works:

4 billion transactions, 2^32, is the integer upper limit for the datatype used in Postgres.

2 billion transactions, 2^31, is the upper limit that PostgreSQL permits before forcing a shutdown.

10 million transactions before the upper limit is reached, WARNING messages consisting of a countdown will be logged.

1 million transactions before the upper limit is reached, PostgreSQL goes to READ-ONLY mode.

Warning Signs

In the case of the autovacuum daemon falling behind across the entire data cluster, review your monitoring solution in order to identify the trend of these metrics:

IO wait increases

CPU load increases

SQL performance decreases

Mitigation steps include:

Reviewing the internal Postgres monitoring metrics and confirming tables are being vacuumed.

Reviewing the Postgres logs, look for an overabundance of canceled autovacuum worker processes.

Reviewing the view “pg_stat_activity” and looking for a query string – PREVENTING TRANSACTION ID WRAPAROUND -. Actually, this is a normal message. But one should not see autovacuum running solely for the purposes of mitigating WRAPAROUND.

Here are example error messages that you can find in the Postgres logs when threatened by a shutdown due to WRAPAROUND:

#<br># When less than 10 million transactions remain before shutdown<br>#<br>WARNING: database "mydb" must be vacuumed within 177009986 transactions<br>HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

1	#<br># When less than 10 million transactions remain before shutdown<br>#<br>WARNING: database "mydb" must be vacuumed within 177009986 transactions<br>HINT: To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

Here’s a set of queries that will help you determine if WRAPAROUND is a risk:

    --<br>    -- Database query for transaction age per database<br>    --  and as a percentage of maximum permitted transactions<br>    --<br>SELECT  datname,<br>       age(datfrozenxid),<br>       (age(datfrozenxid)::numeric/1000000000*100)::numeric(4,2) as "% WRAPAROUND RISK"<br>FROM pg_database ORDER BY 2 DESC;

1	--<br> -- Database query for transaction age per database<br> -- and as a percentage of maximum permitted transactions<br> --<br>SELECT datname,<br> age(datfrozenxid),<br> (age(datfrozenxid)::numeric/1000000000*100)::numeric(4,2) as "% WRAPAROUND RISK"<br>FROM pg_database ORDER BY 2 DESC;

--<br>-- Database query for transaction age per table<br>--<br>SELECT  <br>c.oid::regclass as table_name,<br>greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as "TXID age",<br>(greatest(age(c.relfrozenxid),age(t.relfrozenxid))::numeric/1000000000*100)::numeric(4,2) as "% WRAPAROUND RISK"<br>FROM pg_class c<br>LEFT JOIN pg_class t ON c.reltoastrelid = t.oid<br>WHERE c.relkind IN ('r', 'm')<br>ORDER BY 2 DESC;

-- -- Database query for transaction age per table -- SELECT c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as "TXID age", (greatest(age(c.relfrozenxid),age(t.relfrozenxid))::numeric/1000000000*100)::numeric(4,2) as "% WRAPAROUND RISK" FROM pg_class c LEFT JOIN pg_class t ON c.reltoastrelid = t.oid WHERE c.relkind IN ('r', 'm') ORDER BY 2 DESC;

--<br>-- Other vacuum runtime parameters of interest<br>--  returning TXID age<br>--<br>SELECT  name, setting<br>FROM pg_settings<br>WHERE name ~ 'vacuum'<br>AND name ~'_age$'<br>ORDER BY 1 ASC;

1	--<br>-- Other vacuum runtime parameters of interest<br>-- returning TXID age<br>--<br>SELECT name, setting<br>FROM pg_settings<br>WHERE name ~ 'vacuum'<br>AND name ~'_age$'<br>ORDER BY 1 ASC;

Preventing Transaction ID Wraparound

First and foremost, make certain all tables are regularly vacuumed. A correctly configured autovacuum process takes care of this without it ever becoming an issue. Otherwise, you will need to consider a manual VACUUM strategy.

The following are merely suggestions since each situation is highly subjective.

If you have the time, run the following invocation of vacuumdb. The value for option ‘-j’ can vary from a couple to a value equal to the number of CPUs on the host. The option ‘-a’ will process each database in alphabetical order.

vacuumdb -F -a -z -j 10 -v

1	vacuumdb -F -a -z -j 10 -v

Consider a bash script targeting individual databases if you see one is more urgent than another:

vacuumdb -z -j 10 -v <mydatabase>

1	vacuumdb -z -j 10 -v <mydatabase>

Immediate Action: Approaching Wraparound at < 10 Million Transactions

The following is the set of actions to take when TRANSACTION WRAPAROUND is imminent. Remember, you are in a race against time.

You must vacuum the entire data cluster before the remaining available transaction id drops to 1 million transactions.

Action

The task is to vacuum the databases as quickly as possible.

The tool of choice is the CLI “vacuumdb”.

Use as many threads as reasonable.

Run VACUUM in verbose mode and log the output.

Monitor log output for anomalous messages i.e. vacuum fails, etc.

Run “vacuumdb” against individual databases and, if necessary, individual tables

Avoid using the option ‘-a’

Scripts

Here’s a pair of example scripts that you can use as a starting point when developing your own mitigation protocol.

Method

Identify the database with the oldest TXID

Generate a list of tables in order of the oldest TXID age to the youngest

Feed this list of tables into a script that invokes vacuumdb and VACUUM one table per invocation

The secret sauce is xargs which enables one to utilize as many CPUs as reasonably possible. The following pair of bash scripts invoke vacuumdb against a series of tables. Of course, there’s more than one way to do this.

Script one generates a list of tables in a selected database and calls script two which executes the VACUUM on each of those tables individually.

SCRIPT ONE (go1_highspeed_vacuum.sh)

#!/bin/bash<br>#<br># INVOCATION<br># EX: ./go1_highspeed_vacuum.sh<br>#<br><br>########################################################<br># EDIT AS REQUIRED<br>export CPU=4<br>export PAGER=less PGUSER=postgres PGPASSWORD=mypassword PGDATABASE=db01 PGOPTIONS='-c statement_timeout=0'<br>########################################################<br><br>SQL1="<br>with a as (select   c.oid::regclass as table_name,<br>                    greatest(age(c.relfrozenxid),age(t.relfrozenxid))<br>           from pg_class c<br>           left join pg_class t on c.reltoastrelid = t.oid<br>           where c.relkind in ('r', 'm')<br>            order by 2 desc)<br>select table_name from a<br>"<br><br><br>LIST="$(echo "$SQL1" | psql -t)"<br><br># the 'P' sets the number of CPU to use simultaneously<br>xargs -t -n 1 -P $CPU ./go2_highspeed_vacuum.sh $PGDATABASE<<<$LIST<br><br>echo "$(date): DONE"

#!/bin/bash # # INVOCATION # EX: ./go1_highspeed_vacuum.sh # ######################################################## # EDIT AS REQUIRED export CPU=4 export PAGER=less PGUSER=postgres PGPASSWORD=mypassword PGDATABASE=db01 PGOPTIONS='-c statement_timeout=0' ######################################################## SQL1=" with a as (select c.oid::regclass as table_name, greatest(age(c.relfrozenxid),age(t.relfrozenxid)) from pg_class c left join pg_class t on c.reltoastrelid = t.oid where c.relkind in ('r', 'm') order by 2 desc) select table_name from a " LIST="$(echo "$SQL1" | psql -t)" # the 'P' sets the number of CPU to use simultaneously xargs -t -n 1 -P $CPU ./go2_highspeed_vacuum.sh $PGDATABASE<<<$LIST echo "$(date): DONE"

SCRIPT TWO (go2_highspeed_vacuum.sh)

#!/bin/bash<br><br>########################################################<br># EDIT AS REQUIRED<br>export PAGER=less PGUSER=postgres PGPASSWORD=mypassword PGOPTIONS='-c statement_timeout=0'<br>export DB=$1<br><br>########################################################<br><br>vacuumdb --verbose ${DB} > ${DB}.log 2>&1

#!/bin/bash ######################################################## # EDIT AS REQUIRED export PAGER=less PGUSER=postgres PGPASSWORD=mypassword PGOPTIONS='-c statement_timeout=0' export DB=$1 ######################################################## vacuumdb --verbose ${DB} > ${DB}.log 2>&1

Tips

Be prepared to execute vacuumdb against the databases in REVERSE alphabetical order to avoid clashing with the autovacuum worker processes which vacuums in FORWARD alphabetical order.

Query table “pg_stat_activity”.

Always monitor where the autovacuum processes are working.

Avoid working on the same table that the autovacuum workers are currently processing.

Use the autovacuum workers as an indicator of what databases remain to be processed.

Kill active autovacuum workers when in conflict with a manual vacuum in order to speed things up.

Immediate Action: When PostgreSQL Service Has Shutdown Due to Transaction Wraparound

One recovers from a forced shutdown due to transaction id wraparound by performing a cluster-wide vacuum in single-user mode:

    # it is understood that environment <br>    # variable PGDATA points to the data cluster<br>    #<br>    postgres --single -D $PGDATA postgres <<< 'vacuum analyze'

1	# it is understood that environment <br> # variable PGDATA points to the data cluster<br> #<br> postgres --single -D $PGDATA postgres <<< 'vacuum analyze'

I would suggest scripting the vacuum process because you’ll need to log in to each database to perform the VACUUM.

Generate and edit a list of all the databases:

    postgres --single -D $PGDATA postgres <<< 'select datname from pg_database' <br>    | grep '"' | cut -d '"' -f 2 > list_db

1	postgres --single -D $PGDATA postgres <<< 'select datname from pg_database' <br> \| grep '"' \| cut -d '"' -f 2 > list_db

Here is an example using the aforementioned list “list_db”:

#<br># it is understood the database list has <br><br>#  been edited before invoking this code snippet<br>#<br>for u in $(cat list_db)<br>do<br>    postgres --single -D $PGDATA $u <<< 'vacuum analyze'<br>done

1	#<br># it is understood the database list has <br><br># been edited before invoking this code snippet<br>#<br>for u in $(cat list_db)<br>do<br> postgres --single -D $PGDATA $u <<< 'vacuum analyze'<br>done

TXID Wraparound is one of the scariest scenarios that can occur. Thankfully, this is an extremely rare incident and only occurs when systems are either under extremely heavy load or have been neglected.

Don’t get caught!

Remember: the best DBA is the one that’s never noticed. 🙂