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.
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.