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
v1-0001-Add-freezing-instrumentation-to-VACUUM-VERBOSE.patch
Description: Binary data