PostgreSQL 慢 SQL 排查

前言

所谓 慢 SQL 是指在数据库中执行时间超过指定阈值的语句。慢查询太多，对于业务而言，是有很大风险的，可能随时都会因为某种原因而被触发，并且根据我们的经验，数据库最常出现的问题，都是因为慢查询导致数据库慢了，进而导致整个实例 “雪崩” 从而导致线上故障。 本篇文章将介绍 PostgreSQL 慢 SQL 如何定位排查。

1. 日志参数设置

MySQL 提供为用户提供 slow_query_log 参数来设置慢日志存储：

# 是否开启慢日志
slow_query_log = 1

# 慢日志文件路径
slow_query_log_file = '/logs/slow.log'

# 慢日志阈值，取值范围 [0.000000-3600.000000]
long_query_time = 1

PostgreSQL 也提供相关参数：

# -1 表示不记录语句
log_min_duration_statement = 100ms

# none, ddl, mod, all
# none: 表示不记录慢 SQL
# ddl: ddl 记录所有数据定义语句，例如 CREATE、ALTER 和 DROP 语句
# mod: DDL + INSERT, UPDATE、DELETE、TRUNCATE, 和 COPY FROM
# all: 所有语句都会被记录
log_statement = 'mod'

2023-09-14 14:07:46.695 CST [46385] LOG:  statement: update pgbench_accounts set bid = 11 where abalance = 101;
2023-09-14 14:07:53.698 CST [46385] LOG:  duration: 7003.518 ms

这是将慢 SQL 和错误日志放到一个文件中，个人觉得不太好看，尤其是内容很多的时候，这里如果有人有其他好方法或使用经验，可以评论区发表下。

2. pg_stat_statements 插件

pg_stat_statements 模块提供一种跟踪执行统计服务器执行的所有 SQL 语句的手段。该模块默认是不开启的，如果需要开启需要我们手动对其进进行编译安装，修改配置文件并重启数据库，并在使用前手动载入该模块。

2.1 确认是否安装插件

安装插件之前，要先确认插件是否已经被编译好了，可以到 PostgreSQL 安装目录查看：

ll ./share/extension

输出：

总用量 52
-rw-r--r-- 1 root root 1246 9月  14 15:38 pg_stat_statements--1.0--1.1.sql
-rw-r--r-- 1 root root 1336 9月  14 15:38 pg_stat_statements--1.1--1.2.sql
-rw-r--r-- 1 root root 1454 9月  14 15:38 pg_stat_statements--1.2--1.3.sql
-rw-r--r-- 1 root root  345 9月  14 15:38 pg_stat_statements--1.3--1.4.sql
-rw-r--r-- 1 root root  305 9月  14 15:38 pg_stat_statements--1.4--1.5.sql
-rw-r--r-- 1 root root 1427 9月  14 15:38 pg_stat_statements--1.4.sql
-rw-r--r-- 1 root root  376 9月  14 15:38 pg_stat_statements--1.5--1.6.sql
-rw-r--r-- 1 root root  806 9月  14 15:38 pg_stat_statements--1.6--1.7.sql
-rw-r--r-- 1 root root  191 9月  14 15:38 pg_stat_statements.control
-rw-r--r-- 1 root root  449 9月  14 15:38 pg_stat_statements--unpackaged--1.0.sql
-rw-r--r-- 1 root root  310 9月   5 10:21 plpgsql--1.0.sql
-rw-r--r-- 1 root root  179 9月   5 10:21 plpgsql.control
-rw-r--r-- 1 root root  370 9月   5 10:21 plpgsql--unpackaged--1.0.sql

可以看到一些关于 pg_stat_statements 文件信息，表示插件已被安装完成。如果看到相关文件表示插件没有被编译，需要编译后才能使用。

2.2 编译插件

进入 PostgreSQL 源码目录，后执行下方命令：

# 进入插件目录
cd contrib/pg_stat_statements/

# 编译安装
make && make install

2.3 载入插件

确认编译安装成功后，就可以使用插件了，首先需要修改 postgresql.conf 配置文件：

# 加载动态库
shared_preload_libraries='pg_stat_statements'

# 记录语句的最大行数，默认 5000
pg_stat_statements.max = 10000

# 控制哪些语句会被该模块计数。指定 top 可以跟踪顶层语句（那些直接由客户端发出的语句）
# 指定 all 还可以跟踪嵌套的语句（例如在函数中调用的语句）指定 none 可以禁用语句统计信息收集。
pg_stat_statements.track = all

# 控制模块 是否 跟踪除了 “增删改查” 之外的命令，默认为 on
pg_stat_statements.track_utility = on

# 指定是否在服务器关闭之后还保存语句统计信息，默认为 on 关机前会持久化保存
pg_stat_statements.save = on

然后 重启数据库 后生效。

然后使用 psql 连接 PostgreSQL 服务，载入插件：

postgres=# create extension pg_stat_statements;     
CREATE EXTENSION

查询插件状态：

postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';     
        name        | default_version | installed_version |                          comment                          
--------------------+-----------------+-------------------+-----------------------------------------------------------
 pg_stat_statements | 1.7             | 1.7               | track execution statistics of all SQL statements executed

2.4 插件使用

直接查询插件视图，就可以看到 TOP SQL 情况：

postgres=# select * from  pg_stat_statements limit 1;
-[ RECORD 1 ]-------+------------------------------------------------------------------------
userid              | 10                        //用户id
dbid                | 13547                     //数据库oid
queryid             | 1194713979                //查询id
query               | SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements'   //查询SQL
calls               | 1                         //调用次数
total_time          | 53.363875                 //SQL总共执行时间
min_time            | 53.363875                 //SQL最小执行时间
max_time            | 53.363875                 //SQL最大执行时间
mean_time           | 53.363875                 //SQL平均执行时间
stddev_time         | 0                         //SQL花费时间的表中偏差
rows                | 1                         //SQL返回或者影响的行数
shared_blks_hit     | 1                         //SQL在在shared_buffer中命中的块数
shared_blks_read    | 0                         //SQL从page cache或者磁盘中读取的块数
shared_blks_dirtied | 0                         //SQL语句弄脏的shared_buffer的块数
shared_blks_written | 0                         //SQL语句写入的块数
local_blks_hit      | 0                         //临时表中命中的块数
local_blks_read     | 0                         //临时表需要读的块数
local_blks_dirtied  | 0                         //临时表弄脏的块数
local_blks_written  | 0                         //临时表写入的块数
temp_blks_read      | 0                         //从临时文件读取的块数
temp_blks_written   | 0                         //从临时文件写入的数据块数
blk_read_time       | 0                         //从磁盘或者读取花费的时间
blk_write_time      | 0                         //从磁盘写入花费的时间

3. 慢 SQL 排查手段

3.1 查询当前会话

SELECT pgsa.datname AS database_name
    , pgsa.usename AS user_name
    , pgsa.client_addr AS client_addr
    , pgsa.application_name AS application_name
    , pgsa.state AS state
 , pgsa.backend_start AS backend_start
 , pgsa.xact_start AS xact_start
 , extract(epoch FROM now() - pgsa.xact_start) AS xact_time, pgsa.query_start AS query_start
 , extract(epoch FROM now() - pgsa.query_start) AS query_time
 , pgsa.query AS query_sql
FROM pg_stat_activity pgsa
WHERE pgsa.state != 'idle'
 AND pgsa.state != 'idle in transaction'
 AND pgsa.state != 'idle in transaction (aborted)'
ORDER BY query_time DESC
LIMIT 5;

3.2 查看 TOP SQL

重启并重置 pg_stat_statements 插件，让插件重新收集当前的数据：

create extension pg_stat_statements;
select pg_stat_reset();
select pg_stat_statements_reset();

查看 TOP SQL：

-- 总查询时间 TOP
select * from pg_stat_statements order by total_time desc limit 5;
-- 总 IO 消耗 TOP
select * from pg_stat_statements order by blk_read_time+blk_write_time desc limit 5;
-- 总调用次数 TOP
select * from pg_stat_statements order by calls desc limit 5;

当发现异常会话，一般都是先 kill 掉，然后再分析问题原因，kill 会话相关操作，可以参考：《PostgreSQL 会话管理》