MySQL deletes 7g large tables online without performance impact

Author: skate
Time: 2013/03/01

MySQL deletes 7g large tables online without performance impact

 

How to delete a 7G (or larger) large table in the MySQL database so that it does not affect the I/O of the server, resulting in performance degradation and business impact. Not to mention that it is a MySQL table, that is, a common file. If you delete RM directly, the IO performance of the server will also drop sharply. If you change your mind, delete large files multiple times at to avoid occupying too many server Io resources.

Example:

Version:
Mysql> select version ();
+ ------------ +
| Version () |
+ ------------ +
| 5.1.67-log |
+ ------------ +
1 row in SET (0.05 Sec)

Data table size:
Mysql> select count (1) From user4;
+ ---------- +
| Count (1) |
+ ---------- +
| 1, 36700160 |
+ ---------- +
1 row in SET (1 min 35.66 Sec)

[Root @ racdb2 test] # ll user_bak *
-RW ---- 1 MySQL DBA 10466 Mar 1 user4_bak.frm
-RW ---- 2 MySQL DBA 7734296576 Mar 1 user4_bak.ibd
[Root @ racdb2 test] #

Create an intermediate table to reduce the impact on services.
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)

 

Rename the original table user4
Mysql> rename table user4 to user4_bak;
Query OK, 0 rows affected (0.03 Sec)

Rename the intermediate table as user4. If you need data, you can import some data.
Mysql> rename table user4_tmp to user4;
Query OK, 0 rows affected (0.01 Sec)

Delete an object through a hard link

[Root @ racdb2 test] # ln user4_bak.ibd user4_bak.ibd.hdlk
[Root @ racdb2 test] # ll user_bak *
-RW ---- 1 MySQL DBA 10466 Mar 1 user4_bak.frm
-RW ---- 2 MySQL DBA 7734296576 Mar 1 user4_bak.ibd
-RW ---- 2 MySQL DBA 7734296576 Mar 1 user4_bak.ibd.hdlk
[Root @ racdb2 test] #

Note:
A hard connection allows a file to have multiple valid path names. That is, the index node of the file has more than one connection. Deleting only one connection does not affect the connection between the index node and other nodes. Only when the last connection is deleted will the data block and file connection of the file be released. That is to say, the true condition for deleting a file is that all hard connection files related to the file are deleted.

Found that deleting 7 GB of files is extremely fast
Mysql> drop table user4_bak;
Query OK, 0 rows affected (0.60 Sec)

At this time, the user4_bak table has been deleted in the MySQL database, but the storage space of the system has not been released, as shown below:
[Root @ racdb2 test] # ll user_bak *
-RW ---- 2 MySQL DBA 7734296576 Mar 1 user4_bak.ibd.hdlk

 

Only when we delete the file user4_bak.ibd.hdlk will the disk space be released. How can we use less system resources and minimize the impact on services to release the space? The preceding analysis shows how to gradually release space by using the shrink method of truncate in the coreutils tool set.

 

The script is as follows:
[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'; # decrease by 7384 each time starting from 100. The output result is as follows:
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

 

Execute the script, open another session at the same time, and use iostat to view the system Io pressure
[Root @ racdb2 test] # sh/home/MySQL/rm_bigfile.sh

It is found that MB of files are deleted every 1 s, and there is basically no pressure on the server
[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 Installation
[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 @ racdbtest] # cd coreutils-8.9
[Root @ racdb2 test] #./configure
[Root @ racdb2 test] # Make & make install

Document reference: http://www.gnu.org/software/coreutils/manual/coreutils.html#truncate-invocation

Summary:
1. Usage: hard link and convert to zero
2. Think about methods before you do things.
3. Think about the problem from multiple perspectives. It was originally an online problem. If the problem is solved online, it will be changed to offline. If one deletion has a big impact, it will be deleted multiple times.
 

 

 

--- End ----