pg_xlogdump --stats

Hi.

Here's a patch to make pg_xlogdump print summary statistics instead of
individual records.

By default, for each rmgr it shows the number of records, the size of
rmgr-specific records, the size of full-page images, and the combined
size. With --stats=record it shows these figures for each rmgr/xl_info
combination (omitting those with zero counts for readability).

Here's an example of the output after a few pgbench runs with the
default checkpoint settings. I raised wal_keep_segments, resulting
in 3.5GB of WAL data in pg_xlog.

As you can see in the "Total" line, 96.83% of this is full-page images.
As Andres observed, this is a good demonstration of why one should not
use the default checkpoint_segments in production.

$ ../bin/pg_xlogdump --stats=record 000000010000000000000001 0000000100000000000000DE
Type N (%) Record size (%) FPI size (%) Combined size (%)
---- - --- ----------- --- -------- --- ------------- ---
XLOG/CHECKPOINT_SHUTDOWN 16 ( 0.00) 1152 ( 0.00) 0 ( 0.00) 1152 ( 0.00)
XLOG/CHECKPOINT_ONLINE 80 ( 0.00) 5760 ( 0.01) 0 ( 0.00) 5760 ( 0.00)
XLOG/NEXTOID 12 ( 0.00) 48 ( 0.00) 0 ( 0.00) 48 ( 0.00)
Transaction/COMMIT 71 ( 0.00) 4708 ( 0.00) 0 ( 0.00) 4708 ( 0.00)
Transaction/COMMIT_COMPACT 413956 ( 14.82) 4967472 ( 4.35) 0 ( 0.00) 4967472 ( 0.14)
Storage/CREATE 231 ( 0.01) 3696 ( 0.00) 0 ( 0.00) 3696 ( 0.00)
Storage/TRUNCATE 1 ( 0.00) 16 ( 0.00) 0 ( 0.00) 16 ( 0.00)
CLOG/ZEROPAGE 13 ( 0.00) 52 ( 0.00) 0 ( 0.00) 52 ( 0.00)
Database/CREATE 3 ( 0.00) 48 ( 0.00) 0 ( 0.00) 48 ( 0.00)
RelMap/UPDATE 14 ( 0.00) 7336 ( 0.01) 0 ( 0.00) 7336 ( 0.00)
Heap2/CLEAN 369312 ( 13.22) 10769122 ( 9.43) 2698910088 ( 77.33) 2709679210 ( 75.17)
Heap2/FREEZE_PAGE 53 ( 0.00) 3276 ( 0.00) 327732 ( 0.01) 331008 ( 0.01)
Heap2/VISIBLE 58160 ( 2.08) 1163200 ( 1.02) 599768 ( 0.02) 1762968 ( 0.05)
Heap2/MULTI_INSERT 1 ( 0.00) 59 ( 0.00) 0 ( 0.00) 59 ( 0.00)
Heap2/MULTI_INSERT+INIT 7 ( 0.00) 38874 ( 0.03) 0 ( 0.00) 38874 ( 0.00)
Heap/INSERT 425472 ( 15.23) 22392664 ( 19.61) 6081712 ( 0.17) 28474376 ( 0.79)
Heap/DELETE 1638 ( 0.06) 42588 ( 0.04) 19800 ( 0.00) 62388 ( 0.00)
Heap/UPDATE 53912 ( 1.93) 7145531 ( 6.26) 390264760 ( 11.18) 397410291 ( 11.03)
Heap/HOT_UPDATE 1185607 ( 42.43) 59538947 ( 52.13) 48724168 ( 1.40) 108263115 ( 3.00)
Heap/LOCK 199320 ( 7.13) 4983000 ( 4.36) 1656812 ( 0.05) 6639812 ( 0.18)
Heap/INPLACE 469 ( 0.02) 66676 ( 0.06) 558604 ( 0.02) 625280 ( 0.02)
Heap/INSERT+INIT 2992 ( 0.11) 272895 ( 0.24) 0 ( 0.00) 272895 ( 0.01)
Heap/UPDATE+INIT 1184 ( 0.04) 146420 ( 0.13) 6611352 ( 0.19) 6757772 ( 0.19)
Btree/INSERT_LEAF 81058 ( 2.90) 2224916 ( 1.95) 336444372 ( 9.64) 338669288 ( 9.40)
Btree/INSERT_UPPER 128 ( 0.00) 5272 ( 0.00) 16368 ( 0.00) 21640 ( 0.00)
Btree/SPLIT_L 48 ( 0.00) 171384 ( 0.15) 46712 ( 0.00) 218096 ( 0.01)
Btree/SPLIT_R 80 ( 0.00) 233736 ( 0.20) 39116 ( 0.00) 272852 ( 0.01)
Btree/SPLIT_L_ROOT 7 ( 0.00) 5488 ( 0.00) 0 ( 0.00) 5488 ( 0.00)
Btree/SPLIT_R_ROOT 4 ( 0.00) 2880 ( 0.00) 0 ( 0.00) 2880 ( 0.00)
Btree/DELETE 3 ( 0.00) 454 ( 0.00) 0 ( 0.00) 454 ( 0.00)
Btree/UNLINK_PAGE 4 ( 0.00) 176 ( 0.00) 0 ( 0.00) 176 ( 0.00)
Btree/NEWROOT 33 ( 0.00) 980 ( 0.00) 0 ( 0.00) 980 ( 0.00)
Btree/MARK_PAGE_HALFDEAD 4 ( 0.00) 144 ( 0.00) 0 ( 0.00) 144 ( 0.00)
Btree/VACUUM 66 ( 0.00) 7890 ( 0.01) 18400 ( 0.00) 26290 ( 0.00)
-------- -------- -------- --------
Total 2793959 114206860 [3.17%] 3490319764 [96.83%] 3604526624 [100%]
pg_xlogdump: FATAL: error in WAL record at 0/DEA52150: record with zero length at 0/DEA521B8

(Note: the FATAL error above is just the normal end of WAL.)

In each row,

- Type is rmgr/record
- N is the number of records of that type
- % is the percentage of total records
- Record size is sum(xl_len+SizeOfXLogRecord)
- % is the percentage of the total record size
- FPI size is the size of full-page images
- % is the percentage of the total FPI size
- Combined size is sum(xl_tot_len)
- % is the percentage of the total combined size

The last line ("Total") shows the total number of records of all types,
the total record size (and what percentage that is of the total size),
the total full-page image size (and ditto), and the total combined size
(which is 100% by definition).

The patch is quite straightforward, but became quite long due to the
many switch/cases needed to name each meaningful xl_rmid/xl_info
combination.

I'll add it to the CF.

-- Abhijit