MySQL-How to drop a large table gracefully?

Description:

Drop tables is such a common content of a DBA’s daily work. Drop a small table( such as less than 100 Mega Bytes ), we can just do DROP operations during low load. But in most situations, the tables are more than 100MB, even reach to 100GB(called large table). Then if you drop this kind of tables( including during the low load ), the whole  system may be freezing till the DROP finished, or even been unavailable because of the disk I/O overloaded.

Currently, we use separate tablespaces for different tables (innodb_file_per_table=ON). And MySQL server was often built on the LINUX. That means the data of a table is actually stored in a separate data file. So if we can remove the data file progressively and don’t let the MySQL server knows, then the DROP LARGE TABLE operation will be done gracefully.

Then how can we remove a data file and don’t let MySQL knows?

(1) LINUX allows us to build a hard link to the file, that makes us could remove the data file after DROP TABLE on the MySQL server, and this is invisible to database.

(2) We can then remove the data file step by step by using the TRUNCATE command of LINUX( http://man7.org/linux/man-pages/man1/truncate.1.html ).

How to drop large tables gracefully:

1. Build a hard link to the data file

Build a hard link named testbigfile.ibd.rm, the table name is testbigfile, then we can find the data file on the database

We can use ls -l statement to showing the details:

It shows that the link has been used by two files, testbigfile.idb and testbigfile.idb.rm.

So when we remove a file, it’s not actually removing the file, just the link, that makes the DROP TABLE statement can be finished in a very short time. And if we remove the remaining file, it’s the real data removing.

2. DROP TABLE

After building a hard link to the data file, we can DROP the table on MySQL server:

Then only one file named testbigfile.idb.rm left.

3. Remove the actual file

truncate command allows to remove file step by step, for example:

This means truncate  testbigfile.ibd.rm  for 1GB per time. Then we can run this command multiple times during low load until the file is empty, and use rm command to remove the file at last.