Re: Minimally avoiding Transaction Wraparound in VLDBs

On Wed, 2005-08-31 at 19:24 -0400, Tom Lane wrote:
> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> > A new command is proposed - VACUUM MINIMAL. The *sole* purpose of this
> > command is to do the absolute minimum required to avoid transaction id
> > wraparound. (Better names welcome....)
>
> I do not see the point. If you only need to run it every billion
> transactions, saving a few cycles seems a bit pointless.

It is not simply a few cycles, it could be days of grinding I/O. The
worse it is, the more it gets put off, which makes it worse etc..

...it kindof prevents embedded systems from working happily.

> > This does the same thing as VACUUM except it:
>
> > 1. does not VACUUM any table younger than 4 billion XIDs old
>
> So? Your large tables are likely to be long-lived, so this isn't
> actually going to save a thing in a DB that's been around long enough
> to have an impending wrap problem.

My point was, with table partitioning that the largest tables would
*not* be the longest lived. Any very large table has to be truncated
down to a manageable size at some point. Partitioning completely avoids
the need to DELETE or TRUNCATE, since you use CREATE TABLE to introduce
new partitions and DROP TABLE to remove them.

> > 2. does not VACUUM indexes
> > RATIONALE: Indexes don't contain XIDs so don't *need* to be vacuumed.
>
> Nonstarter. If you remove any tuples, you *must* vacuum indexes to
> remove the corresponding index entries.

Yes, but if no tuples have been deleted or updated, there will be no
tuples removed.

> If you don't remove any tuples,
> you don't scan the indexes anyway IIRC.

No. Even if you remove *zero* tuples, an index is still scanned twice.
Once to not delete the rows and once to not delete the pages.

That isn't as mad as it sounds, since a VACUUM needs to mark pages as
HALF_DEAD on one VACUUM, then come back for a DELETED marker later
because of MVCC. But that behaviour isn't required at all in the
scenario I described, so I was seeking a way to get around that.

We could optimise that, as I said, but I was trying to avoid the problem
entirely. But, I'm easy, if you like the sound of that better I have the
details for that worked out also.

> > By taking those two steps, VACUUM MINIMAL will execute fairly quickly
> > even on large Data Warehouses.
>
> I don't think this'll save a thing.

I understand that the scenarios I describe seem highly atypical, but
there are a number people already running this kind of system. I've
worked with a couple and I've seen a half-dozen others on list, and
there are more coming.

> > This command only makes sense when you *know* that tables don't need
> > vacuuming. That is the case when:
> > - you're using autovacuum, since all tables have dead-tuples removed
> > whenever this is required - and so indexes will have been covered also
>
> If you're using autovacuum then the problem is already taken care of.

autovacuum will respond only to UPDATEs and DELETEs. In the scenario I
outline, these will *never* occur on the largest tables. A VACUUM would
still eventually be required to freeze long lived tuples and this would
not be performed by autovacuum.

> It will be taken care of better in 8.2, if we add per-table tracking
> of XID wraparound risk, but it's handled now.

Thats a good idea, I must have missed the discussion on that.

But what I am suggesting is for a certain class of table, which just
happens to be very large, we defer a VACUUM for as long as possible. The
hope is, and I believe that this could be very likely, that the table
would cease to exist before the table became eligible for VACUUM.

The current viewpoint is that "most" rows never live for 1 billion rows,
so never get frozen. Thats completely valid in something like a Stock
table, but its completely wrong in a TransactionHistory table.

For a historical data table I want to be able to defer the need for a
full table VACUUM for as long as possible, and when/should it ever
occur, I want to avoid scanning the indexes pointlessly.

> The only way that this
> proposal makes any sense is if you are trying not to vacuum at all, ever.

Yes, that is exactly what I'm trying to do, for the largest tables only.

I never want to VACUUM them because I know they don't need it to reclaim
rows and I have a *good* expectation that the table will be dropped, one
day.

For more "normal" tables, I'm happy to VACUUM them and don't want to
alter that behaviour at all.

> You do realize that 2 billion is already the wrap horizon, and you can't
> wait that long if you're doing this on a routine basis rather than
> immediately-when-needed?

OK, 4 billion was just a mistake...I just meant avoid VACUUM as long as
possible. I changed the number immediately before posting, to emphasise
the deferral aspect of this proposal.

Best Regards, Simon Riggs