Re: WAL Insertion Lock Improvements

On Thu, May 11, 2023 at 11:56 AM Michael Paquier <michael(at)paquier(dot)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