mysql线上数据库资源耗尽问题全流程解决方案

随风飘的云
76 阅读12分钟

MySQL 线上数据库资源耗尽是生产环境的高频致命问题，核心表现为 CPU 100%、内存耗尽、磁盘 IO 打满、连接数占满等，若处理不及时会导致业务完全不可用。解决核心逻辑是：先应急止损（恢复业务）→ 定位资源耗尽根因 → 针对性优化 → 长期预防，以下是标准化全流程解决方案。

一、应急止损：5-10 分钟恢复核心业务

资源耗尽时，首要目标是快速降低资源占用、恢复业务访问，而非直接排查根因（避免故障扩大）。

1. 第一步：快速定位资源耗尽类型

先通过系统工具 + MySQL 自带命令，确定是 CPU / 内存 / IO / 连接数哪类资源耗尽：

资源类型排查命令（Linux+MySQL）核心特征
CPU 100%top -H -p <mysqld_pid>（看线程 CPU）、show processlist（看慢查询 / 锁等待）、vmstat 1mysqld 进程 CPU 占比 90%+，show processlist大量Sending data/Sorting result线程
内存耗尽free -m、`ps auxgrep mysqld、show global status like 'Innodb_buffer_pool%'`系统可用内存＜100M，OOM Killer 可能杀死 mysqld，InnoDB 缓冲池命中率极低
磁盘 IO 满iostat -x 1（% util 接近 100%）、iotop（看 mysqld IO 占比）、df -h（磁盘是否满）% util＞90%，磁盘读写速度飙升，SQL 执行耗时陡增
连接数满show variables like 'max_connections'、show global status like 'Threads_connected'报错Too many connections，Threads_connected 接近 max_connections

2. 核心止损操作（按优先级）

（1）kill 慢查询 / 阻塞线程（CPU/IO 高优先）

资源耗尽多由慢查询、锁等待、大事务导致，先 kill 掉消耗资源的线程：

-- 1. 查看所有线程，筛选耗时久、状态异常的（Time列是执行时间，单位秒）
show processlist; 
-- 或更详细的慢查询线程（推荐）
SELECT id, user, host, db, command, time, state, info 
FROM information_schema.processlist 
WHERE time > 60 -- 执行超过60秒的线程
  AND state IN ('Sending data', 'Sorting result', 'Updating', 'Lock wait');

-- 2. kill指定线程（id为processlist中的ID，多个用分号分隔）
KILL 1234; 
KILL 1235;

-- 批量kill（谨慎！避免kill核心业务线程）
SELECT CONCAT('KILL ', id, ';') 
FROM information_schema.processlist 
WHERE time > 60 AND info LIKE '%SELECT * FROM big_table%'; -- 按SQL特征筛选
  • 注意：kill 线程仅临时缓解，需后续定位慢查询根因；避免 kill 系统线程（user 为 system user）。
（2）临时调整 MySQL 参数（应急）
资源问题临时调整参数（仅应急，重启失效）说明
连接数满set global max_connections = 2000;（原默认 151）临时增大连接数，同时 kill 无用连接（如 sleep 线程：kill 睡眠线程ID）
CPU/IO 高set global slow_query_log = ON;（开启慢查询日志）set global long_query_time = 1;（慢查询阈值设为 1 秒）快速捕获慢查询，为后续定位根因；同时可临时关闭非核心业务的读写（如统计查询）
内存耗尽set global innodb_buffer_pool_size = 8G;（需重启生效，应急可先降低）set global query_cache_size = 0;（关闭查询缓存，8.0 已移除）避免内存过度占用，若 OOM 则先重启 MySQL（重启前备份配置）
磁盘 IO 满set global innodb_flush_log_at_trx_commit = 2;（默认 1，降低刷盘频率）set global sync_binlog = 100;（二进制日志每 100 次事务刷盘）临时降低 IO 压力，风险是宕机可能丢失少量数据，恢复后改回 1
（3）隔离故障节点（集群部署优先）

若为主从 / 读写分离架构，先将故障节点从负载均衡摘除，流量切到从库 / 备用节点：

  • 操作：Nginx 屏蔽故障节点 IP、关闭 VIP 指向、K8s 驱逐 MySQL Pod 等；
  • 目的：避免流量持续涌入，给故障节点 “喘息” 时间。
（4）紧急重启 MySQL（最后手段）

若上述操作无效，资源仍耗尽，紧急重启 MySQL（注意：重启会中断业务，需提前通知）：

# Linux重启（按实际部署方式）
systemctl restart mysqld # systemd系统
# 或
service mysqld restart   # sysvinit系统

# 重启后关键检查
mysql -uroot -p -e "show global status like 'Threads_connected';" # 确认连接数正常
mysql -uroot -p -e "show databases;" # 确认数据未丢失
  • 注意：重启前需确认innodb_force_recovery参数为 0（避免数据恢复异常）；重启后立即开启慢查询日志。

二、根因定位：10-60 分钟找到资源耗尽源头

止损后，需深入分析日志和指标，定位资源耗尽的根本原因（避免重复故障）。

1. 分析慢查询日志（CPU/IO 高核心）

慢查询日志是定位资源消耗 SQL 的核心，先确保日志已开启，再分析：

-- 查看慢查询日志路径
show variables like 'slow_query_log_file'; 
-- 示例路径：/var/lib/mysql/localhost-slow.log
核心分析工具：

  • mysqldumpslow（MySQL 自带）：统计慢查询 TOP N

    # 按执行次数排序，显示TOP 10慢查询
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
# 按耗时排序，显示TOP 10慢查询
mysqldumpslow -s t -t 10 /var/lib/mysql/localhost-slow.log

  • pt-query-digest（Percona 工具，推荐）：更详细的慢查询分析

    pt-query-digest /var/lib/mysql/localhost-slow.log > slow_query_analysis.log
慢查询核心特征（资源耗尽元凶）：
  • 无索引的全表扫描（type=ALL）：如SELECT * FROM big_table WHERE name = 'xxx'（name 无索引）；
  • 大表排序 / 分组（Using filesort/Using temporary）：如SELECT * FROM big_table ORDER BY create_time LIMIT 10000；
  • 大事务 / 长连接：如START TRANSACTION;后未提交，持有锁且占用连接；
  • 批量更新 / 删除无限制：如UPDATE big_table SET status=1（无 WHERE 条件，全表更新）；
  • 联表查询无索引：多表 JOIN 未关联索引，导致笛卡尔积。

2. 分析系统资源与 MySQL 状态

（1）CPU 高额外分析

  • 查看 MySQL 线程 CPU 分布：top -H -p <mysqld_pid>，将线程 ID 转为十六进制（printf %x 线程ID），再查对应的 SQL：

    SELECT * FROM performance_schema.threads WHERE THREAD_ID = 十六进制线程ID;

  • 排查是否有频繁执行的小查询（如每秒上千次的无索引简单查询，累加导致 CPU 满）。

（2）内存耗尽分析

  • 核心参数检查：show variables like '%buffer%';，重点关注：

    • innodb_buffer_pool_size：InnoDB 核心缓存，建议设为物理内存的 50%-70%（如 16G 内存设为 10G）；
    • join_buffer_size/sort_buffer_size：每个连接的临时缓冲区，若设置过大（如 1G），多连接时会耗尽内存；
    • tmp_table_size/max_heap_table_size：临时表内存上限，超过则写入磁盘，导致 IO 高。

  • 内存泄漏排查：查看 MySQL 内存占用是否持续增长（ps aux | grep mysqld），若重启后仍快速增长，可能是 MySQL Bug 或异常连接。

（3）磁盘 IO 满分析

  • 排查磁盘使用率：df -h，若磁盘满则先清理日志（慢查询日志、binlog、审计日志）；

  • 排查 IO 热点：iotop -o -p <mysqld_pid>，看是读 IO 还是写 IO 高：

    • 读 IO 高：多为慢查询全表扫描，需优化索引；
    • 写 IO 高：多为批量插入 / 更新、binlog 刷盘频繁、InnoDB 刷脏页（show engine innodb status看脏页比例）。
（4）连接数满分析
  • 查看连接来源：SELECT host, COUNT(*) FROM information_schema.processlist GROUP BY host;，定位连接数过多的客户端（如异常应用、爬虫）；
  • 查看连接状态：SELECT state, COUNT(*) FROM information_schema.processlist GROUP BY state;，若大量Sleep状态，说明连接未释放（应用端连接池配置问题）。

三、针对性优化：1-4 小时根治根因

根据根因定位结果，分场景优化，从 “治标” 到 “治本”：

1. 慢查询 / 索引失效导致的 CPU/IO 高（最常见）

根因优化方案
无索引 / 索引失效新增合适索引（如联合索引），避免冗余索引；例：ALTER TABLE big_table ADD INDEX idx_name_create_time (name, create_time);用EXPLAIN验证索引命中（type列显示range/ref）
大表排序 / 分组用索引覆盖排序（如创建包含排序字段的联合索引）；分页优化（LIMIT offset, size改为基于主键分页：WHERE id > last_id LIMIT size）
全表更新 / 删除分批执行（每批 1000 行），避免锁表和 IO 打满；例：UPDATE big_table SET status=1 WHERE id BETWEEN 1 AND 1000;（循环执行）
联表查询效率低确保 JOIN 字段有索引，小表驱动大表；拆分复杂联表为多个简单查询（应用端聚合）

2. 内存耗尽优化

根因优化方案
缓冲池配置不合理调整innodb_buffer_pool_size（物理内存 50%-70%），开启缓冲池预热：innodb_buffer_pool_dump_at_shutdown=ON/innodb_buffer_pool_load_at_startup=ON
连接级缓冲区过大降低join_buffer_size/sort_buffer_size（默认 256K/256K，按需调大，不超过 1M）；避免全局调大，改为会话级（仅对需要的 SQL 设置）
临时表占用内存过高限制tmp_table_size/max_heap_table_size（建议 16M-32M），临时表优先用磁盘（避免内存溢出）
查询缓存（8.0 前）关闭查询缓存（query_cache_size=0），因缓存失效会导致 CPU 高，8.0 已移除该功能

3. 磁盘 IO 满优化

根因优化方案
日志刷盘频繁业务低峰期调整innodb_flush_log_at_trx_commit=1（核心业务），非核心业务可设为 2；sync_binlog设为 100（平衡性能与数据安全）
脏页刷盘压力大调整innodb_io_capacity（适配磁盘 IO 能力，SSD 设为 2000，HDD 设为 200）；避免批量写入导致脏页比例过高（innodb_max_dirty_pages_pct设为 75）
磁盘空间满清理过期 binlog（设置自动清理：expire_logs_days=7）；归档慢查询日志 / 审计日志；分区表清理历史数据（ALTER TABLE big_table DROP PARTITION p202401;）

4. 连接数满优化

根因优化方案
应用端连接池配置错误调整应用连接池参数（如 HikariCP：maximum-pool-size=50，idle-timeout=300000），避免连接池溢出；关闭闲置连接（wait_timeout=600，interactive_timeout=600）
异常连接 / 攻击防火墙限制 IP 访问（如仅允许应用服务器连接）；开启 MySQL 认证插件（validate_password），禁止弱密码；用max_user_connections限制单用户连接数
连接泄露应用端排查未释放的连接（如 try-with-resources 关闭 Connection）；监控连接池活跃数，异常时告警

四、验证与灰度：4-8 小时确保优化有效

优化后需验证效果，避免二次故障：

  1. 本地 / 预发验证：

    • 执行优化后的 SQL，用EXPLAIN确认索引命中；
    • 压测工具（JMeter/pt-table-checksum）模拟线上流量，监控 CPU / 内存 / IO / 连接数；

  2. 灰度上线：

    • 先在从库 / 备用节点执行优化（如新增索引），验证无性能问题；
    • 核心 SQL 分批上线，观察慢查询日志和资源占用；

  3. 线上监控：

    • 实时监控资源指标（CPU＜70%、内存使用率＜80%、IO % util＜60%、连接数＜max_connections*80%）；
    • 对比优化前后的 SQL 执行耗时（如慢查询数量下降 90%）。

五、长期预防：避免资源耗尽复发

1. 标准化 MySQL 配置（生产环境必配）

[mysqld]
# 基础配置
port = 3306
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
# 连接配置
max_connections = 1000
max_user_connections = 500
wait_timeout = 600
interactive_timeout = 600
# 内存配置
innodb_buffer_pool_size = 10G  # 16G内存设为10G
innodb_buffer_pool_instances = 10 # 与buffer_pool_size对应，每1G一个实例
join_buffer_size = 256K
sort_buffer_size = 256K
tmp_table_size = 32M
max_heap_table_size = 32M
# IO配置
innodb_flush_log_at_trx_commit = 1 # 核心业务设为1，非核心设为2
sync_binlog = 100
innodb_io_capacity = 2000 # SSD设2000，HDD设200
innodb_max_dirty_pages_pct = 75
# 慢查询配置
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON # 记录无索引查询
# 日志清理
expire_logs_days = 7
# 安全配置
skip_name_resolve = ON # 禁止DNS解析，提升连接速度
validate_password = ON

2. 监控体系建设（核心）

  • 系统层监控：Prometheus+Grafana 监控 CPU、内存、磁盘 IO、磁盘空间；

  • MySQL 层监控：

    • 核心指标：连接数、QPS/TPS、慢查询数、InnoDB 缓冲池命中率、脏页比例、锁等待数；
    • 工具：Percona Monitoring and Management（PMM）、Zabbix、Nagios；

  • 告警配置：

    • CPU＞80%、内存使用率＞85%、IO % util＞70%、连接数＞max_connections*80%、慢查询数＞100 / 小时；
    • 告警方式：钉钉 / 短信 / 邮件，分级告警（紧急告警 5 分钟响应）。

3. 开发 & 运维规范落地

  • 开发规范：

    • 所有 SQL 必须走EXPLAIN验证，禁止无索引的全表扫描；
    • 大表操作（更新 / 删除 / 查询）必须分页 / 分批，禁止一次性操作；
    • 禁止在核心表执行SELECT *，仅查询需要的字段；
    • 上线前必须做 SQL 评审，重点检查慢查询风险。

  • 运维规范：

    • 定期（每周）分析慢查询日志，提前优化潜在问题；
    • 每月做一次数据库巡检（索引、碎片、配置）；
    • 核心业务高峰期禁止 DDL 操作（如新增索引），DDL 优先用 pt-online-schema-change（无锁变更）；
    • 定期备份（全量 + 增量），避免优化过程中数据丢失。

4. 架构层面优化（高并发场景）

  • 读写分离：主库写，从库读，分散读压力；
  • 分库分表：大表（千万级以上）按主键 / 业务维度分库分表（如 ShardingSphere），避免单表资源耗尽；
  • 缓存层：热点数据缓存到 Redis，减少 MySQL 查询（如商品详情、用户信息）；
  • 队列削峰：批量写入 / 更新请求放入消息队列（如 RocketMQ），匀速消费，避免瞬间打满 MySQL 资源。

全流程总结

阶段核心动作工具 / 手段
应急止损定位资源类型→kill 慢线程→临时调参→隔离节点→紧急重启top/iostat/jstat、show processlist、kill
根因定位分析慢查询日志→检查 MySQL 状态→排查系统资源→定位核心 SQL / 配置问题mysqldumpslow/pt-query-digest、performance_schema
针对性优化索引优化→参数调整→SQL 重构→资源配置优化EXPLAIN、ALTER TABLE、set global 参数
验证灰度本地压测→灰度上线→实时监控JMeter、Prometheus+Grafana
长期预防标准化配置→监控告警→开发 / 运维规范→架构优化PMM、SQL 评审、分库分表 / 缓存

关键注意事项

  1. 线上修改 MySQL 参数优先用set global（临时），验证有效后写入配置文件（my.cnf），避免重启失效；
  2. 新增索引前需在从库验证，避免主库执行 DDL 锁表；
  3. 批量更新 / 删除时，需关注锁等待和事务大小，避免触发长事务；
  4. 资源耗尽复盘必须落地到代码 / 配置 / 规范，避免 “临时止损后重复故障”。
avatar
文章
阅读
粉丝