PostgreSQL 慢 SQL 排查

文若10个月前技术文章425

前言

所谓 慢 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 会话管理》


相关文章

LINUX 安全运维-OpenSSH安全

LINUX 安全运维-OpenSSH安全

SSH 是建立在应用层基础上的安全协议,是目前较可靠,专为远程登录会话和其他网络服务提供安全性的协议。利用 SSH协议可以有效防止远程管理过程中的信息泄露问题。SSH最初是UNIX系统上的一...

HAProxy

HAProxy

1、HAProxy简介  HAProxy 是一款基于 TCP(第四层)和 HTTP(第七层)应用的代理软件,它也可作为负载均衡器,而且完全免费。 借助 HAProxy,可以快速并且可靠地提供基于 TC...

scylladb下线数据中心

1、在要下线的老数据中心所有节点运行数据修复nodetool -h ::FFFF:127.0.0.1 repair -pr2、更改所有业务keyspace的复制策略不在写入老的数据中心--查看所有的k...

MySQL运维实战之ProxySQL(9.5)proxysql和MySQL Group Replication配合使用

如果后端MySQL使用了Group Replication,可通过配置mysql_group_replication_hostgroups表来实现高可用mysql_group_replication_...

SQL Server优化入门系列(四)——  找到Top SQL

SQL Server优化入门系列(四)—— 找到Top SQL

说明从会话信息中我们可以查看实例当前正在运行的SQL、当前被Block的SQL。但是如果要查看过去一段时间执行的SQL,我们有几个选择1、通过外部SQL审计平台记录所有SQL。2、通过SQL Serv...

flume开启jmx加入grafana

部署flume_exporter需要go环境编译配置go环境首先下载go安装包Linux:https://golang.google.cn/dl/解压、添加环境变量tar -zxvf go*.tar....

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。