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:
for i in `seq 1 1000`
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
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|
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.)
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below.