Wednesday, May 20, 2015

DROP DATABASE command causes MySQL 5.1 to stall briefly

We have a database server running MySQL 5.1 which I cannot issue a DROP DATABASE command during the day or it will hit max connections.

We've put about 1500 smallish databases on it. A funny thing happens whenever a DROP DATABASE command is issued during the day. It will immediately run out of connections and the max connections errors will start occurring. I tested it today when the load wasn't very heavy near end of business day. I had a database to DROP and wanted to see this in action. There were about 150 threads connected and about 10 threads running. When I issued the drop database command it started removing 400+ tables in that particular database and I watched connected threads spike from 150 to 600 instantly and shortly thereafter hit max connections.



I don't understand why this happens, initially I thought it was something to do with replication being single threaded but not sure.

I read up on this blog post and suspect this may be the problem:
https://www.percona.com/blog/2009/06/16/slow-drop-table/

MySQL could be executing LOCK_open for each of those tables causing mutex locks.

No comments:

Post a Comment