PostgreSQL查询性能监控

PostgreSQL查询性能监控通过实时追踪SQL执行效率，识别慢查询与资源瓶颈。借助工具pg_stat_statements分析执行计划、索引利用率及锁竞争情况。结合日志能够快速定位性能问题，优化查询语句，保障系统高并发下的稳定性和响应速度。

安装查询性能监控扩展

安装pg_stat_statements 扩展

pg_stat_statements 是 PostgreSQL 提供的一个扩展，可以收集关于执行的 SQL 语句的统计信息，包括每个查询的执行时间、调用次数等。

安装步骤：

• 找到并修改 postgresql.conf 文件（目录可以使用命令show config_file查找），加入下面代码启用该扩展：
  shared_preload_libraries = 'pg_stat_statements'

还有一些参数可以进行设置：
pg_stat_statements.max = 10000 #保留多少条统计信息，默认值5000

pg_stat_statements.track = all #记录所有sql

pg_stat_statements.track_utility = off #是否跟踪非DML语句

pg_stat_statements.save = on #重启之后是否保留统计信息

• 重新启动PG实例：systemctl restart postgresql-13，运行下面命令检查是否加载扩展：SHOW shared_preload_libraries;
  
• 创建 pg_stat_statements 扩展：CREATE EXTENSION pg_stat_statements;
  （运行下面命令可以验证是否启用扩展，SELECT*FROM pg_extension WHERE extname ='pg_stat_statements';

Docker 容器中安装扩展

如果是在 Docker 容器中运行 PostgreSQL，则需要在容器内修改配置文件。

• 获取容器id或名称，docker ps
  
• 进入容器shell， docker exec -it <container_id_or_name> bash
  
• 修改 shared_preload_libraries 配置项：shared_preload_libraries = 'pg_stat_statements'，并重启容器：
  docker restart <container_id_or_name>
  

查看慢查询

查询 pg_stat_statements 视图来查看慢查询

SELECT 
    query, 
    total_exec_time AS total_time, 
    calls, 
    total_exec_time / calls AS avg_time 
FROM 
    pg_stat_statements
ORDER BY 
    total_exec_time DESC
LIMIT 10;

可能出现的问题

错误: pg_stat_statements must be loaded via shared_preload_libraries

如果按照上述步骤操作出现该报错，很有可能是配置未生效/配置被覆盖

输入sql：select name,setting,source,sourcefile,sourceline,pending_restart from pg_settings where name = 'shared_preload_libraries';

检查sourcefile文件中配置，

数据库性能测试

下面对使用pg_stat_statements是否会影响数据库性能进行测试。

• 安装pgbench
  pgbench -U myuser -i -s 10 pgbench_test
• pgbench -U myuser -i -s 10 pgbench_test ，初始化测试数据库（在初始化前建好pgbench_test）
• 进行基准测试： pgbench -U myuser -c 10 -j 2 -T 60 pgbench_test
  • -c 10：模拟 10 个并发客户端。
  • -j 2：使用 2 个线程。
  • -T 60：进行 60 秒的负载测试。
• 

从上可以看出，pg_stat_statements对性能产生的影响很小，可忽略不计。