Automatic autovacuum to prevent wraparound - PG13.5
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?
On Wed, 2022-06-15 at 12:13 +0100, Mauro Farracha wrote:
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 AuroraThe 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?
A long-running transaction or a prepared transaction.
Or an abandoned replication slot with an old "xmin".
That would be the answer for PostgreSQL. It might apply to Amazon Aurora, unless they
changed the behavior there. Perhaps ask Amazon.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Frankly speaking, Aurora PostgreSQL's behaviour is quite unpredictable.
In our case, the autovacuum was not even getting triggered in spite of
crossing the autovacuum_freeze_max_age. Finally, the database went down
abruptly, which resolved the issue.
Thanks,
Ninad
On Wed, Jun 15, 2022 at 7:57 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
Show quoted text
On Wed, 2022-06-15 at 12:13 +0100, Mauro Farracha wrote:
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 (60Mdaily)
- 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 AuroraThe scenario:
- Out of nowhere (during the weekend), without database activity load orbatches 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?
A long-running transaction or a prepared transaction.
Or an abandoned replication slot with an old "xmin".That would be the answer for PostgreSQL. It might apply to Amazon Aurora,
unless they
changed the behavior there. Perhaps ask Amazon.Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
On Wed, Jun 15, 2022 at 4:13 AM Mauro Farracha <farracha@gmail.com> wrote:
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?
There is a behavior that seems like it might be relevant: VACUUM
interprets autovacuum_multixact_freeze_max_age in a way that accounts
for both MultiXactId consumption and the consumption of "member space"
by MultiXacts. Technically there are 2 SLRUs for MultiXacts, either of
which can wraparound.
This behavior was established by commit 53bb309d2d. It is documented.
Admittedly this whole area of the documentation is in dire need of an
overhaul. :-(
--
Peter Geoghegan