Take Your Sweet Time Dropping Tables in MySQL

Say that you have a large amount of data to process. So you make a table:

CREATE TABLE my_interim_table (...);

You want to access this table from multiple processes so you've chosen to create a normal table rather than a temporary table.

You import the data, process it, and then clean up with:

DROP TABLE my_interim_table;
Query OK, 0 rows affected (9.89 sec)

The simple action of dropping the table took several seconds. That's odd. 🤔

Then you notice something strange. While you were dropping the table, your app suffered a cluster of slow web requests! Those requests all took roughly the same amount of time as the table drop. 😨

It turns out that in MySQL, the simple act of dropping a table can hold up all queries. The table drop kicks off a scan to remove pages from the buffer pool. This is done immediately and uses a mutex that holds up everything else.

You can see this in action if you use SHOW ENGINE INNODB STATUS to review the BUFFER POOL AND MEMORY section. Dropping the table will decrease the database pages and increase the free buffers.

So what can be done? The only successful workaround that we've been able to find so far is to just... wait for it...

No, seriously, that's it: wait for it.

Over time, database activity will naturally push the table out of the buffer pool. This can vary by table size and database activity. For us, waiting 1 to 2 days before dropping large tables is usually long enough for any DROP TABLE statement to run in a fraction of a second.

The downside to just waiting is that if you wait too long you can accumulate a lot of tables. This can slow down the MySQL console start-up if you're using automatic rehashing for table and column name completion. Also, AWS RDS may start to warn you that you have too many tables and recovery time could be impacted.

This performance problem will be fixed in MySQL 8.0.23. Until AWS RDS supports that version, we'll just have to sit tight and wait.

UPDATE - This is NOT fixed in MySQL 8. Although their release notes indicate that performance was improved for dropping large tables, this isn't actually the case and may even be worse. There are several threads on different dev forums discussing how to try to work around this, like this GitHub issue, but they all come back to just waiting several days before dropping the table.

• Why you simply don't drop a huge InnoDB table in production... (This post from Jean Jung was very helpful in diagnosing the problem)
• Bug #98869 - Temp ibt tablespace truncation at disconnection stuck InnoDB under large BP
• MySQL 8.0.23 Release Notes
• Best practices to drop (a large) old table after cut-over