Saturday, October 13, 2012

Freezing Your Tuples Off, Part 2

Continued from Part 1.


The vacuum_freeze_min_age setting determines the youngest XID which will be changed to FrozenXID on data pages which are being vacuumed anyway.  The advantage of setting it low is that far more XIDs will already be frozen when the data page is finally evicted from memory. This is ideal from a maintenance perspective, as the data page may never need to be read from disk for freezing.  The disadvantage of setting it too low is additional time and CPU used during vacuum, especially if the page ends up being vacuumed several times before it's written out.

The other disadvantage of setting it low, according to the pgsql-hackers list, is that you will have less data for reconstructive database forensics if your database gets corrupted.  However, given PostgreSQL's very low incidence of corruption bugs, this is not a serious consideration when balanced against the very high cost of a vacuum freeze on a really large table.

Therefore, we want the setting to be low, but not so low that XIDs used a few minutes ago are expiring.  This is where things get difficult and not auto-tunable in current Postgres.  You really want XIDs to freeze after a few hours, or maybe a day if your application makes use of long-running transactions frequently.  But Postgres has no single-step way to determine how many XIDs you use per hour on average.

The best way is to monitor this yourself.  In recent versions of PostgreSQL, you can get this from pg_stat_database, which has the counters xact_commit and xact_rollback. If these are part of a monitoring scheme you already have in place (such as Nagios/Cacti, Ganglia or Munin), then you can look at the transaction rates using those tools.  If not, you need to follow these three steps:

1. Run this query, and write down the result:

SELECT txid_current();

2. Wait three or four hours (or a day, if you use long-running transactions)

3. Run the query again.

4. Subtract the number from the first query run from the second query run.  If the 2nd number is lower, then you've wrapped around zero, and should try again.

5. Round up to the nearest multiple of 10.

So, as an example:

josh=# select txid_current();

... wait four hours ...

postgres=# select txid_current();

So my transaction burn rate is 1,061,936 for four hours, which I round to 1,000,000.  I then set vacuum_freeze_min_age to 1000000.  The approximate burn rate, 250,000 per hour, is also a handy figure to keep to figure out when XID wraparound will happen next (in about 1 year, if I was starting from XID 3).

Without doing the above, it's fairly hard to estimate a reasonable level, given that XID burn rate depends not only on the amount of write activity you're doing, but how you're grouping the writes into transactions.  For example, in a data collection database, if you're doing each imported fact as a separate standalone INSERT, you could be burning a million XIDs per hour, but if you're batching them in batches of a thousand rows, that cuts you down to 1000 XIDs per hour.  That being said, if you really don't have time to check, here's my rules-of-thumb settings for vacuum_freeze_min_age:

  • Low write activity (100 per minute or less): 50000
  • Moderate write activity (100-500 per minute): 200000
  • High write activity (500 to 4000 per minute): 1000000
  • Very high write activity (higher than 4000 per minute): 10000000

You'll notice that all of these are lower than the default which ships in postgresql.conf.  That default, 100 million, is overly conservative, and means that preemtive freezing almost never happens on databases which run with the defaults.  Also, the default of 100m is half of 200m, the default for autovacuum_freeze_max_age, meaning that even after you've completely vacuumed an entire table, you're left with many XIDs which are 50% of freeze_max_age old.  This causes more wraparound vacuums than are necessary.

Also, to some degree, this isn't worth worrying about below 500 writes/minute, given that it takes 8 years to reach XID wraparound at that rate.  Few PostgreSQL installations go 8 years without a dump/reload.

To be continued in Part 3 ...

NOTE: The above blog entry was updated in April 2015 to reflect and improved method suggested by Noah below.


  1. Very informative. Thank you.

    xact_commit & xact_rollback column values include autovacuum activity also ?
    Because, I observed the query shown here will keep increasing if any activity is not happening on the database.


    1. Raghavendra,

      Hmmmm. I didn't think Autovac used up XIDs. However, I don't know that I ever checked; I'll admit to not being very concerened with XID usage on an idle database. What's your settings for archive_mode and archive_timeout?

  2. xact_commit and xact_rollback are really strange in postgres 9.1 and I'm sure they DO include autovaccum activity. I have submitted a question to DBA exchange which I'm hoping will be answered. By looking into Postgres source code it does look like autovaccum is being taken into account when incrementing these two. I came to conclusion that because of this, it's almost impossible to get accurate stats just for the DB itself.

  3. The above method of measuring the passage of transaction IDs is not correct. It does accurately measure the number of transactions taking place on your system, yes, but that includes pure reads which do not seem to consume a TX ID.

    At any rate, the correct method is to run "select txid_current(), now()" and note the results, then run it again in the future, and divide the differences.

    For example, on my production system, the sum(xact_commit+xact_rollback) method says 2500-2700 per second, whereas the txid_current() method shows ~200 per second.

    1. Oh, thanks! I didn't know that xact_commit included vxids; I assumed that it would exclude them.

  4. I compare XID burn rate VS increase in XID age, both having much different values:

    SELECT SUM(xact_commit + xact_rollback) FROM pg_stat_database;
    Result in :
    816577300 and 862757994 for 1 day (average 32,000 per minute)

    Using XID age query in part 1 :
    SELECT relname, age(relfrozenxid) as xid_age, pg_size_pretty(pg_table_size(oid)) as table_size
    FROM pg_class
    WHERE relkind = 'r' and pg_table_size(oid) > 10737418
    ORDER BY xid_age DESC LIMIT 20;
    Result in 187100025 and 193195670 for the same table in 2 days (average 2,000 per minute)

    Based on your article, i thought they would result almost same value. How could that be?

    1. See Noah's comment above, and the update to the article.