pg_stat_statements 是 PostgreSQL 中的一个扩展模块,用于跟踪 SQL 查询的执行情况和性能统计数据。通过启用和使用 pg_stat_statements,你可以详细了解哪些查询消耗了最多的资源(如时间、I/O 等),进而帮助你进行数据库性能优化。
功能概述
pg_stat_statements 可以记录以下信息:
- SQL 语句的文本(查询被标准化,例如将常量值替换为占位符)。
- 查询执行的次数(计数)。
- 查询的总执行时间和平均执行时间。
- 查询返回的行数。
- 执行中出现的错误数。
- 共享缓冲区命中、读写次数等 I/O 相关信息。
如何启用 pg_stat_statements
- 修改 postgresql.conf 配置文件:
首先,在 postgresql.conf 文件中启用扩展:
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements 需要在 PostgreSQL 启动时加载,所以必须将其加入 shared_preload_libraries。
- 重启 PostgreSQL:
修改完配置文件后,需要重启 PostgreSQL:
sudo systemctl restart postgresql
- 创建扩展:
登录到 PostgreSQL 并创建 pg_stat_statements 扩展:
CREATE EXTENSION pg_stat_statements;
查询统计数据启用后,可以通过查询系统视图 pg_stat_statements 来查看统计信息:
SELECT query, -- 查询的文本(标准化) calls, -- 查询被执行的次数 total_time, -- 查询的总执行时间(毫秒) min_time, -- 查询的最短执行时间(毫秒) max_time, -- 查询的最长执行时间(毫秒) mean_time, -- 查询的平均执行时间(毫秒) rows, -- 查询返回的行数 shared_blks_hit, -- 查询在共享缓冲区的命中次数 shared_blks_read -- 查询从磁盘读取的块数FROM pg_stat_statementsORDER BY total_time DESCLIMIT 10;
这个查询将返回执行时间最长的前 10 个 SQL 查询。
重要字段解释- query: 查询的文本。常量被替换为占位符,因此相同的查询结构会被聚合在一起。
- calls: 查询执行的次数。
- total_time: 查询的总执行时间(所有调用的累计时间)。
- mean_time: 查询的平均执行时间。
- shared_blks_hit: 查询在共享缓冲区中命中的块数,表示从内存中读取的次数。
- shared_blks_read: 查询从磁盘读取的块数,表示 I/O 的负担。
清除统计数据你可以在需要时重置(清除)统计信息:
SELECT pg_stat_statements_reset();
这将清除 pg_stat_statements 中的所有统计数据,从而开始新的统计周期。
性能影响pg_stat_statements 由于需要跟踪大量的查询信息,确实会带来一些性能开销,但通常非常轻微。大多数生产环境中都可以安全启用。如果你有大量复杂查询并且需要对性能问题进行深入分析,启用 pg_stat_statements 是一个非常有用的做法。
实际应用场景- 发现性能瓶颈:通过分析哪些查询占用了最多的资源,你可以识别出最需要优化的 SQL 语句。
- 监控和调优:你可以定期查看哪些查询执行得频繁或效率低下,并调整索引或重写查询。
- 识别长时间运行的查询:对于需要监控慢查询的场景,pg_stat_statements 提供了平均执行时间、最大执行时间等信息,帮助你快速定位问题。
总结pg_stat_statements 是 PostgreSQL 中非常强大且实用的扩展,用于收集和分析查询的统计信息。通过定期分析这些数据,你可以识别性能瓶颈、优化查询、提升数据库的整体性能。
|