作者:太阳
一、查问以后正在运行的 Top SQL
查问以后正在运行的会话中耗时最长的 Top SQL,where 条件可按需批改
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 20;
pg_stat_activity 视图各字段含意:http://postgres.cn/docs/13/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
二、查问某个工夫点之后的 Top SQL
1、pg_stat_statements 介绍
pg_stat_statements 可跟踪服务器执行的所有 SQL 语句的打算信息和执行统计信息,详见官网文档阐明:[http://postgres.cn/docs/13/pgstatstatements.html](http://postgres.cn/docs/13/pgstatstatements.html)
2、装置配置
1) 批改配置文件
# su - postgres
$ vi /data/pgsql13/data/postgresql.conf
shared_preload_libraries='pg_stat_statements,pg_pathman'
pg_stat_statements.max = 10000
pg_stat_statements.track = all
2) 重启失效:$ pg_ctl -D /data/pgsql13/data restart
3) 载入 pg_stat_statement 插件
postgres=# \x
Expanded display is on.
-- 查看可用模块
postgres=# SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';
-[RECORD 1]-----+-----------------------------------------------------------------------
name | pg_stat_statements
default_version | 1.8
installed_version |
comment | track planning and execution statistics of all SQL statements executed
-- 载入模块,载入后 pg_stat_statements 表可失常应用
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
3、pg_stat_statements 各字段含意
postgres=# select * from pg_stat_statements limit 1;
-[RECORD 1]-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
userid | 16480 // 用户 oid
dbid | 163959 // 数据库 oid
queryid | -7584655433466348220 // 查问 id
query | SELECT ...... //SQL 语句
plans | 0 // 打算语句的次数,单位 ms(启用 pg_stat_statements.track_planning 才会统计,默认敞开)
total_plan_time | 0 // 打算语句所破费的总工夫,单位 ms(启用 pg_stat_statements.track_planning 才会统计,默认敞开)
min_plan_time | 0 // 打算语句所破费的最短时间,单位 ms(启用 pg_stat_statements.track_planning 才会统计,默认敞开)
max_plan_time | 0 // 打算语句所破费的最长工夫,单位 ms(启用 pg_stat_statements.track_planning 才会统计,默认敞开)
mean_plan_time | 0 // 打算语句所破费的均匀工夫,单位 ms(启用 pg_stat_statements.track_planning 才会统计,默认敞开)
stddev_plan_time | 0 // 打算语句破费的工夫的总体标准偏差,单位 ms(启用 pg_stat_statements.track_planning 才会统计,默认敞开)
calls | 92545 // 语句被执行的次数
total_exec_time | 1563.9699899999862 // 执行语句所破费的总工夫,单位 ms
min_exec_time | 0.005605 // 执行语句所破费的最短时间,max_exec_time | 7.055763 // 执行语句所破费的最长工夫,mean_exec_time | 0.01689956226700567 // 执行语句所破费的最长工夫,stddev_exec_time | 0.036137014177393116 // 执行语句破费的工夫的总体标准偏差,rows | 17277 // 语句检索或影响的总行数
shared_blks_hit | 394706 // 语句的共享块缓存命中总数
shared_blks_read | 7 // 语句读取的共享块总数
shared_blks_dirtied | 6 // 被语句弄脏的共享块总数
shared_blks_written | 0 // 语句写入的共享块总数
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 // 语句写入块所破费的总工夫
wal_records | 7874 // 语句生成的 WAL 记录总数
wal_fpi | 5 // 语句生成的 WAL 整页图像总数
wal_bytes | 450177 // 语句生成的 WAL 字节总数
oid 是惟一标识,查问用户名与用户 oid 的关系:
postgres=# select userid,userid::regrole from pg_stat_statements group by userid;
-[RECORD 1]----
userid | 10
userid | postgres
...
查问 Top SQL:
-- 按总执行工夫查问 Top SQL
select userid::regrole as user_name,* from pg_stat_statements order by total_exec_time desc limit 20;
-- 按总 IO 耗费查问 Top SQL
select userid::regrole as user_name,* from pg_stat_statements order by blk_read_time+blk_write_time desc limit 20;
-- 按总调用次数查问 Top SQL
select userid::regrole as user_name,* from pg_stat_statements order by calls desc limit 20;
4、pg_stat_statements 的限度
pg_stat_statements 是累积的统计,累积的是 pg_stat_statements 配置后至以后时刻,无奈查问这期间指定工夫范畴内的 Top SQL 状况;
可通过如下命令,清理历史统计信息:
select pg_stat_statements_reset();
5、通过 pg_stat_statements 实现查问指定工夫范畴内的 Top SQL
通过定时清理历史统计信息 + 定时查问 pg_stat_statements 的形式可实现查问指定工夫范畴内的 Top SQL;
如在每天 0 点清理历史统计信息,在每天 9、11、17 点别离查问 pg_stat_statements,可失去每天 0~9、0~11、0~17 这 3 个工夫范畴内的 Top SQL。
更多技术信息请查看云掣官网 https://yunche.pro/?t=yrgw