Mysql online without performance impact delete 7g big table

Mysql online without performance impact delete 7g big table _ MySQL-mysql tutorial

Last Update:2017-05-12 Source: Internet

Author: User

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

Mysql deletes the 7g large table bitsCN.com online without performance impact

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: www.bitsCN.com

Version:

Mysql> select version ();

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

| Version () |

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

| 5.1.67-log |

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

1 row in set (0.05 sec)

Data table size: www.bitsCN.com

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:

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"