Postgres Pro
Downloads
Home   >   mailing lists

Re: Dynamic gathering the values for seq_page_cost/xxx_cost - Mailing list pgsql-hackers

From Andy Fan
Subject Re: Dynamic gathering the values for seq_page_cost/xxx_cost
Date
Msg-id CAKU4AWq3W61vC0R46gyJxEgfEV7VV3-MdTHjRKCL0kqjFB0Ujw@mail.gmail.com
Whole thread Raw
In response to Re: Dynamic gathering the values for seq_page_cost/xxx_cost  (Andy Fan <zhihui.fan1213@gmail.com>)
List pgsql-hackers
Tree view

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%.

-- 
Best Regards
Andy Fan

pgsql-hackers by date:

Previous
From: Andy Fan
Date:
Subject: Re: Dynamic gathering the values for seq_page_cost/xxx_cost
Next
From: Amit Kapila
Date:
Subject: Re: Logical replication from PG v13 and below to PG v14 (devel version) is not working.