MySQL 一次看似简单的 DDL 引发的危机

一、问题背景：一次看似简单的 DDL 引发的危机

某生产环境执行表引擎转换操作时突发报错：

 

ALTER TABLE student ENGINE=InnoDB;
ERROR 1114 (HY000): The table 'student' is full

 

初步排查发现，数据库临时目录/tmp仅剩不足 100MB 空间，而数据目录磁盘使用率已达 95%。通过调整临时目录路径至大空间分区后，操作成功执行。这一现象揭示了 Online DDL 操作中隐藏的空间需求复杂性。

二、Online DDL 的三重空间陷阱

1. 临时日志文件：并发 DML 的 "影子记录者"

• 作用机制：在添加索引、修改表结构等操作中，InnoDB 通过临时日志（ibtmp文件）记录并发的 DML 操作，确保在线操作的一致性。
• 关键参数：innodb_online_alter_log_max_size（默认 128MB）控制单个日志文件大小，若并发修改频繁且 DDL 耗时较长，可能触发DB_ONLINE_LOG_TOO_BIG错误。
• 风险场景：
   

  -- 高并发写入时执行DDL，日志文件快速膨胀
  ALTER TABLE orders ADD INDEX idx_order_time(order_time);
  

   

2. 临时排序文件：重建表的 "幕后杀手"

• 产生场景：当 DDL 操作需要重建表（如修改引擎、添加非索引列）时，MySQL 会在临时目录（tmpdir）生成排序文件，用于临时存储数据。
• 路径陷阱：
  • 默认路径为/tmp，若该目录空间不足（如系统默认的 tmpfs 仅 1GB），会导致Temporary file write failure错误。
  • 案例：将tmpdir设置为 1MB 后，执行ALTER TABLE student ADD INDEX(name)直接报错。

3. 中间表文件：数据目录的 "隐形占用"

• 生成逻辑：Online DDL 过程中会在数据目录创建以#sql-ib开头的临时中间表，大小可能与原表相当。
• 空间计算：假设原表占用 500GB，执行ALTER TABLE ... ENGINE=InnoDB时，数据目录需额外 500GB 空间用于中间表。

三、实战测试：复现与突破空间限制

测试环境搭建

1. 数据目录限制：通过磁盘镜像创建 600MB 受限目录：
    

   dd if=/dev/zero of=/root/test.img bs=60M count=10
   losetup /dev/loop0 /root/test.img
   mount -t ext3 /dev/loop0 /opt/mysql/data/3310/my_test
   

    
2. 临时目录限制：将/tmp大小限制为 1MB：
    

   mount -o remount,size=1M tmpfs /tmp
   

    

场景一：临时排序文件不足

-- 执行添加索引操作（tmpdir=1MB）
ALTER TABLE student ADD INDEX idx_name(name);
ERROR 1878 (HY000): Temporary file write failure

 

解决：扩大临时目录至 500MB 后成功：

mount -o remount,size=500M tmpfs /tmp

 

场景二：中间表空间不足

-- 数据目录使用率超50%时执行引擎转换
ALTER TABLE student ENGINE=InnoDB;
ERROR 1114 (HY000): The table 'student' is full

 

解决：清理数据目录至使用率 < 30% 后执行成功。

四、系统性解决方案

1. 空间预估公式

 

所需空间 = 原表大小 + 临时日志最大体积 + 临时排序文件大小

• 临时日志体积 = 并发 DML 速率 × DDL 耗时 × 单条日志大小
• 临时排序文件 ≈ 原表索引大小（添加索引场景）

2. 核心参数调优

参数名称	作用描述	推荐配置
innodb_online_alter_log_max_size	限制临时日志单个文件大小，避免耗尽磁盘空间	生产环境建议 512MB~2GB，根据并发写入量调整
tmpdir	临时文件存储路径，建议设置为独立大空间分区（如 /data/tmp）	SET GLOBAL tmpdir='/data/tmp';（需重启生效）
sort_buffer_size	排序操作缓冲区大小，调大可减少磁盘临时文件生成	默认为 256KB，复杂排序场景建议 1MB~8MB

3. 操作流程优化

 

 

五、生产环境最佳实践

1. 事前规划
   • 通过pt-duplicate-key-checker提前分析索引必要性，减少无效 DDL。
   • 使用SHOW TABLE STATUS预估表大小，结合公式计算所需空间。
2. 事中监控
   • 实时监控/tmp和数据目录磁盘使用率：
      

     watch -n 5 'df -h /tmp /data/mysql'
     

      
   • 跟踪临时文件生成：

     SELECT * FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME LIKE '%ibtmp%';

      

      
3. 事后清理
   • 定期清理历史临时文件：

     find /data/tmp -name '#sql-ib*.tmp' -mtime +1 -delete

   • 对大表使用pt-osc或gh-ost工具，减少对临时空间的依赖。

六、总结：空间管理的底层逻辑

Online DDL 的空间问题本质是 **"数据流动的中间态管理"**—— 临时日志承载着并发修改的一致性，临时文件记录着结构变更的中间结果，中间表则是数据重构的载体。理解这三重空间需求，提前规划存储资源，才能避免 "小空间引发大故障" 的悲剧。记住：在数据库操作中，每一次数据的 "变形" 都需要足够的 "舞台" 来完成。