Re: WAL Insertion Lock Improvements

On Thu, May 11, 2023 at 11:56 AM Michael Paquier <michael@paquier.xyz> wrote:
>
> 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.

Impressive. I further covered the following test cases. There's a
clear gain with the patch i.e. reducing burden on LWLock's waitlist
lock is helping out.

fsync=off, -T120:
 message_size_b   |   16   |   64   |  256   |  1024  |  4096  | 16384
-------------------+--------+--------+--------+--------+--------+--------
 head_1_clients    |  33609 |  33862 |  32975 |  29722 |  21842 |  10606
 head_2_clients    |  60583 |  60524 |  57833 |  53582 |  38583 |  20120
 head_4_clients    | 115209 | 114012 | 114077 | 102991 |  73452 |  39179
 head_8_clients    | 181786 | 177592 | 174404 | 155350 |  98642 |  41406
 head_16_clients   | 313750 | 309024 | 295375 | 253101 | 159328 |  73617
 head_32_clients   | 406456 | 416809 | 400527 | 344573 | 213756 |  96322
 head_64_clients   | 199619 | 197948 | 198871 | 208606 | 221751 | 107762
 head_128_clients  | 108576 | 108727 | 107606 | 112137 | 173998 | 106976
 head_256_clients  |  75303 |  74983 |  73986 |  76100 | 148209 |  98080
 head_512_clients  |  62559 |  60189 |  59588 |  61102 | 131803 |  90534
 head_768_clients  |  55650 |  54486 |  54813 |  55515 | 120707 |  88009
 head_1024_clients |  54709 |  52395 |  51672 |  52910 | 113904 |  86116
 head_2048_clients |  48640 |  47098 |  46787 |  47582 |  98394 |  80766
 head_4096_clients |  43205 |  42709 |  42591 |  43649 |  88903 |  72362
 v6_1_clients    |  33337 |  32877 |  31880 |  29372 |  21695 |  10596
 v6_2_clients    |  60125 |  60682 |  58770 |  53709 |  38390 |  20266
 v6_4_clients    | 115338 | 114053 | 114232 |  93527 |  74409 |  40437
 v6_8_clients    | 179472 | 183899 | 175474 | 154547 | 101807 |  43508
 v6_16_clients   | 318181 | 318580 | 296591 | 258094 | 159351 |  74758
 v6_32_clients   | 439681 | 447005 | 428459 | 367307 | 218511 |  97635
 v6_64_clients   | 473440 | 478388 | 464287 | 394825 | 244365 | 109194
 v6_128_clients  | 384433 | 412694 | 405916 | 366046 | 232421 | 110274
 v6_256_clients  | 312480 | 303635 | 291900 | 307573 | 206784 | 104171
 v6_512_clients  | 218560 | 189207 | 216267 | 252513 | 186762 |  97918
 v6_768_clients  | 168432 | 155493 | 145941 | 226616 | 178178 |  95435
 v6_1024_clients | 150300 | 132078 | 134657 | 224515 | 172950 |  94356
 v6_2048_clients | 126941 | 120189 | 120702 | 195684 | 158683 |  88055
 v6_4096_clients | 163993 | 140795 | 139702 | 170149 | 139740 |  78907

pg_wal on tmpfs, -T180:
  message_size_b   |   16   |   64   |  256   |  1024  |  4096  | 16384
-------------------+--------+--------+--------+--------+--------+--------
 head_1_clients    |  32956 |  32766 |  32244 |  29772 |  22094 |  11212
 head_2_clients    |  60093 |  60382 |  58825 |  53812 |  39764 |  20953
 head_4_clients    | 117178 | 104986 | 112060 | 103416 |  75588 |  39753
 head_8_clients    | 177556 | 179926 | 173413 | 156684 | 107727 |  42001
 head_16_clients   | 311033 | 313842 | 298362 | 261298 | 165293 |  76183
 head_32_clients   | 425750 | 433988 | 419193 | 370925 | 227392 | 101638
 head_64_clients   | 227463 | 219832 | 221421 | 235603 | 236601 | 113677
 head_128_clients  | 117188 | 116847 | 118414 | 123605 | 194533 | 111480
 head_256_clients  |  80596 |  80541 |  79130 |  83949 | 167529 | 102401
 head_512_clients  |  64912 |  63610 |  63209 |  65554 | 146882 |  94936
 head_768_clients  |  59050 |  57082 |  57061 |  58966 | 133336 |  92389
 head_1024_clients |  56880 |  54951 |  54864 |  56554 | 125270 |  90893
 head_2048_clients |  52148 |  49603 |  50422 |  50692 | 110789 |  86659
 head_4096_clients |  47001 |  46992 |  46075 |  47793 |  99617 |  77762
 v6_1_clients    |  32915 |  32854 |  31676 |  29341 |  21956 |  11220
 v6_2_clients    |  59592 |  59146 |  58106 |  53235 |  38973 |  20943
 v6_4_clients    | 113947 | 114897 |  97349 | 104630 |  73628 |  40719
 v6_8_clients    | 177996 | 179673 | 176190 | 156831 | 104183 |  42884
 v6_16_clients   | 312284 | 317065 | 300130 | 268788 | 165765 |  77299
 v6_32_clients   | 443101 | 450025 | 436774 | 380398 | 229081 | 101916
 v6_64_clients   | 450794 | 469633 | 470252 | 411374 | 253232 | 113722
 v6_128_clients  | 413357 | 399514 | 386713 | 364070 | 236133 | 112780
 v6_256_clients  | 264674 | 252701 | 268273 | 296090 | 208050 | 105477
 v6_512_clients  | 196481 | 154815 | 158316 | 238805 | 188363 |  99507
 v6_768_clients  | 139839 | 132645 | 131391 | 219846 | 179226 |  97808
 v6_1024_clients | 124540 | 119543 | 120140 | 206740 | 174657 |  96629
 v6_2048_clients | 118793 | 113033 | 113881 | 190997 | 161421 |  91888
 v6_4096_clients | 156341 | 156971 | 131391 | 177024 | 146564 |  84096

--enable-atomics=no, -T60:
  message_size_b   |  16   |  64   |  256  | 1024  | 4096  | 16384
-------------------+-------+-------+-------+-------+-------+-------
 head_1_clients    |  1701 |  1686 |  1636 |  1693 |  1523 |  1331
 head_2_clients    |  1751 |  1712 |  1698 |  1769 |  1690 |  1579
 head_4_clients    |  3328 |  3370 |  3405 |  3495 |  3107 |  2713
 head_8_clients    |  6580 |  6521 |  6459 |  6370 |  5470 |  4253
 head_16_clients   | 13433 | 13476 | 12986 | 11461 |  9249 |  6313
 head_32_clients   | 25697 | 26729 | 24879 | 20862 | 14344 |  9454
 head_64_clients   | 51499 | 48322 | 46297 | 35224 | 20970 | 13241
 head_128_clients  | 56777 | 57177 | 59129 | 47687 | 27591 | 16007
 head_256_clients  |  9555 | 10041 |  9526 |  9830 | 13179 | 15776
 head_512_clients  |  5795 |  5871 |  5809 |  5954 |  5828 | 15647
 head_768_clients  |  4322 |  4366 |  4782 |  4624 |  4853 | 12959
 head_1024_clients |  4003 |  3789 |  3647 |  3865 |  4160 |  7991
 head_2048_clients |  2687 |  2573 |  2569 |  2829 |  2918 |  5462
 head_4096_clients |  1694 |  1802 |  1813 |  1948 |  2256 |  5862
 v6_1_clients    |  1560 |  1595 |  1690 |  1621 |  1526 |  1374
 v6_2_clients    |  1737 |  1736 |  1738 |  1663 |  1601 |  1568
 v6_4_clients    |  3575 |  3583 |  3449 |  3137 |  3157 |  2788
 v6_8_clients    |  6660 |  6900 |  6802 |  6158 |  5605 |  4521
 v6_16_clients   | 14084 | 12991 | 13485 | 12628 | 10025 |  6211
 v6_32_clients   | 26408 | 24652 | 24672 | 21441 | 14966 |  9753
 v6_64_clients   | 49537 | 47703 | 45583 | 33524 | 21476 | 13259
 v6_128_clients  | 86938 | 79745 | 73740 | 53007 | 34863 | 15901
 v6_256_clients  | 20391 | 21433 | 21730 | 30836 | 43821 | 15891
 v6_512_clients  | 13128 | 12181 | 12309 | 11596 | 14744 | 15851
 v6_768_clients  | 10511 |  9942 |  9713 |  9373 | 10181 | 15964
 v6_1024_clients |  9264 |  8745 |  8031 |  7500 |  8762 | 15198
 v6_2048_clients |  6070 |  5724 |  5939 |  5987 |  5513 | 10828
 v6_4096_clients |  4322 |  4035 |  3616 |  3637 |  5628 | 10970

--enable-spinlocks=no, -T60:
  message_size_b   |   16   |   64   |  256   |  1024  | 4096  | 16384
-------------------+--------+--------+--------+--------+-------+-------
 head_1_clients    |   1644 |   1716 |   1701 |   1636 |  1569 |  1368
 head_2_clients    |   1779 |   1875 |   1728 |   1728 |  1770 |  1568
 head_4_clients    |   3448 |   3569 |   3330 |   3324 |  3319 |  2780
 head_8_clients    |   6159 |   6996 |   6893 |   6401 |  6308 |  4423
 head_16_clients   |  13195 |  13810 |  13139 |  12892 | 10744 |  6714
 head_32_clients   |  26752 |  26834 |  25749 |  21739 | 18071 |  9706
 head_64_clients   |  52303 |  49759 |  47785 |  36625 | 26993 | 13685
 head_128_clients  |  98325 |  89753 |  83276 |  62302 | 38515 | 16005
 head_256_clients  | 128075 | 124396 | 111059 |  97165 | 56941 | 15779
 head_512_clients  | 140908 | 132622 | 126363 | 119113 | 62572 | 15919
 head_768_clients  | 118694 | 111764 | 109464 | 120368 | 62129 | 15905
 head_1024_clients | 102542 |  99007 |  94291 | 109485 | 62680 | 16039
 head_2048_clients |  57994 |  57003 |  57410 |  60350 | 62487 | 16091
 head_4096_clients |  33995 |  32944 |  34174 |  33483 | 61071 | 15655
 v6_1_clients    |   1743 |   1711 |   1722 |   1655 |  1588 |  1378
 v6_2_clients    |   1714 |   1830 |   1767 |   1667 |  1725 |  1518
 v6_4_clients    |   3638 |   3602 |   3594 |   3452 |  3216 |  2713
 v6_8_clients    |   7047 |   6671 |   7148 |   6342 |  5577 |  4573
 v6_16_clients   |  13885 |  13247 |  13951 |  13037 | 10570 |  6391
 v6_32_clients   |  27766 |  27230 |  27079 |  22911 | 17152 |  9700
 v6_64_clients   |  50748 |  51548 |  47852 |  36479 | 27232 | 13290
 v6_128_clients  |  97611 |  89554 |  85009 |  67349 | 37046 | 16005
 v6_256_clients  | 124475 | 128603 | 108888 |  95277 | 55021 | 15785
 v6_512_clients  | 181639 | 176544 | 152852 | 120914 | 62674 | 15921
 v6_768_clients  | 188600 | 180691 | 158997 | 128740 | 62402 | 15979
 v6_1024_clients | 191845 | 180830 | 161597 | 143032 | 62426 | 15985
 v6_2048_clients | 179227 | 168906 | 173510 | 149689 | 62721 | 16090
 v6_4096_clients | 156613 | 152795 | 154231 | 134587 | 62245 | 15781

--enable-atomics=no --enable-spinlocks=no, -T60:
  message_size_b   |  16   |  64   |  256  | 1024  | 4096  | 16384
-------------------+-------+-------+-------+-------+-------+-------
 head_1_clients    |  1644 |  1768 |  1726 |  1698 |  1544 |  1344
 head_2_clients    |  1805 |  1829 |  1746 |  1869 |  1730 |  1565
 head_4_clients    |  3562 |  3606 |  3571 |  3656 |  3145 |  2704
 head_8_clients    |  6921 |  7051 |  6774 |  6676 |  5999 |  4425
 head_16_clients   | 13418 | 13998 | 13634 | 12640 |  9782 |  6440
 head_32_clients   | 21716 | 21690 | 21124 | 18977 | 14050 |  9168
 head_64_clients   | 27085 | 26498 | 26108 | 23048 | 17843 | 13278
 head_128_clients  | 26704 | 26373 | 25845 | 24056 | 19777 | 15922
 head_256_clients  | 24694 | 24586 | 24148 | 22525 | 23523 | 15852
 head_512_clients  | 21364 | 21143 | 20697 | 20334 | 21770 | 15870
 head_768_clients  | 16985 | 16618 | 16544 | 16511 | 17360 | 15945
 head_1024_clients | 13133 | 13640 | 13521 | 13716 | 14202 | 16020
 head_2048_clients |  8051 |  8140 |  7711 |  8673 |  9027 | 15091
 head_4096_clients |  4692 |  4549 |  4924 |  4908 |  6853 | 14752
 v6_1_clients    |  1676 |  1722 |  1781 |  1681 |  1527 |  1394
 v6_2_clients    |  1868 |  1706 |  1868 |  1842 |  1762 |  1573
 v6_4_clients    |  3668 |  3591 |  3449 |  3556 |  3309 |  2707
 v6_8_clients    |  7279 |  6818 |  6842 |  6846 |  5888 |  4283
 v6_16_clients   | 13604 | 13364 | 14099 | 12851 |  9959 |  6271
 v6_32_clients   | 22899 | 22453 | 22488 | 20127 | 15970 |  8915
 v6_64_clients   | 33289 | 32943 | 32280 | 28683 | 22885 | 13215
 v6_128_clients  | 43614 | 42954 | 41336 | 36660 | 29107 | 15928
 v6_256_clients  | 46542 | 46593 | 45673 | 41064 | 38759 | 15850
 v6_512_clients  | 36303 | 35923 | 34640 | 32828 | 38359 | 15913
 v6_768_clients  | 29654 | 29822 | 29317 | 28703 | 34194 | 15903
 v6_1024_clients | 25871 | 25219 | 25801 | 25099 | 29323 | 16015
 v6_2048_clients | 16497 | 17041 | 16401 | 17128 | 19656 | 15962
 v6_4096_clients | 10067 | 10873 | 10702 | 10540 | 12909 | 16041

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com