【工作】生产环境中MySQL Drop 删除(百G级、T级)大表的解决方法

热门标签

, , , , , , ,

一一网络

做互联网综合资源分享云平台!。

立即加入一一网络

特别声明:文章多为网络转载,资源使用一般不提供任何帮助,特殊资源除外,如有侵权请联系!

状况说明:

在生产环境中,时常会遇到需要删除一个大的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.*
【工作】生产环境中MySQL Drop 删除(百G级、T级)大表的解决方法
可以看到,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

未经允许不得转载:作者:admin, 转载或复制请以 超链接形式 并注明出处 一一网络
原文地址:《【工作】生产环境中MySQL Drop 删除(百G级、T级)大表的解决方法》 发布于2019-06-17

分享到:
赞(1)

评论 抢沙发

评论前必须登录!

  注册



【工作】生产环境中MySQL Drop 删除(百G级、T级)大表的解决方法

长按图片转发给朋友

一一网络
一一网络是一个做互联网的综合资源分享平台,邹洪博客,Emblog,Blog,免费,技术教程,破解源码,资源分享,永久免费,资源分享平台,免费破解,资源分享云平台,永久免费,技术教程分享网站,免费技术学习平台,一一网络是一个专注于网络资源开发和共享加建站教程、源码下载、WordPress主题、网站开发交流、程序软件下载、建站资源、多媒体交流、电影资源、Android软件分享为一体的多互动交流平台。

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录