Detailed explanation of how to gracefully delete a large table in MySQL

Preface

To delete a table, the command that comes to mind subconsciously may be to directly use DROP TABLE "table name". This is a naive approach, because when the expression space to be deleted reaches tens of GB or even hundreds of GB of tables. If such a command is issued, MySQL may be directly blocked, and the external manifestation is a rapid drop in QPS and a slowdown in customer requests.

Solution

1. Manually delete during off-peak hours

This may require the DBA to work tirelessly and get up late at night to delete the table.

2. Clear data first and delete it last

For example, if there are 10 million pieces of data, write a script to delete 200,000 pieces each time, sleep for a while, and then continue executing. This also makes it imperceptible to the user.

3. Make a hard link to the table file (idb file) to speed up deletion

This method uses the knowledge of hard links under Linux to perform quick deletion. If you don't remember, you can go back and look at "Bird Brother's Linux Private Recipe"

ln data_center_update_log.ibd data_center_update_log.ibd.hdlk

[root@mysql01 sports_center]# ll
Total usage 19903792
-rw-r----- 1 mysql mysql 9076 October 17 13:15 data_center_update_log.frm
-rw-r----- 2 mysql mysql 8447328256 December 23 11:35 data_center_update_log.ibd
-rw-r----- 2 mysql mysql 8447328256 December 23 11:35 data_center_update_log.ibd.hdlk

After executing the above command, we have an additional data_center_update_log.ibd.hdlk file. This operation does not actually take up disk space, it just adds a reference to the file on disk.

When we delete any of these files, it will not affect the actual file on the disk, but only reduce its reference count by 1. When the number of references becomes 1, the file will be deleted and IO will be performed to delete it.

It is precisely by utilizing this feature that the original MySQL operation of deleting large files is converted into a simple operating system-level file deletion, thereby reducing the impact on MySQL.

4. Log in to MySQL and execute the drop table operation

Soon, 2 million pieces of data were completed in just 1 second. This operation was performed after creating a hard link.

mysql> drop tables data_center_update_log;
Query OK, 0 rows affected (1.02 sec)


mysql> exit
Bye

Exit and check the data directory again. Only the data_center_update_log.ibd.hdlk hard link file is left. [root@mysql01 sports_center]# ll
Total usage 19903792
-rw-r----- 2 mysql mysql 8447328256 December 23 11:35 data_center_update_log.ibd.hdlk

5. How to correctly delete the ibd.hdlk hard link file?

• Although after dropping the table, the remaining hard link files have nothing to do with mysql. However, if the file is too large, directly deleting it with the rm command will cause a surge in IO overhead and excessive CPU load, which will in turn affect MySQL.
• The method we use here can delete files in a loop and slowly clean up the files. This can be done with a script.
• The Truncate command is often used to shrink or expand a file to a specified size. If the file is larger than the specified size, the extra data will be lost. If the file is shorter, it is extended and the extended portion is read as zero bytes.
  

5.1 Install the truncate command

[root@mysql01 ~]# cruncate
-bash: cruncate: command not found Usually the operating system will install the truncate command, which is in the coreutils installation package. If it is not installed, you can use the following command to install it [root@mysql01 ~]# yum provides truncate
coreutils-8.22-24.el7.x86_64 : A set of basic GNU tools commonly used in shell scripts
Source: base
Matching source:
File name: /usr/bin/truncate

You can see that truncate is provided by the coreutils installation package. Let's install the coreutils installation package:

[root@mysql01 ~]# yum install -y coreutils

5.2 Common options for truncate

-c, --no-create --> Do not create any files
-o, --io-blocks --> Treat size as number of storage blocks, not bytes
-r, --reference=RFILE --> Reference the specified file size
-s, --size=SIZE --> Set the file size to the specified bytes

5.3 truncate_bigfile.sh script

Principle: Use the truncate -s option to specify the file size, specify the size of each file reduction through the script, and sleep for a certain period of time, so as to achieve controllable file deletion.

Attachment: truncate_bigfile.sh script

#!/bin/bash
#

TRUNCATE=/usr/bin/truncate
FILE=$1

if [ x"$1" = x ];then
 echo "Please input filename in"
 exit 1;
else
 SIZE_M=$(du -sm "$1" | awk '{print $1}')

 for i in $(seq "${SIZE_M}" -100 0)
 do
 sleep 1
 echo "${TRUNCATE} -s ${i}M ${FILE}"
 ${TRUNCATE} -s "${i}"M "${FILE}"
 done
fi

if [ $? -eq 0 ];then
    \rm -f "${FILE}"
else
    echo "Please check file"
fi

Summarize

This is the end of this article about how to elegantly delete large tables in MySQL. For more information about elegantly deleting large tables in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!