所谓 慢 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'

输出：

总用量 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

查询插件状态：

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

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;

查看 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;