Re: improving wraparound behavior

Hi,

On 2019-05-03 16:26:46 -0400, Robert Haas wrote:
> 2. Once you get to the point where you start to emit errors when
> attempting to assign an XID, you can still run plain old VACUUM
> because it doesn't consume an XID ... except that if it tries to
> truncate the relation, then it will take AccessExclusiveLock, which
> has to be logged, which forces an XID assignment, which makes VACUUM
> fail. So if you burn through XIDs until the system gets to this
> point, and then you roll back the prepared transaction that caused the
> problem in the first place, autovacuum sits there trying to vacuum
> tables in a tight loop and fails over and over again as soon as hits a
> table that it thinks needs to be truncated. This seems really lame,
> and also easily fixed.
>
> Attached is a patch that disables vacuum truncation if xidWarnLimit
> has been reached. With this patch, in my testing, autovacuum is able
> to recover the system once the prepared transaction has been rolled
> back. Without this patch, not only does that not happen, but if you
> had a database with enough relations that need truncation, you could
> conceivably cause XID wraparound just from running a database-wide
> VACUUM, the one tool you have available to avoid XID wraparound. I
> think that this amounts to a back-patchable bug fix.
>
> (One could argue that truncation should be disabled sooner than this,
> like when we've exceed autovacuum_freeze_max_age, or later than this,
> like when we hit xidStopLimit, but I think xidWarnLimit is probably
> the best compromise.)

I'd actually say the proper fix would be to instead move the truncation
to *after* finishing updating relfrozenxid etc. If we truncate, the
additional cost of another in-place pg_class update, to update relpages,
is basically insignificant. And the risk of errors, or being cancelled,
during truncation is much higher than before (due to the AEL).

> Also, I think that old prepared transactions and stale replication
> slots should be emphasized more prominently. Maybe something like:
>
> HINT: Commit or roll back old prepared transactions, drop stale
> replication slots, or kill long-running sessions.
> Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.

I think it'd be good to instead compute what the actual problem is. It'd
not be particularly hard to show some these in the errdetail:

1) the xid horizon (xid + age) of the problematic database; potentially,
if connected to that database, additionally compute what the oldest
xid is (although that's computationally potentially too expensive)
2) the xid horizon (xid + age) due to prepared transactions, and the
oldest transaction's name
3) the xid horizon (xid + age) due to replication slot, and the "oldest"
slot's name
4) the xid horizon (xid + age) and pid for the connection with the
oldest snapshot.

I think that'd allow users much much easier to pinpoint what's going on.

In fact, I think we probably should additionally add a function that can
display the above. That'd make it much easier to write monitoring
queries.

IMO we also ought to compute the *actual* relfrozenxid/relminmxid for a
table. I.e. the oldest xid actually present. It's pretty common for most
tables to have effective horizons that are much newer than what
GetOldestXmin()/vacuum_set_xid_limits() can return. Obviously we can do
so only when scanning all non-frozen pages. But being able to record
"more aggressive" horizons would often save unnecessary work. And it
ought to not be hard. I think especially for regular non-freeze,
non-wraparound vacuums that'll often result in a much newer relfrozenxid
(as we'll otherwise just have GetOldestXmin() - vacuum_freeze_min_age).

Greetings,

Andres Freund