纲要

• pg_stat_statements —— 官方内核级 SQL 语句执行统计
  • 启用配置与扩展安装
  • 核心视图字段与衍生指标（QPS、平均耗时、缓存命中率）
  • 管理函数与重置统计
• 第三方性能监控与分析平台
  • pganalyze —— 企业级全栈监控与自动调优建议
  • pgBadger —— 基于日志的高性能报告生成器
• 命令行实时监控工具
  • pgCenter —— PostgreSQL 版 top，支持进程级 I/O 与等待事件
  • pgactivity —— 进程级详细监控，含读写 IOPS 与内存
  • pgtop —— 轻量级进程监控，类 top 交互
• 执行计划可视化工具
  • pev —— 执行计划树形图
  • dalibo —— 图形化执行计划分析
  • pgMustard —— 智能优化建议与计划解析
• 配置参数自动调优工具
  • PGConfigurator —— 基于负载与硬件的自适应参数推荐
  • pgtune —— 简易参数配置生成器
• 扩展监控插件
  • pg_stat_kcache —— SQL 级 CPU 与内存消耗统计
  • pg_stat_monitor —— 增强版查询性能监控
  • pg_sampler —— 历史 SQL 执行采样
  • system_stats —— 数据库内直接访问操作系统指标
• 操作系统层诊断命令集
  • top / htop —— 系统资源总览
  • mpstat —— CPU 多核统计
  • perf —— 性能剖析与指令级分析
  • sar —— 系统活动全能报告
  • vmstat —— 虚拟内存与进程状态
  • dstat —— 综合统计（CPU、磁盘、网络、换页等）
  • iostat / iotop —— 磁盘 I/O 监控
  • blktrace —— I/O 栈各阶段耗时追踪
  • nload / nmon —— 网络流量监控

内核插件：pg_stat_statements — 性能诊断的基石

pg_stat_statements 是 PostgreSQL 官方 contrib 模块中最重要的性能追踪工具。它记录服务器上所有执行过的 SQL 语句的计划和执行统计信息，是多数第三方监控工具的数据来源。

启用与配置

该模块需要预加载共享库，因此必须修改 postgresql.conf 并重启数据库：

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
compute_query_id = on                     # 启用查询标识
pg_stat_statements.track = top            # 跟踪顶层语句
pg_stat_statements.track_utility = on     # 跟踪工具命令（如 VACUUM）
pg_stat_statements.track_planning = on    # 跟踪计划生成耗时
pg_stat_statements.max = 10000            # 存储的最大语句数

重启后，在目标数据库中执行扩展创建命令：

CREATE EXTENSION pg_stat_statements;

核心视图字段解析

视图 pg_stat_statements 为每个 (userid, dbid, queryid, top_level) 组合提供一行统计。关键字段如下：

字段	类型	说明
calls	bigint	执行总次数
total_exec_time	double precision	总执行时间（毫秒）
min_exec_time	double precision	最短执行时间
max_exec_time	double precision	最长执行时间
mean_exec_time	double precision	平均执行时间
rows	bigint	处理或影响的总行数
shared_blks_hit	bigint	共享缓冲区命中次数
shared_blks_read	bigint	从磁盘读取的共享块数
shared_blks_dirtied	bigint	脏化的共享块数
shared_blks_written	bigint	写入磁盘的共享块数
temp_blks_read	bigint	临时文件读取块数
temp_blks_written	bigint	临时文件写入块数
blk_read_time	double precision	块读取耗时（需 track_io_timing 启用）
blk_write_time	double precision	块写入耗时（需 track_io_timing 启用）
wal_records	bigint	产生的 WAL 记录数
wal_fpi	bigint	产生的 WAL 全页镜像数
wal_bytes	numeric	产生的 WAL 总字节数

衍生指标与常用分析 SQL

利用累积统计可计算 QPS、平均耗时、缓存命中率等关键性能指标：

-- 1. QPS（每秒查询数，基于自服务器启动以来的总调用数）
SELECT 
    sum(calls) / (EXTRACT(epoch FROM now() - pg_postmaster_start_time())) AS qps
FROM pg_stat_statements;

-- 2. 按总耗时降序排列，找出负载最高的 Top 20 查询
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    100.0 * total_exec_time / sum(total_exec_time) OVER() AS load_percent
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 3. 缓存命中率低于 90% 的查询（可能存在 I/O 瓶颈）
SELECT 
    query,
    calls,
    shared_blks_hit,
    shared_blks_read,
    100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_ratio
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
  AND 100.0 * shared_blks_hit / (shared_blks_hit + shared_blks_read) < 90
ORDER BY shared_blks_read DESC;

-- 4. 临时文件使用量最大的查询（可能导致性能问题）
SELECT 
    query,
    calls,
    temp_blks_read,
    temp_blks_written
FROM pg_stat_statements
ORDER BY (temp_blks_read + temp_blks_written) DESC
LIMIT 10;

管理函数

-- 重置所有统计
SELECT pg_stat_statements_reset();

-- 通过函数直接获取（可控制是否返回查询文本）
SELECT * FROM pg_stat_statements(true);

权限说明：普通用户只能看到自己执行的语句；超级用户或具有 pg_read_all_stats 角色的用户可查看所有语句。

第三方监控平台

pganalyze — 企业级全栈监控

pganalyze 是一款面向 PostgreSQL 的专业性能监控产品，提供云服务和本地部署两种形态。其数据采集器基于 Go 实现，收集 pg_stat_statements、pg_stat_activity、pg_stat_database 以及操作系统指标（CPU、内存、磁盘）。核心功能包括：

• 查询性能分析：自动聚合相似 SQL，计算执行时间、调用频率、缓存命中率等
• Query Advisor：基于执行计划与统计信息，自动识别全表扫描、索引缺失等问题并给出优化建议
• 集群级统一看板：支持多数据库、多实例的集中监控
• 历史数据归档：最长可保留 60 天历史，便于趋势分析

pgBadger — 日志分析报告生成器

pgBadger 是由 Gilles Darold（ora2pg 作者）开发的 Perl 日志分析工具，能以极高速度解析 PostgreSQL 日志并生成 HTML5 报表。

# 安装（源码方式）
git clone https://github.com/darold/pgbadger.git
cd pgbadger
perl Makefile.PL
make && sudo make install

# 生成报告
pgbadger -o report.html /var/log/postgresql/postgresql-*.log

# 分析最近 7 天日志
pgbadger -q $(find /var/log/postgresql/ -mtime -7 -name "postgresql-*.log") -o weekly_report.html

报表包含慢查询分布、高频查询、锁等待、连接统计、错误日志、临时文件使用、检查点等丰富图表，所有图表均可缩放并导出为 PNG。

命令行实时监控工具

pgCenter — PostgreSQL 版 top

pgCenter 使用 Go 编写，提供类似 top 的交互界面，可实时查看 PostgreSQL 进程状态。

# 通过 Docker 运行
docker pull lesovsky/pgcenter:latest
docker run -it --rm lesovsky/pgcenter:latest pgcenter top -h 127.0.0.1 -U postgres -d postgres -p 5432

# 直接安装运行
pgcenter top -h 127.0.0.1 -U postgres -d postgres

pgCenter 支持快捷键切换视图（如 Shift+S 显示每进程系统统计，含 CPU 利用率、I/O 吞吐量、I/O 等待时间），并可查看配置文件与日志文件，无需退出监控界面。

pgactivity — 进程级详细监控

pgactivity 提供比 pgCenter 更详细的进程级信息，包括每秒读写 IOPS、内存占用、等待事件（如 transactionid 等待）、会话总数及工作进程分类。

# 通过 pip 安装
pip install pgactivity

# 运行
pgactivity -h localhost -U postgres -d postgres

pgtop — 轻量级进程监控

pgtop 是最简洁的 PostgreSQL 进程监控工具，交互方式与系统 top 一致：

# 安装（通常由系统包管理器提供）
# 例如 Ubuntu: sudo apt-get install pgtop

pgtop -h localhost -U postgres

支持快捷键：c 显示完整命令，m 按内存排序，e 切换内存单位。

执行计划可视化工具

传统 EXPLAIN 输出冗长且难以直观分析，以下工具将执行计划图形化，降低分析门槛。

pev — 树形图可视化

pev（PostgreSQL Explain Visualizer）将 EXPLAIN (ANALYZE, BUFFERS) 的输出渲染为树形图，清晰展示每个节点的实际行数、耗时和缓冲区命中情况。

# 在线使用或本地部署
# 访问 https://tatiyants.com/pev 粘贴计划文本即可

dalibo — 图形化执行计划分析

dalibo（由 Dalibo 公司开发）同样提供树形计划可视化，并额外标注高成本节点，支持计划对比。

pgMustard — 智能优化建议

pgMustard 不仅可视化执行计划，还内置了基于 PostgreSQL 内部知识的分析引擎，能够针对每个节点给出具体的优化提示，例如：

• 建议创建缺失的索引（并给出候选列组合）
• 检测低效的排序或哈希操作
• 提示统计信息过时

# 访问 https://www.pgmustard.com 上传计划文本

配置参数自动调优工具

PGConfigurator — 基于负载的自适应推荐

PGConfigurator（亦称 pgconfigurator）是一款交互式 Web 工具，根据用户输入的硬件配置（内存、CPU 核心数、磁盘类型）和工作负载特征（OLTP、混合负载、分析型）生成推荐的 postgresql.conf 参数值。

# 在线使用地址
# https://pgconfigurator.cybertec.at/

可指定的参数包括：

• 内存相关：shared_buffers、effective_cache_size、work_mem、maintenance_work_mem
• 并发相关：max_connections、max_parallel_workers
• 存储相关：checkpoint_completion_target、wal_buffers、commit_delay
• 日志与监控：log_min_duration_statement、track_io_timing

pgtune — 简易配置生成器

pgtune 是一款更轻量的命令行工具，只需提供数据库类型（如 OLTP、数据仓库）、内存大小和 CPU 核心数，即可快速生成一组参数建议。

# 安装（通常通过包管理器）
# 例如 Ubuntu: sudo apt-get install pgtune

pgtune -i postgresql.conf -o postgresql-tuned.conf --type OLTP --memory 32GB --cpus 8

与 PGConfigurator 相比，pgtune 生成的参数较少，但适合快速初始配置。

扩展监控插件

pg_stat_kcache — SQL 级 CPU 与内存统计

原生 pg_stat_statements 仅提供 I/O 与时间统计，不包含 CPU 和内存消耗。pg_stat_kcache 扩展可补充这些指标，记录每个查询的 CPU 时间、系统调用次数、页面错误数、内存占用等。

-- 加载扩展（需预加载）
shared_preload_libraries = 'pg_stat_statements, pg_stat_kcache'

CREATE EXTENSION pg_stat_kcache;

-- 查询消耗 CPU 最多的 SQL
SELECT 
    s.query,
    k.cpu_time,
    k.user_time,
    k.system_time,
    k.minflt,
    k.majflt
FROM pg_stat_statements s
JOIN pg_stat_kcache k ON s.queryid = k.queryid
ORDER BY k.cpu_time DESC
LIMIT 10;

pg_stat_monitor — 增强版查询监控

pg_stat_monitor 是 pg_stat_statements 的增强分支，由 Percona 维护，提供更细粒度的统计维度，例如：

• 按时间桶聚合（可统计每分钟的 QPS 变化）
• 计划与执行阶段分别计时
• 包含查询标签（如应用名称、用户）

-- 加载扩展
CREATE EXTENSION pg_stat_monitor;

-- 查看按分钟统计的查询负载
SELECT 
    bucket,
    query,
    calls,
    total_time
FROM pg_stat_monitor
ORDER BY bucket DESC;

pg_sampler — 历史 SQL 采样

pg_sampler 定期采样 pg_stat_activity 并存储历史快照，便于事后回溯当时正在运行的查询。它类似于 Oracle 的 ASH（Active Session History）功能。

system_stats — 数据库内访问操作系统指标

system_stats 扩展将操作系统级指标（如 CPU 负载、内存使用、磁盘空间）封装为数据库函数，允许没有 SSH 权限的数据库用户直接查询服务器状态。

CREATE EXTENSION system_stats;

-- 查看 CPU 信息
SELECT * FROM pg_cpu_info();

-- 查看内存使用
SELECT * FROM pg_memory_info();

-- 查看磁盘空间
SELECT * FROM pg_disk_info();

操作系统层诊断命令集

数据库性能问题常常根植于操作系统层面，以下工具是 DBA 必备的诊断利器。

CPU 相关

• top / htop：实时显示进程 CPU 与内存占用，htop 提供彩色和交互式界面。
• mpstat -P ALL：显示每个 CPU 核心的使用率，识别单核瓶颈。
• perf：Linux 最强大的性能剖析工具，可采样 CPU 事件并生成火焰图。

# 采样 10 秒，查看 CPU 热点
perf record -a -g -- sleep 10
perf report

# 统计单个命令的 CPU 指令数
perf stat -e cycles,instructions,cache-misses pgbench -c 10 -T 10

内存相关

• vmstat 1：实时显示进程、内存、换页、块 I/O、中断、上下文切换等。
• sar -r：内存利用率历史报告。
• dstat：全能统计工具，可同时显示 CPU、磁盘、网络、换页等。

dstat -c -d -n -m --top-io --top-mem

磁盘 I/O 相关

• iostat -x 1：显示每个磁盘的利用率、吞吐量、平均请求大小和等待时间。
• iotop：按进程显示 I/O 读写速率，可快速找出造成 I/O 瓶颈的 PostgreSQL 进程。
• blktrace：追踪 I/O 请求在块设备层的完整生命周期，从提交到完成各阶段耗时，用于深度分析 I/O 延迟。

# 追踪 sda 设备 10 秒
blktrace -d /dev/sda -w 10
# 分析结果
blkparse -i sda.blktrace

网络相关

• nload：实时显示网络入/出流量。
• nmon：全能监控工具，可按 c（CPU）、m（内存）、d（磁盘）、n（网络）切换视图。
• sar -n DEV 1：网络接口流量统计。

API 速览

本节汇总博客中提到的所有核心扩展与工具的函数接口。

pg_stat_statements 核心函数

函数	签名	说明
pg_stat_statements_reset	pg_stat_statements_reset() → void	清空所有统计计数
pg_stat_statements	pg_stat_statements(showtext boolean) → setof record	直接返回统计视图，showtext 控制是否输出查询文本

pg_stat_kcache 核心视图

视图	主要字段	说明
pg_stat_kcache	queryid, cpu_time, user_time, system_time, minflt, majflt	每个查询的 CPU 与页面错误统计
pg_stat_kcache_detail	更细粒度的系统调用计数	高级诊断

system_stats 核心函数

函数	返回类型	说明
pg_cpu_info()	setof record	CPU 型号、核心数、频率
pg_memory_info()	setof record	总内存、可用内存、交换使用
pg_disk_info()	setof record	每个挂载点的总容量、已用、可用
pg_load_avg()	setof record	1、5、15 分钟负载平均值
pg_network_info()	setof record	网络接口 IP、速率、收发包统计

Demo 简单示例

本 Demo 演示如何在一个 PostgreSQL 实例中启用 pg_stat_statements，执行典型查询负载，然后通过 SQL 分析性能瓶颈。

环境准备（使用 Node.js + pg 驱动）：

mkdir pg-monitor-demo && cd pg-monitor-demo
npm init -y
npm install pg

配置 PostgreSQL（确保 postgresql.conf 已包含上述 shared_preload_libraries 并重启）。

创建数据库与扩展（使用 psql 或 Node.js 脚本）：

-- 手动执行或通过脚本
CREATE DATABASE demo;
\c demo
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_stat_kcache;  -- 可选

Node.js 脚本 demo.js：

const { Client } = require('pg');

const client = new Client({
    host: 'localhost',
    port: 5432,
    database: 'demo',
    user: 'postgres',
    password: 'yourpassword'
});

async function run() {
    await client.connect();

    // 1. 创建测试表并插入数据
    await client.query(`
        CREATE TABLE IF NOT EXISTS orders (
            id SERIAL PRIMARY KEY,
            customer_id INT,
            amount DECIMAL(10,2),
            created_at TIMESTAMP DEFAULT NOW()
        );
    `);

    // 插入 10000 条随机数据
    await client.query(`
        INSERT INTO orders (customer_id, amount)
        SELECT (random() * 1000)::int, (random() * 1000)::decimal
        FROM generate_series(1, 10000);
    `);

    // 2. 执行几条查询（模拟负载）
    for (let i = 0; i < 100; i++) {
        await client.query('SELECT * FROM orders WHERE customer_id = $1', [i % 100]);
        await client.query('SELECT COUNT(*) FROM orders WHERE amount > $1', [500]);
    }

    // 3. 查询 pg_stat_statements 分析负载
    const res = await client.query(`
        SELECT 
            query,
            calls,
            total_exec_time,
            mean_exec_time,
            shared_blks_hit + shared_blks_read AS total_blocks,
            round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS hit_ratio
        FROM pg_stat_statements
        WHERE query NOT LIKE '%pg_stat_statements%'
        ORDER BY total_exec_time DESC
        LIMIT 5;
    `);
    console.table(res.rows);

    // 4. 重置统计（可选）
    // await client.query('SELECT pg_stat_statements_reset()');

    await client.end();
}

run().catch(console.error);

运行说明：

node demo.js

代码说明：

• 使用 pg 驱动连接 PostgreSQL，执行建表、数据插入和查询负载。
• 最后查询 pg_stat_statements 获取按总耗时排序的 Top 5 查询，并计算缓存命中率。
• 该 Demo 演示了如何利用内核扩展实时定位性能瓶颈，适用于开发环境验证。

技术点总结：

• pg_stat_statements 的启用与基本查询。
• 通过累积统计计算缓存命中率，识别 I/O 密集型查询。
• Node.js 与 PostgreSQL 的集成示例。

项目难点与解决方案

核心难点：性能监控数据源分散，内核扩展、日志、操作系统命令各成体系，缺乏统一视图，导致问题定位效率低。

解决方案：

• 以 pg_stat_statements 为核心数据源，构建统一查询接口。
• 结合 pg_stat_kcache 和 system_stats 将 CPU/内存/OS 指标整合到数据库中，通过 SQL 联合查询获得全景。
• 使用 pgBadger 定期生成基线报告，便于历史对比。

广度：覆盖 SQL 统计、执行计划、参数调优、操作系统诊断，形成完整的性能观测闭环。

深度：深入内核级别的等待事件分析、I/O 栈追踪（blktrace）以及 CPU 指令级剖析（perf），能够定位微小的性能退化。

复杂度：涉及多个扩展的配置与协作，需要理解 PostgreSQL 内部统计机制和操作系统的性能计数器，对 DBA 的综合能力要求较高。

官方文档

PostgreSQL 官方文档

• pg_stat_statements
• Monitoring Database Activity
• EXPLAIN
• System Statistics Functions

参考链接

• pgCenter GitHub
• pgactivity GitHub
• pgtune GitHub
• pgBadger GitHub
• pganalyze 官方
• pgMustard 官方
• PGConfigurator 在线工具
• 系统性能分析参考（Linux）

总结

本文系统梳理了 PostgreSQL 性能监控的完整工具链：从内核级 pg_stat_statements 的精细统计，到 pgCenter、pgactivity 等实时命令行工具，再到 pgBadger 的历史日志分析，以及 PGConfigurator 的智能调优推荐。

同时，文章深入介绍了 pg_stat_kcache 和 system_stats 等扩展如何补全 CPU、内存和操作系统指标，并给出了操作系统层面 perf、blktrace 等高级诊断工具的应用场景。结合完整的 Demo 示例，读者可以快速搭建自己的性能监控体系，实现从 SQL 到硬件栈的全链路问题定位。