背景
1、产品的问题点
- 不支持优化器成本计算系数自动调整
2、问题点背后涉及的技术原理
- 数据库通过优化器来实现SQL执行计划的最佳选择, 通常最优的执行计划选择标准是代价最低. 而代价则是通过每一个执行node的成本叠加而来, 每一个执行node的成本计算又来自扫描方法(离散、顺序等)、JOIN方法、数据块的多少、返回记录的多少、操作符(函数)的计算次数等组成. 为了达到代价计算的准确性, 需要更丰富、及时的统计信息, 同时需要因子准确性(例如操作符的CPU计算耗费、离散IO、顺序IO等耗费的系数准确性). 如果系数不准确, 即使统计信息准确也无法得到最佳执行计划.
#seq_page_cost = 1.0 # measured on an arbitrary scale
random_page_cost = 1.1 # 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工具.
