Thanks Ashutosh for coming:) On Mon, Sep 21, 2020 at 9:03 PM Ashutosh Bapat <ashutosh.bapat....@gmail.com> wrote:
> On Mon, Sep 21, 2020 at 9:11 AM Andy Fan <zhihui.fan1...@gmail.com> wrote: > > > > Here are some changes for my detection program. > > > > | | seq_read_lat (us) | > random_read_lat (us) | > > | FIO | 12 | > 148 | > > | Previous main.c | 8.5 | > 74 | > > | invalidate_device_cache before each testing | 9 | > 150 | > > | prepare the test data file with O_DIRECT option | 15 | > 150 | > > > > In invalidate_device_cache, I just create another 1GB data file and read > > it. (see invalidate_device_cache function) this is similar as the > previous fio setup. > > > > prepare test data file with O_DIRECT option means in the past, I prepare > the test > > file with buffer IO. and before testing, I do invalidate device cache, > file > > system cache. but the buffered prepared file still get better > performance, I > > have no idea of it. Since I don't want any cache. I use O_DIRECT > > option at last. The seq_read_lat changed from 9us to 15us. > > I still can't find out the 25% difference with the FIO result. (12 us vs > 9 us). > > > > At last, the random_page_cost happens to not change very much. > > > > /u/y/g/fdirect> sudo ./main > > fs_cache_lat = 0.569031us, seq_read_lat = 18.901749us, random_page_lat = > 148.650589us > > > > cache hit ratio: 1.000000 random_page_cost 1.000000 > > cache hit ratio: 0.900000 random_page_cost 6.401019 > > cache hit ratio: 0.500000 random_page_cost 7.663772 > > cache hit ratio: 0.100000 random_page_cost 7.841498 > > cache hit ratio: 0.000000 random_page_cost 7.864383 > > > > This result looks much different from "we should use 1.1 ~ 1.5 for SSD". > > > > Very interesting. Thanks for working on this. In an earlier email you > mentioned that TPCH plans changed to efficient ones when you changed > random_page_cost = =8.6 from 4 and seq_page_cost was set to 1. IIUC, > setting random_page_cost to seq_page_cost to the same ratio as that > between the corresponding latencies improved the plans. Yes. How about > trying this with that ratio set to the one obtained from the latencies > provided by FIO? Do we see any better plans? > My tools set the random_page_cost to 8.6, but based on the fio data, it should be set to 12.3 on the same hardware. and I do see the better plan as well with 12.3. Looks too smooth to believe it is true.. The attached result_fio_mytool.tar.gz is my test result. You can use git show HEAD^^ is the original plan with 8.6. git show HEAD^ show the plan changes after we changed the random_page_cost. git show HEAD shows the run time statistics changes for these queries. I also uploaded the test tool [1] for this for your double check. | | 8.6 | 12.3 | |-----+----------+----------| | Q2 | 2557.064 | 2444.995 | | Q4 | 3544.606 | 3148.884 | | Q7 | 2965.820 | 2240.185 | | Q14 | 4988.747 | 4931.375 | > > page cost is one thing, but there are CPU costs also involved in costs > of expression evaluation. Should those be changed accordingly to the > CPU latency? > > Yes, we need that as well. At the beginning of this thread, I treat all of them equally. In the first reply of Tomas, he mentioned random_page_cost specially. After ~10 months, I tested TPCH on a hardware and then found random_page_cost is set too incorrectly, after fixing it, the result looks much better. So I'd like to work on this special thing first. [1] https://github.com/zhihuiFan/tpch-postgres/blob/master/random_page_cost.sh -- Best Regards Andy Fan
result_fio_mytool.tar.gz
Description: GNU Zip compressed data
