【MySQL】drop大表

时间:2022-02-02 17:26:50

利用硬链接和truncate降低drop table对线上环境的影响 众所周知drop table会严重的消耗服务器IO性能,如果被drop的table容量较大,甚至会影响到线上的正常。

首先,我们看一下为什么drop容量大的table会影响线上服务

直接执行drop table,mysql会将表定义和表数据全都删除,包括磁盘上的物理文件,也包括buffer pool中的内存数据。 这就分两步,第一步从buffer pool中删除,这会涉及到table_cache的lock,如果持有table_cache的lock,这将导致其他查询都无法执行。这种情况在没有innodb_per_table之前尤为严重。另外,mysql5.5.23之后添加lazy drop table功能,这个功能用来解决mutex on the LRU list。其中心思想就是加锁,找到需要被删除的page,删除1024个page之后释放锁让其他thread工作,之后loop。而percona的lazy drop处理起来更优雅一些,其会先加锁,然后找到需要被删除的page,标记,释放锁,后台慢慢删除。

【MySQL】drop大表

之后就是第二步,这步在大容量表的时候更为消耗时间,那就是在os上删除物理文件。大家都知道在ext3上rm一个200G的文件会非常耗时,这是由于ext3存储数据的结构导致,如果一个很大的文件,ext3的i_block无法直接存放,需要多层嵌套才能完全存储下,在这种情况下由于映射的层次多,并且由于多层映射也不会是顺序存储的,就导致了很大的随机IO,这就导致了删除物理文件非常慢的现象。在这种情况下,建议升级到ext4,这是由于ext4比ext3使用extent分配存储空间,其最大的优势就是顺序存储。

ext3:

【MySQL】drop大表

【MySQL】drop大表

ext4:

【MySQL】drop大表

【MySQL】drop大表

知道了原因,我们来说说如何解决。具体步骤如下:

1、建立硬链接。
ln table.ibd table.idb.hdlk
2、mysql执行drop table操作。
drop table if exists tablename;
3、使用truncate删除物理文件。
truncate -s xxxG filename

了解下truncate

此命令不仅能把文件的数据删成0字节,还可以缩减(甚至扩大)文件至指定的大小(通过 -s 选项指定文件大小值)

[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]
$echo -n 12345678 >test
[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]
$cat test
12345678
[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]
$truncate -s 4 test
[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]
$cat test
1234
[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]
$ls -l test
-rw-r--r-- 1 zhiqian.ly users 4 Mar 30 21:03 test
[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]
$truncate -s 1M test
[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]
$cat test
1234
[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]
$ls -l test
-rw-r--r-- 1 zhiqian.ly users 1048576 Mar 30 21:03 test
[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]
$wc -c test
1048576 test
[zhiqian.ly@dbconsole.sqa.bja /home/zhiqian.ly]
$du test
4 test

这里还可以看到一个“奇怪”的现象,本来已经缩至4字节的文件,把它扩展成1M以后,ls 和 wc 的结果显示大小确实是1M,但是 du 的结果却发现大小还是4字节。这也是要注意的地方之一,这种文件称为“空洞文件”,也就是说,文件的部分内容并没有实际存在于硬盘上(即没有分配对应的inode),只是“声称”有1M的大小而已。对于不存在于硬盘上的那部分字节,如果去读的话,也是不会报错的,会读到全0的数据。

删除代码

#!/bin/bash
TRUNCATE=/usr/local/bin/truncate
for i in `seq 2194 -10 10 `; #从2194 开始每次递减10 ,输出结果见下面
do
sleep 2
echo "$TRUNCATE -s ${i}G /tmp/ep_bak.ibd.hdlk "
$TRUNCATE -s ${i}G /tmp/ep_bak.ibd.hdlk
done

其实硬链接和drop table就不用多说了,当多个文件共同指向同一inode,inode链接数N>1,删除任何一个文件都是巨快因为,此时删除的仅仅是指向inode的指针而当N=1时,则不一样了,此时删除的文件相关的所有数据块,所以慢在建立硬链接之后,mysql会认为rm了硬链接文件之后就算操作完毕,不会真正去删除物理文件从而提高了速度。但是对于服务器来说,实际的物理文件还在,如果手动rm,还是会产生很多的io影响,这时候就用到了truncate这个工具。这个工具会根据指定的size大小进行逐步删除,会将对IO造成的影响降到最低。

硬链接与软链接的联系与区别

我们知道文件都有文件名与数据,这在 Linux 上被分成两个部分:用户数据 (user data) 与元数据 (metadata)。用户数据,即文件数据块 (data block),数据块是记录文件真实内容的地方;而元数据则是文件的附加属性,如文件大小、创建时间、所有者等信息。在 Linux 中,元数据中的 inode 号(inode 是文件元数据的一部分但其并不包含文件名,inode 号即索引节点号)才是文件的唯一标识而非文件名。文件名仅是为了方便人们的记忆和使用,系统或程序通过 inode 号寻找正确的文件数据块。下图展示了程序通过文件名获取文件内容的过程。

通过文件名打开文件

【MySQL】drop大表

【MySQL】drop大表

为解决文件的共享使用,Linux 系统引入了两种链接:硬链接 (hard link) 与软链接(又称符号链接,即 soft link 或 symbolic link)。链接为 Linux 系统解决了文件的共享使用,还带来了隐藏文件路径、增加权限安全及节省存储等好处。若一个 inode 号对应多个文件名,则称这些文件为硬链接。换言之,硬链接就是同一个文件使用了多个别名(见 图 2.hard link 就是 file 的一个别名,他们有共同的 inode)。硬链接可由命令 link 或 ln 创建。如下是对文件 oldfile 创建硬链接。

ln oldfile newfile

由于硬链接是有着相同 inode 号仅文件名不同的文件,因此硬链接存在以下几点特性: 文件有相同的 inode 及 data block; 只能对已存在的文件进行创建; 不能交叉文件系统进行硬链接的创建; 不能对目录进行创建,只可对文件创建; 删除一个硬链接文件并不影响其他有相同 inode 号的文件。

软链接与硬链接不同,若文件用户数据块中存放的内容是另一文件的路径名的指向,则该文件就是软连接。软链接就是一个普通文件,只是数据块内容有点特殊。软链接有着自己的 inode 号以及用户数据块(见 图 5 )。因此软链接的创建与使用没有类似硬链接的诸多限制: 软链接有自己的文件属性及权限等; 可对不存在的文件或目录创建软链接; 软链接可交叉文件系统; 软链接可对文件或目录创建; 创建软链接时,链接计数 i_nlink 不会增加; 删除软链接并不影响被指向的文件,但若被指向的原文件被删除,则相关软连接被称为死链接(即 dangling link,若被指向路径文件被重新创建,死链接可恢复为正常的软链接)。

软链接的访问

【MySQL】drop大表

【MySQL】drop大表

参考: