一、安装pg_stat_statements扩展

1.1 检查扩展是否已安装

1
SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

1.2 安装方式

Linux环境安装

  1. 安装插件
1
2
3
4
5
6
# Ubuntu/Debian
sudo apt-get install postgresql-contrib

# CentOS/RHEL
sudo yum install postgresql-contrib

  1. 修改配置文件

    打开 postgresql.conf 文件，找到或添加以下行：

    shared_preload_libraries = ‘pg_stat_statements’

  2. 保存配置文件后，重启 PostgreSQL：

    1
    sudo systemctl restart postgresql

Docker环境安装

  1. 修改配置
1
2
docker exec -it <container_name> /bin/bash
echo "shared_preload_libraries = 'pg_stat_statements'" >> /var/lib/postgresql/data/postgresql.conf
  1. 重启容器
1
docker restart <container_name>

1.3 启用扩展

1
CREATE EXTENSION pg_stat_statements;

二、常用QPS监控查询

2.1 实时QPS查询

1
2
3
4
5
6
7
with
 t1 as (select sum(calls) n from pg_stat_statements),
 t2 as (select sum(calls) n from pg_stat_statements , pg_sleep(1))
select
 t2.n-t1.n the_num_of_queries_per_second
from
 t1,t2;

2.2 QPS 从服务开启到现在

1
2
SELECT sum(calls) / EXTRACT(EPOCH FROM NOW() - pg_postmaster_start_time())
from pg_stat_statements;

2.4 查询执行次数TOP 10

1
2
3
4
5
6
7
8
SELECT query, 
       calls,
       total_exec_time,
       rows,
       (total_exec_time/calls) as avg_time
FROM pg_stat_statements 
ORDER BY calls DESC 
LIMIT 10;

2.5 查询耗时TOP 10

1
2
3
4
5
6
7
8
SELECT query,
       calls,
       total_exec_time,
       (total_exec_time/calls) as avg_time,
       rows
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 10;

2.6 查询返回行数TOP 10

1
2
3
4
5
6
7
SELECT query,
       calls,
       rows,
       (rows/calls) as avg_rows
FROM pg_stat_statements 
ORDER BY rows DESC 
LIMIT 10;

2.7 缓存命中率低的查询

1
2
3
4
5
6
7
8
SELECT query,
       shared_blks_hit,
       shared_blks_read,
       (shared_blks_hit::float / NULLIF(shared_blks_hit + shared_blks_read, 0)) AS hit_rate
FROM pg_stat_statements
WHERE (shared_blks_hit + shared_blks_read) > 0
ORDER BY hit_rate ASC
LIMIT 10;

2.8 IO消耗TOP 10

1
2
3
4
5
6
7
8
SELECT query,
       calls,
       shared_blks_read,
       shared_blks_written,
       temp_blks_written
FROM pg_stat_statements
ORDER BY (shared_blks_read + shared_blks_written + temp_blks_written) DESC
LIMIT 10;

三、注意事项

  1. 性能影响

    • 启用pg_stat_statements会带来轻微性能开销
    • 建议在非高峰期进行监控分析

  2. 统计重置

    1
    SELECT pg_stat_statements_reset();

  3. 监控建议

    • 定期检查高频查询的性能
    • 关注缓存命中率低的SQL
    • 分析临时文件使用情况

  4. 优化方向

    • 针对慢查询优化索引
    • 调整数据库配置参数
    • 优化SQL语句结构

四、参考文档