PostgreSQL高耗sql利器pg_stat_statements部署使用分享
PostgreSQL高耗sql利器pg_stat_statements部署使用分享
前言
PostgreSQL中高耗SQL的获取可以使用pg_stat_statements模块来获取，pg_stat_statements模块提供执行SQL语句的执行统计信息。
该模块必须在postgresql.conf的shared_preload_libraries中增加pg_stat_statements来载入，因为它需要额外的共享内存。增加或移除该模块需要将数据库重启。
当pg_stat_statements被载入时，它会跟踪该服务器的所有数据库的统计信息。该模块提供了视图pg_stat_statements以及函数pg_stat_statements_reset用于访问和操纵这些统计信息。这些视图和函数不是全局可用的，但是可以在指定数据库创建该扩展。
Pg支持通过动态库的方式来扩展pg的功能，在调用动态库涉及的函数时会自动加载这些库，但是某些动态库需要预加载。比如pg_stat_statements。
shared_preload_libraries就是指定在服务器启动时预加载一个或多个shared libraries。它包含一个以逗号分隔的库名称列表。条目之间的空白将被忽略，如果需要在名称中包含空格或逗号，则使用双引号包含库名。此参数只能在服务器启动时设置，注意，如果没有找到指定的库, 服务器将无法启动
1. 创建扩展模块
创建extension模块
postgres=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION
查看pg的扩展组件
SELECT * FROM pg_available_extensions;
2. 配置postgresql.conf参数文件
修改数据库PG_HOME下的postgresql.conf文件
修改postgresql.conf 文件，确保 shared_preload_libraries 参数中包含了 'pg_stat_statements'。
shared_preload_libraries = 'pg_stat_statements'
# 配置 pg_stat_statements
# pg_stat_statements.max：定义了跟踪的 SQL 语句的最大数量，默认值通常是 5000。
# pg_stat_statements.track：控制哪些语句被跟踪（选项包括 all, top, none）。
# pg_stat_statements.save：是否保存统计信息到磁盘（默认为 on），以便在服务器重启后仍然保留。
shared_preload_libraries= 'pg_stat_statements'
pg_stat_statements.max= 10000 #pg_stat_statements中记录的最大的SQL条目数，默认为5000
pg_stat_statements.track= all#记录pg_stat_statements中的
pg_stat_satements.saveon #用来控制数据库在关闭的时候，是否将SQL信息保存到文件中。默认打开
pg_stat_satements.track_utilityon #追踪SQL命令：DQLDDL 以及DQL,DDL以外的其他SQL命令(off只记录DQLDDL)
如果没有配置postgresql.conf文件中的shared_preload_libraries,那么将会提示如下报错：
ERROR:pg_stat_statements must be loaded via shared_preload_libraries
3. 重启数据库
使用pg_ctl重新启动数据库，使扩展生效。
# 创建扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
#查看当前数据库实例中已安装和启用的扩展
SELECT * FROM pg_extension;
pg_ctl start -D $PGDATA -l tmp/pg_rotate_logfile()
4. 验证
进入数据库，查看pg_stat_statements视图，有数据则安装成功。
配置完成并启用了 pg_stat_statements，就可以使用它来获取统计信息：主要的视图是 pg_stat_statements，它提供了每个记录的查询文本、调用次数、总时间等信息。
psql -U postgres -d pgtestdb
select * from pg_stat_statements;
查看版本
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
5. pg_stat_statements视图结构
该视图的结构信息如下：
\d pg_stat_statements;
字段 类型 描述
userid oid 执行该语句的用户 OID
dbid oid 数据库中执行语句的 OID
toplevel boolean 如果查询作为顶级语句执行（如果 pg_stat_statements.track 设置为 top ，则始终为真）
queryid bigint 哈希码用于识别相同的规范化查询。
query text 文本示例语句
plans bigint 语句被计划次数（如果启用了 pg_stat_statements.track_planning，否则为0）
total_plan_time double precision 总规划该语句所花费的时间，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0）
min_plan_time double precision 最小用于规划语句的时间，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0）
max_plan_time double precision 最大用于语句规划的时间，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0）
mean_plan_time double precision 平均花费在语句规划上的时间，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0）
stddev_plan_time double precision 语句规划时间的人口标准差，以毫秒为单位（如果启用了 pg_stat_statements.track_planning，否则为0）
calls bigint 执行语句的次数
total_exec_time double precision 执行该语句所花费的总时间，以毫秒为单位
min_exec_time double precision 执行语句的最短时间，以毫秒为单位
max_exec_time double precision 执行语句所花费的最大时间，以毫秒为单位
mean_exec_time double precision 平均执行语句耗时，以毫秒为单位
stddev_exec_time double precision 生成执行计划的标准偏差时间，单位为毫秒
rows bigint 总行数，由语句检索或影响的行数
shared_blks_hit bigint 语句引发的共享块缓存命中总数
shared_blks_read bigint 语句读取的总共享块数
shared_blks_dirtied bigint 语句导致的共享块脏化的总数
shared_blks_written bigint 语句写入的共享块总数
local_blks_hit bigint 语句导致的本地块缓存命中总数
local_blks_read bigint 语句读取的本地块总数
local_blks_dirtied bigint 语句导致的本地脏块总数
local_blks_written bigint 该语句写入的本地块总数
temp_blks_read bigint 语句读取的临时块总数
temp_blks_written bigint 语句写入的临时块总数
blk_read_time double precision 该语句读取数据文件块所花费的总时间，以毫秒为单位（如果启用 track_io_timing，否则为0）
blk_write_time double precision 该语句写入数据文件块所花费的总时间，以毫秒为单位（如果启用 track_io_timing，否则为0）
temp_blk_read_time double precision 该语句读取临时文件块所花费的总时间，以毫秒为单位（如果启用 track_io_timing，否则为0）
temp_blk_write_time double precision 该语句写入临时文件块所花费的总时间，以毫秒为单位（如果启用 track_io_timing，否则为0）
wal_records bigint 该语句生成的 WAL 记录总数
wal_fpi bigint 该语句生成的 WAL 完整页面图像总数
wal_bytes numeric 该语句生成的 WAL 总字节数
jit_functions bigint 该语句总共 JIT 编译的函数数
jit_generation_time double precision 该语句生成 JIT 代码所花费的总时间，以毫秒为单位
jit_inlining_count bigint 函数内联次数
jit_inlining_time double precision 该语句在内联函数上花费的总时间，以毫秒为单位
jit_optimization_count bigint 语句被优化的次数
jit_optimization_time double precision 该语句在优化上花费的总时间，以毫秒为单位
jit_emission_count bigint 代码被发出次数
jit_emission_time double precision 该语句在生成代码上花费的总时间，以毫秒为单位
由于安全性原因，只有超级用户和pg_read_all_stats角色的成员被允许看到其他用户执行的查询的SQL文本或者queryid。
我们可以看到该试图包含丰富的信息。
- SQL的调用次数，总的耗时，最快执行时间，最慢执行时间，平均执行时间，执行时间的方差（看出抖动），总共扫描或返回或处理了多少行；
- shared buffer的使用情况，命中，未命中，产生脏块，驱逐脏块。
- local buffer的使用情况，命中，未命中，产生脏块，驱逐脏块。
- temp buffer的使用情况，读了多少脏块，驱逐脏块。
- 数据块的读写时间。
6. pg_stat_statements使用
log_min_duration_statement这个参数可以控制阈值的时间，如果查询花费的时间长于此阈值时间，则会记录该SQL。默认为1s。可以使用
ALTER SYSTEM SETlog_min_duration_statement = 1000;
更改阈值记录，单位为ms。
6.1、SQL执行时间获取
我们可以在数据库中看到平均运行时间最高的查询，如下所示：
SELECT total_time, min_time,(total_time/calls) as avg_time, max_time, mean_time, calls, rows,query
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;
返回结果如下：
其中各项的涵义：
total_time：返回查询的总运行时间（以毫秒为单位）。
min_time、avg_time和max_time：返回查询的最小、平均和最大运行时间。
mean_time：使用total_time/调用返回查询的平均运行时间（以毫秒为单位）。
Calls (调用)：返回查询运行的总数。
Rows(行数)：返回由于查询而返回或受影响的行总数。
Query(查询)：返回正在运行的查询。默认情况下，最多显示1024个查询字节。可以使用track_activity_query_size参数更改此值。
6.2 常用查询资源消耗多的SQL
调用次数较多的SQL
select userid,dbid,query,calls,rows,total_exec_time,mean_exec_time from pg_stat_statements order by calls desc limit 10;
总执行时间较长的SQL
select userid,dbid,query,calls,rows,total_exec_time,mean_exec_time from pg_stat_statements order by total_exec_time desc limit 10;
平均执行时间较长的SQL
select userid,dbid,query,calls,rows,total_exec_time,mean_exec_time from pg_stat_statements order by mean_exec_time desc limit 10;
6.3.总执行时间最长的SQL
SELECT query,
calls,
round(total_time::numeric, 2) AS total_time,
round(mean_time::numeric, 2) AS mean_time,
round((100 * total_time sum(total_time) OVER ())::numeric, 2) AS percentage
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;
在上面的查询中，我们可以看到某个query的执行频率，以及测试到的总执行时间，同时还计算了每种查询类型的总运行时间的百分比。
最耗IO的SQL
SELECT query,
calls,
round(total_time::numeric, 2) AS total_time,
round(blk_read_time::numeric, 2) AS io_read_time,
round(blk_write_time::numeric, 2) AS io_write_time,
round((100 * total_time sum(total_time) OVER ())::numeric, 2) AS percentage
FROM pg_stat_statements
ORDER BY blk_read_time + blk_write_time DESC
LIMIT 10;
注意：如果要跟踪IO消耗的时间，还需要打开trace_io_timing参数。
track_io_timing = on
最耗共享内存 SQL
select * from pg_stat_statements order by shared_blks_hit+shared_blks_read desc limit 5;
查看QPS
with
a as (select sum(calls) s, sum(case when ltrim(query,' ') ~* '^select' then calls else 0 end) q from pg_stat_statements),
b as (select sum(calls) s, sum(case when ltrim(query,' ') ~* '^select' then calls else 0 end) q from pg_stat_statements , pg_sleep(1))
select
b.s-a.s, -- QPS
b.q-a.q, -- 读QPS
b.s-b.q-a.s+a.q -- 写QPS
from a,b;
响应时间抖动最严重的TOP 5 SQL
select dbid,query from pg_stat_statements order by stddev_time desc limit 5;
7.重置pg_stat_statements统计信息
pg_stat_statements所获得的统计数据一直累积到重置。
可以使用以下脚本进行按天备份。
备份完成后可以通过具有超级用户权限的用户连接到数据库以重置统计数据来运行重置：
清除累积的统计数据
SELECTpg_stat_statements_reset();
#!/bin/bash
# this script is aimed to delete the expired data;
# and use the vacummdb command to clean up databases.
# Copyright(c) 2016--2016 yuxiangli All Copyright reserved.
echo "-----------------------------------------------------"
echo `date +%Y%m%d%H%M%S`
dates= `date +%Y%m%d`
psql -U hbdx_xxx -h 133.0.xxx.xx -d testdb -p xxx << EOF
create table public.pg_stat_statements_$dates as select * from public.pg_stat_statements;
SELECT pg_stat_statements_reset();
\q
EOF
echo "-----------------------------------------------------"
echo `date +%Y%m%d%H%M%S`
echo "-----------------------------------------------------"
8.禁用 pg_stat_statements 收集数据
如果只是想停止收集新的统计数据，但不想完全移除这个扩展，可以将配置参数 pg_stat_statements.track 设置为 none。这将禁用所有的统计信息收集。
ALTER SYSTEM SET pg_stat_statements.track = 'none';
如果不再需要这个扩展，并且希望彻底从数据库中删除它，可以通过运行下面的 SQL 命令来完成：
DROP EXTENSION IF EXISTS pg_stat_statements;
移除该扩展不会影响已经存储在磁盘上的统计数据；它只会阻止未来的数据收集。如果想清除所有现有的统计数据，可以在移除扩展之前清空相关的视图或表。
9.注意事项
- pg_stat_statements 可能会影响数据库性能，因为它需要跟踪额外的信息。在生产环境中使用时，应该谨慎并监控其影响。
- 统计信息是累积的，除非被重置，否则它们会一直增加。
- 为了更精确的性能分析，可能需要结合使用其他工具和日志，例如 EXPLAIN ANALYZE。
浙公网安备 33010602011771号