MultiXactId wraparound and last aggressive vacuum time

From: Michael Schanne <michael(dot)schanne(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: MultiXactId wraparound and last aggressive vacuum time
Date: 2021-03-22 22:24:48
Message-ID: CAC_kJhQ5160qoKQQkFORDuRvEOge=7S5aLX600bF4c7kFtraAg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

My application is getting the following exception:

InternalError: (psycopg2.InternalError) MultiXactId 808263738 has not been
created yet -- apparent wraparound

I read over
https://www.postgresql.org/docs/9.6/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND
and I suspect the issue was caused by not vacuuming often enough. I can
query the last_autovacuum time from the pg_stat_all_tables view. However,
I found this in the documentation:

VACUUM uses the visibility map to determine which pages of a table must be
scanned. Normally, it will skip pages that don't have any dead row versions
even if those pages might still have row versions with old XID values.
Therefore, normal VACUUMs won't always freeze every old row version in the
table. Periodically, VACUUM will perform an aggressive vacuum, skipping
only those pages which contain neither dead rows nor any unfrozen XID or
MXID values. vacuum_freeze_table_age controls when VACUUM does that:
all-visible but not all-frozen pages are scanned if the number of
transactions that have passed since the last such scan is greater than
vacuum_freeze_table_age minus vacuum_freeze_min_age. Setting
vacuum_freeze_table_age to 0 forces VACUUM to use this more aggressive
strategy for all scans.

How can I tell when the last "aggressive" auto-vacuum was performed? The
vacuum_freeze_table_age parameter is at the default value (200000000).

I am using postgresql 9.6.

Thanks,
Mike

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Saha, Sushanta K 2021-03-23 01:04:44 Re: [E] Re: CSV From Oracle with timestamp column getting errors
Previous Message Tim Cross 2021-03-22 20:56:42 Re: CSV From Oracle with timestamp column getting errors