Re: VACUUM/ANALYZE counting of in-doubt tuples

Hi,

Please read the below is some skepticism. I am not an expert with
regard to statistics and vacuum internals. Hopefully it just keeps the
thinking caps moving.

Tom Lane wrote:
> There was some discussion in pgsql-performance about the problem that
> the live-and-dead-tuple counts that ANALYZE reports to the stats
> collector don't reflect insert-in-progress tuples properly:
> http://archives.postgresql.org/pgsql-performance/2007-11/msg00225.php
> I proposed a patch here:
> http://archives.postgresql.org/pgsql-patches/2007-11/msg00169.php
> to switch ANALYZE over to using HeapTupleSatisfiesVacuum() to determine
> tuple status, which would at least give it awareness of tuples that are
> in-doubt because of concurrent transactions.
>
> On further thought though, that's not the whole story, and in fact
> VACUUM itself isn't doing very well at accounting for in-doubt tuples.
> The current implementation is that whatever live and dead tuple totals
> are arrived at by a VACUUM or ANALYZE are sent to the stats collector
> and simply overwrite its counters on arrival. Meanwhile, the
> transaction that is responsible for an in-doubt tuple will send a
> stats message to increment either the live-tuple or dead-tuple count
> as appropriate when it commits or aborts. If that happens before
> the VACUUM or ANALYZE completes, the increment will get overwritten
> by VACUUM/ANALYZE's total; if afterwards, the increment will get
> added onto the total. So ideally we'd count the state change as already
> done if we knew the other transaction would commit first, otherwise
> not.
>
So given autovacuum starts on the basis of the stats collector. The
problem we are having is that one vacuum will be followed by a second
basically useless vacuum for long running insert transactions that commit.
> Since we don't know whether the other transaction will commit or abort,
> much less when, it seems hopeless to have any really accurate accounting
> for in-doubt tuples. We could avoid the whole problem if we gave up on
> having VACUUM/ANALYZE reset the stats counters (instead, VACUUM would
> just send an incremental dead-tuple-count reduction, and ANALYZE would
> do nothing); as indeed was already proposed by Itagaki-san. But I am
> still convinced that that cure would be worse than the disease --- if
> the counters are driven entirely by incremental updates then there is
> no way to correct errors that will inevitably creep in, and the
> accumulation of errors will allow the counts to diverge arbitrarily
> far from reality. I'm happier with the idea that the error in the
> counts can be bounded by the number of in-doubt tuples seen by the
> last VACUUM/ANALYZE.
>
> So having said all that, we need to devise heuristic rules that minimize
> the error. In that light, I suggest that the accounting rules assume
> that the transactions responsible for in-doubt tuples will finish after
> the VACUUM/ANALYZE finishes. That probably sounds wrong, because in
> most of the sorts of applications we think about, updating transactions
> are short and are likely to finish before the VACUUM/ANALYZE does. But
> what I'm thinking is that a short transaction by definition can't change
> very many rows. The cases that can seriously hurt the accuracy of our
> counts are long-running bulk inserts or updates, and for those it seems
> entirely likely that ANALYZE in particular could finish first.
>
This may be the case, but I'm going to throw in the usual where is your
evidence question. Saying it doesn't actually make it so. This makes
the importance of the heuristic rules and determining what they are very
important.

A potential though for measuring them, and possibly accounting for tuple
changes would be;
1. At the beginning of vacuum of a table, cache the live/dead tuple
statistics
2. At the end of vacuum of the table, compare current stats with cached
version from (1).
3. Wipe out stats row and put the vacuum values with the changes between
(1) and (2) included in the final stats row.

All transactions that committed during the vacuum run will be included
in the changes (1) -> (2). All transactions that rolled back will be in
the same boat. The problem in my logic is; If vacuum starts, we check
page X, a transaction alters page X and commits, transaction then update
stats, vacuum finished and alters the stats again. We have double
counting :( The goal is to come up with the best way to minimize size
of the count error that could be experienced.

I suppose it all gets back to the original point. What evidence do we
have for how big the problem is and what cases are we fixing by a
particular heuristic rule. I'm sure if it's a major problem we can
invent all sorts of complicated algorithms to track the stats correctly
and update them at the end of the vacuum. Is that worth the performance
hit and the trouble? If we adopt the wrong solution can we make the
situation worse for other types of workloads?
> Given that assumption, then the rule should be to not count
> INSERT_IN_PROGRESS tuples at all --- they will be added to the
> appropriate count when the inserting transaction commits or aborts.
> And DELETE_IN_PROGRESS tuples should be counted as live --- if the
> deleting transaction commits, it will increment the dead-tuple count
> at that time, and if it aborts then the tuple remains live.
>
> I feel fairly comfortable with this analysis for ANALYZE, and the
> patch I posted yesterday can easily be adjusted to accommodate it.
> However, what of VACUUM? As that code stands, every non-removable
> tuple (including RECENTLY_DEAD ones) is counted as live, and the
> dead-tuples count gets reset to zero. That seems clearly bogus.
> But the other-transaction-commits-second hypothesis seems a good bit
> more dubious for VACUUM than it is for ANALYZE.
>
> Should we attempt to adjust VACUUM's accounting as well, or leave it
> for 8.4? For that matter, should adjusting ANALYZE be left for 8.4?
> Thoughts?
>
Give all my unqualified statements above, I'd be tempted to make sure we
can measure the problem with 8.3 and get a proper solution into 8.4.
Analyze seems safe to change now. It doesn't actually make change to
the live/dead tuple counts (I don't think) so is a lower risk. I feel
uneasy about the vacuum stuff as we don't know the exact side effects a
change like that could have. It could have a larger impact that
suspected. And at beta3/rc1 I feel it's too late in the cycle.

Regards

Russell Smith