There have been recent discussions about DROP TABLE performance in InnoDB. (You can refer to Peter’s post https://www.percona.com/blog/2011/02/03/performance-problem-with-innodb-and-drop-table/ and these bug reports: http://bugs.mysql.com/bug.php?id=51325 and http://bugs.mysql.com/bug.php?id=56332.) It may not sound that serious, but if your workload often uses DROP TABLE and you have a big buffer pool, it may be a significant issue. This can get especially painful, as during this operation InnoDB holds the LOCK_open mutex, which prevents other queries from executing. So, this is a problem for a server with a large amount of memory, like the one we have in our lab: a Cisco UCS C250 with 340GB of RAM.
To fix this problem, we implemented “background table drop”, which is available in Percona Server 5.1.56 and Percona Server 5.5.10. Also, looking at the MySQL 5.6.2 announcement, the InnoDB team has implemented “cleaning thread”, which is also supposed to fix this problem.
I ran a small benchmark to see what improvement we have. Hardware was the Cisco UCS C250, the InnoDB buffer_pool size was 144GB, and I filled the buffer pool with different amounts of data: 104GB, 52GB, 21GB. I ran a simple cycle. First I created 1000 tables:
|
1 2 3 4 |
for i in `seq 1 1000` do mysql -Bse "CREATE TABLE ts_$i (begin_time int(11) NOT NULL, end_time int(11) NOT NULL, PRIMARY KEY (begin_time)) ENGINE=InnoDB" test2 done |
And then I dropped it. I measured the time to drop 1000 times in MySQL 5.1.56, in Percona Server 5.1.56 with “innodb_lazy_drop_table”, and in MySQL 5.6.2.
Here are my results (results in seconds; fewer is better):
| Allocated pages, GB | MySQL 5.1.56 | Percona Server 5.1.56 | MySQL 5.6.2 |
| 21GB | 310 | 95 | 234 |
| 52GB | 776 | 210 | 548 |
| 104GB | 1635 | 466 | 1210 |
Also, to show the dynamics, here is a graph showing the number of dropped tables per second (more is better):
As we see, all three versions are affected by a bigger buffer pool: The more memory used, the slower DROP TABLE operates. MySQL 5.6.2 shows some improvement compared to MySQL 5.1.56, but Percona Server 5.1.56 has a better result.
The relative comparison for the 104GB case: Pecona Server is 3.5x better than MySQL 5.1.56 and 2.6x better than MySQL 5.6.2.
(Disclaimer: The “innodb_lazy_drop_table” feature in Percona Server and this benchmark are sponsored by a Well Known Social Network.)
