pgsql: Revise pgstat's tracking of tuple changes to improve the

From: tgl(at)postgresql(dot)org (Tom Lane)
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Revise pgstat's tracking of tuple changes to improve the
Date: 2009-12-30 20:32:14
Message-ID: 20091230203215.01037753FB7@cvs.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers

Log Message:
-----------
Revise pgstat's tracking of tuple changes to improve the reliability of
decisions about when to auto-analyze.

The previous code depended on n_live_tuples + n_dead_tuples - last_anl_tuples,
where all three of these numbers could be bad estimates from ANALYZE itself.
Even worse, in the presence of a steady flow of HOT updates and matching
HOT-tuple reclamations, auto-analyze might never trigger at all, even if all
three numbers are exactly right, because n_dead_tuples could hold steady.

To fix, replace last_anl_tuples with an accurately tracked count of the total
number of committed tuple inserts + updates + deletes since the last ANALYZE
on the table. This can still be compared to the same threshold as before, but
it's much more trustworthy than the old computation. Tracking this requires
one more intra-transaction counter per modified table within backends, but no
additional memory space in the stats collector. There probably isn't any
measurable speed difference; if anything it might be a bit faster than before,
since I was able to eliminate some per-tuple arithmetic operations in favor of
adding sums once per (sub)transaction.

Also, simplify the logic around pgstat vacuum and analyze reporting messages
by not trying to fold VACUUM ANALYZE into a single pgstat message.

The original thought behind this patch was to allow scheduling of analyzes
on parent tables by artificially inflating their changes_since_analyze count.
I've left that for a separate patch since this change seems to stand on its
own merit.

Modified Files:
--------------
pgsql/src/backend/commands:
analyze.c (r1.144 -> r1.145)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/analyze.c?r1=1.144&r2=1.145)
vacuum.c (r1.400 -> r1.401)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/vacuum.c?r1=1.400&r2=1.401)
vacuumlazy.c (r1.125 -> r1.126)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/commands/vacuumlazy.c?r1=1.125&r2=1.126)
pgsql/src/backend/postmaster:
autovacuum.c (r1.105 -> r1.106)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/autovacuum.c?r1=1.105&r2=1.106)
pgstat.c (r1.194 -> r1.195)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/postmaster/pgstat.c?r1=1.194&r2=1.195)
pgsql/src/include:
pgstat.h (r1.84 -> r1.85)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/pgstat.h?r1=1.84&r2=1.85)

Browse pgsql-committers by date

  From Date Subject
Next Message Tom Lane 2009-12-30 21:21:33 pgsql: Dept of second thoughts: recursive case in ANALYZE shouldn't emit
Previous Message User Bmomjian 2009-12-30 19:40:45 pg-migrator - pg_migrator: Reduce server starts/stops, adjust status