PostgreSQL插件-pg_stat_statements-安装和使用

插件介绍

pg_stat_statements模块提供了一种跟踪服务器执行的所有 SQL 语句的规划和执行统计信息的方法。

插件相关介绍可参考：PostgreSQL插件-pg_stat_statements-跟踪SQL查询计划和执行的统计数据。

版本说明：这里用的是pg16版本的pg_stat_statements 1.10 （不用pg版本有对应的插件版本，不同版本的插件内容可能会有所差异，例如可能会新增某字段）。

插件安装

1.修改配置文件postgresql.conf

因为pg_stat_statements这个插件的数据是存放在内存里面的，而且需要在初始化的时候就申请一块内存区域，因此需要将插件配置在shared_preload_libraries参数里面（如果之前已经有配置了插件，多个插件之间用逗号分隔）

shared_preload_libraries = 'pg_stat_statements'

2.插件相关参数

参数默认值

如果不配置参数，默认是这些值：

#SELECT * from pg_settings WHERE name ~ 'pg_stat_statements';

pg_stat_statements.max = 5000
pg_stat_statements.save = on
pg_stat_statements.track = top
pg_stat_statements.track_planning =	off
pg_stat_statements.track_utility = on

参数说明

• pg_stat_statements.max ：pg_stat_statements视图记录行数，如果实际行数超过该值，会将最少使用的记录删掉，源码详见 entry_dealloc 函数 。
• pg_stat_statements.save：指定是否在服务器关闭时保存语句统计信息。如果是off，则不会在关机时保存统计信息，也不会在服务器启动时重新加载统计信息。缺省值为 on。
• pg_stat_statements.track：控制模块对哪些语句进行计数。top指定跟踪顶级语句（由客户端直接发出的语句）、all跟踪嵌套语句（如在函数中调用的语句）或none禁用语句统计信息收集。缺省值为top 。
• pg_stat_statements.track_planning：控制模块是否跟踪计划操作和工期。启用此参数可能会产生明显的性能损失，尤其是当具有相同查询结构的语句由许多并发连接执行时，这些并发连接争用更新少量pg_stat_statements条目。缺省值为 off。
• pg_stat_statements.track_utility：控制模块是否跟踪实用程序命令。实用程序命令是 除 SELECT、INSERT、UPDATE 和 DELETE以外的所有命令。缺省值为on 。

特别注意pg_stat_statements.max参数

1. 改参数范围：100 … 1073741823
2. 不能太大，上面提到了数据是存在内存里面的，太大的话会占用很多内存，以至于可能会影响正常业务。

设置太小日志会有警告

这里设置的是10，会有警告WARNING日志，而且是不生效的，即还是默认值。

2025-02-10 13:55:52.287 CST,,,21388,,67a994e8.538c,71,,2025-02-10 13:55:52 CST,,0,DEBUG:  00000: find_in_dynamic_libpath: trying "/usr/local/pgsql/lib/pg_stat_statements"
2025-02-10 13:55:52.287 CST,,,21388,,67a994e8.538c,72,,2025-02-10 13:55:52 CST,,0,LOCATION:  find_in_dynamic_libpath, dfmgr.c:583
2025-02-10 13:55:52.287 CST,,,21388,,67a994e8.538c,73,,2025-02-10 13:55:52 CST,,0,DEBUG:  00000: find_in_dynamic_libpath: trying "/usr/local/pgsql/lib/pg_stat_statements.so"
2025-02-10 13:55:52.287 CST,,,21388,,67a994e8.538c,74,,2025-02-10 13:55:52 CST,,0,LOCATION:  find_in_dynamic_libpath, dfmgr.c:583
2025-02-10 13:55:52.288 CST,,,21388,,67a994e8.538c,75,,2025-02-10 13:55:52 CST,,0,WARNING:  22023: 10 is outside the valid range for parameter "pg_stat_statements.max" (100 .. 1073741823)
2025-02-10 13:55:52.288 CST,,,21388,,67a994e8.538c,76,,2025-02-10 13:55:52 CST,,0,LOCATION:  parse_and_validate_value, guc.c:3137
2025-02-10 13:55:52.288 CST,,,21388,,67a994e8.538c,77,,2025-02-10 13:55:52 CST,,0,DEBUG:  00000: loaded library "pg_stat_statements"
2025-02-10 13:55:52.288 CST,,,21388,,67a994e8.538c,78,,2025-02-10 13:55:52 CST,,0,LOCATION:  load_libraries, miscinit.c:1841

插件使用

1.创建插件

CREATE extension pg_stat_statements;

2.使用插件

关键视图：pg_stat_statements。

相关使用可参考：PostgreSQL插件-pg_stat_statements-查找最耗费资源的SQL(Top SQL)

SELECT * from pg_stat_statements;
-- 相关字段
-- userid	dbid	toplevel	queryid	query	plans	total_plan_time	min_plan_time	max_plan_time	mean_plan_time	stddev_plan_time	calls	total_exec_time	min_exec_time	max_exec_time	mean_exec_time	stddev_exec_time	rows	shared_blks_hit	shared_blks_read	shared_blks_dirtied	shared_blks_written	local_blks_hit	local_blks_read	local_blks_dirtied	local_blks_written	temp_blks_read	temp_blks_written	blk_read_time	blk_write_time	temp_blk_read_time	temp_blk_write_time	wal_records	wal_fpi	wal_bytes	jit_functions	jit_generation_time	jit_inlining_count	jit_inlining_time	jit_optimization_count	jit_optimization_time	jit_emission_count	jit_emission_time

3.重置数据

SELECT pg_stat_statements_reset();

-- 最近重置数据事件可见stats_reset列
SELECT * from pg_stat_statements_info;
-- dealloc	stats_reset

4.删除插件

DROP extension pg_stat_statements;

可能会出现的问题

1.没有编译安装插件

会提示，没有pg_stat_statements.so

处理方法：编译安装pg_stat_statements。

cd contrib/pg_stat_statements
make && make install

2.没有配置shared_preload_libraries

需要注意：这个报错，不是出现在CREATE extension，而是在使用时。

处理方法：配置shared_preload_libraries。

SELECT * from pg_stat_statements;
-- > ERROR:  pg_stat_statements must be loaded via shared_preload_libraries

3.跟踪数据太多，导致内存占用过大

需要注意：这个报错，不是出现在CREATE extension，而是出现在使用时。

处理方法：调小参数 。

pg_stat_statements.max = 5000