Automatic autovacuum to prevent wraparound - PG13.5

From: Mauro Farracha <farracha(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Automatic autovacuum to prevent wraparound - PG13.5
Date: 2022-06-15 11:13:31
Message-ID: CAAhWs6BDVPYJ3_cei-wLyFVToWiUza2kkkZD2pevxuGAn3-K2Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello guys,

Have recently upgraded from PG10 to PG13.5 and would like to understand the
reason why we are seeing triggered autovacuum to prevent the wraparound
while all the metrics are still far off from the multixact/freeze max ages
defined. And inclusive there was one time where it was triggered as
aggressive.

Some background:
- autovacuum_freeze_max_age: 400M
- autovacuum_multixact_freeze_max_age: 800M
- the activity is mostly insert intensive in one particular table (60M
daily)
- the team execute vacuum freeze verbose every day at night to keep the
multixact ids down
- we generally reach near 70M mxids before running vacuum freeze at night
- the postgresql is Aurora

The scenario:
- Out of nowhere (during the weekend), without database activity load or
batches running, with previous nightly run of vacuum freeze, in the middle
of the day, with xids and mxids below 20M we are seeing autovacuum being
triggered to prevent wraparound.

My question is why this is occurring, which condition might be responsible
for this behaviour?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ray O'Donnell 2022-06-15 11:55:37 Re: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390
Previous Message Ian Dauncey 2022-06-15 10:58:48 RE: PostgreSQL 12.2 on Linux ubuntu 20.4 / s390