状况说明:

在生产环境中,时常会遇到需要删除一个大的mysql数据表,一般表的大小都在百G以上,因为INNODB会维护一个全局独占锁(在table cache上面),所以mysql在对大表做DROP TABLE 过程中,所有操作都会被HANG住,这个过程会持续很长时间,直到DROP TABLE完成才释放,这样就会影响到线上数据库的使用。
同时,从硬盘上删除一个百G以上的文件,也会造成系统的I/O开销飙高,影响其他程序运行,这也是我们需要避免和解决的问题。

原理分析:

本文中快速 DROP TABLE 利用了操作系统的 Hard Link(硬链接) 的原理。当文件iNode 的引用数 N = 1 时,删除文件需要去把这个文件相关的所有数据块清除,所以会比较耗时。而当多个文件名同时指向同一个 iNode 时,这个 iNode 的引用数 N > 1,删除其中任何一个文件名都会很快。因为其直接的物理文件块没有被删除,只是删除了一个指针而已.
所以我们删除大表的思路如下:
  1. 为要删除的数据表建立硬连接
  2. 数据库中执行drop table tablename 命令删除表
  3. 删除硬连接文件释放硬盘空间
例如:我们生产中有个119G的日志备份表DAC_LOG_JZ_20160526_bak
# ll -i DAC_LOG_JZ_20160526_bak.*
302973072 -rw-rw---- 1 mysql mysql        25368 Apr 26  2016 DAC_LOG_JZ_20160526_bak.frm
302973073 -rw-rw---- 1 mysql mysql 127137742848 May 24  2016 DAC_LOG_JZ_20160526_bak.ibd
由上可以看到其数据文件为DAC_LOG_JZ_20160526_bak.ibd,inode号302973073,引用数为1个,接下来我们为其创建一个硬连接。
# ln DAC_LOG_JZ_20160526_bak.ibd DAC_LOG_JZ_20160526_bak.ibd.hdlk
# ll -i DAC_LOG_JZ_20160526_bak.*
20171122114344
可以看到,iNode号为302973073的引用数由1变为2,接下来我们登录数据库,删除表DAC_LOG_JZ_20160526_bak
mysql> drop table DAC_LOG_JZ_20160526_bak;
Query OK, 0 rows affected (1.86 sec)
由上可以看出,非常快速(1.86sec)的删除了表DAC_LOG_JZ_20160526_bak,剩下的任务就是删除真正的物理文件,释放空间了。
因为此时innode的引用计数已经变为了1,直接删除DAC_LOG_JZ_20160526_bak.ibd.hdlk便会真正的删除物理文件。但因为物理文件较大,删除大文件仍会引起较高的磁盘IO开销。因此可以使用少量逐次删除的方式来删除大的数据文件。truncate工具可以用于增加或缩减指定文件的尺寸,而不会引起I/O的飙高,方法如下:
# for i in `seq 119 -1 1 ` ;do sleep 1;truncate -s ${i}G DAC_LOG_JZ_20160526_bak.ibd.hdlk;done  
# rm -rf DAC_LOG_JZ_20160526_bak.ibd.hdlk
说明:从119G开始,每次缩减1G,停1秒,继续缩减,直到文件只剩1G,最后使用rm命令删除剩余的部分。(for循环中文件大小根据自己实际情况更改)
但是,由于此命令在软件coreutils-8.22-18.el7.x86_64中,而在centos 5.8系统的版本的软件包中并没有包含此命令,所以我们还可以采用别的方法实现,如下:
# ionice -c 2 -n 6 rm -rf DAC_LOG_JZ_20160526_bak.ibd.hdlk 
ionice 是一个可以对另一个程序设置或获取 I/O 调度级别和优先级的有用程序。如果没有给出参数或者只有 -p 参数,那么 ionice 将会查询该进程的当前的 I/O 调度级别以及优先级。如下:
# ionice -p PID
而在本例中,-c 指定命令调度的数字(0 表示无、1 表示实时、2 表示尽力、3 表示空闲)即指定命令在I/O处于什么状态中执行,-n 指定命令运行的优先级别,上面的命令就是使用尽力调度级别(2),并且使用低优先级(6)

总结:

以上就是对一个超大mysql数据表的删除思路,我们可以学到的知识点:
  • 如何删除一个百G级、T级的数据表而不引起数据库hang住,影响使用
  • 如何删除一个百G级、T级的文件而不影响系统的I/O使用
  • 对于一个超大数据库的删除思路一样,就是先删除其中较大的表,最后在执行DROP DATABASE删除整个库
参考:
https://dbarobin.com/2015/01/15/solution-of-droping-large-table-under-mysql/
https://linux.cn/article-8537-1.html
Last modification:November 7th, 2019 at 03:28 am
如果觉得我的文章对你有用,请随意赞赏