外观
PostgreSQL 慢查询指标
慢查询日志配置
1. 启用慢查询日志
sql
-- 设置慢查询阈值，单位为毫秒
ALTER SYSTEM SET log_min_duration_statement = 500;
-- 记录所有慢查询，包括被终止的查询
ALTER SYSTEM SET log_min_duration_statement = 0;
-- 关闭慢查询日志
ALTER SYSTEM SET log_min_duration_statement = -1;
-- 验证配置
SHOW log_min_duration_statement;2. 配置日志格式
sql
-- 设置详细的日志前缀格式
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';
-- 设置日志格式为CSV（可选）
ALTER SYSTEM SET log_destination = 'stderr,csvlog';
-- 验证配置
SHOW log_line_prefix;
SHOW log_destination;3. 配置日志记录内容
sql
-- 记录查询执行计划
ALTER SYSTEM SET log_executor_stats = on;
-- 记录查询规划器统计信息
ALTER SYSTEM SET log_planner_stats = on;
-- 记录缓冲区使用情况
ALTER SYSTEM SET log_bufferings = on;
-- 验证配置
SHOW log_executor_stats;
SHOW log_planner_stats;pg_stat_statements 扩展
1. 安装和配置
sql
-- 安装扩展
CREATE EXTENSION pg_stat_statements;
-- 配置扩展参数
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.max = 10000;
ALTER SYSTEM SET pg_stat_statements.track = 'all';
ALTER SYSTEM SET pg_stat_statements.track_utility = on;
ALTER SYSTEM SET pg_stat_statements.save = on;
-- 重启数据库使配置生效
-- pg_ctl restart2. 常用慢查询指标查询
sql
-- 获取执行时间最长的前10个查询
SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time,
max_exec_time,
rows
FROM
pg_stat_statements
ORDER BY
total_exec_time DESC
LIMIT 10;
-- 获取调用次数最多的前10个查询
SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time,
rows
FROM
pg_stat_statements
ORDER BY
calls DESC
LIMIT 10;
-- 获取平均执行时间最长的前10个查询
SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time,
max_exec_time
FROM
pg_stat_statements
ORDER BY
mean_exec_time DESC
LIMIT 10;慢查询指标类型
1. 时间相关指标
| 指标名称 | 描述 | 单位 |
|---|---|---|
total_exec_time | 查询总执行时间 | 毫秒 |
mean_exec_time | 查询平均执行时间 | 毫秒 |
max_exec_time | 查询最大执行时间 | 毫秒 |
min_exec_time | 查询最小执行时间 | 毫秒 |
stddev_exec_time | 查询执行时间标准差 | 毫秒 |
2. 调用相关指标
| 指标名称 | 描述 | 单位 |
|---|---|---|
calls | 查询调用次数 | 次 |
rows | 查询返回总行数 | 行 |
rows/sec | 查询每秒返回行数 | 行/秒 |
calls/sec | 查询每秒调用次数 | 次/秒 |
3. 资源消耗指标
| 指标名称 | 描述 | 单位 |
|---|---|---|
shared_blks_hit | 共享缓冲区命中次数 | 块 |
shared_blks_read | 共享缓冲区读取次数 | 块 |
shared_blks_dirtied | 共享缓冲区修改次数 | 块 |
shared_blks_written | 共享缓冲区写入次数 | 块 |
local_blks_hit | 本地缓冲区命中次数 | 块 |
local_blks_read | 本地缓冲区读取次数 | 块 |
temp_blks_read | 临时缓冲区读取次数 | 块 |
temp_blks_written | 临时缓冲区写入次数 | 块 |
blk_read_time | 块读取时间 | 毫秒 |
blk_write_time | 块写入时间 | 毫秒 |
慢查询指标监控工具
1. 内置工具
sql
-- 使用pg_stat_activity监控当前慢查询
SELECT
pid,
datname,
usename,
application_name,
client_addr,
backend_start,
query_start,
state_change,
state,
wait_event_type,
wait_event,
query
FROM
pg_stat_activity
WHERE
state = 'active'
AND now() - query_start > interval '500ms';
-- 使用EXPLAIN ANALYZE分析查询执行计划
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date >= '2023-01-01'
ORDER BY total_amount DESC
LIMIT 100;2. 第三方工具
- pgBadger：生成HTML格式的慢查询分析报告
- pgsi：PostgreSQL日志统计工具
- pg_stat_monitor：增强版的pg_stat_statements
- Prometheus + Grafana：集中式监控和可视化
- Datadog：云原生监控平台
- New Relic：应用性能监控平台
慢查询指标分析方法
1. 识别关键指标
- 高总执行时间：影响整体性能的查询
- 高平均执行时间：单次执行耗时较长的查询
- 高调用次数：频繁执行的查询
- 高资源消耗：消耗大量CPU、内存或I/O的查询
2. 分析执行计划
sql
-- 获取查询的执行计划
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SETTINGS)
SELECT * FROM orders
WHERE customer_id = 123
AND order_date >= '2023-01-01';
-- 比较不同执行计划
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT * FROM orders
WHERE customer_id = 123
AND order_date >= '2023-01-01';3. 优化建议生成
根据慢查询指标分析，生成优化建议：
- 添加索引：为频繁查询的列添加索引
- 优化查询：重写查询语句，避免全表扫描
- 调整配置：修改数据库参数，如work_mem、shared_buffers等
- 重构表结构：分区表、调整数据类型等
- 使用物化视图：预计算复杂查询结果
慢查询指标告警配置
1. 使用pg_cron定期检查
sql
-- 安装pg_cron扩展
CREATE EXTENSION pg_cron;
-- 配置cron.job_queue_processes
ALTER SYSTEM SET cron.job_queue_processes = 4;
-- 创建慢查询告警任务
SELECT cron.schedule('*/5 * * * *', $$
INSERT INTO alert_log (alert_type, alert_message, alert_time)
SELECT 'slow_query',
format('Query executed %s times with total time %s ms: %s',
calls, total_exec_time, query),
now()
FROM pg_stat_statements
WHERE total_exec_time > 10000 -- 10秒
ORDER BY total_exec_time DESC
LIMIT 5;
$$);
-- 查看所有定时任务
SELECT * FROM cron.job;2. 使用Prometheus和Alertmanager
yaml
# Prometheus配置示例
scrape_configs:
- job_name: 'postgresql'
static_configs:
- targets: ['localhost:9187']
# Alertmanager告警规则示例
groups:
- name: postgresql-alerts
rules:
- alert: SlowQuery
expr: pg_stat_statements_total_exec_time > 10000
for: 5m
labels:
severity: warning
annotations:
summary: "Slow query detected"
description: "Query {{ $labels.queryid }} has total execution time {{ $value }} ms"慢查询指标最佳实践
1. 配置建议
| 场景 | 建议配置 |
|---|---|
| 开发环境 | log_min_duration_statement = 100ms |
| 测试环境 | log_min_duration_statement = 200ms |
| 生产环境 | log_min_duration_statement = 500ms |
| 性能调优 | log_min_duration_statement = 0 |
2. 监控频率
- 实时监控：使用pg_stat_activity监控当前慢查询
- 定期分析：每天或每周生成慢查询报告
- 趋势分析：每月或每季度进行趋势分析
3. 数据保留
sql
-- 重置pg_stat_statements统计数据
SELECT pg_stat_statements_reset();
-- 定期重置统计数据（使用pg_cron）
SELECT cron.schedule('0 0 * * *', 'SELECT pg_stat_statements_reset();');4. 安全考虑
- 限制pg_stat_statements的访问权限
- 避免在生产环境记录所有查询（log_min_duration_statement = 0）
- 定期清理慢查询日志，避免磁盘空间不足
- 加密敏感数据的慢查询日志
常见问题及解决方案
1. 慢查询日志不记录
问题：配置了慢查询日志，但没有记录慢查询
解决方案：
sql
-- 检查慢查询阈值配置
SHOW log_min_duration_statement;
-- 检查日志收集器是否启用
SHOW logging_collector;
-- 检查日志级别
SHOW log_min_messages;
-- 检查日志目标
SHOW log_destination;2. pg_stat_statements不显示数据
问题：安装了pg_stat_statements扩展，但没有统计数据
解决方案：
sql
-- 检查扩展是否正确安装
SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';
-- 检查shared_preload_libraries配置
SHOW shared_preload_libraries;
-- 检查pg_stat_statements参数
SHOW pg_stat_statements.track;
SHOW pg_stat_statements.max;
-- 检查是否有查询执行
SELECT * FROM pg_stat_statements;3. 慢查询指标过多
问题：pg_stat_statements记录了过多的查询，难以分析
解决方案：
sql
-- 增加pg_stat_statements.max值
ALTER SYSTEM SET pg_stat_statements.max = 20000;
-- 调整track参数，只跟踪用户查询
ALTER SYSTEM SET pg_stat_statements.track = 'top';
-- 定期重置统计数据
SELECT pg_stat_statements_reset();
-- 使用pg_stat_statements_filter扩展过滤查询常见问题（FAQ）
Q1：慢查询阈值设置多少合适？
A1：慢查询阈值的设置取决于业务需求和系统性能：
- 开发环境：100-200ms，便于开发人员调试
- 测试环境：200-500ms，接近生产环境
- 生产环境：500ms-1s，平衡性能和日志量
- 性能调优：0ms，记录所有查询
Q2：pg_stat_statements和慢查询日志有什么区别？
A2：pg_stat_statements和慢查询日志的区别：
| 特性 | pg_stat_statements | 慢查询日志 |
|---|---|---|
| 存储位置 | 内存 | 文件 |
| 数据格式 | 结构化数据 | 文本或CSV |
| 保留时间 | 重启后丢失（可配置保存） | 取决于日志策略 |
| 查询信息 | 统计汇总 | 详细日志 |
| 性能影响 | 较低 | 较高 |
| 分析难度 | 较易 | 较难 |
Q3：如何导出慢查询指标？
A3：导出慢查询指标的方法：
sql
-- 导出pg_stat_statements数据
COPY (
SELECT queryid, query, calls, total_exec_time, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 100
) TO '/tmp/slow_queries.csv' WITH CSV HEADER;
-- 使用pg_dump导出
pg_dump -d mydb -t pg_stat_statements -f pg_stat_statements.sqlQ4：慢查询指标会影响数据库性能吗？
A4：慢查询指标的性能影响：
- 慢查询日志：较高，尤其是log_min_duration_statement = 0时
- pg_stat_statements：较低，因为只记录统计汇总
- pg_stat_monitor：中等，提供更详细的监控
Q5：如何自动化分析慢查询指标？
A5：自动化分析慢查询指标的方法：
- 使用pg_cron定期生成慢查询报告
- 使用pgBadger生成HTML格式的分析报告
- 使用Prometheus和Grafana创建可视化仪表板
- 集成到CI/CD流程中，自动检测性能回归
- 使用机器学习工具预测慢查询
Q6：如何比较不同时间段的慢查询指标？
A6：比较不同时间段慢查询指标的方法：
- 定期导出慢查询数据，存储到外部数据库
- 使用时间序列数据库（如InfluxDB）存储指标
- 使用Grafana创建趋势图，比较不同时间段的指标
- 使用自定义脚本比较不同导出文件
案例研究：电商平台慢查询优化
1. 场景描述
某电商平台在促销期间出现性能问题，用户访问速度变慢，数据库CPU使用率高达90%。
2. 问题分析
sql
-- 查看慢查询指标
SELECT
queryid,
query,
calls,
total_exec_time,
mean_exec_time,
max_exec_time
FROM
pg_stat_statements
ORDER BY
total_exec_time DESC
LIMIT 5;
-- 分析执行计划
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE order_date >= '2023-01-01'
AND status = 'completed'
ORDER BY order_date DESC
LIMIT 1000;3. 解决方案
sql
-- 添加索引
CREATE INDEX idx_orders_status_date ON orders(status, order_date);
-- 优化查询
EXPLAIN ANALYZE
SELECT id, order_date, customer_id, total_amount, status
FROM orders
WHERE status = 'completed'
AND order_date >= '2023-01-01'
ORDER BY order_date DESC
LIMIT 1000;
-- 调整配置参数
ALTER SYSTEM SET work_mem = '16MB';
ALTER SYSTEM SET random_page_cost = 1.1;4. 优化效果
| 指标 | 优化前 | 优化后 |
|---|---|---|
| 总执行时间 | 12000ms | 800ms |
| 平均执行时间 | 600ms | 40ms |
| 调用次数 | 20 | 20 |
| CPU使用率 | 90% | 40% |