Shulou(Shulou.com)06/01 Report--

1. Background

In the production environment, there may be a need to delete an unimportant large table, because the large table takes up a lot of disk space. If we drop this table directly, it usually takes more than 20 seconds. We will always feel that the master MySQL will be stuck. Now we will give everyone a correct delete posture.

2. First of all, let's look at this big table.

Shell > ls log_api_call_01_01*-l

-rw-r- 1 mysql mysql 9362 Jun 22 15:35 log_api_call_01_01.frm

-rw-r- 1 mysql mysql 293334036112 Jun 22 15:38 log_api_call_01_01.ibd

Shell > ln log_api_call_01_01.ibd log_api_call_01_01ibd.bak

Shell > ls log_api_call_01_01*-l

-rw-rw-- 1 MySQL mysql 9362 Apr 14 23:03 log_api_call_01_01.frm

-rw-r- 1 mysql mysql 293334036112 Jun 22 15:38 log_api_call_01_01.ibd

-rw-r- 1 mysql mysql 293334036112 Jun 22 15:38 log_api_call_01_01.ibd.bak

Mysql > drop table log_api_call_01_01

Query Ok, 0 rows affacted (0.92sec)

By doing this, you can reduce the hang residence time of the mysql drop big table, and then delete the real * .b text * .bak piece during the business trough.

As for the principle:

Is to use the principle of OS HARD LINK.

When multiple filenames point to the same INODE at the same time, the number of references to the INODE is N > 1, and deleting any one filename will be quick.

Because its direct physical file block has not been deleted. Just deleted a pointer.

When the reference number of INODE is Number1, deleting a file needs to clear all data blocks related to the file, so it will be time-consuming

For large table operations, there are actually several Tips:

You can replace drop table with rename table first.

Mysql > rename table log_api_call_01_01 to log_api_call_01_01_bak

Delete physical files

For i in `seq 100-1 1`; do sleep 2ten sudo truncate-s ${I} G / data/mysql/manager/log_api_call_01_01.ibd.bak;done sudo rm-rf / data/mysql/manager/log_api_call_01_01.ibd.bak

This action is also quickly equivalent to changing a file name.

You can also consider using the XFS file system, which is faster for Drop tables.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.