MySQL TRUNCATE TABLE blocks queries in other databases

innodbMySQLtruncate

When I run automated tests against my test_development database, I often observe degraded performance for my production users, even though those users aren't accessing the same database as the test suite. In viewing the process list, it seems that the degradation corresponds to frequent TRUNCATE TABLE statements which run on the test db (to reset the db state for the next test). I see a system lock for the TRUNCATE TABLE statement, and I see "Waiting for query cache lock" for the production users. Here is part of my process list: 

mysql> show full processlist\G
*************************** 8. row ***************************
 Id: 67503075
User: delivery
Host: xxxxxxxxxxx:34734
db: test_development
Command: Query
Time: 0
State: System lock
Info: TRUNCATE `change_version`
*************************** 12. row ***************************
Id: 67503141
User: delivery
Host: xxxxxxxxxxx:37959
db: delivery
Command: Query
Time: 0
State: Waiting for query cache lock
Info: SELECT COUNT(*) FROM `address_single_4` WHERE `address_single_4`.`reportId` = 75483054
12 rows in set (0.04 sec)

Why is the TRUNCATE TABLE statement (apparently) blocking queries that are not in the same database, and how can I fix it?

I am on MySQL 5.6.21, running in Amazon RDS. All tables are InnoDB.

Best Answer

TRUNCATE would have to invalidate all dependent queries in the query cache, so that's probably why it's taking a lock on it. If your query cache is sufficiently large, invalidation might take some time. Since MySQL "databases" are more like schemas, a single query cache is maintained for all databases in the MySQL instance, so there really is no isolation between them.

You may want to consider disabling query cache or, better yet, using different MySQL instances for testing and production.

Related Question