#StackBounty: #mysql How to drop a 1000+ databases with innodb_file_per_table=1 without hanging the MySQL process?

Bounty: 100

We have a recurring process in which we want to, and need to, clean up our databases. Every client or prospect gets its own database (with 300 tables, and increasing every month), which is spun up within seconds, and seeded with some basic data.

After several months, the databases need to be cleaned up. We simply call DROP DATABASE customer_1 for each database (giving the MySQL server 10 seconds between each statement to ‘rest’), followed by DROP USER 'customer_1'@'127.0.0.1').

Every so often, the entire database just hangs. SHOW PROCESSLIST gives

Id     User       Command    Time    State         Info
[pid]  adm-user   Query      300     System lock   DROP DATABASE `customer_1`

No new queries will complete. Killing the relevant query pid will result in Command=Killing, and that’s it. Nothing happens. The MySQL daemon cannot be stopped either, because it’s still waiting on completing the query.

We’ve resulted to powering off the entire server, restarting it, and having MySQL do its automated crash recovery, which works fine. After which, we can drop another 10-30 databases, and then this event repeats itself.

We’ve read plenty on the subject, including but not limited to:

Seems like the consensus is, yes, it’s MySQL with uses a global mutex lock on the table(space), combined with a large buffer pool size.

Our my.cnf:

innodb_file_per_table   = 1
innodb_buffer_pool_size = 9G
innodb_log_file_size    = 256M
innodb_flush_method     = O_DIRECT
table_open_cache        = 200000
table_definition_cache  = 110000
innodb_flush_log_at_trx_commit = 2

Is there any way in which we can drop databases responsibly — ie., without having the server go down for other prospects?

I’ve read that simply removing all table files could work, dropping the database afterwards, in which MySQL should simply remove references to the database.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.