关于mysql:MySQL-执行-Online-DDL-操作报错空间不足

在 MySQL 中执行 Online DDL 之前，须要保障在三个方面的空间短缺。

作者：徐文梁，爱可生 DBA 成员，一个执着于技术的数据库工程师，次要负责数据库日常运维工作。善于 MySQL，Redis 及其他常见数据库也有涉猎；喜爱垂钓，看书，看风光，结交新敌人。

爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

本文约 1400 字，预计浏览须要 4 分钟。

问题背景

客户反馈对某张表执行 alter table table_name engine=innodb; 时报错空间有余。

通过登录数据库查看客户的 tmpdir 设置的门路，发现是 /tmp。该目录磁盘空间自身较小，调整 tmpdir 的门路与数据目录雷同，从新执行 ALTER 操作执行胜利。

问题到此结束了，然而故事并没有完结。通过查看官网信息，咱们能够从这个小小的报错中深挖更多信息。

信息解读

从官网的阐述中，咱们能够理解到，在进行 Online DDL 操作时，须要保障以下三个方面的空间短缺，否则可能会导致空间有余报错。

长期日志文件

当进行 Online DDL 操作创立索引或者更改表时，长期日志文件会记录期间的并发 DML 操作，长期日志文件最大值由 innodb_online_alter_log_max_size 参数管制，如果 Online DDL 操作耗时较长（如果表数据量较大这是很有可能的），并且期间并发 DML 对表中的记录批改较多，则可能导致长期日志文件大小超过 innodb_online_alter_log_max_size 值，从而引发 DB_ONLINE_LOG_TOO_BIG 谬误，并回滚未提交的并发 DML 操作。

长期排序文件

对于会重建表的 Online DDL 操作，在创立索引期间，会将长期排序文件写入到 MySQL 的长期目录。仅思考 UNIX 零碎，对应的参数为 _tmpdir_，如果 /tmp 目录比拟小，请设置该参数为其余目录，否则可能会因为无奈包容排序文件而导致 Online DDL 失败。

两头表文件

对于会重建表的 Online DDL 操作，会在与原始表雷同的目录中创立一个长期两头表文件，两头表文件可能须要与原始表大小相等的空间。两头表文件名以 #sql-ib 前缀结尾，仅在 Online DDL 操作期间短暂呈现。

前置筹备

针对官网的阐述，咱们能够进行理论测试，这里对长期排序文件和两头表文件场景进行测试，为了故事更好的倒退，先做一些筹备工作：

1. 创立一个测试库

数据目录对应为 /opt/mysql/data/3310/my_test。

create database my_test;

2. 限度数据目录大小

# 创立一个指定大小的磁盘镜像文件，这里为 600M
dd if=/dev/zero of=/root/test.img bs=60M count=10

#挂载设施
losetup /dev/loop0 /root/test.img

#格式化设施
mkfs.ext3 /dev/loop0

#挂载为文件夹，则限度其文件夹空间大小为 600M
mount -t ext3 /dev/loop0 /opt/mysql/data/3310/my_test

#批改属组为 MySQL 服务对应用户
chown -R mysql.mysql /opt/mysql/data/3310/my_test

3. 创立一张测试表

CREATE TABLE `student` (`id` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`score` int(11) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` varchar(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. 插入一些数据

留神：数据量不要太小，小于 /opt/mysql/data/3310/my_test 目录的一半，倡议 30% 左右。

./mysql_random_data_load -h127.0.0.1 -P3310 -uuniverse_op -p'xxx' --max-threads=8 my_test student 1500000

5. 批改 /tmp 大小

这里 tmpdir 目录为 /tmp，批改 /tmp 大小为一个较小值。

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

6. 批改其余参数

批改 tmp_table_size 和 max_heap_table_size 值为较小值，这里仅仅为了便于生成磁盘临时文件，生产环境不倡议，会重大影响性能。

set sort_buffer_size=128*1024;
set tmp_table_size=128*1024;

场景测试

登录数据库执行如下操作，能够察看到增加索引失败，报错信息如下：

mysql> alter table student add  idx_name index(name);
ERROR 1878 (HY000): Temporary file write failure.

执行如下操作批改 /tmp 目录大小，再次执行 ALTER 操作胜利。

[root@localhost ~]# mount -o remount,size=500M tmpfs /tmp
mysql> alter table student add index(name);
Query OK, 0 rows affected (4.92 sec)
Records: 0 Duplicates: 0 Warnings: 0

察看 /opt/mysql/data/3310/my_test 目录已应用空间，如果使用率较低， 倡议持续插入数据到磁盘空间使用率超过 50%

执行如下操作，会报如下谬误：

mysql> alter table student engine=innodb;
ERROR 1114 (HY000): The table 'student' is full

问题总结

好了，最初总结一下。为了咱们的 Online DDL 操作顺利进行，须要留神以下几点：

1. 在进行操作前，记得先查看 innodb_online_alter_log_max_size 值，预估下是否须要批改。

   能够间接批改为一个较大值，然而没有百分百的坏事，害处就是如果业务在 DDL 操作期间并发 DML 批改记录较多，Online DDL 完结时锁定表以利用记录的 DML 工夫会减少。所以，抉择好时机很重要，在对的工夫做对的事，当然是在业务低峰期，或者思考工具吧（pt-osc 或 ghost）。

2. 在装置实例时即设置 tmpdir 为正当的值。

   舒适提醒，该值不反对动静批改，真呈现问题就晚了，毕竟生产上不容许轻易重启服务的。

3. 及时关注磁盘空间。

   不要等到磁盘空间快满了才想着通过 Online DDL 操作进行碎片空间清理。例如 optimize table table_name;，alter table table_name engine=innodb; 等操作。这些操作自身也是须要额定的空间的，期待你的可能是 FAILURE。