1

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?

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

1 Answer 1

1

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 and acknowledge you have read our privacy policy.