On 2015-06-08 15:15:04 +0200, Andres Freund wrote:
> 1) the autovacuum trigger logic isn't perfect yet. I.e. especially with
> autovacuum=off you can get into situations where emergency vacuums
> aren't started when necessary. This is particularly likely to happen
> if either very large multixacts are used, or if the server has been
> shut down while emergency autovacuum where happening. No corruption
> ensues, but it's not easy to get out of.
A first version to address this problem can be found appended to this
email.
Basically it does:
* Whenever more than MULTIXACT_MEMBER_SAFE_THRESHOLD are used, signal
autovacuum once per members segment
* For both members and offsets, once hitting the hard limits, signal
autovacuum everytime. Otherwise we loose the information when
restarting the database, or when autovac is killed. I ran into this a
bunch of times while testing.
Regards,
Andres
>From 9949d8ce4b69b4fd693da08d8e1854fd259a33a9 Mon Sep 17 00:00:00 2001
From: Andres Freund <andres@xxxxxxxxxxx>
Date: Mon, 8 Jun 2015 13:41:42 +0200
Subject: [PATCH] Improve multixact emergency autovacuum logic.
Previously autovacuum was not necessarily triggered if space in the
members slru got tight. The first problem was that the signalling was
tied to values in the offsets slru, but members can advance much
faster. Thats especially a problem if old sessions had been around that
previously prevented the multixact horizon to increase. Secondly the
skipping logic doesn't work if the database was restarted after
autovacuum was triggered - that knowledge is not preserved across
restart. This is especially a problem because it's a common
panic-reaction to restart the database if it gets slow to
anti-wraparound vacuums.
Fix the first problem by separating the logic for members from
offsets. Trigger autovacuum whenever a multixact crosses a segment
boundary, as the current member offset increases in irregular values, so
we can't use a simple modulo logic as for offsets. Add a stopgap for
the second problem, by signalling autovacuum whenver ERRORing out
because of boundaries.
Backpatch into 9.3, where it became more likely that multixacts wrap
around.
---
src/backend/access/transam/multixact.c | 61 +++++++++++++++++++++++++---------
1 file changed, 45 insertions(+), 16 deletions(-)
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index d3336a8..3bc170d 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -980,10 +980,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
* Note these are pretty much the same protections in GetNewTransactionId.
*----------
*/
- if (!MultiXactIdPrecedes(result, MultiXactState->multiVacLimit) ||
- !MultiXactState->oldestOffsetKnown ||
- (MultiXactState->nextOffset - MultiXactState->oldestOffset
- > MULTIXACT_MEMBER_SAFE_THRESHOLD))
+ if (!MultiXactIdPrecedes(result, MultiXactState->multiVacLimit))
{
/*
* For safety's sake, we release MultiXactGenLock while sending
@@ -999,19 +996,17 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
LWLockRelease(MultiXactGenLock);
- /*
- * To avoid swamping the postmaster with signals, we issue the autovac
- * request only once per 64K multis generated. This still gives
- * plenty of chances before we get into real trouble.
- */
- if (IsUnderPostmaster && (result % 65536) == 0)
- SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
-
if (IsUnderPostmaster &&
!MultiXactIdPrecedes(result, multiStopLimit))
{
char *oldest_datname = get_database_name(oldest_datoid);
+ /*
+ * Immediately kick autovacuum into action as we're already
+ * in ERROR territory.
+ */
+ SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
+
/* complain even if that DB has disappeared */
if (oldest_datname)
ereport(ERROR,
@@ -1032,6 +1027,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
{
char *oldest_datname = get_database_name(oldest_datoid);
+ /*
+ * To avoid swamping the postmaster with signals, we issue the autovac
+ * request only once per 64K multis generated. This still gives
+ * plenty of chances before we get into real trouble.
+ */
+ if (IsUnderPostmaster && (result % 65536) == 0)
+ SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
+
/* complain even if that DB has disappeared */
if (oldest_datname)
ereport(WARNING,
@@ -1099,6 +1102,10 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
if (MultiXactState->offsetStopLimitKnown &&
MultiXactOffsetWouldWrap(MultiXactState->offsetStopLimit, nextOffset,
nmembers))
+ {
+ /* see comment in the corresponding offsets wraparound case */
+ SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
+
ereport(ERROR,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("multixact \"members\" limit exceeded"),
@@ -1109,10 +1116,32 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
MultiXactState->offsetStopLimit - nextOffset - 1),
errhint("Execute a database-wide VACUUM in database with OID %u with reduced vacuum_multixact_freeze_min_age and vacuum_multixact_freeze_table_age settings.",
MultiXactState->oldestMultiXactDB)));
- else if (MultiXactState->offsetStopLimitKnown &&
- MultiXactOffsetWouldWrap(MultiXactState->offsetStopLimit,
- nextOffset,
- nmembers + MULTIXACT_MEMBERS_PER_PAGE * SLRU_PAGES_PER_SEGMENT * OFFSET_WARN_SEGMENTS))
+ }
+
+ /*
+ * Check whether we should kick autovacuum into action, to prevent members
+ * wraparound. XXX: We use a much larger window to trigger autovacuum than just
+ * the warning limit. It's debatable whether that's a good idea.
+ */
+ if (!MultiXactState->oldestOffsetKnown ||
+ (MultiXactState->nextOffset - MultiXactState->oldestOffset
+ > MULTIXACT_MEMBER_SAFE_THRESHOLD))
+ {
+ /*
+ * To avoid swamping the postmaster with signals, we issue the autovac
+ * request only when crossing a segment boundary. With default
+ * compilation settings that's rougly after 50k members. This still
+ * gives plenty of chances before we get into real trouble.
+ */
+ if ((MXOffsetToMemberPage(nextOffset) / SLRU_PAGES_PER_SEGMENT) !=
+ (MXOffsetToMemberPage(nextOffset + nmembers) / SLRU_PAGES_PER_SEGMENT))
+ SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
+ }
+
+ if (MultiXactState->offsetStopLimitKnown &&
+ MultiXactOffsetWouldWrap(MultiXactState->offsetStopLimit,
+ nextOffset,
+ nmembers + MULTIXACT_MEMBERS_PER_PAGE * SLRU_PAGES_PER_SEGMENT * OFFSET_WARN_SEGMENTS))
ereport(WARNING,
(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
errmsg("database with OID %u must be vacuumed before %d more multixact members are used",
--
2.4.0.rc2.1.g3d6bc9a
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
