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):
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking bind-address = 127.0.0.1 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 log-queries-not-using-indexes expire_logs_days = 10 max_binlog_size = 100M [mysqldump] quick quote-names max_allowed_packet = 16M [isamchk] 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.