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 操作顺利进行,需要注意以下几点:
-
在进行操作前,记得先检查
innodb_online_alter_log_max_size
值,预估下是否需要修改。可以直接修改为一个较大值,但是没有百分百的好事,坏处就是如果业务在 DDL 操作期间并发 DML 修改记录较多,Online DDL 结束时锁定表以应用记录的 DML 时间会增加。所以,选择好时机很重要,在对的时间做对的事,当然是在业务低峰期,或者考虑工具吧(pt-osc 或 ghost)。
-
在安装实例时即设置 tmpdir 为合理的值。
温馨提示,该值不支持动态修改,真出现问题就晚了,毕竟生产上不允许随便重启服务的。
-
及时关注磁盘空间。
不要等到磁盘空间快满了才想着通过 Online DDL 操作进行碎片空间清理。例如
optimize table table_name;
,alter table table_name engine=innodb;
等操作。这些操作本身也是需要额外的空间的,等待你的可能是 FAILURE。
更多技术文章,请访问:https://opensource.actionsky.com/
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台,覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库,为开发和运维提供流程自动化能力,提升上线效率,提高数据质量。
SQLE 获取
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
开源日报:目前还没有“大模型版 Linux”;nginx 核心开发者创建新分支
欢迎阅读 OSCHINA 编辑部出品的开源日报,每天更新一期。 # 2024.2.19 今日要点 OpenSource Daily 核心 Nginx 开发者创建新分支 Freenginx 作为 Nginx Web 服务器的长期核心开发人员之一,Maxim Dounin 宣布创建该项目的一个新分支,名为 Freenginx。 Maxim Dounin 决定分叉 Nginx 是因为与 F5 发生了分歧,F5 于 2019 年收购了 Nginx 公司。Dounin 在宣布 Freenginx 时表示将不再参与由 F5 负责的 nginx 开发。取而代之的是启动另一个项目,由开发人员而非公司实体来运营。 谷歌开源 Magika —— AI 驱动的文件类型检测工具 谷歌开源了由 AI 驱动的文件内容类型识别工具,声称能够在毫秒级内精确识别超过 100 种不同文件类型,无论是二进制文件还是文本文件。Magika 是基于深度学习技术的文件类型识别系统,用于准确检测二进制和文本文件类型。在底层,Magika 采用定制的、高度优化的深度学习模型,即使在 CPU 上运行,也能在几毫秒内实现精确的文件识别。...
- 下一篇
OurBMC大咖说 | 第3期: BMC国产化和开源之路
栏目介绍:"OurBMC大咖说" 是由 OurBMC 社区精心策划的线上讲座栏目,邀请 BMC 相关领域大咖共同探讨 BMC 全栈技术的发展趋势、挑战和机遇。无论你是初学者还是资深从业者,"OurBMC大咖说" 都将为你提供一个宝贵的学习和交流的平台。 欢迎各位关注 "OurBMC大咖说",聆听大咖们的智慧之声,共同推动 BMC 全栈技术的进步和发展! 本期人物介绍:李林晶,成都信息工程大学电子信息工程专业98 级学员,中山大学 MBA 学员,长期专注于计算机体系嵌入式方向,参与了众多国家级科技项目。2014 年开始,作为汉为的创始人之一,负责飞腾架构产品在多个国家基础核心领域的落地,迄今已取得多个行业全国首台套的成功范例,支持各行业用户获得国家级科技进步一等奖 3 次,二等奖、三等奖若干,目前负责汉为产品需求定义端的全面数字化转型。 相较于其它信创行业的国产化替代,服务器BMC的国产化并不像其它领域那样在聚光灯下被众人所关注。服务器作为互联网云计算等领域的底层硬件基础设施架构,承载着关键业务的运行支持和各项技术的迭代优化,不可避免地对服务器整机性能有着卓越的要求,对系统可维护性有着远...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- CentOS7编译安装Gcc9.2.0,解决mysql等软件编译问题
- Windows10,CentOS7,CentOS8安装MongoDB4.0.16
- CentOS7设置SWAP分区,小内存服务器的救世主
- CentOS7安装Docker,走上虚拟化容器引擎之路
- CentOS关闭SELinux安全模块
- CentOS6,7,8上安装Nginx,支持https2.0的开启
- Eclipse初始化配置,告别卡顿、闪退、编译时间过长
- CentOS8安装MyCat,轻松搞定数据库的读写分离、垂直分库、水平分库
- Hadoop3单机部署,实现最简伪集群
- Docker使用Oracle官方镜像安装(12C,18C,19C)