Mysql 5.6.17 drop large table locks the server for minutes

MySQL

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?

Best Answer

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.

Related Solutions

Mysql – Adding a new slave slowing down previous replicas

Your new slave server can possibly be slow in some respect and not able to process the queries in the binary log as quickly as the other servers.

You should also look at the possibility that you may have run a very heavy query that caused the new slave to lag as well as the other slaves.

Regarding the new slave, you may have a performance problem that needs to be resolved. Possibilities could include:

Slow hard disks (you mentioned you are buying new ones)
Bad my.cnf configuration. Flushes too often to disk? flush method isn't O_DIRECT?
Long running queries from master server - consider using row-based replication if you feel it helps your overall system performance.
Caches on the new slave aren't warm for running the queries from the master - do you use INSERT INTO ... SELECT .. FROM statements? or non-deterministic insert statements like ones with sub-queries?
Raid card with battery - do the other servers have it and the slave doesn't?

MySQL hang in load data infile command

Please look carfully at session 1.

Lock table v3_zone_date WRITE; 
SET AUTOCOMMIT=0;  
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date  
FIELDS TERMINATED BY  ',';  
COMMIT; 
SET AUTOCOMMIT=1;

should be

Lock table v3_zone_date WRITE; 
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date  
FIELDS TERMINATED BY  ',';  
UNLOCK TABLES;

No need to have these lines

SET AUTOCOMMIT=0;
COMMIT; 
SET AUTOCOMMIT=1;

because MySQL 5.0 Certification Study Guide, Chapter 29, Page 418 Bulletpoint #1 says

UNLOCK TABLES implicitly commits only if you have explicitly locked tables with LOCK TABLES. SET AUTOCOMMIT = 1 implicitly commits only if autocommit mode wasn't already enabled.

LOCK TABLES will hold up any transaction-based row locks that are trying to be acquired.

Since the tables are locked independently, you must just need to increase the size of your InnoDB Buffer Pool (innodb_buffer_pool_size) to make more room for row locks across all InnoDB tables.

Best Answer

Related Solutions

Mysql – Adding a new slave slowing down previous replicas

MySQL hang in load data infile command

Related Question