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

Source: Internet
Author: User
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:

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

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 ----

BitsCN.com

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.