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