印刻数据库

外观

PostgreSQL 慢查询风暴 ​

慢查询风暴是指数据库突然出现大量慢查询，导致系统性能急剧下降，甚至出现服务不可用的情况。慢查询风暴是 PostgreSQL 数据库运维中常见的故障之一，需要快速定位和解决。本文档详细介绍了慢查询风暴的定义、原因、诊断方法和解决方案。

慢查询风暴概述 ​

1. 什么是慢查询风暴 ​

慢查询风暴是指在短时间内，数据库出现大量执行时间超过阈值的查询，导致：

  • CPU 使用率飙升
  • 内存使用率急剧上升
  • 磁盘 I/O 负载过高
  • 连接数迅速增加
  • 数据库响应时间延长
  • 服务不可用

2. 慢查询风暴的危害 ​

  • 服务不可用：大量慢查询导致数据库无法处理正常请求
  • 业务中断：影响业务系统的正常运行，造成经济损失
  • 数据丢失风险：严重的慢查询风暴可能导致数据库崩溃，增加数据丢失风险
  • 恢复困难：慢查询风暴可能导致系统处于亚健康状态，恢复时间长

慢查询风暴的原因 ​

1. 应用程序问题 ​

  • 代码逻辑问题：应用程序中存在低效的查询逻辑
  • SQL 书写问题：SQL 查询缺少索引或使用了低效的查询方式
  • 连接管理问题：应用程序连接泄漏或连接池配置不合理
  • 批量操作问题：一次性执行大量数据操作

2. 数据库问题 ​

  • 索引问题：缺少必要的索引或索引失效
  • 统计信息过期：表的统计信息过期，导致查询优化器选择了低效的执行计划
  • 锁竞争：大量查询竞争同一资源，导致锁等待
  • 资源配置不足：数据库资源（CPU、内存、磁盘）配置不足

3. 外部因素 ​

  • 数据量突增：业务数据量突然增加，导致原有的查询计划不再高效
  • 并发量突增：并发用户数突然增加，超过数据库处理能力
  • 硬件故障：存储设备或网络出现故障，导致 I/O 延迟增加
  • 系统负载过高：服务器同时运行其他高负载应用

慢查询风暴的诊断方法 ​

1. 实时监控 ​

1.1 使用 pg_stat_activity 视图 ​

sql
-- 查看当前执行的慢查询
SELECT 
    pid, 
    usename, 
    datname, 
    query, 
    now() - query_start AS duration,
    wait_event_type,
    wait_event,
    state
FROM pg_stat_activity
WHERE state = 'active' 
    AND now() - query_start > interval '5 seconds'
ORDER BY duration DESC;

-- 查看慢查询数量
SELECT 
    count(*) AS slow_query_count
FROM pg_stat_activity
WHERE state = 'active' 
    AND now() - query_start > interval '5 seconds';

1.2 使用系统监控工具 ​

bash
# 监控 CPU 使用率
top -p $(pgrep -d ',' postgres)

# 监控内存使用率
free -h

# 监控磁盘 I/O
iostat -xvm 1

# 监控网络 I/O
iftop -i eth0

2. 慢查询日志分析 ​

2.1 启用慢查询日志 ​

sql
-- 启用慢查询日志
ALTER SYSTEM SET log_min_duration_statement = 5000; -- 记录执行时间超过5秒的查询
ALTER SYSTEM SET log_statement = 'ddl'; -- 记录 DDL 语句
ALTER SYSTEM SET log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h ';
ALTER SYSTEM SET log_checkpoints = on;
ALTER SYSTEM SET log_connections = on;
ALTER SYSTEM SET log_disconnections = on;

SELECT pg_reload_conf();

2.2 分析慢查询日志 ​

bash
# 查看慢查询日志
tail -f /var/lib/pgsql/14/data/log/postgresql-$(date +%Y-%m-%d).log | grep -i "duration:"

# 使用 pgBadger 分析慢查询日志
pgbadger -o slow_query_report.html /var/lib/pgsql/14/data/log/postgresql-$(date +%Y-%m-%d).log

3. 使用 pg_stat_statements 扩展 ​

sql
-- 查看执行时间最长的查询
SELECT 
    queryid,
    query,
    calls,
    total_time,
    mean_time,
    stddev_time,
    rows,
    100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- 查看查询计划
EXPLAIN ANALYZE <slow_query>;

4. 锁等待分析 ​

sql
-- 查看锁等待情况
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

慢查询风暴的解决方案 ​

1. 紧急处理措施 ​

1.1 终止慢查询 ​

sql
-- 终止所有执行时间超过5秒的查询
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active' 
    AND now() - query_start > interval '5 seconds';

-- 终止特定查询
SELECT pg_terminate_backend(<pid>);

1.2 限制连接数 ​

sql
-- 临时降低最大连接数
ALTER SYSTEM SET max_connections = 100;

-- 重启服务生效
-- sudo systemctl restart postgresql-14

1.3 增加资源临时配置 ​

sql
-- 临时增加 work_mem 配置
ALTER SYSTEM SET work_mem = '16MB';

-- 临时增加 maintenance_work_mem 配置
ALTER SYSTEM SET maintenance_work_mem = '1GB';

SELECT pg_reload_conf();

2. 根本原因分析与解决 ​

2.1 优化查询 ​

  • 添加索引：为慢查询添加必要的索引

    sql
    CREATE INDEX idx_table_column ON table(column);

  • 重写查询：优化查询逻辑，避免全表扫描

    sql
    -- 优化前
SELECT * FROM users WHERE name LIKE '%John%';

-- 优化后
CREATE INDEX idx_users_name ON users(name);
-- 或使用全文搜索
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);

  • 使用合适的连接方式：避免笛卡尔积和不必要的连接

2.2 更新统计信息 ​

sql
-- 更新所有表的统计信息
ANALYZE VERBOSE;

-- 更新特定表的统计信息
ANALYZE VERBOSE <table_name>;

-- 更新特定列的统计信息
ANALYZE VERBOSE <table_name> (<column1>, <column2>);

2.3 优化表结构 ​

  • 分区表：将大表拆分为分区表

    sql
    -- 创建分区表
CREATE TABLE sales (
    id SERIAL,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL
) PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

  • 清理表膨胀：

    sql
    -- 重建表
VACUUM FULL ANALYZE <table_name>;

-- 重建索引
REINDEX TABLE <table_name>;

3. 应用程序优化 ​

  • 优化连接池配置：调整连接池大小和超时设置
  • 减少不必要的查询：合并查询，避免 N+1 查询问题
  • 使用缓存：对频繁访问的数据使用缓存
  • 异步处理：将耗时操作改为异步处理
  • 批量操作：将多个小查询合并为批量操作

慢查询风暴的预防措施 ​

1. 监控与告警 ​

  • 设置慢查询告警：当慢查询数量超过阈值时发送告警
  • 监控查询执行时间：实时监控查询执行时间，发现异常及时处理
  • 监控系统资源：监控 CPU、内存、磁盘 I/O 等系统资源

2. 定期维护 ​

  • 定期分析慢查询日志：每周或每月分析慢查询日志，找出性能瓶颈
  • 定期更新统计信息：确保表的统计信息及时更新
  • 定期优化表和索引：清理表膨胀，重建索引
  • 定期审查查询：审查应用程序中的查询，优化低效查询

3. 开发规范 ​

  • SQL 书写规范：制定 SQL 书写规范，避免低效查询
  • 索引设计规范：制定索引设计规范，确保索引的有效性
  • 代码审查：在代码审查中重点关注数据库查询
  • 性能测试：在发布前进行性能测试，确保查询性能符合要求

4. 资源规划 ​

  • 合理配置资源：根据业务需求配置足够的 CPU、内存、磁盘资源
  • 考虑未来增长：预留足够的资源用于未来业务增长
  • 使用合适的存储：使用 SSD 等高性能存储设备

慢查询风暴案例分析 ​

案例一：缺少索引导致的慢查询风暴 ​

问题现象 ​

  • 数据库 CPU 使用率突然飙升至 100%
  • 大量慢查询，执行时间超过 30 秒
  • 应用程序响应时间延长，出现超时

诊断过程 ​

  1. 使用 pg_stat_activity 视图查看当前执行的查询
  2. 发现大量相同的 SELECT 查询，缺少必要的索引
  3. 使用 EXPLAIN ANALYZE 分析查询计划，发现全表扫描

解决方案 ​

sql
-- 添加缺失的索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- 终止慢查询
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE query LIKE '%SELECT * FROM orders WHERE customer_id = %';

优化效果 ​

  • CPU 使用率从 100% 降至 30%
  • 查询执行时间从 30 秒降至 10 毫秒
  • 应用程序响应时间恢复正常

案例二：统计信息过期导致的慢查询风暴 ​

问题现象 ​

  • 数据库突然出现大量慢查询
  • 查询执行计划发生变化
  • 系统负载急剧上升

诊断过程 ​

  1. 检查慢查询日志，发现查询计划发生变化
  2. 检查表的统计信息，发现统计信息过期
  3. 手动更新统计信息后，查询计划恢复正常

解决方案 ​

sql
-- 更新统计信息
ANALYZE VERBOSE orders;

-- 终止慢查询
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'active' 
    AND now() - query_start > interval '10 seconds';

优化效果 ​

  • 查询计划恢复正常
  • 慢查询数量减少
  • 系统负载恢复正常

版本差异注意事项 ​

版本差异说明
PostgreSQL 9.x慢查询日志功能相对简单，缺少一些高级特性
PostgreSQL 10+增强了慢查询日志功能，支持更多的日志格式和配置选项
PostgreSQL 12+改进了查询优化器，提高了查询计划的质量
PostgreSQL 13+增强了 pg_stat_statements 扩展，提供了更多的统计信息
PostgreSQL 14+改进了查询并行执行，提高了复杂查询的性能
PostgreSQL 15+增强了慢查询日志的可读性，更容易分析慢查询

慢查询管理最佳实践 ​

  1. 启用慢查询日志：确保慢查询日志已启用，并配置合适的阈值
  2. 定期分析慢查询：每周或每月分析慢查询日志，找出性能瓶颈
  3. 监控实时查询：使用 pg_stat_activity 视图实时监控查询执行情况
  4. 使用 pg_stat_statements：部署 pg_stat_statements 扩展，收集查询统计信息
  5. 优化查询：为慢查询添加必要的索引，优化查询逻辑
  6. 更新统计信息：定期更新表的统计信息，确保查询优化器选择正确的执行计划
  7. 清理表膨胀：定期清理表膨胀，提高查询性能
  8. 制定开发规范：制定 SQL 书写和索引设计规范
  9. 性能测试：在发布前进行性能测试，确保查询性能符合要求
  10. 建立应急预案：制定慢查询风暴的应急预案，快速响应

总结 ​

慢查询风暴是 PostgreSQL 数据库运维中常见的故障之一，需要快速定位和解决。通过实时监控、日志分析和查询优化，可以有效地应对慢查询风暴。

预防慢查询风暴同样重要，包括启用慢查询日志、定期分析慢查询、更新统计信息、优化表结构和制定开发规范等措施。通过建立完善的慢查询管理机制，可以减少慢查询风暴的发生，提高数据库系统的可用性和可靠性。

在实际运维工作中，应根据具体情况选择合适的解决方案，并不断总结经验，优化慢查询管理策略，确保数据库系统的稳定运行。