乐趣区

关于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_sizemax_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-oscghost)。

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

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

  3. 及时关注磁盘空间。

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

退出移动版