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

程序员文章站 2022-06-12 14:50:47

...

bitsCN.com

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

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

例子： www.bitsCN.com

版本：

mysql> select version();

+------------+

| version() |

+------------+

| 5.1.67-log |

+------------+

1 row in set (0.05 sec)

数据表大小： www.bitsCN.com

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

mysql> show tables;

+----------------+

| Tables_in_test |

+----------------+

| a |

| b |

| user |

| user1 |

| user2 |

| user3 |

| user4 |

| user4_tmp |

| utf8 |

+----------------+

9 rows in set (0.01 sec)

把原始表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 ，输出结果见下面

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"