印刻数据库

外观

PostgreSQL 性能分析工具 ​

内置性能分析工具 ​

PostgreSQL 提供了多种内置的性能分析工具，可以帮助识别和解决性能问题。这些工具包括统计视图、系统函数和命令行工具。

EXPLAIN 与 EXPLAIN ANALYZE ​

EXPLAIN 是 PostgreSQL 最常用的性能分析工具，用于查看查询的执行计划。EXPLAIN ANALYZE 不仅显示执行计划，还会实际执行查询并显示执行时间和其他统计信息。

EXPLAIN 基本用法 ​

sql
-- 查看简单查询的执行计划
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 查看复杂查询的执行计划
EXPLAIN SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.name
ORDER BY order_count DESC;

-- 查看执行计划的详细信息
EXPLAIN (VERBOSE, COSTS, BUFFERS, FORMAT JSON)
SELECT * FROM users WHERE email = 'test@example.com';

EXPLAIN ANALYZE 用法 ​

sql
-- 实际执行查询并查看执行计划
EXPLAIN (ANALYZE, VERBOSE, COSTS, BUFFERS, TIMING)
SELECT * FROM users WHERE email = 'test@example.com';

-- 关闭计时信息（减少开销）
EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM users WHERE email = 'test@example.com';

-- 格式化输出为 JSON
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM users WHERE email = 'test@example.com';

执行计划解读 ​

执行计划包含以下关键信息：

  • 节点类型：如 Seq Scan、Index Scan、Hash Join 等
  • 成本估算：启动成本和总成本
  • 行估算：预计返回的行数
  • 实际执行时间：每个节点的实际执行时间
  • 缓冲区使用情况：共享缓冲区、本地缓冲区和临时缓冲区的使用情况
Seq Scan on users  (cost=0.00..100.00 rows=1 width=100) (actual time=0.100..1.000 rows=1 loops=1)
  Filter: (email = 'test@example.com'::text)
  Rows Removed by Filter: 999
  Buffers: shared hit=10

pg_stat_statements 扩展 ​

pg_stat_statements 是 PostgreSQL 提供的一个扩展，用于收集和统计 SQL 语句的执行信息。通过 pg_stat_statements，可以识别最消耗资源的查询，包括执行时间、内存使用、IO 操作等。

安装与配置 ​

sql
-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 配置 postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pgsql.stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.track_utility = on
pg_stat_statements.save = on

基本用法 ​

sql
-- 查看最消耗时间的查询
SELECT
    substring(query, 1, 100) AS query,
    calls,
    round(total_exec_time::numeric, 2) AS total_time,
    round(mean_exec_time::numeric, 2) AS avg_time,
    rows,
    round(100 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit_rate
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- 查看最频繁执行的查询
SELECT
    substring(query, 1, 100) AS query,
    calls,
    round(total_exec_time::numeric, 2) AS total_time,
    round(mean_exec_time::numeric, 2) AS avg_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

-- 查看最消耗IO的查询
SELECT
    substring(query, 1, 100) AS query,
    shared_blks_read,
    shared_blks_hit,
    temp_blks_written,
    calls,
    round(total_exec_time::numeric, 2) AS total_time
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 20;

-- 重置统计信息
SELECT pg_stat_statements_reset();

统计视图 ​

PostgreSQL 提供了丰富的统计视图，用于监控数据库的运行状态。

pg_stat_activity ​

pg_stat_activity 视图显示当前数据库中的活动会话信息，包括查询内容、执行时间、等待事件等。

sql
-- 查看当前活动会话
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    wait_event_type,
    wait_event,
    query,
    query_start,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

-- 查看长时间运行的查询
SELECT
    pid,
    usename,
    query,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE now() - query_start > interval '5 minutes'
    AND state != 'idle'
ORDER BY duration DESC;

-- 查看等待事件统计
SELECT
    wait_event_type,
    wait_event,
    count(*) AS count,
    state
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY wait_event_type, wait_event, state
ORDER BY count DESC;

pg_stat_database ​

pg_stat_database 视图显示数据库级别的统计信息，包括连接数、事务数、IO 操作等。

sql
-- 查看数据库统计信息
SELECT
    datname,
    numbackends AS active_connections,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    round(100 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_rate,
    temp_files,
    temp_bytes,
    deadlocks
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1')
ORDER BY datname;

pg_stat_user_tables ​

pg_stat_user_tables 视图显示用户表的统计信息，包括扫描次数、更新次数、活行和死行数量等。

sql
-- 查看表统计信息
SELECT
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_live_tup,
    n_dead_tup,
    round(100 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_tuple_ratio,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    autovacuum_count
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

pg_stat_user_indexes ​

pg_stat_user_indexes 视图显示用户索引的使用情况，包括扫描次数、索引元组读取次数等。

sql
-- 查看索引使用情况
SELECT
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- 查看未使用的索引
SELECT
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
    AND pg_relation_size(indexrelid) > 1024 * 1024  -- 只显示大于 1MB 的索引
ORDER BY pg_relation_size(indexrelid) DESC;

命令行性能分析工具 ​

PostgreSQL 提供了多种命令行工具，用于监控和分析数据库性能。

pg_stat ​

pg_stat 是一个命令行工具，用于显示数据库服务器的统计信息。

bash
# 显示数据库统计信息
pg_stat

# 显示指定数据库的统计信息
pg_stat -d app_db

# 显示表统计信息
pg_stat -t users

pg_top ​

pg_top 是一个类似 top 的命令行工具，用于实时监控 PostgreSQL 进程和性能指标。

bash
# 启动 pg_top
pg_top

# 监控特定数据库
pg_top -d app_db

# 显示帮助信息
pg_top -h

pg_waldump ​

pg_waldump 用于查看 WAL 日志的内容，帮助分析 WAL 生成和复制问题。

bash
# 查看 WAL 日志内容
pg_waldump /path/to/wal/000000010000000000000001

# 查看 WAL 日志的统计信息
pg_waldump -s /path/to/wal/000000010000000000000001

# 查看特定 LSN 范围的 WAL 日志
pg_waldump -r 0/12345678 -R 0/87654321 /path/to/wal/

vacuumlo ​

vacuumlo 用于清理数据库中未使用的大型对象（LOBs）。

bash
# 清理指定数据库中的未使用 LOBs
vacuumlo app_db

# 预览要清理的 LOBs
vacuumlo -n app_db

# 删除 LOBs 并记录日志
vacuumlo -v app_db > vacuumlo.log

第三方性能分析工具 ​

除了内置工具外，还有许多第三方工具可以帮助分析 PostgreSQL 性能。

pgBadger ​

pgBadger 是一个功能强大的 PostgreSQL 日志分析工具，可以生成详细的 HTML 报告，包括慢查询统计、错误分析、连接统计等。

安装与使用 ​

bash
# 安装 pgBadger（Debian/Ubuntu）
sudo apt-get install pgbadger

# 分析日志并生成报告
pgbadger /var/lib/postgresql/14/main/log/postgresql-*.log -o /tmp/pgbadger_report.html

# 分析特定时间段的日志
pgbadger --begin "2024-06-01" --end "2024-06-30" /var/lib/postgresql/14/main/log/postgresql-*.log -o /tmp/june_report.html

# 分析慢查询日志
pgbadger --slow-queries 1000 /var/lib/postgresql/14/main/log/postgresql-*.log -o /tmp/slow_queries.html

报告解读 ​

pgBadger 报告包含以下主要部分：

  • 概览：显示日志的基本统计信息
  • 连接统计：显示连接数、用户分布等
  • 查询统计：显示查询类型分布、执行时间分布等
  • 慢查询：显示执行时间最长的查询
  • 错误分析：显示错误类型和频率
  • 锁等待：显示锁等待事件
  • 事务统计：显示事务提交和回滚情况

pgCluu ​

pgCluu 是一个 PostgreSQL 性能监控和报告生成工具，可以分析日志文件和系统统计信息，生成全面的 HTML 报告。

安装与使用 ​

bash
# 安装 pgCluu
sudo apt-get install pgcluu

# 收集统计信息
pgcluu_collectd -d /var/lib/postgresql/14/main -o /tmp/pgcluu_data

# 生成报告
pgcluu -i /tmp/pgcluu_data -o /tmp/pgcluu_report

pgbouncer ​

pgbouncer 是一个 PostgreSQL 连接池工具，可以监控和管理数据库连接，提高系统的并发处理能力。

监控 pgbouncer ​

bash
# 连接到 pgbouncer 管理数据库
psql -h pgbouncer_host -p 6432 -U pgbouncer pgbouncer

# 查看连接池状态
SHOW pools;

# 查看客户端连接
SHOW clients;

# 查看服务器端连接
SHOW servers;

# 查看统计信息
SHOW stats;

pgAdmin ​

pgAdmin 是 PostgreSQL 的官方图形化管理工具，提供了丰富的性能监控功能。

性能监控功能 ​

  • 仪表板：显示数据库的关键性能指标
  • 服务器状态：显示连接数、锁等待、事务状态等
  • 活动会话：显示当前活动会话的详细信息
  • 查询工具：集成了 EXPLAIN 和 EXPLAIN ANALYZE 功能
  • 报表：生成各种性能报表

Prometheus + Grafana ​

Prometheus 和 Grafana 是流行的开源监控组合，可以监控 PostgreSQL 的各种性能指标，并生成直观的可视化仪表板。

配置 PostgreSQL Exporter ​

PostgreSQL Exporter 用于将 PostgreSQL 的监控指标导出到 Prometheus。

bash
# 下载并运行 PostgreSQL Exporter
docker run -d --name postgres-exporter \
  -e DATA_SOURCE_NAME="postgresql://postgres:password@postgres:5432/postgres?sslmode=disable" \
  -p 9187:9187 \
  prometheuscommunity/postgres-exporter

配置 Prometheus ​

yaml
# prometheus.yml
scrape_configs:
  - job_name: 'postgresql'
    static_configs:
      - targets: ['postgres-exporter:9187']

Grafana 仪表板 ​

Grafana 提供了多种 PostgreSQL 监控仪表板模板，如 ID 为 9628 的 PostgreSQL Overview 仪表板。

性能分析最佳实践 ​

1. 建立性能基线 ​

  • 收集正常运行时的性能指标
  • 确定关键指标的正常范围
  • 定期监控和比较性能变化

2. 重点关注慢查询 ​

  • 配置慢查询日志，记录执行时间超过阈值的查询
  • 使用 pg_stat_statements 识别最消耗资源的查询
  • 定期分析慢查询，优化执行计划和查询逻辑

3. 监控资源使用情况 ​

  • 监控 CPU、内存、磁盘 I/O 等系统资源
  • 监控 PostgreSQL 的连接数、锁等待、事务数等
  • 设置告警阈值，及时发现异常情况

4. 定期分析执行计划 ​

  • 对于频繁执行的查询，定期检查执行计划
  • 当数据分布发生变化时，重新分析执行计划
  • 使用 EXPLAIN ANALYZE 验证优化效果

5. 优化索引使用 ​

  • 监控索引的使用情况，删除未使用的索引
  • 为频繁查询的列创建合适的索引
  • 优化复合索引的列顺序

6. 定期维护数据库 ​

  • 定期执行 VACUUM 和 ANALYZE 操作
  • 监控死元组比例，及时清理
  • 定期重建索引，减少索引膨胀

常见问题（FAQ） ​

Q1: 如何识别慢查询？ ​

A1: 识别慢查询的方法包括：

  1. 配置慢查询日志：在 postgresql.conf 中设置 log_min_duration_statement = 1000（记录执行时间超过 1 秒的查询）
  2. 使用 pg_stat_statements 扩展：查看最消耗时间的查询
  3. 使用 EXPLAIN ANALYZE：分析特定查询的执行计划和执行时间
  4. 使用第三方工具：如 pgBadger、pgCluu 等分析日志

Q2: 如何解读 EXPLAIN 输出？ ​

A2: EXPLAIN 输出包含以下关键信息：

  • 节点类型：表示查询的执行方式，如 Seq Scan（顺序扫描）、Index Scan（索引扫描）、Hash Join（哈希连接）等
  • 成本估算：启动成本（返回第一行的成本）和总成本（返回所有行的成本）
  • 行估算：预计返回的行数
  • 实际执行时间：EXPLAIN ANALYZE 显示的实际执行时间
  • 缓冲区使用情况：共享缓冲区（shared）、本地缓冲区（local）和临时缓冲区（temp）的使用情况

Q3: 为什么查询执行计划会变化？ ​

A3: 查询执行计划变化的原因包括：

  1. 统计信息变化：表的行数、数据分布发生变化
  2. 索引变化：创建、删除或修改索引
  3. 参数变化：修改了影响执行计划的参数，如 work_mem、random_page_cost 等
  4. PostgreSQL 版本升级：优化器算法改进

Q4: 如何优化索引使用？ ​

A4: 优化索引使用的方法包括：

  1. 为频繁查询的列创建索引
  2. 优化复合索引的列顺序，将选择性高的列放在前面
  3. 避免创建过多索引，减少写入开销
  4. 监控索引使用情况，删除未使用的索引
  5. 定期重建索引，减少索引膨胀

Q5: 如何监控数据库的连接数？ ​

A5: 监控数据库连接数的方法包括：

  1. 使用 pg_stat_activity 视图：查看当前连接数和连接状态
  2. 设置连接数告警：当连接数接近 max_connections 时触发告警
  3. 使用连接池：如 pgbouncer，管理和监控连接数
  4. 使用监控工具：如 Prometheus + Grafana，可视化连接数变化

Q6: 如何处理锁等待问题？ ​

A6: 处理锁等待问题的方法包括：

  1. 识别阻塞会话：使用 pg_blocking_pids() 函数找出阻塞者
  2. 分析锁类型和等待事件：查看 wait_event_type 和 wait_event
  3. 优化查询逻辑：减少事务持有锁的时间
  4. 使用适当的隔离级别：避免不必要的锁竞争
  5. 考虑使用行级锁：减少锁的粒度