表空间、binlog 与在线 DDL

磁盘写满和锁表 DDL 是两类“人为制造”的故障：前者通常源于 binlog 或慢日志无限堆积，后者源于在高峰直接对千万级大表执行 ALTER TABLE。它们事前都有充分信号、事中有明确取舍，完全可以靠流程而不是运气避开。

现象与背景

  • 报错 ERROR 1114 (HY000): The table 'xxx' is full 或 No space left on device。
  • 写入全部失败，连 SELECT 也开始报错，因为无法写 redo 或临时文件。
  • 执行 ALTER TABLE 后业务大面积卡住，SHOW PROCESSLIST 里全是 Waiting for table metadata lock。
  • 已删除大量数据但磁盘空间没释放，data_length 看起来也没变小。

定位思路

  1. 先看文件系统水位与 inode，确认是空间不足还是 inode 耗尽。
  2. 按目录下钻，判断占用来自 binlog、日志、undo 还是业务数据。
  3. 确认没有删除后仍被进程占用的“幽灵文件”。
  4. 清理时从最安全的一端开始：binlog 与日志优先，数据文件最后动。
  5. 需要改表结构时，先判断操作属于 INSTANT、INPLACE 还是 COPY。

第一步：磁盘被谁吃掉

# 文件系统水位与 inode
df -h
df -i
# 数据目录下钻，默认 /var/lib/mysql
du -sh /var/lib/mysql/* 2>/dev/null | sort -rh | head -20
ls -lh /var/lib/mysql/*bin.0* 2>/dev/null | tail -20   # binlog
ls -lh /var/lib/mysql/*.log 2>/dev/null                # 慢日志与错误日志

如果 df 显示已满而 du 加起来远小于容量，说明有被删除但仍被进程占用的文件：

lsof +L1 | grep -i mysql
# 结论：不要 rm 正在写的日志文件，应改用 PURGE BINARY LOGS 或 FLUSH LOGS
磁盘占用来源典型量级安全清理方式
binlog几 GB 到几百 GBPURGE BINARY LOGS 或调小过期时间
undo 表空间与长事务并发度相关等长事务结束后自动回收
ibdata1独立表空间下通常不大不能直接删，需重建实例
慢日志 / 错误日志可能几十 GBlogrotate 归档或 FLUSH LOGS
临时文件大排序、大 DDL 时突增清理 tmpdir，同时排查大 SQL
业务数据与索引主要占用归档历史数据、回收碎片

第二步：管理 binlog

-- 5.7 / 8.0 通用
SHOW BINARY LOGS;
SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';  -- 8.0，默认 2592000（30 天）
SHOW VARIABLES LIKE 'expire_logs_days';            -- 5.7，8.0 已废弃

PURGE BINARY LOGS TO 'mysql-bin.000123';           -- 清理到该文件之前
PURGE BINARY LOGS BEFORE '2025-01-01 00:00:00';    -- 按时间清理

SET GLOBAL expire_logs_days = 7;                   -- 5.7
SET GLOBAL binlog_expire_logs_seconds = 604800;    -- 8.0，7 天

PURGE BINARY LOGS 的安全边界必须清楚，删错了只能靠备份恢复：

边界说明
正在写的 binlog 不能删只能清 SHOW BINARY LOGS 列表最后一行之前的文件
从库未同步的位置不能删先看各从库 Relay_Master_Log_File，取最小值之前的部分
备份恢复点不能删还想做基于时间点恢复（PITR），就必须保留对应 binlog
下游订阅方不能忽略Canal、Debezium 等订阅方回溯不了的位点同样不能删

第三步：大表 DDL 的风险

ALTER TABLE 的代价取决于算法，选错算法等于制造一次全站故障。

算法是否重建表是否阻塞写适用条件
INSTANT否不阻塞8.0.12+ 加列、改默认值、重命名列等有限操作
INPLACE部分操作需重建一般不阻塞 DML，结尾需短时元数据锁加/删二级索引、改默认值等在线操作
COPY是阻塞写改列类型、改字符集、加主键等必须重建的操作
-- 明确指定算法，让它失败而不是悄悄降级为 COPY
ALTER TABLE orders ADD INDEX idx_status_created (status, created_at),
  ALGORITHM=INPLACE, LOCK=NONE;

-- 8.0 加列可秒级完成（列位置只能是 LAST）
ALTER TABLE orders ADD COLUMN remark VARCHAR(255) DEFAULT NULL, ALGORITHM=INSTANT;
场景推荐工具理由
百万行以内的小表直接 ALTER ... ALGORITHM=INPLACE, LOCK=NONE简单，无需额外运维
大表加索引优先 Online DDL，否则 gh-ost / pt-online-schema-change加索引主要重建索引，代价可控
大表改列类型、改字符集gh-ost 或 pt-osc必须重建表，需影子表加切换

线上铁律：加索引与改表结构必须走 Online DDL 或 gh-ost，且不要在业务高峰执行。另外，只要还有一个未提交的长事务，DDL 就会一直等待元数据锁，并把它后面的所有查询一起堵死——这是 DDL 引发全站故障最常见的路径，8.0 可用 performance_schema.metadata_locks（LOCK_STATUS = 'PENDING'）确认。

第四步：碎片与空间回收

SELECT table_schema, table_name,
       ROUND(data_length/1024/1024, 1)  AS data_mb,
       ROUND(index_length/1024/1024, 1) AS index_mb,
       ROUND(data_free/1024/1024, 1)    AS free_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
ORDER BY data_length + index_length DESC
LIMIT 20;

data_free 就是碎片空间，但回收手段的代价差别很大：

手段代价是否把空间还给操作系统
ALTER TABLE t ENGINE=InnoDB / ALTER ... FORCE全程重建表，长 INPLACE，期间磁盘可能翻倍是（innodb_file_per_table=ON 时）
OPTIMIZE TABLE大表上等价于重建，锁写或长时间占用 IO是，但收益常被高估
DELETE 历史数据只标记删除，空间留在表内否，需再重建才释放
分区表 DROP PARTITION秒级，几乎无锁是，大表归档首选

OPTIMIZE TABLE 不改变查询性能上限，data_free 不大时纯属浪费窗口；真正有效的是按时间分区后裁剪分区、以及定期归档冷数据。前提是 innodb_file_per_table = ON，否则空间只能留在共享表空间里。

处置动作

  1. 磁盘满：先 PURGE BINARY LOGS、清理慢日志与 tmpdir，这几步最安全且通常几分钟见效。
  2. 空间仍紧：再考虑 DROP PARTITION 或分批删除历史数据，禁止直接 DELETE 整表。
  3. DDL 卡在元数据锁：找出并结束持有表锁的长事务，而不是 kill 执行 DDL 的会话。
  4. 全程记录清理了哪些文件、释放多少空间、影响哪些业务，避免二次故障。

预防与巡检项

  • binlog 保留期按恢复需求设定（一般 7 天），数据目录水位超过 80% 告警。
  • 慢日志与错误日志用 logrotate 管理，单文件上限固定，禁止无限增长。
  • 大表 DDL 放进变更窗口，执行前必须先跑一次长事务查询，确认无人持有未提交事务。
  • 表设计规范：所有表有主键、innodb_file_per_table=ON、大表按时间分区。
  • 定期归档冷数据，让表体积随业务增长可控；备份保留策略与 PURGE 边界一起评审。

小结：磁盘满与 DDL 锁表都是可预防的运维问题，定位按 df -h、du -sh、SHOW BINARY LOGS、information_schema.tables 逐层下钻，处置优先清 binlog 与日志、其次才动数据；大表 DDL 按操作类型选择 INSTANT、INPLACE 或 gh-ost/pt-osc，绝不在高峰对千万级表跑 ALGORITHM=COPY，空间回收用分区裁剪与归档取代昂贵的 OPTIMIZE TABLE。

笔记加载中…

标签：#故障排查 #运维

返回知识库