How to Speed Up Dropping Large MySQL Tables and Avoid IO Bottlenecks

It is well known that dropping a large MySQL table can heavily consume server I/O and even affect online services.

Why dropping a large table impacts online services

When DROP TABLE is executed, MySQL deletes both the table definition and the data, removing the physical files on disk and the memory pages in the buffer pool.

This involves two steps. The first step removes pages from the buffer pool, which requires the table_cache lock; holding this lock blocks other queries. Before innodb_per_table this was especially severe. Since MySQL 5.5.23 a lazy‑drop mechanism was added to reduce the mutex on the LRU list: it locks, deletes 1024 pages, releases the lock, and repeats. Percona’s lazy drop is more elegant: it locks, marks pages for deletion, releases the lock, and a background thread deletes them.

The second step, which dominates for large tables, deletes the physical file on the OS. On ext3, removing a 200 GB file is very slow because ext3 stores block pointers in multiple levels, causing random I/O. Upgrading to ext4, which uses extents for sequential allocation, greatly speeds up deletion.

ext3:

ext4:

Solution steps

1. Create a hard link

ln table.ibd table.idb.hdlk

2. Execute DROP TABLE

drop table if exists tablename;

3. Use truncate to delete the physical file gradually

truncate -s 1024*1024*4 filename

After creating the hard link, MySQL thinks the file has been removed, so it does not block other queries. The actual file remains on disk; using truncate removes it in small chunks, minimizing I/O impact.

Usage: truncate OPTION... FILE...
Shrink or extend the size of each FILE to the specified size

A FILE argument that does not exist is created.

If a FILE is larger than the specified size, the extra data is lost.
If a FILE is shorter, it is extended and the extended part (hole)
reads as zero bytes.

-c, --no-create    do not create any files
-o, --io-blocks    treat SIZE as number of IO blocks instead of bytes
-r, --reference=RFILE  base size on RFILE
-s, --size=SIZE    set or adjust the file size by SIZE
...

Original link: http://www.cnblogs.com/billyxp/p/3664672.html