Re: PG17 optimizations to vacuum

On 03/09/2024 00:11, Heikki Linnakangas wrote:
> On 03/09/2024 00:01, Peter Geoghegan wrote:
>> On Mon, Sep 2, 2024 at 4:58 PM Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
>> wrote:
>>> Do you have any non-default settings? "select name,
>>> current_setting(name), source  from pg_settings where setting <>
>>> boot_val;" would show that.
>>
>> What about page checksums?
>>
>> One simple explanation is that we're writing extra FPIs to set hint
>> bits. But that explanation only works if you assume that page-level
>> checksums are in use (or that wal_log_hints is turned on).
>
> Hmm, yeah, that might be it. With chceksums enabled, I see ~120k WAL
> records, vs ~90k without checksums. But there's no difference between
> v16 and master.

Looking at the pg_waldump output from this test:

> ... > rmgr: XLOG len (rec/tot): 49/ 8209, tx: 0, lsn:
0/FE052AA8, prev 0/FE0528A8, desc: FPI_FOR_HINT , blkref #0: rel
1663/5/16396 blk 73 FPW
> rmgr: Heap2 len (rec/tot): 507/ 507, tx: 0, lsn: 0/FE054AD8, prev 0/FE052AA8, desc: PRUNE snapshotConflictHorizon: 754, nredirected: 0, ndead: 226, nunused: 0, redirected: [], dead: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226], unused: [], blkref #0: rel 1663/5/16396 blk 73
> rmgr: XLOG len (rec/tot): 49/ 8209, tx: 0, lsn: 0/FE054CD8, prev 0/FE054AD8, desc: FPI_FOR_HINT , blkref #0: rel 1663/5/16396 blk 74 FPW
> rmgr: Heap2 len (rec/tot): 507/ 507, tx: 0, lsn: 0/FE056D08, prev 0/FE054CD8, desc: PRUNE snapshotConflictHorizon: 754, nredirected: 0, ndead: 226, nunused: 0, redirected: [], dead: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226], unused: [], blkref #0: rel 1663/5/16396 blk 74
> ...

The pattern of WAL records with checksums enabled is silly: For each
page, we first write an FPI record, an immediately after that a PRUNE
record that removes all the tuples on it, leaving the page empty.

This is the same with v16 and v17, but we certainly left money on the
table by not folding that FPI into the VACUUM/PRUNE record.

--
Heikki Linnakangas
Neon (https://neon.tech)