Re: WAL Insertion Lock Improvements

On Wed, May 10, 2023 at 09:04:47PM +0900, Michael Paquier wrote:
> It took me some time, but I have been able to deploy a big box to see
> the effect of this patch at a rather large scale (64 vCPU, 512G of
> memory), with the following test characteristics for HEAD and v6:
> - TPS comparison with pgbench and pg_logical_emit_message().
> - Record sizes of 16, 64, 256, 1k, 4k and 16k.
> - Clients and jobs equal at 4, 16, 64, 256, 512, 1024, 2048, 4096.
> - Runs of 3 mins for each of the 48 combinations, meaning 96 runs in
> total.
>
> And here are the results I got:
> message_size_b | 16 | 64 | 256 | 1024 | 4096 | 16k
> ------------------|--------|--------|--------|--------|-------|-------
> head_4_clients | 3026 | 2965 | 2846 | 2880 | 2778 | 2412
> head_16_clients | 12087 | 11287 | 11670 | 11100 | 9003 | 5608
> head_64_clients | 42995 | 44005 | 43592 | 35437 | 21533 | 11273
> head_256_clients | 106775 | 109233 | 104201 | 80759 | 42118 | 16254
> head_512_clients | 153849 | 156950 | 142915 | 99288 | 57714 | 16198
> head_1024_clients | 122102 | 123895 | 114248 | 117317 | 62270 | 16261
> head_2048_clients | 126730 | 115594 | 109671 | 119564 | 62454 | 16298
> head_4096_clients | 111564 | 111697 | 119164 | 123483 | 62430 | 16140
> v6_4_clients | 2893 | 2917 | 3087 | 2904 | 2786 | 2262
> v6_16_clients | 12097 | 11387 | 11579 | 11242 | 9228 | 5661
> v6_64_clients | 45124 | 46533 | 42275 | 36124 | 21696 | 11386
> v6_256_clients | 121500 | 125732 | 104328 | 78989 | 41949 | 16254
> v6_512_clients | 164120 | 174743 | 146677 | 98110 | 60228 | 16171
> v6_1024_clients | 168990 | 180710 | 149894 | 117431 | 62271 | 16259
> v6_2048_clients | 165426 | 162893 | 146322 | 132476 | 62468 | 16274
> v6_4096_clients | 161283 | 158732 | 162474 | 135636 | 62461 | 16030

Another thing I was wondering is if it would be able to see a
difference by reducing the I/O pressure. After mounting pg_wal to a
tmpfs, I am getting the following table:
message_size_b | 16 | 64 | 256 | 1024 | 4096 | 16000
-------------------+--------+--------+--------+--------+--------+-------
head_4_clients | 86476 | 86592 | 84645 | 76784 | 57887 | 30199
head_16_clients | 277006 | 278431 | 263238 | 228614 | 143880 | 67237
head_64_clients | 373972 | 370082 | 352217 | 297377 | 190974 | 96843
head_256_clients | 144510 | 147077 | 146281 | 189059 | 156294 | 88345
head_512_clients | 122863 | 119054 | 127790 | 162187 | 142771 | 84109
head_1024_clients | 140802 | 138728 | 147200 | 172449 | 138022 | 81054
head_2048_clients | 175950 | 164143 | 154070 | 161432 | 128205 | 76732
head_4096_clients | 161438 | 158666 | 152057 | 139520 | 113955 | 69335
v6_4_clients | 87356 | 86985 | 83933 | 76397 | 57352 | 30084
v6_16_clients | 277466 | 280125 | 259733 | 224916 | 143832 | 66589
v6_64_clients | 388352 | 386188 | 362358 | 302719 | 190353 | 96687
v6_256_clients | 365797 | 360114 | 337135 | 266851 | 172252 | 88898
v6_512_clients | 339751 | 332384 | 308182 | 249624 | 158868 | 84258
v6_1024_clients | 301294 | 295140 | 276769 | 226034 | 148392 | 80909
v6_2048_clients | 268846 | 261001 | 247110 | 205332 | 137271 | 76299
v6_4096_clients | 229322 | 227049 | 217271 | 183708 | 124888 | 69263

This shows more difference from 64 clients up to 4k records, without
degradation noticed across the board.
--
Michael