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