PostgreSQL 慢查询日志配置与分析

慢查询日志配置

1. 基础配置

sql

-- 设置慢查询阈值（毫秒），记录执行时间超过该值的查询
ALTER SYSTEM SET log_min_duration_statement = 500;

-- 验证配置
SHOW log_min_duration_statement;

2. 高级配置

sql

-- 记录所有查询，包括执行时间为0的查询
ALTER SYSTEM SET log_min_duration_statement = 0;

-- 关闭慢查询日志
ALTER SYSTEM SET log_min_duration_statement = -1;

-- 记录查询计划（PostgreSQL 9.0+）
ALTER SYSTEM SET log_executor_stats = on;

-- 记录缓冲池使用情况
ALTER SYSTEM SET log_buffer_usages = on;

-- 记录锁等待情况
ALTER SYSTEM SET log_lock_waits = on;

3. 日志格式配置

sql

-- 设置详细的日志格式，包含执行时间
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h,time=%d ';

-- 记录完整的查询语句，不受log_statement限制
ALTER SYSTEM SET log_statement = 'all';

-- 或仅记录修改数据的语句
ALTER SYSTEM SET log_statement = 'mod';

4. 配置验证

sql

-- 查看所有慢查询相关配置
SELECT name, setting, unit, context
FROM pg_settings
WHERE name LIKE '%log%' AND (name LIKE '%duration%' OR name LIKE '%statement%' OR name LIKE '%query%');

-- 重新加载配置（无需重启数据库）
SELECT pg_reload_conf();

慢查询日志分析

1. 手动分析方法

bash

# 使用grep搜索慢查询
 grep -A 5 -B 5 "duration:" /var/log/postgresql/postgresql-*.log

# 统计慢查询出现次数最多的SQL模式
 grep "duration:" /var/log/postgresql/postgresql-*.log | \
  sed 's/.*duration: \([0-9]*\).*statement: \(.*\)/\2/' | \
  sort | uniq -c | sort -nr | head -10

# 按执行时间排序慢查询
 grep "duration:" /var/log/postgresql/postgresql-*.log | \
  sort -t " " -k 6 -nr | head -20

2. 使用pgBadger工具分析

bash

# 安装pgBadger
apt-get install pgbadger

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

# 生成特定时间段的报告
pgbadger -o slow_query_202301.html --start-date 2023-01-01 --end-date 2023-01-31 /var/log/postgresql/postgresql-*.log

3. 使用pg_stat_statements扩展

sql

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

-- 查看慢查询统计
SELECT 
    queryid,
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    max_exec_time,
    rows
FROM 
    pg_stat_statements
WHERE 
    mean_exec_time > 500 -- 平均执行时间超过500毫秒
ORDER BY 
    mean_exec_time DESC
LIMIT 10;

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

4. 使用pg_stat_plans扩展

sql

-- 安装扩展
CREATE EXTENSION IF NOT EXISTS pg_stat_plans;

-- 查看慢查询计划统计
SELECT 
    planid,
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM 
    pg_stat_plans
WHERE 
    mean_time > 500
ORDER BY 
    mean_time DESC
LIMIT 10;

慢查询优化策略

1. 索引优化

sql

-- 查看慢查询的执行计划
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

-- 如果没有使用索引，创建合适的索引
CREATE INDEX idx_users_email ON users(email);

-- 查看索引使用情况
SELECT 
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan AS scan_count,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM 
    pg_stat_user_indexes
WHERE 
    idx_scan > 0
ORDER BY 
    idx_scan DESC;

2. 查询重写

sql

-- 优化前：使用SELECT *
SELECT * FROM users WHERE created_at > '2023-01-01';

-- 优化后：只选择需要的列
SELECT user_id, username, email FROM users WHERE created_at > '2023-01-01';

-- 优化前：使用子查询
SELECT * FROM orders WHERE user_id IN (SELECT user_id FROM users WHERE active = true);

-- 优化后：使用JOIN
SELECT o.* FROM orders o JOIN users u ON o.user_id = u.user_id WHERE u.active = true;

3. 硬件和配置优化

sql

-- 增加共享缓冲区大小
ALTER SYSTEM SET shared_buffers = '4GB';

-- 增加工作内存
ALTER SYSTEM SET work_mem = '64MB';

-- 增加维护工作内存
ALTER SYSTEM SET maintenance_work_mem = '1GB';

-- 启用并行查询
ALTER SYSTEM SET max_parallel_workers_per_gather = 4;
ALTER SYSTEM SET max_parallel_workers = 8;

生产环境最佳实践

1. 合理设置慢查询阈值

环境类型	建议阈值	说明
开发环境	100ms	便于开发人员快速发现性能问题
测试环境	500ms	接近生产环境，但更严格
生产环境	1000ms	平衡性能监控和系统开销
高性能生产环境	2000ms	减少日志量，聚焦严重性能问题

2. 慢查询日志管理

sql

-- 配置日志旋转
ALTER SYSTEM SET log_rotation_size = 100MB;
ALTER SYSTEM SET log_rotation_age = 1d;

-- 配置日志压缩
-- 使用logrotate工具实现

3. 定期分析慢查询

每周生成一次慢查询报告
每月进行一次全面的性能分析
重大版本更新后进行性能基准测试

4. 监控慢查询趋势

sql

-- 创建慢查询统计视图
CREATE OR REPLACE VIEW daily_slow_queries AS
SELECT 
    date_trunc('day', log_time) AS log_day,
    COUNT(*) AS slow_query_count,
    AVG(duration) AS avg_duration,
    MAX(duration) AS max_duration
FROM 
    pg_log
WHERE 
    message LIKE '%duration:%' 
    AND duration > 1000
GROUP BY 
    log_day
ORDER BY 
    log_day DESC;

-- 查询慢查询趋势
SELECT * FROM daily_slow_queries;

常见问题与解决方案

1. 慢查询日志不记录任何内容

问题：配置了慢查询日志，但日志中没有慢查询记录

解决方案：

sql

-- 检查慢查询阈值是否设置过高
SHOW log_min_duration_statement;

-- 检查日志级别
SHOW log_min_messages;

-- 检查日志收集器是否启用
SHOW logging_collector;

-- 检查日志目标
SHOW log_destination;

2. 慢查询日志量过大

问题：慢查询日志增长过快，占用大量磁盘空间

解决方案：

sql

-- 调整慢查询阈值
ALTER SYSTEM SET log_min_duration_statement = 2000;

-- 减少日志详细程度
ALTER SYSTEM SET log_error_verbosity = 'terse';

-- 配置日志旋转和清理
ALTER SYSTEM SET log_rotation_size = 50MB;
ALTER SYSTEM SET log_rotation_age = 6h;

3. 慢查询日志中缺少执行计划

问题：需要查看慢查询的执行计划，但日志中没有

解决方案：

sql

-- 启用执行计划日志
ALTER SYSTEM SET log_executor_stats = on;

-- 或使用auto_explain扩展
CREATE EXTENSION IF NOT EXISTS auto_explain;
ALTER SYSTEM SET auto_explain.log_min_duration = 500;
ALTER SYSTEM SET auto_explain.log_analyze = on;
ALTER SYSTEM SET auto_explain.log_buffers = on;
ALTER SYSTEM SET auto_explain.log_timing = on;

常见问题（FAQ）

Q1：log_min_duration_statement和auto_explain有什么区别？

A1：

log_min_duration_statement：记录慢查询的基本信息，包括执行时间和查询语句
auto_explain：记录慢查询的详细执行计划，包括成本估算、实际行数、缓冲区使用等
建议同时使用，以获得完整的慢查询分析信息

Q2：如何在不重启数据库的情况下启用慢查询日志？

A2：

sql

-- 修改配置
ALTER SYSTEM SET log_min_duration_statement = 500;
ALTER SYSTEM SET log_statement = 'all';

-- 重新加载配置
SELECT pg_reload_conf();

Q3：慢查询日志对数据库性能有影响吗？

A3：

启用慢查询日志会增加一定的性能开销，主要体现在CPU和I/O方面
合理设置慢查询阈值可以降低性能影响
建议在生产环境中使用较高的阈值（如1000ms）
对于高性能要求的系统，可以考虑使用pg_stat_statements代替慢查询日志

Q4：如何分析慢查询的执行计划？

A4：

使用EXPLAIN ANALYZE直接在数据库中分析
启用auto_explain扩展，自动记录执行计划
使用pgBadger等工具生成可视化的执行计划报告
关注执行计划中的全表扫描、嵌套循环、高成本操作

Q5：如何监控慢查询的实时情况？

A5：

使用pg_stat_statements扩展实时查看慢查询
使用pg_stat_activity查看当前正在执行的慢查询
配置监控工具（如Prometheus + Grafana）设置慢查询告警
使用pg_top或pg_activity等工具实时监控

Q6：慢查询优化的一般步骤是什么？

A6：

配置慢查询日志，收集慢查询数据
分析慢查询日志，识别性能瓶颈
查看执行计划，确定优化方向
实施优化措施（添加索引、重写查询等）
验证优化效果
持续监控和调整

Q7：如何使用pg_stat_statements分析慢查询？

A7：

sql

-- 查看最耗时的查询
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM 
    pg_stat_statements
ORDER BY 
    mean_exec_time DESC
LIMIT 10;

慢查询优化案例

案例1：添加索引优化

问题：

sql

-- 慢查询
SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

解决方案：

sql

-- 添加复合索引
CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

-- 验证优化效果
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 123 AND order_date > '2023-01-01';

案例2：查询重写优化

问题：

sql

-- 慢查询：使用子查询
SELECT * FROM products WHERE product_id IN (
    SELECT product_id FROM order_items WHERE quantity > 10
);

解决方案：

sql

-- 优化后：使用JOIN
SELECT DISTINCT p.* FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.quantity > 10;

案例3：配置优化

问题：大量慢查询，执行计划显示缓冲区命中率低

解决方案：

sql

-- 增加共享缓冲区大小
ALTER SYSTEM SET shared_buffers = '8GB';

-- 增加工作内存
ALTER SYSTEM SET work_mem = '128MB';

-- 重新加载配置
SELECT pg_reload_conf();

PostgreSQL 慢查询日志配置与分析 ​

慢查询日志配置 ​

1. 基础配置 ​

2. 高级配置 ​

3. 日志格式配置 ​

4. 配置验证 ​

慢查询日志分析 ​

1. 手动分析方法 ​

2. 使用pgBadger工具分析 ​

3. 使用pg_stat_statements扩展 ​

4. 使用pg_stat_plans扩展 ​

慢查询优化策略 ​

1. 索引优化 ​

2. 查询重写 ​

3. 硬件和配置优化 ​

生产环境最佳实践 ​

1. 合理设置慢查询阈值 ​

2. 慢查询日志管理 ​

3. 定期分析慢查询 ​

4. 监控慢查询趋势 ​

常见问题与解决方案 ​

1. 慢查询日志不记录任何内容 ​

2. 慢查询日志量过大 ​

3. 慢查询日志中缺少执行计划 ​

常见问题（FAQ） ​

Q1：log_min_duration_statement和auto_explain有什么区别？ ​

Q2：如何在不重启数据库的情况下启用慢查询日志？ ​

Q3：慢查询日志对数据库性能有影响吗？ ​

Q4：如何分析慢查询的执行计划？ ​

Q5：如何监控慢查询的实时情况？ ​

Q6：慢查询优化的一般步骤是什么？ ​

Q7：如何使用pg_stat_statements分析慢查询？ ​

慢查询优化案例 ​

案例1：添加索引优化 ​

案例2：查询重写优化 ​

案例3：配置优化 ​

PostgreSQL 慢查询日志配置与分析

慢查询日志配置

1. 基础配置

2. 高级配置

3. 日志格式配置

4. 配置验证

慢查询日志分析

1. 手动分析方法

2. 使用pgBadger工具分析

3. 使用pg_stat_statements扩展

4. 使用pg_stat_plans扩展

慢查询优化策略

1. 索引优化

2. 查询重写

3. 硬件和配置优化

生产环境最佳实践

1. 合理设置慢查询阈值

2. 慢查询日志管理

3. 定期分析慢查询

4. 监控慢查询趋势

常见问题与解决方案

1. 慢查询日志不记录任何内容

2. 慢查询日志量过大

3. 慢查询日志中缺少执行计划

常见问题（FAQ）

Q1：log_min_duration_statement和auto_explain有什么区别？

Q2：如何在不重启数据库的情况下启用慢查询日志？

Q3：慢查询日志对数据库性能有影响吗？

Q4：如何分析慢查询的执行计划？

Q5：如何监控慢查询的实时情况？

Q6：慢查询优化的一般步骤是什么？

Q7：如何使用pg_stat_statements分析慢查询？

慢查询优化案例

案例1：添加索引优化

案例2：查询重写优化

案例3：配置优化