Sign up ×
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.

I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you can see in the following snippet.

mysql> CREATE TABLE `test` (`id` int(11) not null auto_increment,
   -> PRIMARY KEY(`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (4.61 sec)

mysql> insert into test values ();
Query OK, 1 row affected (1.92 sec)

mysql> insert into test values ();
Query OK, 1 row affected (0.88 sec)

mysql> insert into test values ();
Query OK, 1 row affected (1.10 sec)

mysql> insert into test values ();
Query OK, 1 row affected (6.27 sec)

mysql> select * from test;
| id |
|  1 |
|  2 |
|  3 |
|  4 |
4 rows in set (0.00 sec)

mysql> delete from test where id = 2;
Query OK, 1 row affected (0.28 sec)

mysql> delete from test where id = 3;
Query OK, 1 row affected (6.37 sec)

I have been looking at htop and the long waiting times are not because of abnormal CPU load. It's almost zero, and memory usage is also normal. If I create the same table using the MyISAM engine, then it works normally. My my.cnf file contains this (if I remember right I haven't changed anything from the default Debian configuration):

port        = 3306
socket      = /var/run/mysqld/mysqld.sock
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

user        = mysql
pid-file    = /var/run/mysqld/
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
language    = /usr/share/mysql/english
bind-address        =
key_buffer      = 40M
max_allowed_packet  = 16M
thread_stack        = 128K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 100
table_cache            = 64
thread_concurrency     = 10
query_cache_limit   = 1M
query_cache_size        = 40M
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 2
expire_logs_days    = 10
max_binlog_size         = 100M

max_allowed_packet  = 16M

key_buffer      = 16M
!includedir /etc/mysql/conf.d/

I have also tried to restart the server, but it doesn't solve anything.

The slow queries log doesn't give any extra information.

share|improve this question

migrated from Nov 2 '12 at 22:09

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

usually the linux default mysql setups are not configured for optimum innodb usage. google 'setting mysql linux to use innodb'. – Codeguy007 Nov 2 '12 at 13:48
Is this a Virtual Machine local to your workstation? – Mannoj Mar 7 '13 at 11:47
It's a VM, but not local. It's a paid hosting. – Ximo Puig Bruguera Apr 10 '13 at 6:58
can you provide some iostat information during the insert, the result for SHOW VARIABLES LIKE '%version%' and SHOW VARIABLES LIKE '%innodb%'; – altmannmarcelo May 10 '13 at 8:39

4 Answers 4

Still, the times (1s-6s) are grossly unreasonable. Even with everything set "wrong", 0.1s for a simple INSERT should be expected.

Is this running in a "cloud"? Is something else going on?

share|improve this answer

Kindly ensure you have innodb_buffer_pool_size to 80% of your VM's memory. And make other changes for InnoDB that best suite for your application. Below are the few recommendations. First try buffer_pool and let me know how inserts are going for you.

share|improve this answer
Actually innodb_file_per_table can slow it down if you have many tables. This is because mysql has to call sync() and wait for the kernel to return for every data file instead of only having to call it once. – Sarel Botha Jul 9 '13 at 13:48

It sounds like the disk is running really slow. Try using dd to see how fast you can write data to the disk.

share|improve this answer

If your database is already slow, running batch inserts may decrease your performance further. Especially in InnoDB where each write operation is recorded in transaction logs.

Suggest that you first tune your system and/or upgrade hardware before taking the benefit of batch insertions in InnoDB.

share|improve this answer
On the contrary, Batch INSERTs benefit in two ways: (a) less overhead in network/parsing/etc, and (b) fewer COMMITs. – Rick James Feb 7 at 1:09
@RickJames that's true in general. To debug slow database instances, the approach suggested (tuning database settings and/or hardware upgrade) helps. – v1nay Feb 9 at 7:57

Your Answer


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.