Attached is another autovacuum (and VACUUM VERBOSE) instrumentation
patch. This one adds instrumentation about freezing to the report
autovacuum makes to the server log. Specifically, it makes the output
look like this:
regression=# vacuum (freeze,verbose) foo;
INFO: aggressively vacuuming "regression.public.foo"
INFO: finished vacuuming "regression.public.foo": index scans: 0
pages: 0 removed, 45 remain, 45 scanned (100.00% of total)
tuples: 0 removed, 10000 remain, 0 are dead but not yet removable
removable cutoff: 751, which was 0 XIDs old when operation ended
new relfrozenxid: 751, which is 2 XIDs ahead of previous value
XIDs processed: 45 pages from table (100.00% of total) had 10000 tuples frozen
index scan not needed: 0 pages from table (0.00% of total) had 0 dead
item identifiers removed
I/O timings: read: 0.023 ms, write: 0.000 ms
avg read rate: 2.829 MB/s, avg write rate: 5.658 MB/s
buffer usage: 95 hits, 2 misses, 4 dirtied
WAL usage: 91 records, 1 full page images, 133380 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
VACUUM
Notice the new line about freezing, which we always output -- it's the
line that begins with "XIDs processed", that appears about half way
down. The new line is deliberately placed after the existing "new
relfrozenxid" line and before the existing line about dead item
identifiers. This placement of the new instrumentation seems logical
to me; freezing is related to relfrozenxid (obviously), but doesn't
need to be shoehorned into the prominent early line that reports on
tuples removed/remain[ing].
Like its neighboring "dead item identifier" line, this new line shows
the number of items/tuples affected, and the number of heap pages
affected -- with heap pages shown both as an absolute number and as a
percentage of rel_pages (in parentheses). The main cost associated
with freezing is the WAL overhead, so emphasizing pages here seems
like the way to go -- pages are more interesting than tuples. This
format also makes it relatively easy to get a sense of the *relative*
costs of the overhead of each distinct class/category of maintenance
performed.
--
Peter Geoghegan
