From: Jeremy Schneider <schnjere@amazon.com>
To: pgsql-bugs@postgresql.org <pgsql-bugs@postgresql.org>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Cc: Bossart, Nathan <bossartn@amazon.com>
Cc: Nasby, Jim <nasbyj@amazon.com>
Subject: Re: ERROR: multixact X from before cutoff Y found to be still running
Date: Wed, 4 Sep 2019 18:01:05 -0700
Message-ID: <be665139-6bdc-9535-eaa6-c3c84d3900c7@amazon.com> (raw)
In-Reply-To: <DAFB8AFF-2F05-4E33-AD7F-FF8B0F760C17@amazon.com>
References: <DAFB8AFF-2F05-4E33-AD7F-FF8B0F760C17@amazon.com>
On 9/4/19 17:37, Nathan Bossart wrote:
> Hi,
>
> Currently, if you hold a multixact open long enough to generate an
> "oldest multixact is far in the past" message during VACUUM, you may
> see the following ERROR:
>
> WARNING: oldest multixact is far in the past
> HINT: Close open transactions with multixacts soon to avoid wraparound problems.
> ERROR: multixact X from before cutoff Y found to be still running
>
> Upon further inspection, I found that this is because the multixact
> limit used in this case is the threshold for which we emit the "oldest
> multixact" message. Instead, I think the multixact limit should be
> set to the result of GetOldestMultiXactId(), effectively forcing a
> minimum freeze age of zero. The ERROR itself is emitted by
> FreezeMultiXactId() and appears to be a safeguard against problems
> like this.
>
> I've attached a patch to set the limit to the oldest multixact instead
> of the "safeMxactLimit" in this case. I'd like to credit Jeremy
> Schneider as the original reporter.
This was fun (sortof) - and a good part of the afternoon for Nathan,
Nasby and myself today. A rather large PostgreSQL database with default
autovacuum settings had a large table that started getting behind on
Sunday. The server has a fairly large number of CPUs and a respectable
workload. We realized today that with their XID generation they would
go read-only to prevent wraparound tomorrow. (And perfectly healthy XID
age on Sunday - that's wraparound in four days! Did I mention that I'm
excited for the default limit GUC change in pg12?) To make matters more
interesting, whenever we attempted to run a VACUUM command we
encountered the ERROR message that Nate quoted on every single attempt!
There was a momentary mild panic based on the "ERRCODE_DATA_CORRUPTED"
message parameter in heapam.c FreezeMultiXactId() ... but as we looked
closer we're now thinking there might just be an obscure bug in the code
that sets vacuum limits.
Nathan and Nasby and myself have been chatting about this for quite
awhile but the vacuum code isn't exactly the simplest thing in the world
to reason about. :) Anyway, it looks to me like
MultiXactMemberFreezeThreshold() is intended to progressively reduce the
vacuum multixact limits across multiple vacuum runs on the same table,
as pressure on the members space increases. I'm thinking there was just
a small oversight in writing the formula where under the most aggressive
circumstances, vacuum could actually be instructed to delete multixacts
that are still in use by active transactions and trigger the failure we
observed.
Nate put together an initial patch (attached to the previous email,
which was sent only to the bugs list). We couldn't quite come to a
consensus and on the best approach, but we decided that he'd kick of the
thread and I'd throw out an alternative version of the patch that might
be worth discussion. [Attached to this email.] Curious what others think!
-Jeremy
--
Jeremy Schneider
Database Engineer
Amazon Web Services
Attachments:fix_multixact_limit_v2.patch (text/plain, 1.2K) downloaddiff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.cindex e154507ecd..f099127510 100644--- a/src/backend/commands/vacuum.c+++ b/src/backend/commands/vacuum.c@@ -893,6 +893,7 @@ vacuum_set_xid_limits(Relation rel,
int effective_multixact_freeze_max_age;
TransactionId limit;
TransactionId safeLimit;
+ MultiXactId oldestMxact;
MultiXactId mxactLimit;
MultiXactId safeMxactLimit;
@@ -970,7 +971,8 @@ vacuum_set_xid_limits(Relation rel,
Assert(mxid_freezemin >= 0);
/* compute the cutoff multi, being careful to generate a valid value */
- mxactLimit = GetOldestMultiXactId() - mxid_freezemin;+ oldestMxact = GetOldestMultiXactId();+ mxactLimit = oldestMxact - mxid_freezemin;
if (mxactLimit < FirstMultiXactId)
mxactLimit = FirstMultiXactId;
@@ -984,7 +986,14 @@ vacuum_set_xid_limits(Relation rel,
ereport(WARNING,
(errmsg("oldest multixact is far in the past"),
errhint("Close open transactions with multixacts soon to avoid wraparound problems.")));
- mxactLimit = safeMxactLimit;+ if (MultiXactIdPrecedes(oldestMxact, safeMxactLimit))+ {+ mxactLimit = oldestMxact;+ }+ else+ {+ mxactLimit = safeMxactLimit;+ }
}
*multiXactCutoff = mxactLimit;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: schnjere@amazon.com, pgsql-hackers@postgresql.org, bossartn@amazon.com, nasbyj@amazon.com
Subject: Re: ERROR: multixact X from before cutoff Y found to be still running
In-Reply-To: <be665139-6bdc-9535-eaa6-c3c84d3900c7@amazon.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox