第12期吐槽:SQL执行计划不对？能好就见鬼了！优化器还在用几十年前的参数模板,环境自适应能力几乎为零
文中参考文档点击阅读原文打开, 同时推荐2个学习环境:
1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》
2、有web浏览器就能用的云起实验室: 《免费体验PolarDB开源数据库》
3、PolarDB开源数据库内核、最佳实践等学习图谱: https://www.aliyun.com/database/openpolardb/activity
第12期吐槽:太傻了,不支持根据环境自动校准代价系数!
1、产品的问题点
PG不支持根据环境, 自动调整优化器成本计算公式中的系数值. 我们可以把这些系数理解为环境因素.
现在还在用数十年前硬件条件所使用的系数值, 你觉得它能选出正确的执行计划吗? 例如随机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小工具, 希望有个小工具跑一下之后给出系数的推荐值.
本期彩蛋-转发本文点赞超过30获T恤1件.已经送完了哥哥们厉害哟,期待下次活动
感谢ProtonBase（https://www.protonbase.com）对活动礼品的支持
ProtonBase 是一款数据库与大数据的统一产品，基于分布式 Data Warebase 理念。有兴趣的同学可参考阅读：通往 AGI 之路，数据系统还需挑战哪些物理极限？
活动方法: 转发本条公众号内容, 点赞超过30即可获得T恤1件, 6.1号活动截止之前填加以下微信发截图, 限量, 先到先得。感谢您的转发.
文章中的参考文档请点击阅读原文获得.
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号: