Thread: pgsql: Teach autovacuum about multixact member wraparound.
Teach autovacuum about multixact member wraparound. The logic introduced in commit b69bf30b9bfacafc733a9ba77c9587cf54d06c0c and repaired in commits 669c7d20e6374850593cb430d332e11a3992bbcf and 7be47c56af3d3013955c91c2877c08f2a0e3e6a2 helps to ensure that we don't overwrite old multixact member information while it is still needed, but a user who creates many large multixacts can still exhaust the member space (and thus start getting errors) while autovacuum stands idly by. To fix this, progressively ramp down the effective value (but not the actual contents) of autovacuum_multixact_freeze_max_age as member space utilization increases. This makes autovacuum more aggressive and also reduces the threshold for a manual VACUUM to perform a full-table scan. This patch leaves unsolved the problem of ensuring that emergency autovacuums are triggered even when autovacuum=off. We'll need to fix that via a separate patch. Thomas Munro and Robert Haas Branch ------ master Details ------- http://git.postgresql.org/pg/commitdiff/53bb309d2d5a9432d2602c93ed18e58bd2924e15 Modified Files -------------- doc/src/sgml/maintenance.sgml | 8 ++- src/backend/access/transam/multixact.c | 88 ++++++++++++++++++++++++++++++++ src/backend/commands/vacuum.c | 16 ++++-- src/backend/postmaster/autovacuum.c | 28 +++++++--- src/include/access/multixact.h | 1 + 5 files changed, 130 insertions(+), 11 deletions(-)
Robert Haas <rhaas@postgresql.org> wrote: > This patch leaves unsolved the problem of ensuring that emergency > autovacuums are triggered even when autovacuum=off. We'll need > to fix that via a separate patch. I think we also need something like your previously-posted multixact-truncate-race.patch as a follow-on. I'm not at all convinced that there is no possibility of truncating new data as things stand; and even if that is true now, it seems terribly fragile. (Only mentioning it because it wasn't noted in the commit message.) -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On Fri, May 8, 2015 at 1:23 PM, Kevin Grittner <kgrittn@ymail.com> wrote: > Robert Haas <rhaas@postgresql.org> wrote: > >> This patch leaves unsolved the problem of ensuring that emergency >> autovacuums are triggered even when autovacuum=off. We'll need >> to fix that via a separate patch. > > I think we also need something like your previously-posted > > multixact-truncate-race.patch as a follow-on. I'm not at all > convinced that there is no possibility of truncating new data as > things stand; and even if that is true now, it seems terribly > fragile. (Only mentioning it because it wasn't noted in the commit > message.) I agree. I'm writing up a summary of open issues now. I didn't mention it in the commit message because it's not related to the autovacuum part of the problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company