We run Mysql 5.6.17.

Dropping a large InnoDB table 10GB caused the whole server to lock for about 40 seconds. We have 55GB innodb buffer pool size. I read bunch of old bugs that could be the source of this problem, but as far as I can see they should be fixed: (http://bugs.mysql.com/bug.php?id=64284). Also the same lock happened when we tried alter table with LOCK=NONE. The alter was going well and at the end when it was switching the table the whole server locked for 10 minutes This was very large table. Also it was reproducable on our slave the same alter also locked the whole database for few minutes even though we have no reads on the slave. We use one file per table. Any ideas?

migrated from serverfault.com May 30 '14 at 13:42

This question came from our site for system and network administrators.

  • this problem happens on ext3, so if it's your case make sure you migrate to ext4 or xfs. – akuzminsky Jun 3 '14 at 21:21

RDBMS in general aren't best at dropping tables at runtime, tends to be done more at deployment time. That aside, there are times when whatever process (thinking ETL) in facts need to create/drop tables. How about, try to TRUNCATE the table first, and then DROP it. See if that reproduces the locking behavior you are seeing.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy