linying

文章目录

PostgreSQL pg_stat_statements：解锁性能分析的秘密武器

发布日期: 2024-06-11
分类: PostgreSQL
标签: PostgreSQL 数据库

欢迎体验这篇关于 PostgreSQL pg_stat_statements 扩展的教学指南！如果你正在使用 PostgreSQL 管理数据库，并且希望深入了解数据库的性能瓶颈，pg_stat_statements 是一个不可或缺的工具。这篇文章将以通俗易懂的方式，带你从零开始掌握 pg_stat_statements 的功能、安装方法、数据解读技巧，以及如何利用它优化数据库性能。无论你是数据库新手还是希望提升分析能力的开发者，这篇指南都将为你提供实用的知识和实践方法。

1. 什么是 pg_stat_statements？

pg_stat_statements 是 PostgreSQL 的一个扩展模块，用于跟踪数据库中执行的 SQL 语句的性能统计信息。它记录每条 SQL 语句的执行频率、耗时、资源消耗等详细信息，帮助你识别慢查询、资源密集型查询以及潜在的性能瓶颈。相比于其他监控工具，pg_stat_statements 的独特优势在于：

  • 轻量级：它直接集成在 PostgreSQL 中，资源占用极低。
  • 全面性：捕获所有类型的 SQL 语句（包括 SELECT、INSERT、UPDATE、DELETE 等）。
  • 规范化：将相似查询归一化为模板（如将 SELECT * FROM users WHERE id = 1 和 SELECT * FROM users WHERE id = 2 视为同一查询），便于分析。

通过 pg_stat_statements，你可以回答以下问题：

  • 哪些查询执行最频繁？
  • 哪些查询耗时最长或消耗最多资源？
  • 数据库的性能瓶颈在哪里？

在开始之前，确保你：

  • 已安装 PostgreSQL（推荐最新稳定版本，如 17.x）。
  • 能通过 psql 或 GUI 工具（如 pgAdmin）访问数据库。
  • 有管理员权限以安装扩展和修改配置文件。

接下来，我们将从安装开始，逐步探索 pg_stat_statements 的强大功能。

2. 安装与配置 pg_stat_statements

2.1 安装扩展

pg_stat_statements 是 PostgreSQL 的内置扩展，通常随数据库安装，但需要手动启用。以下是安装步骤：

  1. 检查扩展是否可用： 在 psql 中运行以下命令，确认扩展存在：

    1

    SELECT * FROM pg_available_extensions WHERE name = 'pg_stat_statements';

    如果返回结果包含 pg_stat_statements，说明扩展可用。

  2. 修改配置文件： 编辑 postgresql.conf 文件，启用 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            # 跟踪 utility 命令（如 CREATE、ALTER）
pg_stat_statements.save = on                     # 保存统计信息到重启后
    • shared_preload_libraries：确保 pg_stat_statements 在数据库启动时加载。
    • pg_stat_statements.max：设置跟踪的唯一查询数量，默认 5000，建议根据负载调整。
    • pg_stat_statements.track：可选值包括 top（仅顶层查询）、all（包括嵌套查询）。

  3. 重启数据库： 修改配置文件后，重启 PostgreSQL 使配置生效：

    1

    sudo systemctl restart postgresql

  4. 在数据库中启用扩展： 连接到目标数据库，运行以下命令：

    1

    CREATE EXTENSION pg_stat_statements;

    这会在当前数据库中创建 pg_stat_statements 视图。

  5. 验证安装： 检查扩展是否启用：

    1

    SELECT * FROM pg_stat_statements LIMIT 1;

    如果返回数据或空结果（无查询统计时），说明扩展已成功安装。

2.2 注意事项

  • 权限：只有超级用户或有 CREATE 权限的用户可以启用扩展。
  • 多数据库：需要在每个需要监控的数据库中运行 CREATE EXTENSION。
  • 资源开销：pg_stat_statements 的内存占用与 pg_stat_statements.max 成正比，建议根据服务器内存调整。

3. pg_stat_statements 视图详解

启用扩展后，pg_stat_statements 会在数据库中创建一个视图 pg_stat_statements，记录每条规范化 SQL 语句的统计信息。以下是视图中的关键列及其含义：

列名 描述
userid 执行查询的用户的 OID。
dbid 数据库的 OID。
queryid 查询的唯一标识符（基于查询文本的哈希）。
query 规范化后的查询文本（如 SELECT * FROM users WHERE id = $1）。
calls 查询执行的次数。
total_exec_time 查询总执行时间（毫秒，PostgreSQL 13 及以上）。
rows 查询返回或影响的行数。
shared_blks_hit 从共享缓冲区命中的块数。
shared_blks_read 从磁盘读取的共享块数。
blk_read_time 读取块的耗时（需启用 track_io_timing）。
temp_blks_written 写入临时文件的块数（通常与排序或哈希操作相关）。

示例查询： 查看执行时间最长的前 5 条查询：

1
2
3
4
5
6
7
8
9

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 5;

输出样例：

query: SELECT * FROM orders WHERE user_id = $1
calls: 10000
total_exec_time: 15000.50
avg_time: 1.50
rows: 50000

4. 使用 pg_stat_statements 进行性能分析

pg_stat_statements 的核心价值在于帮助你发现性能问题。以下是几种常见分析场景及实用查询。

4.1 识别慢查询

慢查询是性能瓶颈的常见原因。以下查询找出平均执行时间最长的查询：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    query,
    calls,
    total_exec_time,
    total_exec_time / calls AS avg_time_ms,
    rows
FROM pg_stat_statements
WHERE calls > 0
ORDER BY avg_time_ms DESC
LIMIT 10;

优化建议：

  • 检查是否缺少索引。例如，如果 SELECT * FROM orders WHERE user_id = $1 很慢，考虑为 user_id 添加索引：

    1

    CREATE INDEX idx_orders_user_id ON orders(user_id);

  • 分析执行计划，使用 EXPLAIN ANALYZE 检查查询是否使用了索引。

4.2 发现高频查询

频繁执行的查询即使单次耗时短，也可能累积大量开销。以下查询找出执行次数最多的查询：

1
2
3
4
5
6
7
8

SELECT
    query,
    calls,
    total_exec_time,
    rows
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

优化建议：

  • 如果高频查询是简单查找，考虑使用缓存（如 Redis）减少数据库压力。
  • 合并相似查询，减少重复执行。

4.3 分析 I/O 密集型查询

高 I/O 消耗可能导致性能下降。以下查询找出读取磁盘块最多的查询：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    query,
    calls,
    shared_blks_read,
    shared_blks_hit,
    (shared_blks_hit::float / (shared_blks_hit + shared_blks_read)) AS hit_ratio
FROM pg_stat_statements
WHERE shared_blks_read + shared_blks_hit > 0
ORDER BY shared_blks_read DESC
LIMIT 10;

优化建议：

  • 提高缓冲区命中率，增加 shared_buffers 配置。
  • 检查表是否需要分区或索引优化。

4.4 监控临时文件使用

大量临时文件写入通常与复杂排序或哈希操作相关。以下查询找出临时文件使用量最高的查询：

1
2
3
4
5
6
7
8
9

SELECT
    query,
    calls,
    temp_blks_written,
    total_exec_time
FROM pg_stat_statements
WHERE temp_blks_written > 0
ORDER BY temp_blks_written DESC
LIMIT 10;

优化建议：

  • 增加 work_mem 配置，减少临时文件写入：

    1

    SET work_mem = '16MB';

  • 优化查询，避免不必要的排序或分组。

5. 实际案例分析

假设你管理一个博客平台的数据库，包含 posts 和 comments 表。用户反馈页面加载变慢，你决定使用 pg_stat_statements 排查问题。

5.1 场景：慢查询排查

运行以下查询，发现一条耗时高的查询：

1
2
3
4

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

结果：

query: SELECT * FROM comments WHERE post_id = $1 ORDER BY created_at DESC
total_exec_time: 20000.00
calls: 5000
avg_time: 4.00

分析：

  • 该查询平均耗时 4ms，累计耗时 20 秒，说明是性能瓶颈。
  • 使用 EXPLAIN ANALYZE 检查执行计划，发现缺少 post_id 的索引。

解决方案： 创建索引：

1

CREATE INDEX idx_comments_post_id ON comments(post_id);

重新运行查询，观察 total_exec_time 是否减少。

5.2 场景：高频查询优化

发现一条高频查询：

1
2
3
4

SELECT query, calls, total_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 1;

结果：

query: SELECT title FROM posts WHERE id = $1
calls: 100000
total_exec_time: 5000.00

分析：

  • 该查询执行 10 万次，累计耗时 5 秒，虽然单次耗时短，但频率高。
  • 考虑缓存查询结果。

解决方案： 在应用层使用 Redis 缓存 posts 表的标题，减少数据库查询。

6. 优化与高级技巧

6.1 重置统计信息

pg_stat_statements 的统计信息会累积，可能包含过时数据。定期重置以聚焦当前性能：

1

SELECT pg_stat_statements_reset();

6.2 启用 I/O 计时

要分析磁盘 I/O 耗时，启用 track_io_timing：

1
2

ALTER SYSTEM SET track_io_timing = on;
SELECT pg_reload_conf();

然后检查 blk_read_time 和 blk_write_time 列。

6.3 结合其他视图

将 pg_stat_statements 与其他系统视图结合，获取更全面的分析：

  • pg_stat_activity：查看当前运行的查询，排查锁冲突。
  • pg_stat_all_tables：检查表上的死元组，优化 autovacuum。

示例： 找出死元组多且查询耗时高的表：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    pss.query,
    pss.total_exec_time,
    pst.relname,
    pst.n_dead_tup
FROM pg_stat_statements pss
JOIN pg_stat_all_tables pst ON pss.relid = pst.relid
WHERE pst.n_dead_tup > 0
ORDER BY pss.total_exec_time DESC
LIMIT 10;

6.4 自动化监控

使用外部工具（如 Prometheus + Grafana）监控 pg_stat_statements 数据，设置慢查询告警。例如，导出 total_exec_time 和 calls 到 Prometheus。

7. 常见问题与解决方案

7.1 查询文本被截断

症状：query 列中的 SQL 文本不完整。 解决方案：

  • 增加 pg_stat_statements.track_utility 参数：

    pg_stat_statements.track_utility = on

  • 检查 track_activity_query_size（默认 1024 字节），必要时增加：

    track_activity_query_size = 2048

7.2 统计信息不准确

症状：total_exec_time 或 calls 数据异常。 解决方案：

  • 确保 pg_stat_statements.track = all。
  • 重置统计信息：SELECT pg_stat_statements_reset();。
  • 检查是否有多实例干扰（如主从复制）。

7.3 性能开销过高

症状：启用 pg_stat_statements 后数据库变慢。 解决方案：

  • 降低 pg_stat_statements.max（如 5000）。
  • 限制跟踪范围，设置 pg_stat_statements.track = top。

8. 总结与实践建议

通过本文，你深入了解了 pg_stat_statements 的安装、功能和性能分析方法。以下是一些实践建议：

  • 定期分析：每周运行慢查询和高频查询分析，优化索引和查询。
  • 结合工具：使用 pgAdmin 或 Grafana 可视化 pg_stat_statements 数据。
  • 谨慎重置：重置统计信息前备份数据（如导出到 CSV）。
  • 持续学习：结合 EXPLAIN ANALYZE 和 autovacuum 优化，全面提升性能。

动手实践：

  1. 在你的数据库中启用 pg_stat_statements，运行示例查询查看慢查询。
  2. 针对最慢的查询，使用 EXPLAIN ANALYZE 分析并创建索引。
  3. 监控高频查询，尝试在应用层添加缓存。
  4. 设置日志记录慢查询（log_min_duration_statement = 1000），与 pg_stat_statements 对比。

希望这篇指南能帮助你利用 pg_stat_statements 解锁 PostgreSQL 性能分析的潜力！如果有任何问题或需要进一步探讨，欢迎留言交流。

评论 0