PostgreSQL 使用 pg_stat_statements 查询慢 SQL

PostgreSQL 性能优化 About 3,851 words

pg_stat_statements 介绍

PostgreSQL内置了pg_stat_statements插件。

备注：如果使用make world及makr install-world编译安装的PostgreSQL，默认安装了该插件。

编译插件

cd /home/postgres/postgresql-16.1/contrib/pg_stat_statements

编译安装

make & make install

加载依赖库

查看预加载依赖库

show shared_preload_libraries;

修改配置，预加载依赖库

alter system set shared_preload_libraries = pg_stat_statements;

如果有多个插件，可以使用以下命令

alter system set shared_preload_libraries = pg_stat_statements, auto_explain, "pg_jieba.so";

重启数据库

对于shared_preload_libraries的修改，需要重启才能生效。

创建扩展

create extension pg_stat_statements;

查看相关设置

select * from pg_settings where name like 'pg_stat_statements%';

字段含义

列名	类型	描述
userid	OID	执行查询的用户的ID（引用 `pg_authid.oid`）
dbid	OID	执行查询的数据库的ID（引用 `pg_database.oid`）
toplevel	BOOL	如果查询是作为顶级语句执行的，则为真（如果 `pg_stat_statements.track` 设置为 top，则始终为真）
queryid	BIGINT	用于标识相同规范化查询的哈希值
query	TEXT	执行的 SQL 语句
plans	BIGINT	语句被计划的次数（如果启用了 `pg_stat_statements.track_planning`，否则为零）
total_plan_time	DOUBLE PRECISION	语句规划的总时间（毫秒），（如果启用了 `pg_stat_statements.track_planning`，否则为零）
min_plan_time	DOUBLE PRECISION	语句规划的最短时间（毫秒），（如果禁用了 `pg_stat_statements.track_planning` 或从未计划过，则为零）
max_plan_time	DOUBLE PRECISION	语句规划的最长时间（毫秒），（如果禁用了 `pg_stat_statements.track_planning` 或从未计划过，则为零）
mean_plan_time	DOUBLE PRECISION	语句规划的平均时间（毫秒），（如果启用了 `pg_stat_statements.track_planning`，否则为零）
stddev_plan_time	DOUBLE PRECISION	语句规划时间的标准差（毫秒），（如果启用了 `pg_stat_statements.track_planning`，否则为零）
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	查询过程中写出的临时块数
shared_blk_read_time	DOUBLE PRECISION	查询读取共享块的总时间（毫秒），（如果启用了 `track_io_timing`，否则为零）
shared_blk_write_time	DOUBLE PRECISION	查询写入共享块的总时间（毫秒），（如果启用了 `track_io_timing`，否则为零）
local_blk_read_time	DOUBLE PRECISION	查询读取本地块的总时间（毫秒），（如果启用了 `track_io_timing`，否则为零）
local_blk_write_time	DOUBLE PRECISION	查询写入本地块的总时间（毫秒），（如果启用了 `track_io_timing`，否则为零）
temp_blk_read_time	DOUBLE PRECISION	查询读取临时块的总时间（毫秒），（如果启用了 `track_io_timing`，否则为零）
temp_blk_write_time	DOUBLE PRECISION	查询写入临时块的总时间（毫秒），（如果启用了 `track_io_timing`，否则为零）
wal_records	BIGINT	查询生成的 WAL (Write-Ahead Logging) 记录数量
wal_fpi	BIGINT	查询生成的 WAL (Write-Ahead Logging) 完全页面图像数量
wal_bytes	NUMERIC	查询生成的 WAL (Write-Ahead Logging) 字节数
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	语句生成代码花费的总时间（毫秒）
jit_deform_count	BIGINT	查询 JIT 编译的元组变形函数的总数
jit_deform_time	DOUBLE PRECISION	查询在 JIT 编译元组变形函数时花费的总时间（毫秒）
stats_since	TIMESTAMP WITH TIME ZONE	收集此语句统计信息的开始时间
minmax_stats_since	TIMESTAMP WITH TIME ZONE	收集此语句最小/最大统计信息的开始时间（`min_plan_time`、`max_plan_time`、`min_exec_time` 和 `max_exec_time` 字段）

常用 SQL

找出耗时最长的10条语句。

SELECT
    total_exec_time,
    mean_exec_time as avg_ms,
    calls,
    query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

记录的语句长度

默认的pg_stat_statements只记录1KB文字大小，可使用命令修改。

alter system set track_activity_query_size = 2048;

官方文档

https://www.postgresql.org/docs/17/pgstatstatements.html

Views: 869 · Posted: 2024-06-18

———— END ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓