Performance Bottlenecks and Mitigation Strategies for MySQL DROP TABLE on Large InnoDB Tables

1. Background

When using MySQL with large InnoDB tables and innodb_file_per_table=1, dropping such tables can cause a noticeable slowdown of the whole database, even for simple SELECT or DML statements unrelated to the table being removed.

2. Known Bottlenecks

Percona identified the bottleneck as CPU contention on the buffer‑pool mutex. During lazy drop the engine must clean all pages belonging to the table’s tablespace from the buffer pool, holding the buffer‑pool mutex for a long time if the pool is large (e.g., 500 GB).

MySQL 5.5.23 introduced its own lazy‑drop implementation: after processing a certain number of pages the engine releases the buffer‑pool and flush‑list mutexes, yields the CPU, and later reacquires the locks to continue cleaning. The LRU list is not touched because its pages will be reclaimed later by the LRU algorithm.

buf_LRU_flush_or_remove_pages(BUF_REMOVE_FLUSH_NO_WRITE) --> buf_LRU_remove_pages --> buf_flush_dirty_pages --> buf_pool_mutex_enter
    |__ buf_flush_or_remove_pages --> buf_flush_list_mutex_enter
        |__ buf_flush_try_yield
        |__ buf_flush_list_mutex_exit
    |__ buf_pool_mutex_exit

Later Percona merged the upstream lazy‑drop code and removed its own implementation.

3. IO Bottleneck

Even with the buffer‑pool optimisation, MySQL 5.6/5.7 still suffers from a severe performance drop when dropping large tables concurrently. Stack traces show the bottleneck moves to the file‑system I/O performed by the unlink of the .ibd file.

The DROP TABLE sequence includes acquiring dict_sys‑mutex, starting an InnoDB transaction, updating the data dictionary, executing lazy‑drop, writing an MLOG_FILE_DELETE redo log, unlinking the .ibd file, committing the transaction, and releasing dict_sys‑mutex.

Because the dict_sys‑mutex is held while the large .ibd file is unlinked, other concurrent DROP TABLE statements block on this mutex and also acquire all table‑cache locks, causing almost all other statements to stall in “Opening tables”.

mysql_execute_command --> mysql_rm_table --> tdc_remove_table --> table_cache_manager.lock_all_and_tdc
    |__ Table_cache_manager::free_table --> intern_close_table --> closefrm --> ha_innobase::close --> row_prebuilt_free --> dict_table_close --> mutex_enter(&dict_sys->mutex)
    |__ table_cache_manager.unlock_all_and_tdc

The source‑level call chain for DROP TABLE is roughly:

row_drop_table_for_mysql --> row_mysql_lock_data_dictionary
    |__ trx_start_for_ddl
    |__ clean up data dictionary
    |__ row_drop_table_from_cache
    |__ row_drop_single_table_tablespace --> fil_delete_tablespace --> buf_LRU_flush_or_remove_pages
        |__ os_file_delete --> unlink
    |__ row_mysql_unlock_data_dictionary

4. Solutions

4.1 Without Source Changes

Perform table drops during off‑peak hours.

Increase innodb_buffer_pool_instances to reduce the impact of the buffer‑pool mutex.

Before dropping, create a hard link to the .ibd file so the DROP TABLE finishes quickly; delete the file later during a low‑load window.

4.2 With Source Modifications

Release dict_sys‑mutex before the unlink operation. This removes the mutex contention but is unsafe because a crash between releasing the mutex and the actual file deletion can leave an orphan .ibd file and cause CREATE TABLE failures. Fixing this requires changing the semantics of MLOG_FILE_DELETE so that crash‑recovery deletes the file if it still exists.

Replace the single unlink with a series of truncate operations. Rename the .ibd file (e.g., add a .trash suffix), enqueue it, and let a background thread or the InnoDB master thread truncate the file in small batches. This reduces file‑system log pressure and avoids long mutex holds.

5. Summary

The article explains the two main performance bottlenecks—buffer‑pool mutex contention and large‑file I/O—encountered when dropping big InnoDB tables, and presents both operational work‑arounds and code‑level redesigns to mitigate the issue.