【工作】生产环境中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.*
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

免责声明:务必仔细阅读

  • 本站为个人博客,博客所转载的一切破解、path、补丁、注册机和注册信息及软件等资源文章仅限用于学习和研究目的;不得将上述内容用于商业或者非法用途,否则,一切后果请用户自负。本站信息来自网络,版权争议与本站无关。

  • 本站为非盈利性站点,打赏作为用户喜欢本站捐赠打赏功能,本站不贩卖软件等资源,所有内容不作为商业行为。

  • 本博客的文章中涉及的任何解锁和解密分析脚本,仅用于测试和学习研究,禁止用于商业用途,不能保证其合法性,准确性,完整性和有效性,请根据情况自行判断.

  • 本博客的任何内容,未经许可禁止任何公众号、自媒体进行任何形式的转载、发布。

  • 博客对任何脚本资源教程问题概不负责,包括但不限于由任何脚本资源教程错误导致的任何损失或损害.

  • 间接使用相关资源或者参照文章的任何用户,包括但不限于建立VPS或在某些行为违反国家/地区法律或相关法规的情况下进行传播, 博客对于由此引起的任何隐私泄漏或其他后果概不负责.

  • 请勿将博客的任何内容用于商业或非法目的,否则后果自负.

  • 如果任何单位或个人认为该博客的任何内容可能涉嫌侵犯其权利,则应及时通知并提供身份证明,所有权证明至admin@proyy.com.我们将在收到认证文件后删除相关内容.

  • 任何以任何方式查看此博客的任何内容的人或直接或间接使用该博客的任何内容的使用者都应仔细阅读此声明。博客保留随时更改或补充此免责声明的权利。一旦使用并复制了博客的任何内容,则视为您已接受此免责声明.

您必须在下载后的24小时内从计算机或手机中完全删除以上内容.

您使用或者复制了本博客的任何内容,则视为已接受此声明,请仔细阅读


更多福利请关注一一网络微信公众号或者小程序

一一网络微信公众号
打个小广告,宝塔服务器面板,我用的也是,很方便,重点是免费的也能用,没钱太难了,穷鬼一个,一键全能部署及管理,送你3188元礼包,点我领取https://www.bt.cn/?invite_code=MV9kY3ZwbXo=


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

发表评论

发表评论

一一网络-提供最优质的文章集合

立即查看 了解详情