MySQL TRUNCATE TABLE blocks queries in other databases

Question

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.

Answer 1

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.

Answer 2

While the query cache may well be adding a bit extra pain to this case, it's most likely not the primary cause for the slowdown.

There is a bug in all current MySQL versions including MySQL 5.7 that can cause instance wide freezes when "truncate table" is called on an InnoDB table.

Bug details here: https://bugs.mysql.com/bug.php?id=68184

It's apparently fixed in the (as per 6 Oct. 2016) unreleased Mysql 5.8.

Here's the changelog for the fix:

On a system with a large InnoDB buffer pool and innodb_adaptive_hash_index enabled, TRUNCATE TABLE operations could cause a temporary drop in system performance due to an LRU scan that occurred when removing an InnoDB table's adaptive hash index entries. To address this problem, TRUNCATE TABLE now invokes the same code as DROP TABLE and CREATE TABLE. The problem was addressed for DROP TABLE in MySQL 5.5.23.