digoal
2021-08-30
PostgreSQL , 自动成本校准器
1、产品的问题点
- 不支持根据环境, 自动调整优化器成本计算公式中的系数值. 我们可以把这些系数理解为环境因素.
- 现在还在用数十年前硬件条件所使用的系数值, 你觉得它能选出正确的执行计划吗? 例如随机IO和顺序IO的代价比是4, 那是机械盘时代的特征, 现在都2024了, 全面SSD化. 其他CPU等相关算子系数就更不用说了.
很多人寻找数据库的标准配置模板, 我想告诉你, 没有! 一方面是环境不同, 另一方面是负载特征不同.
炮弹要命中目标, 除了万有引力, 还得考虑当时的风向、风速等, 甚至不同的地方可能引力系数也不一样(例如北极和赤道, 地球和月球相比).
声音在不同环境的传播速度是不一样的, 数据库的各种扫描操作、CPU计算等, 在不同的硬件环境相对速度也不一样:
- 常温15° 下，固体中的声速约为5200米每秒，液体中的声速约为1500米每秒，空气中的声速15摄氏度约340米每秒。
- 例如声音在固体传播速度：大理石 3810m/s，铝（棒） 5000m/s，铁（棒) 5200m/s；
- 在液体传播速度：海水（25℃） 1531m/s，冰 3230m/s ；
- 在气体传播速度：空气(0℃) 331m/s ，空气（15℃） 340m/s。
2、问题点背后涉及的技术原理
- 数据库通过优化器来实现SQL执行计划的最佳选择, 通常最优的执行计划选择标准是代价最低. 而代价则是通过每一个执行node的成本叠加而来, 每一个执行node的成本计算又来自扫描方法(离散、顺序等)、JOIN方法、数据块的多少、返回记录的多少、操作符(函数)的计算次数等组成. 为了达到代价计算的准确性, 需要更丰富、及时的统计信息, 同时需要因子准确性(例如操作符的CPU计算耗费、离散IO、顺序IO等耗费的系数准确性). 如果系数不准确, 即使统计信息准确也无法得到最佳执行计划.
#seq_page_cost = 1.0 # measured on an arbitrary scale
random_page_cost = 4 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#parallel_setup_cost = 1000.0 # same scale as above
#parallel_tuple_cost = 0.1 # same scale as above
#min_parallel_table_scan_size = 8MB
#min_parallel_index_scan_size = 512kB
#effective_cache_size = 4GB
3、这个问题将影响哪些行业以及业务场景
- 所有行业
4、会导致什么问题?
- 系数取决于硬件环境, 不同的硬件环境应该配置不同的系数, 例如机械盘的离散IO性能比顺序IO性能差很多, 然而SSD的离散IO和顺序IO性能差不多.
- 系数配置不正确导致执行计划不准确, 性能差劲. 例如应该使用index scan的可能会使用seq scan或bitmap index scan.
5、业务上应该如何避免这个坑
- 校准成本代价系数
6、业务上避免这个坑牺牲了什么, 会引入什么新的问题
- 管理成本较高, 每一批硬件应该提前测试出合理的代价系数. 一般用户不懂, 操作也非常复杂.
- 《优化器成本因子校对(disk,ssd,memory IO开销精算) - PostgreSQL real seq_page_cost & random_page_cost in disks,ssd,memory》
- 《优化器成本因子校对 - PostgreSQL explain cost constants alignment to timestamp》
7、数据库未来产品迭代如何修复这个坑
- 希望有个硬件库对应的系数表, 或者可以根据环境自动化测试出对应的系数. 类似pg内置的pg_test_fsync, pg_test_timing小工具, 希望有个小工具跑一下之后给出系数的推荐值.
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
