# checking permissions during DROP DATABASE

**URL:** <https://forums.percona.com/t/checking-permissions-during-drop-database/1865>\
**Category:** Other MySQL® Questions\
**Created:** [August 10, 2012, 1:09pm UTC](https://forums.percona.com/t/checking-permissions-during-drop-database/1865 "2012-08-10T13:09:48Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![m0topilot](https://avatars.discourse-cdn.com/v4/letter/m/e8c25b/32.png) [@m0topilot](https://forums.percona.com/u/m0topilot)\
**Post date:** [August 10, 2012, 1:09pm UTC](https://forums.percona.com/t/checking-permissions-during-drop-database/1865/1 "2012-08-10T13:09:48Z")

</div>

Ok this is what I do know. Performing a drop on a Database causes the query to be in “Checking Permissions” state for a long time. This appears to be CPU Bound as nothing really is happening with disk I/O. The thread itself is using 100% of one of the CPU cores. It seems to be related to the INORMATION\_SCHEMA and so any other query from other user on the DBMS hangs.

We are dropping large databases around 16GB to 65GB with tens of thousands of tables anywhere from 10K to 34K tables. Does it need to check permissions for every table? Any way to by pass that if we are logged in with super user privileges?

There is a MySQL bug thread that is, I think loosely coupled to to my problem, that suggests upgrading to 5.1.64. The current stable GA release for Percona is 5.1.63 (one short). We are currently using 5.1.56 [URL][MySQL Bugs: #60961: show tables very slow when not in system disk cache](http://bugs.mysql.com/bug.php?id=60961%5B/URL%5D)

Is there anything in the my.cnf file below that stands out? We are using innodb\_file\_per\_table and innodb\_lazy\_drop\_table enabled.

Percona Version: 5.1.56  
OS:CentOS release 5.5 (Final) 64-bit  
CPU: Intel(R) Xeon(R) CPU E5620 @ 2.40GHz  
cache size: 12288 KB  
Cores: 16  
Memory: 72GB  
Disk:RAID 10, 300GB SAS 10K  
Server: Dell R710

[mysqld]  
server\_id = 230  
datadir = /var/lib/mysql  
log\_bin = mysql-bin  
#relay\_log = relay-bin  
expire\_logs\_days = 1  
max\_allowed\_packet = 32M  
collation\_server = utf8\_general\_ci  
character\_set\_server = utf8  
init\_connect =‘SET NAMES utf8’  
old\_passwords = 0  
character\_set\_server = utf8  
collation\_server = utf8\_general\_ci  
open\_files\_limit = 65535  
max\_connections = 200

tmp\_table\_size = 32M  
sort\_buffer = 32M  
key\_buffer\_size = 3GB  
join\_buffer\_size = 4M  
read\_buffer\_size = 128K  
thread\_cache\_size = 50  
max\_allowed\_packet = 32M  
query\_cache\_type = 0  
query\_cache\_size = 32M  
default\_storage\_engine = ‘innodb’

innodb\_file\_per\_table = 1  
innodb\_lazy\_drop\_table = 1  
innodb\_buffer\_pool\_size = 50G  
innodb\_log\_file\_size = 256M  
innodb\_log\_buffer\_size = 32M  
innodb\_log\_files\_in\_group = 2  
innodb\_flush\_log\_at\_trx\_commit = 2  
innodb\_flush\_method = O\_DIRECT  
innodb\_open\_files = 4096  
table\_definition\_cache = 65536  
table\_open\_cache = 10240

slow-query-log  
log-slow-verbosity=full

character-set-server=utf8  
default-collation=utf8\_general\_ci  
default-character-set=utf8  
init-connect=‘SET NAMES utf8’  
collation-server=utf8\_general\_ci  
[mysql]  
no\_auto\_rehash  
[client]  
default-character-set=utf8

---

<div class="post-metadata">

**Author:** ![revin](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/revin/32/824_2.png) [@revin](https://forums.percona.com/u/revin)\
**Post date:** [August 11, 2012, 8:45pm UTC](https://forums.percona.com/t/checking-permissions-during-drop-database/1865/2 "2012-08-11T20:45:36Z")

</div>

There are a number of things involved when dropping a database, and with your number of tables involved more work needs to be done - i.e. how big your table cache is, innodb data dictionary operations etc. Have you tried dropping per table so it is less intrusive?

---

<div class="post-metadata">

**Author:** ![m0topilot](https://avatars.discourse-cdn.com/v4/letter/m/e8c25b/32.png) [@m0topilot](https://forums.percona.com/u/m0topilot)\
**Post date:** [August 13, 2012, 11:13am UTC](https://forums.percona.com/t/checking-permissions-during-drop-database/1865/3 "2012-08-13T11:13:38Z")

</div>

Hi Thanks for the response, I believe this is what your asking for:

table\_definition\_cache = 65536  
table\_open\_cache = 10240  
innodb\_dict\_size\_limit=0

I have to add that I did have innodb\_flush\_method = O\_DIRECT commented out. It’s also commented out in our production environment, but we are not dropping databases in production.

What I don’t get is that if we are using a multi threaded databases, why is the system preventing other threads from accessing INFORMATION\_SCHEMA or other tables?

Thanks,  
Matt