PostgreSQL教程FG094-PG数据库性能监控：pg_stat_statements使用
本文档风哥主要介绍PostgreSQL的pg_stat_statements扩展的使用方法，包括启用、配置、查询和分析性能数据等内容。风哥教程参考PostgreSQL官方文档Server Administration内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。
学习交流加群风哥QQ113257174
Part01-基础概念与理论知识
1.1 pg_stat_statements的概念
pg_stat_statements是PostgreSQL的一个扩展，用于收集和统计SQL语句的执行情况，包括执行时间、调用次数、返回行数等信息。通过pg_stat_statements，DBA可以监控和分析数据库的性能，识别慢查询和性能瓶颈。更多视频教程www.fgedu.net.cn
from oracle:www.itpux.com
- 收集SQL语句的执行统计信息
- 记录执行时间、调用次数、返回行数等指标
- 支持按执行时间、调用次数等排序
- 提供详细的查询执行计划信息
- 帮助识别慢查询和性能瓶颈
1.2 pg_stat_statements的优势
pg_stat_statements的优势包括：
- 实时监控：实时收集和统计SQL语句的执行情况
- 详细信息：提供详细的执行统计信息，包括执行时间、调用次数、返回行数等
- 易于使用：通过SQL查询即可获取统计信息
- 性能影响小：对数据库性能的影响较小
- 可定制性：支持配置参数，适应不同的监控需求
- 兼容性好：与PostgreSQL的版本兼容性良好
1.3 pg_stat_statements的架构
pg_stat_statements的架构包括：
– 共享内存：存储SQL语句的执行统计信息
– 哈希表：用于快速查找和更新SQL语句的统计信息
– 后台进程：负责收集和维护统计信息
– 系统视图：提供查询统计信息的接口
# pg_stat_statements工作原理
1. 当SQL语句执行时，pg_stat_statements会捕获该语句
2. 计算语句的哈希值，用于在哈希表中查找
3. 如果语句已存在，更新其统计信息
4. 如果语句不存在，添加到哈希表中并初始化统计信息
5. 通过pg_stat_statements视图提供统计信息查询
# pg_stat_statements统计信息
– queryid：SQL语句的哈希值
– query：SQL语句文本
– calls：调用次数
– total_exec_time：总执行时间
– mean_exec_time：平均执行时间
– min_exec_time：最小执行时间
– max_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：块写入时间
Part02-生产环境规划与建议
2.1 pg_stat_statements规划
pg_stat_statements规划要点：
1. 确定监控需求：根据业务需求确定监控的范围和深度
2. 配置pg_stat_statements：设置合适的配置参数
3. 部署监控系统：结合第三方工具进行监控
4. 制定监控策略：定期分析和优化慢查询
5. 建立告警机制：当出现性能问题时及时告警
# 监控需求分析
– 监控范围：所有SQL语句或特定类型的SQL语句
– 监控深度：基本统计信息或详细执行计划
– 监控频率：实时监控或定期分析
– 存储需求：统计信息的存储方式和保留时间
# 部署方案
– 本地部署：在PostgreSQL服务器上直接使用pg_stat_statements
– 集中部署：使用第三方工具集中收集和分析统计信息
– 混合部署：结合本地和集中部署的优点
2.2 pg_stat_statements配置建议
pg_stat_statements配置建议：
– shared_preload_libraries：设置为’pg_stat_statements’，启用扩展
– pg_stat_statements.max：最大存储的语句数，建议设置为10000
– pg_stat_statements.track：跟踪的语句类型，建议设置为’all’
– pg_stat_statements.track_utility：是否跟踪工具语句，建议设置为’on’
– pg_stat_statements.track_planning：是否跟踪规划时间，建议设置为’on’
– pg_stat_statements.save：服务器关闭时是否保存统计信息，建议设置为’on’
# 配置建议
– 对于生产环境：
– pg_stat_statements.max = 10000
– pg_stat_statements.track = all
– pg_stat_statements.track_utility = on
– pg_stat_statements.track_planning = on
– pg_stat_statements.save = on
– 对于测试环境：
– pg_stat_statements.max = 5000
– pg_stat_statements.track = all
– pg_stat_statements.track_utility = on
– pg_stat_statements.track_planning = on
– pg_stat_statements.save = on
# 性能影响
– 内存使用：pg_stat_statements会占用一定的共享内存
– CPU使用：收集统计信息会增加一定的CPU开销
– 存储使用：保存统计信息会占用一定的存储空间
– 建议：在生产环境中合理配置参数，平衡监控需求和性能影响
2.3 pg_stat_statements监控策略
pg_stat_statements监控策略：
- 定期分析：定期查询pg_stat_statements视图，分析慢查询和性能瓶颈
- 实时监控：结合第三方工具，实时监控数据库性能
- 告警机制：当出现性能问题时及时告警
- 优化建议：根据分析结果，提供优化建议
- 趋势分析：分析性能趋势，预测潜在问题
Part03-生产环境项目实施方案
3.1 启用pg_stat_statements
3.1.1 配置shared_preload_libraries参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改shared_preload_libraries参数
shared_preload_libraries = ‘pg_stat_statements’
# 保存并退出
# 重新启动PostgreSQL服务
$ sudo systemctl restart postgresql
# 验证修改结果
$ sudo -u pgsql psql -c “SHOW shared_preload_libraries;”
shared_preload_libraries
———————————–
pg_stat_statements
(1 row)
3.1.2 创建pg_stat_statements扩展
$ sudo -u pgsql psql -c “CREATE EXTENSION pg_stat_statements;”
CREATE EXTENSION
# 验证扩展创建
$ sudo -u pgsql psql -c “\dx”
List of installed extensions
Name | Version | Schema | Description
——————–+———+————+———————————————————————
pg_stat_statements | 1.10 | public | track execution statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
3.2 pg_stat_statements配置
3.2.1 配置pg_stat_statements参数
$ sudo vi /postgresql/data/postgresql.conf
# 添加pg_stat_statements配置
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.track_planning = on
pg_stat_statements.save = on
# 保存并退出
# 重新启动PostgreSQL服务
$ sudo systemctl restart postgresql
# 验证修改结果
$ sudo -u pgsql psql -c “SHOW pg_stat_statements.max;”
pg_stat_statements.max
————————
10000
(1 row)
$ sudo -u pgsql psql -c “SHOW pg_stat_statements.track;”
pg_stat_statements.track
————————–
all
(1 row)
$ sudo -u pgsql psql -c “SHOW pg_stat_statements.track_utility;”
pg_stat_statements.track_utility
———————————–
on
(1 row)
$ sudo -u pgsql psql -c “SHOW pg_stat_statements.track_planning;”
pg_stat_statements.track_planning
————————————
on
(1 row)
$ sudo -u pgsql psql -c “SHOW pg_stat_statements.save;”
pg_stat_statements.save
————————
on
(1 row)
3.3 pg_stat_statements使用
3.3.1 查询执行统计信息
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;”
queryid | query | calls | total_exec_time | mean_exec_time | rows
————+—————————————————+——-+—————-+—————-+——
1234567890 | SELECT * FROM fgedu_employees WHERE salary > $1 | 100 | 50000.00 | 500.00 | 1000
2345678901 | SELECT * FROM fgedu_fgfgfgfgsales WHERE amount > $1 | 50 | 20000.00 | 400.00 | 500
3456789012 | SELECT * FROM fgedu_departments WHERE location = $1 | 20 | 5000.00 | 250.00 | 10
# 查询执行时间最长的语句
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10;”
# 查询调用次数最多的语句
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY calls DESC LIMIT 10;”
# 查询返回行数最多的语句
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY rows DESC LIMIT 10;”
3.3.2 分析执行计划
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”
# 重置统计信息
$ sudo -u pgsql psql -c “SELECT pg_stat_statements_reset();”
pg_stat_statements_reset
————————–
(1 row)
# 查看详细的统计信息
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, min_exec_time, max_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 FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;”
3.3.3 监控特定数据库的查询
$ sudo -u pgsql psql -d fgedu_production -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;”
# 监控特定用户的查询
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements WHERE fgeduid = (SELECT oid FROM pg_fgedu WHERE usename = ‘postgres’) ORDER BY mean_exec_time DESC LIMIT 10;”
# 监控特定类型的查询
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements WHERE query LIKE ‘%SELECT%’ ORDER BY mean_exec_time DESC LIMIT 10;”
Part04-生产案例与实战讲解
4.1 启用pg_stat_statements案例
4.1.1 案例描述
场景：一个生产环境的PostgreSQL数据库，需要启用pg_stat_statements扩展来监控和分析SQL语句的执行情况。
4.1.2 实施方案
$ lsb_release -a
Distributor ID: Ubuntu
Description: Ubuntu 22.04.3 LTS
Release: 22.04
Codename: jammy
$ pgsql –version
pgsql (PostgreSQL) 14.10
# 2. 配置shared_preload_libraries参数
$ sudo vi /postgresql/data/postgresql.conf
# 修改shared_preload_libraries参数
shared_preload_libraries = ‘pg_stat_statements’
# 保存并退出
# 3. 重新启动PostgreSQL服务
$ sudo systemctl restart postgresql
# 4. 验证修改结果
$ sudo -u pgsql psql -c “SHOW shared_preload_libraries;”
shared_preload_libraries
———————————–
pg_stat_statements
(1 row)
# 5. 创建pg_stat_statements扩展
$ sudo -u pgsql psql -c “CREATE EXTENSION pg_stat_statements;”
CREATE EXTENSION
# 6. 配置pg_stat_statements参数
$ sudo vi /postgresql/data/postgresql.conf
# 添加pg_stat_statements配置
pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.track_planning = on
pg_stat_statements.save = on
# 保存并退出
# 7. 重新启动PostgreSQL服务
$ sudo systemctl restart postgresql
# 8. 验证pg_stat_statements配置
$ sudo -u pgsql psql -c “SHOW pg_stat_statements.max;”
pg_stat_statements.max
————————
10000
(1 row)
$ sudo -u pgsql psql -c “SHOW pg_stat_statements.track;”
pg_stat_statements.track
————————–
all
(1 row)
# 9. 测试pg_stat_statements
$ sudo -u pgsql psql -d fgedu_production -c “SELECT * FROM fgedu_employees WHERE salary > 8000;”
# 查看统计信息
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 5;”
4.2 pg_stat_statements分析案例
4.2.1 案例描述
场景：通过pg_stat_statements发现了一些慢查询，需要分析这些查询的执行情况并找出性能瓶颈。
4.2.2 实施方案
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;”
queryid | query | calls | total_exec_time | mean_exec_time | rows
————+—————————————————+——-+—————-+—————-+——
1234567890 | SELECT * FROM fgedu_employees WHERE salary > $1 | 100 | 50000.00 | 500.00 | 1000
2345678901 | SELECT * FROM fgedu_fgfgfgfgsales WHERE amount > $1 | 50 | 20000.00 | 400.00 | 500
# 2. 分析慢查询执行计划
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”
QUERY PLAN
———————————————————————————————————————-
Seq Scan on fgedu_employees (cost=0.00..1000.00 rows=1000 width=100) (actual time=0.010..5.000 rows=1000 loops=1)
Filter: (salary > 8000)
Rows Removed by Filter: 9000
Planning Time: 0.100 ms
Execution Time: 5.100 ms
# 3. 分析表结构和索引
$ sudo -u pgsql psql -d fgedu_production -c “\d fgedu_employees”
Table “public.fgedu_employees”
Column | Type | Collation | Nullable | Default
———-+———+———–+———-+————————————–
id | integer | | not null | nextval(‘fgedu_employees_id_seq’::regclass)
name | text | | not null |
department | text | | not null |
salary | numeric | | not null |
hire_date | date | | not null |
Indexes:
“fgedu_employees_pkey” PRIMARY KEY, btree (id)
# 4. 分析统计信息
$ sudo -u pgsql psql -d fgedu_production -c “SELECT * FROM pg_stat_fgedu_tables WHERE relname = ‘fgedu_employees’;”
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | last_vacuum | last_analyze | vacuum_count | analyze_count
——-+————+—————+———-+————–+———-+—————+———–+———–+———–+—————+————+————+———————+————-+————–+————–+—————
12345 | public | fgedu_employees | 1000 | 1000000 | 0 | 0 | 10000 | 0 | 0 | 0 | 10000 | 0 | 0 | | 2026-04-01 | 0 | 1
# 5. 分析原因
# 原因：
# 1. 没有为salary列创建索引，导致全表扫描
# 2. 表数据量较大，全表扫描需要处理大量数据
# 3. 统计信息已更新，但索引缺失
4.3 pg_stat_statements优化案例
4.3.1 案例描述
场景：通过pg_stat_statements分析发现了性能瓶颈，需要通过创建索引和优化查询语句来提高性能。
4.3.2 实施方案
$ sudo -u pgsql psql -d fgedu_production -c “CREATE INDEX idx_fgedu_employees_salary ON fgedu_employees(salary);”
# 2. 验证索引创建
$ sudo -u pgsql psql -d fgedu_production -c “\d fgedu_employees”
Table “public.fgedu_employees”
Column | Type | Collation | Nullable | Default
———-+———+———–+———-+————————————–
id | integer | | not null | nextval(‘fgedu_employees_id_seq’::regclass)
name | text | | not null |
department | text | | not null |
salary | numeric | | not null |
hire_date | date | | not null |
Indexes:
“fgedu_employees_pkey” PRIMARY KEY, btree (id)
“idx_fgedu_employees_salary” btree (salary)
# 3. 分析优化后的查询执行计划
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”
QUERY PLAN
———————————————————————————————————————-
Bitmap Heap Scan on fgedu_employees (cost=10.00..100.00 rows=1000 width=100) (actual time=0.010..0.500 rows=1000 loops=1)
Recheck Cond: (salary > 8000)
Heap Blocks: exact=100
-> Bitmap Index Scan on idx_fgedu_employees_salary (cost=0.00..9.75 rows=1000 width=0) (actual time=0.005..0.005 rows=1000 loops=1)
Index Cond: (salary > 8000)
Planning Time: 0.100 ms
Execution Time: 0.600 ms
# 4. 优化查询语句
# 优化前的查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT * FROM fgedu_employees WHERE salary > 8000;”
# 优化后的查询
$ sudo -u pgsql psql -d fgedu_production -c “EXPLAIN ANALYZE SELECT id, name, salary FROM fgedu_employees WHERE salary > 8000;”
QUERY PLAN
———————————————————————————————————————-
Index Only Scan using idx_fgedu_employees_salary on fgedu_employees (cost=0.29..50.00 rows=1000 width=50) (actual time=0.005..0.300 rows=1000 loops=1)
Index Cond: (salary > 8000)
Heap Fetches: 0
Planning Time: 0.100 ms
Execution Time: 0.400 ms
# 5. 验证优化效果
# 重置统计信息
$ sudo -u pgsql psql -c “SELECT pg_stat_statements_reset();”
# 执行优化后的查询
$ sudo -u pgsql psql -d fgedu_production -c “SELECT id, name, salary FROM fgedu_employees WHERE salary > 8000;”
# 查看统计信息
$ sudo -u pgsql psql -c “SELECT queryid, query, calls, total_exec_time, mean_exec_time, rows FROM pg_stat_statements ORDER BY mean_exec_time ASC;”
# 比较优化前后的执行时间
# 优化前：5.100 ms
# 优化后：0.400 ms
# 性能提升：约12倍
Part05-风哥经验总结与分享
5.1 pg_stat_statements最佳实践
pg_stat_statements最佳实践：
- 合理配置：根据系统资源和监控需求，合理配置pg_stat_statements参数
- 定期分析：定期查询pg_stat_statements视图，分析慢查询和性能瓶颈
- 及时优化：根据分析结果，及时优化慢查询和性能瓶颈
- 结合其他工具：结合其他监控工具，如pgBadger、Prometheus等，全面监控数据库性能
- 重置统计信息：在进行性能测试或优化后，重置统计信息，以便准确评估优化效果
- 监控趋势：分析性能趋势，预测潜在问题，提前采取措施
- 文档记录：记录分析结果和优化措施，便于后续参考
- 持续改进：持续监控和优化，确保系统性能
5.2 pg_stat_statements常见问题
pg_stat_statements常见问题及解决方案：
- 内存使用过高：解决方案：调整pg_stat_statements.max参数，减少存储的语句数
- 性能影响：解决方案：在生产环境中合理配置参数，平衡监控需求和性能影响
- 统计信息不准确：解决方案：定期重置统计信息，确保统计信息的准确性
- 语句被截断：解决方案：调整postgresql.conf中的track_activity_query_size参数
- 扩展未启用：解决方案：确保正确配置shared_preload_libraries参数并创建扩展
- 统计信息丢失：解决方案：设置pg_stat_statements.save = on，在服务器关闭时保存统计信息
- 查询计划信息不完整：解决方案：设置pg_stat_statements.track_planning = on，跟踪规划时间
- 工具语句未跟踪：解决方案：设置pg_stat_statements.track_utility = on，跟踪工具语句
5.3 pg_stat_statements使用技巧
pg_stat_statements使用技巧：
- 查询优化：
- 按执行时间排序，识别慢查询
- 按调用次数排序，识别频繁执行的查询
- 按返回行数排序，识别返回大量数据的查询
- 结合执行计划分析，找出性能瓶颈
- 监控策略：
- 定期执行分析脚本，自动识别慢查询
- 设置告警机制，当出现性能问题时及时告警
- 结合第三方工具，实现可视化监控
- 建立性能基准，评估优化效果
- 性能调优：
- 为慢查询创建合适的索引
- 优化查询语句，减少不必要的计算和数据传输
- 调整PostgreSQL配置参数，优化系统性能
- 定期更新统计信息，确保优化器生成正确的执行计划
- 故障排查：
- 使用pg_stat_statements快速定位性能问题
- 分析查询执行情况，找出问题原因
- 验证优化措施的效果
- 建立性能问题的排查流程
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息
