mysql在线无性能影响删除7G大表

来源：互联网发布：易语言充值卡源码编辑：程序博客网时间：2024/06/02 21:58

author：skate
time：2013/03/01

mysql在线无性能影响删除7G大表

如何在mysql数据库里删除7G(或更大)大表，使其又不影响服务器的io，导致性能下降影响业务。先不说其是mysql表，就是普通文件，如果直接rm删除，也会使服务器的io性能急剧下降；换个思路如果用化整为零的方式，分多次大大文件一点一点删除，就可以避免因删除文件占用太多服务器io资源

例子：

版本：
mysql> select version();
+------------+
| version() |
+------------+
| 5.1.67-log |
+------------+
1 row in set (0.05 sec)

数据表大小：
mysql> select count(1) from user4;
+----------+
| count(1) |
+----------+
| 36700160 |
+----------+
1 row in set (1 min 35.66 sec)

[root@racdb2 test]# ll user_bak*
-rw-rw---- 1 mysql dba 10466 Mar 1 13:50 user4_bak.frm
-rw-rw---- 2 mysql dba 7734296576 Mar 1 14:28 user4_bak.ibd
[root@racdb2 test]#

创建一个中间表，来见减少对业务影响
mysql> create table user4_tmp engine=innodb select * from user4 where 1=2;
Query OK, 0 rows affected (0.18 sec)
Records: 0 Duplicates: 0 Warnings: 0

把原始表user4重命名
mysql> rename table user4 to user4_bak;
Query OK, 0 rows affected (0.03 sec)

把中间表重命名为原始表user4，如果需要数据，可以导入部分数据
mysql> rename table user4_tmp to user4;
Query OK, 0 rows affected (0.01 sec)

通过文件的硬链接方式删除文件

[root@racdb2 test]# ln user4_bak.ibd user4_bak.ibd.hdlk
[root@racdb2 test]# ll user_bak*
-rw-rw---- 1 mysql dba 10466 Mar 1 13:50 user4_bak.frm
-rw-rw---- 2 mysql dba 7734296576 Mar 1 14:28 user4_bak.ibd
-rw-rw---- 2 mysql dba 7734296576 Mar 1 14:28 user4_bak.ibd.hdlk
[root@racdb2 test]#

注意：
硬连接的作用是允许一个文件拥有多个有效路径名，即文件的索引节点有一个以上的连接。只删除一个连接并不影响索引节点本身和其它的连接，只有当最后一个连接被删除后，文件的数据块及文件的连接才会被释放。也就是说，文件真正删除的条件是与之相关的所有硬连接文件均被删除。

发现删除7G的文件巨快
mysql> drop table user4_bak;
Query OK, 0 rows affected (0.60 sec)

这个时候在mysql数据库里已经删除了表user4_bak，但系统的存储空间还没有释放，如下所示：
[root@racdb2 test]# ll user_bak*
-rw-rw---- 2 mysql dba 7734296576 Mar 1 14:28 user4_bak.ibd.hdlk

只有我们把文件user4_bak.ibd.hdlk删除，磁盘空间才会被释放，那如何尽量少占用系统资源，最小化影响业务来释放这个空间呢？前面已经分析通过化整为零的方式，通过coreutils工具集中的truncate对大文件进行shrink来逐渐释放空间.

脚本如下：
[root@racdb2 test]# more /home/mysql/rm_bigfile.sh
#!/bin/bash
#author：skate
#time：2013/02/28
#function：rm huge file

TRUNCATE=/usr/local/bin/truncate
for i in `seq 7384 -100 10 `; #从7384开始每次递减100 ，输出结果见下面
do
sleep 1
echo "$TRUNCATE -s ${i}M /tmp/user4_bak.ibd.hdlk "
$TRUNCATE -s ${i}M /mysqldata/data/test/user_bak.ibd.hdlk
done

执行脚本，然后同时开另一个session，用iostat查看系统io的压力
[root@racdb2 test]# sh /home/mysql/rm_bigfile.sh

发现每1s删除100M的文件，服务器基本没有压力
[root@racdb2 coreutils-8.9]# iostat -mx 2 | grep "sda2"
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00    10.45 0.00 1.00     0.00     0.04    92.00     0.00    0.50   0.50   0.05
sda2              0.00     2.99 0.00 9.95     0.00     0.05    10.40     0.03    3.20   0.25   0.25
sda2              0.00     9.50 0.00 1.00     0.00     0.04    84.00     0.00    1.50   1.50   0.15
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     9.50 0.00 1.00     0.00     0.04    84.00     0.00    0.50   0.50   0.05
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00    14.50 0.00 1.00     0.00     0.06   124.00     0.00    0.50   0.50   0.05
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     9.00 0.00 1.00     0.00     0.04    80.00     0.00    0.50   0.50   0.05
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     9.50 0.00 1.00     0.00     0.04    84.00     0.00    0.50   0.50   0.05
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     9.00 0.00 1.00     0.00     0.04    80.00     0.00    0.50   0.50   0.05
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00    10.55 0.50 10.05     0.00     0.08    16.00     0.04    3.95   1.43   1.51
sda2              0.00     0.00 2.01 0.00     0.01     0.00     8.00     0.02    8.00   8.00   1.61
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     7.54 0.00 1.01     0.00     0.03    68.00     0.00    0.50   0.50   0.05
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     9.50 0.00 1.00     0.00     0.04    84.00     0.00    0.50   0.50   0.05
sda2              0.00     2.99 0.00 1.00     0.00     0.02    32.00     0.00    0.50   0.50   0.05
sda2              0.00     0.00 0.00 0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00
sda2              0.00     3.02 0.50 1.01     0.00     0.02    24.00     0.02   11.33 11.33   1.71

coreutils的安装
[root@racdb2 test]#wget http://ftp.gnu.org/gnu/coreutils/coreutils-8.9.tar.gz
[root@racdb2 test]#tar -zxvf coreutils-8.9.tar.gz
[root@racdb2 test]#cd coreutils-8.9
[root@racdb2 test]#./configure
[root@racdb2 test]#make && make install

文档参考：http://www.gnu.org/software/coreutils/manual/coreutils.html#truncate-invocation

总结：
1.使用方法：硬链接和化整为零
2.做事之前先思考方法，不要急于动手
3.多角度思考问题，本来是在线问题，在线解决起来，束缚较多，那就把在线变离线；一次删除影响大，那就变多次删除

---end----