【DB吐槽大会】第12期 - 没有自动成本校准器

背景

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工具.