Slow database drop in mysql

Question

Just installed a new 24 core server and it is noticeably slower when doing truncates and drops. Example:

$ mysql -u root lmcdental_test2
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 58
Server version: 5.6.23-72.1 Percona Server (GPL), Release 72.1, Revision 0503478

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop database lmcdental_test6;
Query OK, 66 rows affected (6.65 sec)

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000022 |
| checking permissions | 0.000548 |
| Opening tables       | 0.000017 |
| System lock          | 6.651106 |
| Opening tables       | 0.000061 |
| System lock          | 0.000041 |
| Opening tables       | 0.000571 |
| System lock          | 0.000668 |
| query end            | 0.000009 |
| closing tables       | 0.000044 |
| freeing items        | 0.000025 |
| cleaning up          | 0.000043 |
+----------------------+----------+
12 rows in set, 1 warning (0.00 sec)

Please notice the "System lock". This is running Ubuntu 14.04LTS.

The same database on a 16 core system is almost instantaneous. Any ideas on how to mitigate the 6.65 second system lock?

Thank you.

Answer 1

I have addressed this once before : MySQL database drop insanely slow

My suggestion in that post was this

SET unique_checks = 0;
SET foreign_key_checks = 0;
SET GLOBAL innodb_stats_on_metadata = 0;
DROP DATABASE db_madeintouch;
SET GLOBAL innodb_stats_on_metadata = 1;
SET foreign_key_checks = 1;
SET unique_checks = 1;

If all your data uses InnoDB, this might work for you.

Four years ago, I thought having tons of users was also a symptom.

If a patch was missed, here is another likely suspect: Bug #61188 DROP TABLE extremely slow

[15 Aug 2011 22:14] James Day

The fix for this is in MySQL 5.5.15 and is expected to be in 5.1.59. At the time of writing it hasn't yet been added to the release notes but that should happen soon.

[7 Oct 2011 0:46] John Russell Added to changelog:

The DROP TABLE command for an InnoDB table could be very slow, in a configuration with a combination of table compression, partitioning, and a large buffer pool.

Please give it a try and see tell us if it worked.