Question :
Something appears to be wrong with my MySQL even with very, very simple operations on almost empty (~100) tables
E.g. DROP TABLE IF EXISTS hookup_gender takes 10+ seconds
mysql> show processlist;
+----+---------------+---------------------+---------------+---------+------+----------------------+--------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------------+---------------------+---------------+---------+------+----------------------+--------------------------------------+
| 2 | pegpro_hookup | 130.88.149.86:51593 | pegpro_hookup | Sleep | 43 | | NULL |
| 92 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 93 | pegpro_hookup | localhost | pegpro_hookup | Query | 7 | checking permissions | DROP TABLE IF EXISTS `hookup_gender` |
+----+---------------+---------------------+---------------+---------+------+----------------------+--------------------------------------+
Whereas a basic table create takes 30+ seconds
mysql> show processlist;
+----+---------------+---------------------+---------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+---------------+---------------------+---------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 2 | pegpro_hookup | 130.88.149.86:51593 | pegpro_hookup | Sleep | 24 | | NULL |
| 92 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 93 | pegpro_hookup | localhost | pegpro_hookup | Query | 25 | creating table | CREATE TABLE `hookup_message`
(
`id` INTEGER NOT NULL AUTO_INCREMENT,
`related_post_id` INTEGER NO |
+----+---------------+---------------------+---------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
What might be causing this slowdown?
What further information can I provide?
Thanks for your time,
Info:
top - 02:44:33 up 1:15, 2 users, load average: 1.59, 1.57, 1.05
Tasks: 86 total, 1 running, 85 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni,100.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 2097152k total, 398904k used, 1698248k free, 0k buffers
Swap: 0k total, 0k used, 0k free, 0k cached
Error log
root@tent:~$ tail -f /var/lib/mysql/mysql-bin.err
111107 1:29:00 [Note] Event Scheduler: Purging the queue. 0 events
111107 1:29:01 InnoDB: Starting shutdown...
111107 01:29:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
111107 1:29:41 [Note] Plugin 'FEDERATED' is disabled.
111107 1:29:41 InnoDB: Initializing buffer pool, size = 8.0M
111107 1:29:41 InnoDB: Completed initialization of buffer pool
111107 1:29:55 InnoDB: Started; log sequence number 0 2618946
111107 1:30:06 [Note] Event Scheduler: Loaded 0 events
111107 1:30:06 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.59-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
iostat:
root@tent:~$ iostat
Linux 2.6.18-274.3.1.el5.028stab094.3 07/11/11
avg-cpu: %user %nice %system %iowait %steal %idle
0.15 0.05 0.08 2.45 0.00 97.28
Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn
Thanks for your time,
Answer :
It appears you are using innodb. During a table drop, the innodb engine locks the entire buffer pool and wipes out any references to the tables, and then drops the tables. This takes a fair bit of time when you have a large buffer pool.
One option is to switch to using the Percona builds of mysql. They have a specific feature to help this out
http://www.percona.com/docs/wiki/percona-server:features:misc_system_variables
When innodb_file_per_table is set to 1, doing a DROP TABLE can take a
long time on servers with a large buffer pool, even on an empty InnoDB
table. This is because InnoDB has to scan through the buffer pool to
purge pages that belong to the corresponding tablespace. Furthermore,
no other queries can start while that scan is in progress.When innodb_lazy_drop_table is ON, XtraDB optimizes that process by only marking the pages corresponding to the tablespace being
deleted. It defers the actual work of evicting those pages until it
needs to find some free pages in the buffer pool.When innodb_lazy_drop_table is OFF, the usual behavior for dropping tables is in effect.