Hello fellow PostgreSQL users and developers, I installed a new Fedora release last week and ran into a peculiar problem:
Each night I need to run computations through about 70.000 rows and using release 16.3 that took about 4 hours to complete, but using release 16.9 the same computations now take about 8 hours to complete. I've checked the unix sockets, inet sockets and the NVMe drive for deviations between the different kernels, but found none. Configuration files are exactly identical as well. Somewhere in between release 16.3 and release 16.9, changes must have been implemented that make the execution engine about two times slower than it was. Two particular queries, that run about 70.000 times each night, gave the following results: Query 1 over unix sockets: release 16.3: Time: 194.093 ms Time: 169.714 ms Time: 174.424 ms Time: 170.285 ms release 16.9: Time: 281.554 ms Time: 290.257 ms Time: 290.886 ms Time: 287.528 ms Query 2 over unix sockets: release 16.3: Time: 132.500 ms Time: 133.182 ms Time: 134.511 ms Time: 130.818 ms release 16.9: Time: 251.238 ms Time: 247.749 ms Time: 246.862 ms Time: 248.541 ms These differences add up to about 4 hours additional computation time each night. Apparently I have to scale down on the amount of work that can be done in one night. Keep up the good work, but don't lose the big picture out of sight :) Hopefully this issue can be resolved quickly. Kind regards, Mischa.
caching: O_DEFAULT bsizex: 536870912 clock_0 - transfer speed: 9.9752 gbit/s | reading speed 17.9452 gbit/s | writing speed 22.4601 gbitb/s caching: O_DEFAULT bsizex: 268435456 clock_0 - transfer speed: 14.3776 gbit/s | reading speed 39.3486 gbit/s | writing speed 22.6560 gbitb/s caching: O_DEFAULT bsizex: 134217728 clock_0 - transfer speed: 15.4396 gbit/s | reading speed 48.3407 gbit/s | writing speed 22.6853 gbitb/s caching: O_DEFAULT bsizex: 67108864 clock_0 - transfer speed: 16.9925 gbit/s | reading speed 61.8310 gbit/s | writing speed 23.4324 gbitb/s caching: O_DEFAULT bsizex: 33554432 clock_0 - transfer speed: 18.3229 gbit/s | reading speed 77.1374 gbit/s | writing speed 24.0315 gbitb/s caching: O_DEFAULT bsizex: 16777216 clock_0 - transfer speed: 20.8833 gbit/s | reading speed 108.9043 gbit/s | writing speed 25.8382 gbitb/s caching: O_DEFAULT bsizex: 8388608 clock_0 - transfer speed: 22.0910 gbit/s | reading speed 126.1014 gbit/s | writing speed 26.7833 gbitb/s caching: O_DEFAULT bsizex: 4194304 clock_0 - transfer speed: 22.2087 gbit/s | reading speed 129.0302 gbit/s | writing speed 26.8262 gbitb/s caching: O_DEFAULT bsizex: 2097152 clock_0 - transfer speed: 22.4760 gbit/s | reading speed 129.8027 gbit/s | writing speed 27.1831 gbitb/s caching: O_DEFAULT bsizex: 1048576 clock_0 - transfer speed: 22.1907 gbit/s | reading speed 131.0745 gbit/s | writing speed 26.7135 gbitb/s caching: O_DEFAULT bsizex: 524288 clock_0 - transfer speed: 22.2590 gbit/s | reading speed 128.8037 gbit/s | writing speed 26.9097 gbitb/s caching: O_DEFAULT bsizex: 262144 clock_0 - transfer speed: 21.9857 gbit/s | reading speed 126.1830 gbit/s | writing speed 26.6250 gbitb/s caching: O_DEFAULT bsizex: 131072 clock_0 - transfer speed: 22.2351 gbit/s | reading speed 125.7980 gbit/s | writing speed 27.0093 gbitb/s caching: O_DEFAULT bsizex: 65536 clock_0 - transfer speed: 22.0201 gbit/s | reading speed 123.8908 gbit/s | writing speed 26.7800 gbitb/s caching: O_DEFAULT bsizex: 32768 clock_0 - transfer speed: 21.5402 gbit/s | reading speed 116.9676 gbit/s | writing speed 26.4026 gbitb/s caching: O_DEFAULT bsizex: 16384 clock_0 - transfer speed: 20.4506 gbit/s | reading speed 104.0826 gbit/s | writing speed 25.4517 gbitb/s caching: O_DEFAULT bsizex: 8192 clock_0 - transfer speed: 18.6940 gbit/s | reading speed 85.1698 gbit/s | writing speed 23.9512 gbitb/s caching: O_DEFAULT bsizex: 4096 clock_0 - transfer speed: 16.0439 gbit/s | reading speed 62.7441 gbit/s | writing speed 21.5560 gbitb/s caching: O_DEFAULT bsizex: 2048 clock_0 - transfer speed: 11.3407 gbit/s | reading speed 44.1189 gbit/s | writing speed 15.2645 gbitb/s caching: O_DEFAULT bsizex: 1024 clock_0 - transfer speed: 7.1023 gbit/s | reading speed 23.8517 gbit/s | writing speed 10.1139 gbitb/s caching: O_DEFAULT bsizex: 512 clock_0 - transfer speed: 4.0164 gbit/s | reading speed 12.3590 gbit/s | writing speed 5.9500 gbitb/s real 0m14.967s user 0m0.345s sys 0m14.365s real 0m14.808s user 0m0.367s sys 0m14.254s real 0m14.869s user 0m0.336s sys 0m14.339s real 0m14.833s user 0m0.344s sys 0m14.298s
unix socket client side half duplex - block size 4194304 - 45.2704 gbit/s in 00:00:00.044s unix socket client side half duplex - block size 2097152 - 55.5509 gbit/s in 00:00:00.036s unix socket client side half duplex - block size 1048576 - 58.8859 gbit/s in 00:00:00.033s unix socket client side half duplex - block size 524288 - 58.2225 gbit/s in 00:00:00.034s unix socket client side half duplex - block size 262144 - 58.1700 gbit/s in 00:00:00.034s unix socket client side half duplex - block size 131072 - 56.1482 gbit/s in 00:00:00.035s unix socket client side half duplex - block size 65536 - 48.6464 gbit/s in 00:00:00.041s unix socket client side full duplex - block size 4194304 - 69.7083 gbit/s in 00:00:00.028s unix socket client side full duplex - block size 2097152 - 91.7894 gbit/s in 00:00:00.021s unix socket client side full duplex - block size 1048576 - 99.7059 gbit/s in 00:00:00.020s unix socket client side full duplex - block size 524288 - 100.8980 gbit/s in 00:00:00.019s unix socket client side full duplex - block size 262144 - 100.9336 gbit/s in 00:00:00.019s unix socket client side full duplex - block size 131072 - 88.7272 gbit/s in 00:00:00.022s unix socket client side full duplex - block size 65536 - 83.2570 gbit/s in 00:00:00.024s real 0m0.426s user 0m0.085s sys 0m0.331s real 0m0.435s user 0m0.105s sys 0m0.328s real 0m0.423s user 0m0.092s sys 0m0.330s real 0m0.455s user 0m0.106s sys 0m0.346s inet socket client side half duplex - block size 4194304 - 41.2669 gbit/s in 00:00:00.048s inet socket client side half duplex - block size 2097152 - 45.6475 gbit/s in 00:00:00.043s inet socket client side half duplex - block size 1048576 - 49.4035 gbit/s in 00:00:00.040s inet socket client side half duplex - block size 524288 - 46.2171 gbit/s in 00:00:00.043s inet socket client side half duplex - block size 262144 - 42.4133 gbit/s in 00:00:00.047s inet socket client side half duplex - block size 131072 - 38.3230 gbit/s in 00:00:00.052s inet socket client side half duplex - block size 65536 - 29.2942 gbit/s in 00:00:00.068s inet socket client side full duplex - block size 4194304 - 61.3553 gbit/s in 00:00:00.032s inet socket client side full duplex - block size 2097152 - 81.3670 gbit/s in 00:00:00.024s inet socket client side full duplex - block size 1048576 - 88.0088 gbit/s in 00:00:00.022s inet socket client side full duplex - block size 524288 - 80.9585 gbit/s in 00:00:00.024s inet socket client side full duplex - block size 262144 - 73.5646 gbit/s in 00:00:00.027s inet socket client side full duplex - block size 131072 - 62.3053 gbit/s in 00:00:00.032s inet socket client side full duplex - block size 65536 - 49.4230 gbit/s in 00:00:00.040s real 0m0.551s user 0m0.087s sys 0m0.374s real 0m0.567s user 0m0.077s sys 0m0.399s real 0m0.564s user 0m0.100s sys 0m0.377s real 0m0.559s user 0m0.084s sys 0m0.386s
caching: O_DEFAULT bsizex: 536870912 clock_0 - transfer speed: 11.3790 gbit/s | reading speed 21.4227 gbit/s | writing speed 24.2709 gbit/s caching: O_DEFAULT bsizex: 268435456 clock_0 - transfer speed: 15.5011 gbit/s | reading speed 40.9989 gbit/s | writing speed 24.9251 gbit/s caching: O_DEFAULT bsizex: 134217728 clock_0 - transfer speed: 17.6066 gbit/s | reading speed 54.9851 gbit/s | writing speed 25.9002 gbit/s caching: O_DEFAULT bsizex: 67108864 clock_0 - transfer speed: 18.6125 gbit/s | reading speed 65.4611 gbit/s | writing speed 26.0074 gbit/s caching: O_DEFAULT bsizex: 33554432 clock_0 - transfer speed: 18.7383 gbit/s | reading speed 70.8724 gbit/s | writing speed 25.4736 gbit/s caching: O_DEFAULT bsizex: 16777216 clock_0 - transfer speed: 19.6474 gbit/s | reading speed 79.3714 gbit/s | writing speed 26.1109 gbit/s caching: O_DEFAULT bsizex: 8388608 clock_0 - transfer speed: 23.3509 gbit/s | reading speed 128.1086 gbit/s | writing speed 28.5562 gbit/s caching: O_DEFAULT bsizex: 4194304 clock_0 - transfer speed: 23.5222 gbit/s | reading speed 133.3489 gbit/s | writing speed 28.5604 gbit/s caching: O_DEFAULT bsizex: 2097152 clock_0 - transfer speed: 23.5681 gbit/s | reading speed 132.9610 gbit/s | writing speed 28.6459 gbit/s caching: O_DEFAULT bsizex: 1048576 clock_0 - transfer speed: 23.5237 gbit/s | reading speed 133.5069 gbit/s | writing speed 28.5552 gbit/s caching: O_DEFAULT bsizex: 524288 clock_0 - transfer speed: 23.4491 gbit/s | reading speed 132.4745 gbit/s | writing speed 28.4927 gbit/s caching: O_DEFAULT bsizex: 262144 clock_0 - transfer speed: 23.4734 gbit/s | reading speed 130.9693 gbit/s | writing speed 28.5994 gbit/s caching: O_DEFAULT bsizex: 131072 clock_0 - transfer speed: 23.4796 gbit/s | reading speed 129.3243 gbit/s | writing speed 28.6883 gbit/s caching: O_DEFAULT bsizex: 65536 clock_0 - transfer speed: 23.2556 gbit/s | reading speed 126.6223 gbit/s | writing speed 28.4880 gbit/s caching: O_DEFAULT bsizex: 32768 clock_0 - transfer speed: 22.6672 gbit/s | reading speed 119.8358 gbit/s | writing speed 27.9552 gbit/s caching: O_DEFAULT bsizex: 16384 clock_0 - transfer speed: 21.3938 gbit/s | reading speed 105.1884 gbit/s | writing speed 26.8562 gbit/s caching: O_DEFAULT bsizex: 8192 clock_0 - transfer speed: 19.5415 gbit/s | reading speed 84.7934 gbit/s | writing speed 25.3938 gbit/s caching: O_DEFAULT bsizex: 4096 clock_0 - transfer speed: 16.4048 gbit/s | reading speed 61.8558 gbit/s | writing speed 22.3260 gbit/s caching: O_DEFAULT bsizex: 2048 clock_0 - transfer speed: 11.8012 gbit/s | reading speed 42.9604 gbit/s | writing speed 16.2708 gbit/s caching: O_DEFAULT bsizex: 1024 clock_0 - transfer speed: 7.2613 gbit/s | reading speed 22.7643 gbit/s | writing speed 10.6624 gbit/s caching: O_DEFAULT bsizex: 512 clock_0 - transfer speed: 4.1195 gbit/s | reading speed 11.7396 gbit/s | writing speed 6.3466 gbit/s real 0m14.210s user 0m0.442s sys 0m13.229s real 0m14.832s user 0m0.460s sys 0m13.828s real 0m14.136s user 0m0.437s sys 0m13.147s real 0m14.181s user 0m0.461s sys 0m13.181s
caching: O_DEFAULT bsizex: 536870912 clock_0 - transfer speed: 10.4430 gbit/s | reading speed 19.2180 gbit/s | writing speed 22.8715 gbitb/s caching: O_DEFAULT bsizex: 268435456 clock_0 - transfer speed: 14.5036 gbit/s | reading speed 40.0894 gbit/s | writing speed 22.7252 gbitb/s caching: O_DEFAULT bsizex: 134217728 clock_0 - transfer speed: 15.6014 gbit/s | reading speed 49.3970 gbit/s | writing speed 22.8038 gbitb/s caching: O_DEFAULT bsizex: 67108864 clock_0 - transfer speed: 15.9606 gbit/s | reading speed 57.2996 gbit/s | writing speed 22.1232 gbitb/s caching: O_DEFAULT bsizex: 33554432 clock_0 - transfer speed: 18.0972 gbit/s | reading speed 74.6408 gbit/s | writing speed 23.8895 gbitb/s caching: O_DEFAULT bsizex: 16777216 clock_0 - transfer speed: 20.0400 gbit/s | reading speed 97.7219 gbit/s | writing speed 25.2100 gbitb/s caching: O_DEFAULT bsizex: 8388608 clock_0 - transfer speed: 21.6595 gbit/s | reading speed 121.1607 gbit/s | writing speed 26.3745 gbitb/s caching: O_DEFAULT bsizex: 4194304 clock_0 - transfer speed: 22.1617 gbit/s | reading speed 130.3016 gbit/s | writing speed 26.7036 gbitb/s caching: O_DEFAULT bsizex: 2097152 clock_0 - transfer speed: 22.1685 gbit/s | reading speed 129.2783 gbit/s | writing speed 26.7568 gbitb/s caching: O_DEFAULT bsizex: 1048576 clock_0 - transfer speed: 22.1710 gbit/s | reading speed 128.9511 gbit/s | writing speed 26.7747 gbitb/s caching: O_DEFAULT bsizex: 524288 clock_0 - transfer speed: 22.2617 gbit/s | reading speed 128.1476 gbit/s | writing speed 26.9425 gbitb/s caching: O_DEFAULT bsizex: 262144 clock_0 - transfer speed: 22.4473 gbit/s | reading speed 128.1538 gbit/s | writing speed 27.2142 gbitb/s caching: O_DEFAULT bsizex: 131072 clock_0 - transfer speed: 22.4262 gbit/s | reading speed 126.7889 gbit/s | writing speed 27.2456 gbitb/s caching: O_DEFAULT bsizex: 65536 clock_0 - transfer speed: 22.2661 gbit/s | reading speed 124.2854 gbit/s | writing speed 27.1259 gbitb/s caching: O_DEFAULT bsizex: 32768 clock_0 - transfer speed: 21.9235 gbit/s | reading speed 119.8753 gbit/s | writing speed 26.8307 gbitb/s caching: O_DEFAULT bsizex: 16384 clock_0 - transfer speed: 20.7082 gbit/s | reading speed 105.6189 gbit/s | writing speed 25.7588 gbitb/s caching: O_DEFAULT bsizex: 8192 clock_0 - transfer speed: 18.7807 gbit/s | reading speed 85.4591 gbit/s | writing speed 24.0706 gbitb/s caching: O_DEFAULT bsizex: 4096 clock_0 - transfer speed: 16.1803 gbit/s | reading speed 63.4216 gbit/s | writing speed 21.7222 gbitb/s caching: O_DEFAULT bsizex: 2048 clock_0 - transfer speed: 11.4014 gbit/s | reading speed 44.0279 gbit/s | writing speed 15.3858 gbitb/s caching: O_DEFAULT bsizex: 1024 clock_0 - transfer speed: 7.1768 gbit/s | reading speed 23.9776 gbit/s | writing speed 10.2426 gbitb/s caching: O_DEFAULT bsizex: 512 clock_0 - transfer speed: 4.0060 gbit/s | reading speed 12.3397 gbit/s | writing speed 5.9316 gbitb/s real 0m14.900s user 0m0.352s sys 0m13.909s real 0m15.107s user 0m0.385s sys 0m13.966s real 0m14.625s user 0m0.359s sys 0m13.694s real 0m14.451s user 0m0.383s sys 0m13.513s
caching: O_DEFAULT bsizex: 536870912 clock_0 - transfer speed: 11.5127 gbit/s | reading speed 21.2377 gbit/s | writing speed 25.1422 gbitb/s caching: O_DEFAULT bsizex: 268435456 clock_0 - transfer speed: 15.1280 gbit/s | reading speed 39.6857 gbit/s | writing speed 24.4474 gbitb/s caching: O_DEFAULT bsizex: 134217728 clock_0 - transfer speed: 16.4836 gbit/s | reading speed 49.2687 gbit/s | writing speed 24.7714 gbitb/s caching: O_DEFAULT bsizex: 67108864 clock_0 - transfer speed: 17.7945 gbit/s | reading speed 61.0054 gbit/s | writing speed 25.1226 gbitb/s caching: O_DEFAULT bsizex: 33554432 clock_0 - transfer speed: 19.4765 gbit/s | reading speed 77.8634 gbit/s | writing speed 25.9736 gbitb/s caching: O_DEFAULT bsizex: 16777216 clock_0 - transfer speed: 22.3610 gbit/s | reading speed 111.1173 gbit/s | writing speed 27.9949 gbitb/s caching: O_DEFAULT bsizex: 8388608 clock_0 - transfer speed: 23.1920 gbit/s | reading speed 123.8179 gbit/s | writing speed 28.5377 gbitb/s caching: O_DEFAULT bsizex: 4194304 clock_0 - transfer speed: 23.3520 gbit/s | reading speed 127.3784 gbit/s | writing speed 28.5944 gbitb/s caching: O_DEFAULT bsizex: 2097152 clock_0 - transfer speed: 23.3874 gbit/s | reading speed 128.7250 gbit/s | writing speed 28.5801 gbitb/s caching: O_DEFAULT bsizex: 1048576 clock_0 - transfer speed: 23.2396 gbit/s | reading speed 128.2874 gbit/s | writing speed 28.3811 gbitb/s caching: O_DEFAULT bsizex: 524288 clock_0 - transfer speed: 23.1285 gbit/s | reading speed 126.8130 gbit/s | writing speed 28.2881 gbitb/s caching: O_DEFAULT bsizex: 262144 clock_0 - transfer speed: 23.0948 gbit/s | reading speed 125.0391 gbit/s | writing speed 28.3270 gbitb/s caching: O_DEFAULT bsizex: 131072 clock_0 - transfer speed: 22.7048 gbit/s | reading speed 126.0140 gbit/s | writing speed 27.6950 gbitb/s caching: O_DEFAULT bsizex: 65536 clock_0 - transfer speed: 22.4669 gbit/s | reading speed 121.1185 gbit/s | writing speed 27.5836 gbitb/s caching: O_DEFAULT bsizex: 32768 clock_0 - transfer speed: 21.3464 gbit/s | reading speed 110.2566 gbit/s | writing speed 26.4716 gbitb/s caching: O_DEFAULT bsizex: 16384 clock_0 - transfer speed: 20.6017 gbit/s | reading speed 101.1161 gbit/s | writing speed 25.8734 gbitb/s caching: O_DEFAULT bsizex: 8192 clock_0 - transfer speed: 19.3454 gbit/s | reading speed 85.6852 gbit/s | writing speed 24.9870 gbitb/s caching: O_DEFAULT bsizex: 4096 clock_0 - transfer speed: 16.0426 gbit/s | reading speed 61.1331 gbit/s | writing speed 21.7505 gbitb/s caching: O_DEFAULT bsizex: 2048 clock_0 - transfer speed: 12.0249 gbit/s | reading speed 45.3245 gbit/s | writing speed 16.3673 gbitb/s caching: O_DEFAULT bsizex: 1024 clock_0 - transfer speed: 7.5059 gbit/s | reading speed 23.8823 gbit/s | writing speed 10.9462 gbitb/s caching: O_DEFAULT bsizex: 512 clock_0 - transfer speed: 4.2159 gbit/s | reading speed 12.3522 gbit/s | writing speed 6.4004 gbitb/s real 0m14.336s user 0m0.490s sys 0m13.655s real 0m14.632s user 0m0.469s sys 0m13.980s real 0m14.276s user 0m0.452s sys 0m13.649s real 0m14.369s user 0m0.441s sys 0m13.719s
unix socket client side half duplex - block size 4194304 - 48.6606 gbit/s in 00:00:00.041s unix socket client side half duplex - block size 2097152 - 39.0594 gbit/s in 00:00:00.051s unix socket client side half duplex - block size 1048576 - 43.9512 gbit/s in 00:00:00.045s unix socket client side half duplex - block size 524288 - 56.1577 gbit/s in 00:00:00.035s unix socket client side half duplex - block size 262144 - 57.1135 gbit/s in 00:00:00.035s unix socket client side half duplex - block size 131072 - 55.9237 gbit/s in 00:00:00.035s unix socket client side half duplex - block size 65536 - 49.5638 gbit/s in 00:00:00.040s unix socket client side full duplex - block size 4194304 - 70.1681 gbit/s in 00:00:00.028s unix socket client side full duplex - block size 2097152 - 93.5016 gbit/s in 00:00:00.021s unix socket client side full duplex - block size 1048576 - 102.6958 gbit/s in 00:00:00.019s unix socket client side full duplex - block size 524288 - 100.2557 gbit/s in 00:00:00.019s unix socket client side full duplex - block size 262144 - 99.3542 gbit/s in 00:00:00.020s unix socket client side full duplex - block size 131072 - 95.4654 gbit/s in 00:00:00.020s unix socket client side full duplex - block size 65536 - 88.6368 gbit/s in 00:00:00.022s real 0m0.441s user 0m0.091s sys 0m0.347s real 0m0.419s user 0m0.085s sys 0m0.332s real 0m0.410s user 0m0.089s sys 0m0.320s real 0m0.418s user 0m0.088s sys 0m0.328s inet socket client side half duplex - block size 4194304 - 39.0976 gbit/s in 00:00:00.051s inet socket client side half duplex - block size 2097152 - 46.3779 gbit/s in 00:00:00.043s inet socket client side half duplex - block size 1048576 - 48.3162 gbit/s in 00:00:00.041s inet socket client side half duplex - block size 524288 - 44.9600 gbit/s in 00:00:00.044s inet socket client side half duplex - block size 262144 - 41.6684 gbit/s in 00:00:00.047s inet socket client side half duplex - block size 131072 - 37.5009 gbit/s in 00:00:00.053s inet socket client side half duplex - block size 65536 - 30.3324 gbit/s in 00:00:00.065s inet socket client side full duplex - block size 4194304 - 63.0656 gbit/s in 00:00:00.031s inet socket client side full duplex - block size 2097152 - 83.6890 gbit/s in 00:00:00.023s inet socket client side full duplex - block size 1048576 - 87.1004 gbit/s in 00:00:00.022s inet socket client side full duplex - block size 524288 - 79.0701 gbit/s in 00:00:00.025s inet socket client side full duplex - block size 262144 - 71.5871 gbit/s in 00:00:00.027s inet socket client side full duplex - block size 131072 - 60.8069 gbit/s in 00:00:00.032s inet socket client side full duplex - block size 65536 - 49.5884 gbit/s in 00:00:00.040s real 0m0.555s user 0m0.082s sys 0m0.386s real 0m0.556s user 0m0.087s sys 0m0.381s real 0m0.555s user 0m0.067s sys 0m0.400s real 0m0.563s user 0m0.078s sys 0m0.396s
