关于 PolarDB PostgreSQL 版

PolarDB PostgreSQL 版是一款阿里云自主研发的云原生关系型数据库产品，100% 兼容 PostgreSQL，高度兼容Oracle语法；采用基于 Shared-Storage 的存储计算分离架构，具有极致弹性、毫秒级延迟、HTAP 、Ganos全空间数据处理能力和高可靠、高可用、弹性扩展等企业级数据库特性。同时，PolarDB PostgreSQL 版具有大规模并行计算能力，可以应对 OLTP 与 OLAP 混合负载。

概述

慢 SQL 是一个 DBA 无法绕过的问题，在云数据库快速崛起的时代背景下，也将成为云客户支持同学必须要面对的一个问题。因此本文将分享一些 PostgreSQL 慢 SQL 的优化思路，希望能够为读者在解决慢 SQL 问题时提供一些参考。文章先介绍了下背景，再提供了几种监控慢 SQL 的方法。

背景

慢 SQL 是指执行时间较长的 SQL 语句。其中“慢”是一个相对值，不同场景下的标准也会不一样。例如：在一般场景下，执行时间超过 5s 的 SQL 可以称为慢 SQL；在高并发场景下这个标准可能就提高为 100 ms。

慢 SQL 是影响数据库性能最常见的因素之一，可能会造成以下几种危害：

造成 SQL 响应变慢，影响客户业务及用户体验；
增加系统负载、消耗系统资源，导致并发处理能力下降，过高的系统负载或资源占用，会导致数据库崩溃；
引发长事务，带来长事务的一系列影响，在 PostgreSQL 中可能会导致数据库年龄回卷，无法清理死元组等问题。

监控慢SQL

首先，我们需要拿到慢 SQL，因此需要对慢 SQL 进行监控，这里提供几种监控慢 SQL 的手段。

日志监控

PostgreSQL 中主要通过记录日志的方式对慢 SQL 进行监控，所以要确保实例打开日志收集功能，同时记录 SQL 执行时间作为参考。这些功能由对应的参数控制，这里列举出几个核心参数的参考值：

参数名	参数值	功能描述
logging_collector	on	开启日志收集功能
log_min_duration_statement	5000	记录执行时间超过 5s 的SQL语句
log_duration	on	记录每条执行完成SQL的执行时间
log_statement	all	记录所有执行过的SQL语句

其中，logging_collector控制实例的日志收集功能，修改后需要重启实例生效，日志监控 SQL 需要把这个参数打开。log_min_duration_statement表示记录执行时间超过设定值的 SQL，默认值为 -1，单位 ms，表示关闭功能，设置成 5000 表示记录执行超过 5s 的 SQL，如果有明确的执行时间标准，设定这个参数的值即可。如果没有明确的标准或者想监控其他 SQL 执行时间的情况，可以打开log_duration参数，同时把log_statement设置为'all'，这种场景下，日志会记录所有执行的SQL，并且当 SQL 执行完成时会记录上它的执行时间。

日志记录内容示例：

LOG:  duration: 5203.752 ms  statement: insert into tab_test select i from generate_series(1,1000000)i;

pg_stat_statements 插件监控

PostgreSQL 提供了 pg_stat_statements 插件用于追踪一个实例执行的所有 SQL 语句的统计信息。这个插件更详细的介绍可以参考PostgreSQL pg_stat_statements 插件文档。

如果要对数据库开启监控，需要在某个库创建 pg_stat_statements 插件，因为插件监控可以跨数据库，所以在任意一个数据库创建插件即可。为排除其他干扰，在监控开始之前建议先执行pg_stat_statements_reset()函数，清除之前的统计信息，之后可以通过查询 pg_stat_statements 视图来查看 SQL 执行的统计信息。例如，查看实例平均执行时间 Top 5 的 SQL 可以执行下面的 SQL：

SELECT * FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;

假如一个数据库中慢 SQL 较多或者性能影响较大，我们优先查看 pg_stat_statements 视图信息来确定解决慢 SQL 的优先级。因为一条执行时间为 1s 的 SQL 在一段时间内执行了 5000 次带来的性能影响要远比相同时间内一条执行时间为 100s 的 SQL 执行了 1 次带来的影响大。

pg_stat_activity 视图监控

PostgreSQL 提供了 pg_stat_activity 视图，用于查询当前实例的所有进程信息。pg_stat_activity 视图的更多介绍可以参考pg_stat_activity 视图文档。

上面提到的两种监控方式，都需要SQL 执行完成才会被监控到。在某些场景下，SQL 执行了很长时间也不会结束，遇到这样的情况可以通过查看 pg_stat_activity 视图，筛选出执行时间超过阈值，状态（state）为 active 的进程，得到执行SQL的信息。例如，查看SQL执行时间超过 100s 还未结束的进程可以执行下面的SQL：

SELECT (now() - query_start) as d_time, * FROM pg_stat_activity WHERE now() - query_start > interval '100' and state = 'active' order by d_time DESC limit 5;

云数据库厂商监控服务

随着云数据库产品的兴起，部分云厂商会提供云监控服务，其中也包括慢 SQL 监控能力和 SQL 分析能力。用户可以“一键”直达需要的监控信息，省去配置和分析的工作。例如，阿里云数据库提供了免费的慢 SQL 监控能力，以及可选的付费使用SQL洞察功能。

优化慢SQL

慢SQL常见产生原因

优化 SQL 之前，我们还需要了解下慢 SQL 产生的原因。导致 SQL 执行慢的原因有很多种，这里列出常见的几种原因：

实例硬件资源限制，例如CPU、内存、磁盘IO性能，服务器规格等影响；
客户业务 SQL 不合理，例如多张大表缺少join条件，添加无效、重复条件；
实例并发连接数过高，导致资源等待，进一增大连接数，产生“雪崩”现象；
实例存在锁阻塞问题，例如对热表进行truncate 操作，长事务长时间持有锁，阻塞其余 SQL；
SQL 自身执行较慢，例如过滤条件缺少索引，优化器产生较差的执行计划，执行器内存受限，缺少某些场景的优化能力等。

其中 SQL 自身执行较慢涉及的因素较多，较为复杂，也是本文接下来要聚焦的场景。

查看执行计划

排查慢 SQL 首先要分析下 SQL 的执行计划。 PostgreSQL 中通过 EXPLAIN 指令查看执行计划。有关EXPLAIN 指令使用方法参考介绍和指令。其中分析慢 SQL 常用的选项是 ANALYZE/BUFFERS/VERBOSE。PostgreSQL 的执行计划为树状结构，有关执行计划的解读参考PostgreSQL 执行计划解读。

使用 EXPLAIN 指令查看 PostgreSQL 优化器给出的执行计划，主要关注下节点的执行方式，代价估计值。这一步能够让我们对优化器产生的计划有一个了解。然后，使用 EXPLAIN (ANALYZE) 得到带有实际耗时以及返回行数的执行计划。注意！添加 ANALYZE 选项会真正执行 SQL，针对 DML 的操作需要开启一个事务操作：

BEGIN;
EXPLAIN (ANALYZE) <DML(UPDATE/INSERT/DELETE) SQL>;
ROLLBACK;

排查优化

对于使用 EXPLAIN (ANALYZE) 能够得到执行计划的慢SQL，排查的基本思路为：自顶向下，筛查出耗时最多的节点（注意！每层节点显示的时间是包含下层所有子节点总共的执行时间，所以每层节点的耗时为本层节点耗时减去下层节点的耗时），重点分析这个节点的耗时的原因。如果优化后，依然不能达到要求，则循环上述步骤，直到满足要求。在实际场景中，执行计划往往比较复杂，可以借助一些工具查看执行计划，提升效率，这里推荐一个执行计划解读工具。

总结

本文主要分享了 PostgreSQL SQL 自身执行较慢优化的一些经验和原理。开头先表达了分析和解决慢 SQL 的必要性，再介绍了几种监控慢 SQL 的方法。

参考文献

[1] Proceedings of ACM SIGMOD International Conference on Management of Data, 1998, Pages 436-447

PostgreSQL 慢 SQL 优化分享（一）