慢查询优化

使用Pigsty优化慢查询的一个例子

下面以Pigsty自带的沙箱环境为例,介绍一个使用Pigsty监控系统处理慢查询的过程。

慢查询:模拟

因为没有实际的业务系统,这里我们以一种简单快捷的方式模拟系统中的慢查询。即pgbench自带的tpc-c

在主库上执行以下命令

ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey ;

该命令会移除 pgbench_accounts 表上的主键,导致相关查询变慢,系统瞬间雪崩过载。

图1:单个从库实例的QPS从500下降至7,Query RT下降至300ms

图2:系统负载达到200%,触发机器负载过大,与查询响应时间过长的报警规则。

慢查询:定位

首先,使用PG Cluster面板定位慢查询所在的具体实例,这里以 pg-test-2为例

然后,使用PG Query面板定位具体的慢查询:编号为 -6041100154778468427

图3:从查询总览中发现异常慢查询

该查询表现出:

  • 响应时间显著上升: 17us 升至 280ms
  • QPS 显著下降: 从500下降到 7
  • 花费在该查询上的时间占比显著增加

可以确定,就是这个查询变慢了!

接下来,利用PG Stat Statements面板或PG Query Detail,根据查询ID定位慢查询的具体语句。

图4:定位的查询是SELECT abalance FROM pgbench_accounts WHERE aid = $1

慢查询:猜想

接下来,我们需要推断慢查询产生的原因。

SELECT abalance FROM pgbench_accounts WHERE aid = $1

该查询以 aid 作为过滤条件查询 pgbench_accounts 表,如此简单的查询变慢,大概率是这张表上的索引出了问题。

用屁股想都知道是索引少了,因为就是我们自己删掉的嘛!

分析查询后提出猜想: 该查询变慢是pgbench_accounts表上aid列缺少索引

下一步,查阅 PG Table Detail 面板,检查 pgbench_accounts 表上的访问,来验证我们的猜想

图5: pgbench_accounts 表上的访问情况

通过观察,我们发现表上的索引扫描归零,与此同时顺序扫描却有相应增长。这印证了我们的猜想!

慢查询:解决

确定了问题根源后,我们将着手解决。

尝试在 pgbench_accounts 表上为 aid 列添加索引,看看能否解决这个问题。

加上索引后,神奇的事情发生了。

图6:可以看到,查询的响应时间与QPS已经恢复正常。

图7:系统的负载也恢复正常

慢查询:样例

通过这篇教程,您已经掌握了慢查询优化的一般方法论。

图8:一个慢查询优化的实际例子,将系统的饱和度从40%降到了4%

最后修改 2021-05-06: fix typos (0f00cf2)