checking permissions during DROP DATABASE

MySQL & MariaDB Other MySQL® Questions
1

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

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

2

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?

3

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