一次火烧眉毛的 PostgreSQL 事故分析

发布: 2022-02-24   更新: 2022-02-26   分类: 数据库   标签: PostgreSQL

文章目录

前几天 PostgreSQL 社区有一篇比较有意思的文章:A Hairy PostgreSQL Incident备份),讲述了一个由于升级 PostgreSQL 导致线上出现慢查询的排查过程,作者写的非常诙谐幽默,这里简单复述下相关过程:

  1. 从之前某个版本升级 11 后,有一个服务出现慢查询,应急人员已经定位到导致问题的模块,但还不清楚与原因
  2. pg_stat_activity 由于包含大量 in (...) 的查询,无法定位出耗时高的 SQL
  3. 有一个可供对比的老版本数据库,无查询慢的问题
  4. 该文作者尝试做两个环境下,数据库的火焰图,但还是没明显线索
  5. 经过作者一番脑补分析,重新审视火焰图,发现了细微差别
  6. 成功复现,网上搜索找到了一个 hack 的方案,重新发布,结束

具体细节可以去阅读原文,该文一个美中不足的地方是没进行根因分析,找到修复方案后就没继续跟踪了,笔者正好最近读完了 The Internals of PostgreSQL,正好分析下该文的故障,来巩固相关知识点,看看从这次事故中能学到什么。

pg_stat_activity

原文评论中,有人提到可以用 where id = any() 来代替 where id in .. ,理由是这样在 pg_stat_activity 里面相似查询只会有一个 query id,便于查找问题 SQL。它俩的具体区别可参考下面这个问题:

这算是一个知识点,但更重要的是 pg_stat_activity 应该要记录哪些超时的 SQL,而不能仅仅只包含执行成功的,超时的往往意味着问题更大,从原文看是没有记录。笔者在设计系统时也犯类似错误,日志里忽略掉了那些超时的错误请求。

Bitmap Index Scan

一般来说,数据库为了支持加速查询,会使用 B+Tree 来做索引,这里的 bitmap index 又是做什么用的呢?

对于类似 where id = ? 的点查来说,先查索引,再查数据的方式是效率最高的,但是对于区间查询来说,情况就复杂了,极端情况就是区间查询会涉及到大量数据,先去查索引,再根据索引去查数据,就不如直接顺序扫描表了,因为索引对应的数据不是连续的,会造成大量随机 IO,而随机 IO 效率是很差的。

介于『先查索引再查数据表』与『直接扫描表』中间的,就是 bitmap index scan 了,这种方式通过索引查到合适的行时,不会立马去查对于的数据,而是记录在一个 bitmap 中,等查完索引后,再根据 bitmap 去顺序扫表即可。(图片来源

https://img.alicdn.com/imgextra/i3/581166664/O1CN01YMvPiT1z6A7jpJxZe_!!581166664.jpg
Bitmap Index Scan 示意图

Cost-based Query plan

关系型数据库在进行查询计划的优化时,一般会采用基于 cost 的优化器,相比启发式的优化器更灵活、更有效,他们的区别不是本文重点,感兴趣的读者可参考:How We Built a Cost-Based SQL Optimizer,这里不再赘述。优化器可以说是不同数据库差距最大的地方,那么 PostgreSQL 是怎么计算 cost 的呢?

先回到原文,造成慢查询的原因是 PostgreSQL 选择了一个不合适的索引(206G),相比好的查询,多了一个 BitmapAnd 操作,相关 SQL 如下:

1
2
3
4
SELECT *
FROM table
WHERE (text_col1, numeric_col3) IN (('first1',1),('second1',2), ... )
  AND timestamp_col1 <= :1

该表本身 845G,有两个二级索引:

  • index1, (text_col1, text_col2) 229G
  • index2, (numeric_col1, numeric_col2, numeric_col3, timestamp_col1) 206G

正常时候的查询计划如下(后文称为 PA):

 Bitmap Heap Scan on table  (cost=3791.09..80234213.68 rows=6713 width=273) (actual time=74.911..74.953 rows=0 loops=1)
   Output: <all columns>
   Recheck Cond: (((text_col1)::text = 'first1'::text) OR ... OR ((text_col1)::text = 'second1'::text))
   Filter: ((timestamp_col1 <= '2022-02-09 00:00:00'::timestamp without time zone) AND ((((text_col1)::text = 'first1'::text) AND ... AND (numeric_col3 = '2'::numeric))))
   ->  BitmapOr  (cost=3791.09..3791.09 rows=212565 width=0) (actual time=74.905..74.946 rows=0 loops=1)
         ->  Bitmap Index Scan on index1  (cost=0.00..38.65 rows=2261 width=0) (actual time=0.026..0.026 rows=0 loops=1)
               Index Cond: ((text_col1)::text = 'first1'::text)
         ->  Bitmap Index Scan on index1  (cost=0.00..38.65 rows=2261 width=0) (actual time=0.446..0.446 rows=0 loops=1)
               Index Cond: ((text_col1)::text = 'second1'::text)
         ->  Bitmap Index Scan on index1  (cost=0.00..38.65 rows=2261 width=0) (actual time=0.447..0.447 rows=0 loops=1)
               Index Cond: ((text_col1)::text = 'third1'::text)

出问题时候的查询计划如下(后文称为 PB):

 Bitmap Heap Scan on table  (cost=50887511.91..3195672174.06 rows=73035 width=24)
   Recheck Cond: ((((text_col1)::text = 'first1'::text) OR ...
     ...OR ((text_col1)::text = 'second1'::text)) AND (timestamp_col1 <= '2021-02-09 00:00:00'::timestamp without time zone))
   Filter: ((((text_col1)::text = 'first1'::text) AND (numeric_col3 = '1'::numeric)) OR ...
    ...OR (((text_col1)::text = 'second1'::text) AND (numeric_col3 = '2'::numeric)))
   ->  BitmapAnd  (cost=50887511.91..50887511.91 rows=418910 width=0)
         ->  BitmapOr  (cost=13320.96..13320.96 rows=513729 width=0)
               ->  Bitmap Index Scan on index1  (cost=0.00..36.56 rows=2114 width=0)
                     Index Cond: ((text_col1)::text = 'first1'::text)
               ->  Bitmap Index Scan on index1  (cost=0.00..36.56 rows=2114 width=0)
                     Index Cond: ((text_col1)::text = 'second1'::text)
               ->  Bitmap Index Scan on index1  (cost=0.00..36.56 rows=2114 width=0)
                     Index Cond: ((text_col1)::text = 'third1'::text)
         ->  Bitmap Index Scan on index2  (cost=0.00..50874172.44 rows=2597695782 width=0)
               Index Cond: (timestamp_col1 <= '2021-02-09 00:00:00'::timestamp without time zone)
(493 rows)

触发 BitmapAnd 的条件是 timestamp 列,在 2022-02-09 时没有 BitmapAnd,改成 2021-02-09 时则会出现。根据原文描述,timestamp 的取值范围很少。

一般正常来说,随着时间增加,数据量越来越多,即 timestamp<=2021-02-09 匹配的结果小于 timestamp<=2022-02-09 ,根据上面 bitmap index scan 的知识,这里推测下 PostgreSQL 的决策逻辑,不一定正确,仅供参考:

  • 在小于 2022 时,基本上所有数据都符合条件,如果这时再去选择 bitmap index scan,就不如直接顺序扫表了,又由于还有另一个条件,因此就选择直接 scan 它的结果集
  • 当小于 2021 时,匹配的结果相比上述条件会减少,PostgreSQL 认为 bitmap index scan 相比顺序扫表更合适,因此 bitmap 的方式就用上了,貌似 PostgreSQL 没有考虑其他 where 条件可能会过滤掉大部分数据,而是针对每个 where 条件,分别计算的 cost。

但这里有个明显的问题,在 where 条件涉及两个索引时,用其中一个条件则可以过滤掉大部分数据(可以看到,PB 的 BitmapOr 预估输入行只有 513729),第二个索引不一定要用得上,可以直接在第一个条件的结果集上进行过滤即可,这其实就是 PA 的计划:

  • 有 212565 条数据符合条件一(in 语句)
  • 把条件二(时间戳小于语句)应用到条件一的结果集上后,会有 6713 条记录

如何计算 cost

对于一次数据查询,分为两个过程:准备阶段(start_up_cost)与执行阶段(run_cost)。下面来介绍不同扫描方式下的 cost 计算。

顺序扫表

start_up_cost = 0
run_cost = cpu_run_cost + disk_run_cost
         = (cpu_tuple_cost + cpu_operator_cost) * NumOfTuple + seq_page_cost * NumOfPage
         = (0.001 + 0.0025) * NumOfTuple + 1.0 * NumOfPage

需要注意的是,上面参数没有单位,用的是相对概念,比如 sql_page_cost 表示顺序扫 page 的代价,默认是 1.0,与之对应的参数是 random_page_cost 默认值是 4.0,表示随机扫比顺序扫慢四倍,这是基于 HDD 盘的假设,对于 SSD 或者内存来说,就有些大了。这里有个真实案例供参考:

索引扫描

start_up_cost = (ceil(log2 NumOfTuple) + HeightOfIndex + 1) * 50 * cpu_operator_cost
run_cost = index_cpu_cost + table_cpu_cost + index_io_cost + table_io_cost
# 其中
index_cpu_cost = selectivity * NumOfIndexTuple * (cpu_index_tuple_cost + qual_op_cost)
table_cpu_cost = selectivity * NumOfTuple * (cpu_tuple_cost)
index_io_cost = ceil(selectivity * NumOfPage) * random_page_cost
table_io_cost = max_io_cost + indexCorrelation^2 *(min_io_cost - max_io_cost)
# 其中
max_io_cost = NumOfPage * random_page_cost
min_io_cost = (1 * random_page_cost + ceil(selectivity * NumOfPage) - 1) * seq_page_cost
  • 准备阶段只考虑从索引树的 root 开始,遍历到叶子节点,并将这个 page 的第一个 tuple 的读入为止,不需要读取到符合 where 条件的 tuple
  • HeightOfIndex 表示 index 树的高度
  • 运行阶段涉及到索引与表,是它们两者 CPU 与 IO cost 的和
  • selectivity 表示 where 条件的选择性,主要有两种计算方式:MCV 与 histogram,这里不再展开介绍,可参考 The Internals of PostgreSQL : Chapter 3 Query Processing 相关章节
  • indexCorrelation 表示索引的相关性,表示的是一个行的物理存储顺序与其值顺序的相关性,取值范围 [-1, 1] ,越靠近两侧,相关性越高,cost 代价也越低
  • max_io_cost 表示 IO 的最坏 cost,即随机扫
  • min_io_cost 表示 IO 最好的 cost, 也就是顺序扫描所选表的所有页
https://img.alicdn.com/imgextra/i4/581166664/O1CN01DZA2431z6A7lgbUrt_!!581166664.jpg
索引相关性示意图

这里只是介绍了索引扫描 cost 的基本计算方式,需要结合实际案例分析来巩固,可以参考下面这篇文章,该作者从问题出发,一步步深入分析了慢 SQL 产生的原因,如何改变查询计划来解决问题,甚至还有源码分析,非常具有参考价值,值得反复阅读

Query Hint

在该文评论中,批评最为激烈的是:都 2022 年了,为什么 PostgreSQL 不支持 query hint,而要用 CTE 这种 hack 的方式!关于这个问题 PostgreSQL 社区也讨论了很久,甚至有一个专门的 WIKI 页面:

核心论点如下:

  • Hint 是一种解决问题的临时方案,随着数据的增长,数据分布可能会不一样,因此 hint 可能会失效
  • PostgreSQL 作为世界上最先进的开源数据库,在多数情况下,比 DBA 更了解如何执行一个 query
  • Hint 是很多商业数据库的主要卖点,算是一种商业模式,但这并不代表 hint 的必要性
  • 相比之前带有 hint 的数据库,PostgreSQL 可以通过调整参数来达到改变 query plan,2ndQuadrant 公司的 Hinting at PostgreSQL 这篇文章,有更具体介绍

看到这部分时,突然想到知乎上的一个问题:

早期互联网的发展讲究的是『快糙猛』,出了问题要赶紧修,管他什么原理,先修复了再说,而这种观点正是 PostgreSQL 所摒弃的。而 PostgreSQL 近几年又有燎原之势,恰恰说明了其先进性,之前的数据库无法满足业务的复杂查询需求。

https://img.alicdn.com/imgextra/i1/581166664/O1CN01rCAJ1i1z6A7YO7idU_!!581166664.jpg
db-engine 常见关系型数据趋势图

当然,PostgreSQL 也不是完美的,Hint 也不是一文不值,在看 PostgreSQL Optimizer Methodology (Robert Haas) - YouTube 时,作者就介绍了 PostgreSQL 一个不能很好处理的 case:

https://img.alicdn.com/imgextra/i4/581166664/O1CN0148MqIJ1z6A7Tb3rxd_!!581166664.jpg
The Most Annoying Optimizer Fail in PostgreSQL

Robert Haas 是 PostgreSQL 的主要贡献者,EnterpriseDB 的 VP,他在 Talking about the PostgreSQL Optimizer at CMU 一文中也承认了 hint 的价值。

总结

PostgreSQL 作为世界上最先进的开源数据库,是业界许多产品的鼻祖,比如华为的 GaussDB,MPP 架构的 Greenplum 等。在没看 The Internals of PostgreSQL 之前,一直以为计算 cost 是多深不可测的东西,但其实这都是心理作用,再复杂的逻辑也是一行行代码写出来。当然笔者的 PostgreSQL 学习之路也才刚开始,文中难免有不足之处,敬请包涵和指教。

扩展阅读

评论

欢迎读者通过邮件与我交流,也可以在 MastodonTwitter 上关注我。