Hi, I've briefly looked at this patch this week, and done a bit of testing. I don't have any comments about the correctness - it does seem correct to me and I haven't noticed any crashes/issues, but I'm not familiar with the WALBufMappingLock enough to have insightful opinions.
I have however decided to do a bit of benchmarking, to better understand
the possible benefits of the change. I happen to have access to an Azure
machine with 2x AMD EPYC 9V33X (176 cores in total), and NVMe SSD that
can do ~1.5GB/s.
The benchmark script (attached) uses the workload mentioned by Andres
some time ago [1]
SELECT pg_logical_emit_message(true, 'test', repeat('0', $SIZE));
with clients (1..196) and sizes 8K, 64K and 1024K. The aggregated
results look like this (this is throughput):
| 8 | 64 | 1024
clients | master patched | master patched | master patched
---------------------------------------------------------------------
1 | 11864 12035 | 7419 7345 | 968 940
4 | 26311 26919 | 12414 12308 | 1304 1293
8 | 38742 39651 | 14316 14539 | 1348 1348
16 | 57299 59917 | 15405 15871 | 1304 1279
32 | 74857 82598 | 17589 17126 | 1233 1233
48 | 87596 95495 | 18616 18160 | 1199 1227
64 | 89982 97715 | 19033 18910 | 1196 1221
96 | 92853 103448 | 19694 19706 | 1190 1210
128 | 95392 103324 | 20085 19873 | 1188 1213
160 | 94933 102236 | 20227 20323 | 1180 1214
196 | 95933 103341 | 20448 20513 | 1188 1199
To put this into a perspective, this throughput relative to master:
clients | 8 64 1024
----------------------------------
1 | 101% 99% 97%
4 | 102% 99% 99%
8 | 102% 102% 100%
16 | 105% 103% 98%
32 | 110% 97% 100%
48 | 109% 98% 102%
64 | 109% 99% 102%
96 | 111% 100% 102%
128 | 108% 99% 102%
160 | 108% 100% 103%
196 | 108% 100% 101%
That does not seem like a huge improvement :-( Yes, there's 1-10%
speedup for the small (8K) size, but for larger chunks it's a wash.
Looking at the pgbench progress, I noticed stuff like this:
...
progress: 13.0 s, 103575.2 tps, lat 0.309 ms stddev 0.071, 0 failed
progress: 14.0 s, 102685.2 tps, lat 0.312 ms stddev 0.072, 0 failed
progress: 15.0 s, 102853.9 tps, lat 0.311 ms stddev 0.072, 0 failed
progress: 16.0 s, 103146.0 tps, lat 0.310 ms stddev 0.075, 0 failed
progress: 17.0 s, 57168.1 tps, lat 0.560 ms stddev 0.153, 0 failed
progress: 18.0 s, 50495.9 tps, lat 0.634 ms stddev 0.060, 0 failed
progress: 19.0 s, 50927.0 tps, lat 0.628 ms stddev 0.066, 0 failed
progress: 20.0 s, 50986.7 tps, lat 0.628 ms stddev 0.062, 0 failed
progress: 21.0 s, 50652.3 tps, lat 0.632 ms stddev 0.061, 0 failed
progress: 22.0 s, 63792.9 tps, lat 0.502 ms stddev 0.168, 0 failed
progress: 23.0 s, 103109.9 tps, lat 0.310 ms stddev 0.072, 0 failed
progress: 24.0 s, 103503.8 tps, lat 0.309 ms stddev 0.071, 0 failed
progress: 25.0 s, 101984.2 tps, lat 0.314 ms stddev 0.073, 0 failed
progress: 26.0 s, 102923.1 tps, lat 0.311 ms stddev 0.072, 0 failed
progress: 27.0 s, 103973.1 tps, lat 0.308 ms stddev 0.072, 0 failed
...
i.e. it fluctuates a lot. I suspected this is due to the SSD doing funny
things (it's a virtual SSD, I'm not sure what model is that behind the
curtains). So I decided to try running the benchmark on tmpfs, to get
the storage out of the way and get the "best case" results.
This makes the pgbench progress perfectly "smooth" (no jumps like in the
output above), and the comparison looks like this:
| 8 | 64 | 1024
clients | master patched | master patched | master patched
---------|---------------------|--------------------|----------------
1 | 32449 32032 | 19289 20344 | 3108 3081
4 | 68779 69256 | 24585 29912 | 2915 3449
8 | 79787 100655 | 28217 39217 | 3182 4086
16 | 113024 148968 | 42969 62083 | 5134 5712
32 | 125884 170678 | 44256 71183 | 4910 5447
48 | 125571 166695 | 44693 76411 | 4717 5215
64 | 122096 160470 | 42749 83754 | 4631 5103
96 | 120170 154145 | 42696 86529 | 4556 5020
128 | 119204 152977 | 40880 88163 | 4529 5047
160 | 116081 152708 | 42263 88066 | 4512 5000
196 | 115364 152455 | 40765 88602 | 4505 4952
and the comparison to master:
clients 8 64 1024
-----------------------------------------
1 99% 105% 99%
4 101% 122% 118%
8 126% 139% 128%
16 132% 144% 111%
32 136% 161% 111%
48 133% 171% 111%
64 131% 196% 110%
96 128% 203% 110%
128 128% 216% 111%
160 132% 208% 111%
196 132% 217% 110%
Yes, with tmpfs the impact looks much more significant. For 8K the
speedup is ~1.3x, for 64K it's up to ~2x, for 1M it's ~1.1x.
That being said, I wonder how big is the impact for practical workloads.
ISTM this workload is pretty narrow / extreme, it'd be much easier if we
had an example of a more realistic workload, benefiting from this. Of
course, it may be the case that there are multiple related bottlenecks,
and we'd need to fix all of them - in which case it'd be silly to block
the improvements on the grounds that it alone does not help.
Another thought is that this is testing the "good case". Can anyone
think of a workload that would be made worse by the patch?
regards
--
Tomas Vondra
wal-lock-test.sh
Description: application/shellscript
1 8 1 32360.720422 1 8 4 69034.498679 1 8 8 97856.155556 1 8 16 150678.469704 1 8 32 172498.553643 1 8 48 164346.625750 1 8 64 159014.373602 1 8 96 154308.968981 1 8 128 153070.025149 1 8 160 152398.579533 1 8 196 152499.913279 1 64 1 20265.155745 1 64 4 33515.744417 1 64 8 40616.584584 1 64 16 62001.284081 1 64 32 71461.714678 1 64 48 76700.054878 1 64 64 83707.795055 1 64 96 87334.903499 1 64 128 88940.444230 1 64 160 89243.161196 1 64 196 89215.246989 1 1024 1 3134.138654 1 1024 4 3453.282696 1 1024 8 4062.608650 1 1024 16 5741.159368 1 1024 32 5456.404084 1 1024 48 5254.142769 1 1024 64 5097.623840 1 1024 96 5025.123281 1 1024 128 5067.270042 1 1024 160 5045.267858 1 1024 196 4950.114624 2 8 1 30970.661991 2 8 4 70115.085547 2 8 8 103309.451036 2 8 16 148771.564161 2 8 32 167258.962500 2 8 48 167103.391885 2 8 64 161785.561487 2 8 96 154672.561661 2 8 128 152699.473284 2 8 160 152721.469700 2 8 196 152234.605979 2 64 1 19078.053334 2 64 4 26968.864255 2 64 8 39028.759683 2 64 16 62050.399715 2 64 32 71147.588662 2 64 48 77207.757426 2 64 64 83765.460634 2 64 96 86200.341975 2 64 128 88665.062408 2 64 160 88900.681475 2 64 196 88372.683921 2 1024 1 3107.217782 2 1024 4 3373.750863 2 1024 8 4116.137612 2 1024 16 5690.828609 2 1024 32 5474.277180 2 1024 48 5213.658196 2 1024 64 5113.054759 2 1024 96 5018.684604 2 1024 128 5029.997575 2 1024 160 4947.026038 2 1024 196 4965.384431 3 8 1 32764.003876 3 8 4 68617.116969 3 8 8 100800.464431 3 8 16 147455.450179 3 8 32 172275.871776 3 8 48 168635.224017 3 8 64 160610.899054 3 8 96 153452.018984 3 8 128 153161.298526 3 8 160 153004.286111 3 8 196 152631.904278 3 64 1 21687.637557 3 64 4 29252.602566 3 64 8 38004.259409 3 64 16 62197.853940 3 64 32 70939.117361 3 64 48 75324.373779 3 64 64 83789.673353 3 64 96 86052.281144 3 64 128 86883.928593 3 64 160 86052.994161 3 64 196 88217.869246 3 1024 1 3003.093905 3 1024 4 3520.149847 3 1024 8 4079.581309 3 1024 16 5703.233290 3 1024 32 5411.427630 3 1024 48 5176.168081 3 1024 64 5099.770741 3 1024 96 5015.419124 3 1024 128 5044.012489 3 1024 160 5006.879554 3 1024 196 4940.204463
1 8 1 32920.529060 1 8 4 69155.687197 1 8 8 76546.325153 1 8 16 108026.408474 1 8 32 123389.337626 1 8 48 124278.932183 1 8 64 121293.472435 1 8 96 120989.570297 1 8 128 123004.893408 1 8 160 115944.422211 1 8 196 114967.864608 1 64 1 19109.675355 1 64 4 22874.050626 1 64 8 27766.410311 1 64 16 42566.957235 1 64 32 42725.923712 1 64 48 48806.617744 1 64 64 45066.656345 1 64 96 39773.099383 1 64 128 40453.958219 1 64 160 42447.934359 1 64 196 40639.935207 1 1024 1 3184.621852 1 1024 4 2811.484769 1 1024 8 3056.072876 1 1024 16 5203.108768 1 1024 32 4928.815375 1 1024 48 4701.495927 1 1024 64 4644.123449 1 1024 96 4555.515478 1 1024 128 4537.754239 1 1024 160 4533.126967 1 1024 196 4521.031943 2 8 1 31254.352550 2 8 4 68245.759361 2 8 8 85251.050699 2 8 16 116262.700608 2 8 32 128268.654835 2 8 48 121853.775976 2 8 64 121748.783022 2 8 96 120029.154398 2 8 128 116940.020495 2 8 160 115543.315516 2 8 196 115355.481247 2 64 1 19345.115658 2 64 4 23213.376739 2 64 8 28641.579926 2 64 16 43348.310325 2 64 32 45889.047513 2 64 48 42526.290872 2 64 64 45953.444942 2 64 96 44010.001135 2 64 128 41356.612222 2 64 160 43431.862330 2 64 196 39488.159667 2 1024 1 3175.016765 2 1024 4 2890.924221 2 1024 8 3429.420682 2 1024 16 5151.690899 2 1024 32 4846.273357 2 1024 48 4719.115840 2 1024 64 4608.736106 2 1024 96 4549.030651 2 1024 128 4532.874873 2 1024 160 4499.330948 2 1024 196 4492.198084 3 8 1 33173.375450 3 8 4 68936.430299 3 8 8 77563.691424 3 8 16 114784.220085 3 8 32 125992.955894 3 8 48 130579.287266 3 8 64 123244.725726 3 8 96 119491.126983 3 8 128 117668.329736 3 8 160 116755.647926 3 8 196 115767.161259 3 64 1 19412.315801 3 64 4 27667.969430 3 64 8 28243.114681 3 64 16 42990.347428 3 64 32 44152.472947 3 64 48 42745.958683 3 64 64 37228.308561 3 64 96 44303.557471 3 64 128 40829.105540 3 64 160 40909.961234 3 64 196 42166.620733 3 1024 1 2965.085534 3 1024 4 3043.745972 3 1024 8 3061.188565 3 1024 16 5047.976618 3 1024 32 4954.200195 3 1024 48 4731.022120 3 1024 64 4640.514094 3 1024 96 4564.880356 3 1024 128 4516.039649 3 1024 160 4502.365825 3 1024 196 4503.075016
1 8 1 11369.173626 1 8 4 27212.619477 1 8 8 39139.500586 1 8 16 58483.989287 1 8 32 76799.480510 1 8 48 88971.383172 1 8 64 90946.010938 1 8 96 94082.507550 1 8 128 97091.827132 1 8 160 94794.139859 1 8 196 96906.809672 1 64 1 7701.924723 1 64 4 12485.602619 1 64 8 13603.201577 1 64 16 15157.809136 1 64 32 17216.302345 1 64 48 18031.572943 1 64 64 19330.948373 1 64 96 19574.158145 1 64 128 20058.198853 1 64 160 19819.874620 1 64 196 20372.371957 1 1024 1 914.301883 1 1024 4 1292.283682 1 1024 8 1359.113047 1 1024 16 1298.098190 1 1024 32 1220.611391 1 1024 48 1205.941668 1 1024 64 1189.194994 1 1024 96 1187.049284 1 1024 128 1192.480409 1 1024 160 1184.048764 1 1024 196 1185.107403 2 8 1 12374.635765 2 8 4 26798.851721 2 8 8 38109.478919 2 8 16 58512.453890 2 8 32 74192.258083 2 8 48 85755.511176 2 8 64 89955.234563 2 8 96 91113.776960 2 8 128 94999.310954 2 8 160 94876.382467 2 8 196 95345.998570 2 64 1 7513.177286 2 64 4 12441.372874 2 64 8 14521.771490 2 64 16 14604.810682 2 64 32 17355.288706 2 64 48 18721.093928 2 64 64 18573.671314 2 64 96 19612.357657 2 64 128 19972.318751 2 64 160 20164.295006 2 64 196 20562.462757 2 1024 1 924.880845 2 1024 4 1271.212409 2 1024 8 1344.170723 2 1024 16 1303.207096 2 1024 32 1224.095730 2 1024 48 1213.525330 2 1024 64 1202.165658 2 1024 96 1191.616646 2 1024 128 1182.746859 2 1024 160 1187.975140 2 1024 196 1183.382139 3 8 1 11978.399197 3 8 4 26700.765141 3 8 8 39503.781213 3 8 16 55622.576688 3 8 32 72537.367551 3 8 48 86255.238261 3 8 64 89879.771312 3 8 96 92538.762113 3 8 128 94873.849443 3 8 160 95004.052802 3 8 196 95933.428069 3 64 1 7436.540937 3 64 4 12547.675983 3 64 8 14509.120566 3 64 16 14375.960503 3 64 32 18431.834133 3 64 48 18527.684926 3 64 64 18794.590319 3 64 96 19682.993800 3 64 128 20180.293271 3 64 160 20339.026407 3 64 196 20495.427550 3 1024 1 990.600380 3 1024 4 1333.704939 3 1024 8 1328.613817 3 1024 16 1303.638972 3 1024 32 1238.427906 3 1024 48 1188.474912 3 1024 64 1194.047460 3 1024 96 1192.783728 3 1024 128 1188.248009 3 1024 160 1179.442388 3 1024 196 1190.117887 4 8 1 12168.307416 4 8 4 25184.333418 4 8 8 39231.247336 4 8 16 58443.664703 4 8 32 75746.555647 4 8 48 90054.917365 4 8 64 88884.835250 4 8 96 93353.170374 4 8 128 97205.579178 4 8 160 95009.692803 4 8 196 95539.068223 4 64 1 7041.666763 4 64 4 12430.738388 4 64 8 14146.961536 4 64 16 16122.447010 4 64 32 17364.317847 4 64 48 18774.245333 4 64 64 19303.574329 4 64 96 19816.973344 4 64 128 20101.364329 4 64 160 20354.463942 4 64 196 20516.712485 4 1024 1 996.286746 4 1024 4 1290.325850 4 1024 8 1345.093255 4 1024 16 1299.460260 4 1024 32 1244.898587 4 1024 48 1197.853322 4 1024 64 1199.870436 4 1024 96 1190.149355 4 1024 128 1188.251277 4 1024 160 1160.855579 4 1024 196 1189.855109 5 8 1 11428.363040 5 8 4 25658.987654 5 8 8 37727.193768 5 8 16 55433.060860 5 8 32 75011.467091 5 8 48 86941.342575 5 8 64 90243.395561 5 8 96 93175.001261 5 8 128 92790.604237 5 8 160 94980.927873 5 8 196 95941.769969 5 64 1 7403.334087 5 64 4 12166.357062 5 64 8 14800.001427 5 64 16 16765.802576 5 64 32 17576.385594 5 64 48 19026.089207 5 64 64 19161.693357 5 64 96 19783.122879 5 64 128 20113.381340 5 64 160 20459.130945 5 64 196 20295.132586 5 1024 1 1015.564917 5 1024 4 1334.869152 5 1024 8 1364.487310 5 1024 16 1314.098928 5 1024 32 1238.767245 5 1024 48 1191.475866 5 1024 64 1196.632271 5 1024 96 1186.717845 5 1024 128 1189.243781 5 1024 160 1187.180892 5 1024 196 1189.364557
1 8 1 12317.226445 1 8 4 26855.732281 1 8 8 39903.466659 1 8 16 60731.531297 1 8 32 82777.322916 1 8 48 95542.854643 1 8 64 99644.256142 1 8 96 104251.602854 1 8 128 102834.006626 1 8 160 100572.313124 1 8 196 103973.519464 1 64 1 7577.964077 1 64 4 12318.759501 1 64 8 14748.762411 1 64 16 14231.772209 1 64 32 16244.728664 1 64 48 17778.572266 1 64 64 18304.477114 1 64 96 19565.106664 1 64 128 19407.486232 1 64 160 20215.695516 1 64 196 20383.871185 1 1024 1 914.666536 1 1024 4 1305.518060 1 1024 8 1340.584987 1 1024 16 1266.445382 1 1024 32 1228.486179 1 1024 48 1203.756448 1 1024 64 1193.629534 1 1024 96 1194.123849 1 1024 128 1190.975518 1 1024 160 1191.785043 1 1024 196 1196.460027 2 8 1 11958.860133 2 8 4 26626.508789 2 8 8 40253.724295 2 8 16 60002.799595 2 8 32 83149.563064 2 8 48 95330.335973 2 8 64 96129.526968 2 8 96 103291.018420 2 8 128 103430.083221 2 8 160 103258.458809 2 8 196 103783.518446 2 64 1 7191.087785 2 64 4 12577.492819 2 64 8 14729.111487 2 64 16 16021.140192 2 64 32 17525.939815 2 64 48 17852.055739 2 64 64 18840.292995 2 64 96 19630.977686 2 64 128 19934.413024 2 64 160 20351.252250 2 64 196 20531.889225 2 1024 1 935.860426 2 1024 4 1308.022051 2 1024 8 1351.368829 2 1024 16 1274.389869 2 1024 32 1238.071879 2 1024 48 1226.176142 2 1024 64 1224.725568 2 1024 96 1215.826268 2 1024 128 1216.949432 2 1024 160 1215.688056 2 1024 196 1214.216540 3 8 1 12326.911795 3 8 4 27120.117141 3 8 8 38250.372984 3 8 16 60090.179615 3 8 32 82082.202988 3 8 48 95136.365275 3 8 64 97458.044956 3 8 96 102922.285078 3 8 128 103524.221252 3 8 160 103145.409797 3 8 196 104287.404306 3 64 1 7464.067450 3 64 4 12226.423097 3 64 8 14853.217923 3 64 16 15818.727262 3 64 32 16514.378100 3 64 48 18418.796058 3 64 64 18897.532994 3 64 96 19689.402975 3 64 128 20018.477620 3 64 160 20338.432602 3 64 196 20537.746222 3 1024 1 929.491995 3 1024 4 1241.404273 3 1024 8 1331.524345 3 1024 16 1291.163267 3 1024 32 1238.709817 3 1024 48 1242.507181 3 1024 64 1232.785859 3 1024 96 1228.135291 3 1024 128 1215.435446 3 1024 160 1217.645454 3 1024 196 1198.460500 4 8 1 11688.238158 4 8 4 27321.555789 4 8 8 40088.720765 4 8 16 59811.073133 4 8 32 82524.991695 4 8 48 96482.052576 4 8 64 99024.883317 4 8 96 103268.500425 4 8 128 103132.909829 4 8 160 100909.022764 4 8 196 103901.514802 4 64 1 7408.080771 4 64 4 12331.327665 4 64 8 14094.152931 4 64 16 16638.514133 4 64 32 17487.959019 4 64 48 18575.710191 4 64 64 19243.111312 4 64 96 19735.707166 4 64 128 20006.085076 4 64 160 20342.491789 4 64 196 20493.607330 4 1024 1 968.003742 4 1024 4 1316.647992 4 1024 8 1357.678499 4 1024 16 1281.671282 4 1024 32 1253.227470 4 1024 48 1241.454419 4 1024 64 1225.827642 4 1024 96 1196.116933 4 1024 128 1226.784896 4 1024 160 1225.561699 4 1024 196 1206.739884 5 8 1 11883.980847 5 8 4 26669.702614 5 8 8 39760.957327 5 8 16 58948.792240 5 8 32 82455.061729 5 8 48 94984.516212 5 8 64 96320.343506 5 8 96 103506.758294 5 8 128 103701.231999 5 8 160 103292.744486 5 8 196 100758.729238 5 64 1 7083.543263 5 64 4 12085.107580 5 64 8 14267.496258 5 64 16 16642.828867 5 64 32 17858.258493 5 64 48 18173.645663 5 64 64 19265.534194 5 64 96 19910.901503 5 64 128 19997.493354 5 64 160 20366.727187 5 64 196 20617.523700 5 1024 1 953.788263 5 1024 4 1295.068843 5 1024 8 1357.042109 5 1024 16 1280.522200 5 1024 32 1208.835982 5 1024 48 1218.924947 5 1024 64 1226.971434 5 1024 96 1213.624846 5 1024 128 1217.212594 5 1024 160 1220.802223 5 1024 196 1177.745371
