> > > It's probably worth testing on various other storage systems to see >> how that applies to those. >> >> Yes, I can test more on new hardware once I get it. Now it is still in > progress. > However I can only get a physical machine with SSD or Virtual machine with > SSD, other types are hard for me right now. > > Here is a result on a different hardware. The test method is still not changed.[1]
Hardware Info: Virtual Machine with 61GB memory. Linux Kernel: 5.4.0-31-generic Ubuntu # lshw -short -C disk H/W path Device Class Description ===================================================== /0/100/4/0 /dev/vda disk 42GB Virtual I/O device /0/100/5/0 /dev/vdb disk 42GB Virtual I/O device The disk on the physical machine is claimed as SSD. This time the FIO and my tools can generate the exact same result. fs_cache_lat = 0.957756us, seq_read_lat = 70.780327us, random_page_lat = 438.837257us cache hit ratio: 1.000000 random_page_cost 1.000000 cache hit ratio: 0.900000 random_page_cost 5.635470 cache hit ratio: 0.500000 random_page_cost 6.130565 cache hit ratio: 0.100000 random_page_cost 6.192183 cache hit ratio: 0.000000 random_page_cost 6.199989 | | seq_read_lat(us) | random_read_lat(us) | | FIO | 70 | 437 | | MY Tool | 70 | 438 | The following query plans have changed because we change random_page_cost to 4 to 6.2, the Execution time also changed. | | random_page_cost=4 | random_page_cost=6.2 | |-----+--------------------+----------------------| | Q1 | 2561 | 2528.272 | | Q10 | 4675.749 | 4684.225 | | Q13 | 18858.048 | 18565.929 | | Q2 | 329.279 | 308.723 | | Q5 | 46248.132 | 7900.173 | | Q6 | 52526.462 | 47639.503 | | Q7 | 27348.900 | 25829.221 | Q5 improved by 5.8 times and Q6 & Q7 improved by ~10%. [1] https://www.postgresql.org/message-id/CAKU4AWpRv50k8E3tC3tiLWGe2DbKaoZricRh_YJ8y_zK%2BHdSjQ%40mail.gmail.com -- Best Regards Andy Fan
