PostgreSQL慢SQL的定位排查方法

目录

• 前言
• 1. 日志参数设置
• 2. pg_stat_statements 插件
• 2.1 确认是否安装插件
• 2.2 编译插件
• 2.3 载入插件
• 2.4 插件使用
• 3. 慢 SQL 排查手段
• 3.1 查询当前会话
• 3.2 查看 TOP 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 语句的手段。该模块默认是不开启的，如果需要开启需要我们手动对其进进行编译安装，修改配置文件并重启数据库，并在使用前手动载入该python模块。

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-- http://www.devze.com1 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_statementsjavascript.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'   //javascript查询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;

到此这篇关于PostgreSQL慢SQL的定位排查方法的文章就介绍到这了,更多相关PostgreSQL慢SQ排查L内容请搜索编程客栈(www.devze.com)以前的文章或继续浏览下面的相关文章希望大家以后多多支持编程客栈(www.devze.com)！