PG Slow Query Optimization

Locating, Optimizing, Assessing Slow Queries with Pigsty monitoring system

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

慢查询：模拟

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

在主库上执行以下命令

  1. ALTER TABLE pgbench_accounts DROP CONSTRAINT pgbench_accounts_pkey ;

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

Slow Query Optimize - 图1

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

Slow Query Optimize - 图2

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

慢查询：定位

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

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

Slow Query Optimize - 图3

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

该查询表现出：

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

可以确定，就是这个查询变慢了！

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

Slow Query Optimize - 图4

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

慢查询：猜想

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

  1. SELECT abalance FROM pgbench_accounts WHERE aid = $1

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

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

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

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

Slow Query Optimize - 图5

图5： pgbench_accounts 表上的访问情况

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

慢查询：解决

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

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

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

Slow Query Optimize - 图6

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

Slow Query Optimize - 图7

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

慢查询：样例

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

Slow Query Optimize - 图8

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

Last modified 2022-06-04: fill en docs (5a858d3)