Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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,

share|improve this question

migrated from stackoverflow.com Nov 20 '11 at 16:17

This question came from our site for professional and enthusiast programmers.

    
Why does it matter? Table creation speed isn't a production issue. For one thing it looks like the database shuts down and restarts. –  le dorfier Nov 7 '11 at 3:02
    
We need more specfics: 1) Are you using InnoDB or does this happens with MyISAM tables as well? 2) Do you have innodb_file_per_table enabled or disabled? –  RolandoMySQLDBA Nov 21 '11 at 2:41
    
I bet you are using ext3 for your filesystem. –  Aaron Brown Apr 2 '12 at 0:02
    
I certainly am, why would this make a difference? –  Pez Cuckow Apr 10 '12 at 13:25

1 Answer 1

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.