Slow InnoDB INSERT/UPDATE/DELETE

Background

A couple of weeks ago all of my INSERT's/UPDATE's/DELETE's became overall slow (not SELECT's). Before that, everything was normal (mysql statements executed in <0,01 seconds). Now a simple INSERT takes about 0,1 seconds. It's the same for all tables in the database and even for the simpliest query in a table with no data and only one index, it's not related to any advanced or complex queries.

Example:

mysql> INSERT INTO test (name) VALUES ('Andy');
Query OK, 1 row affected (0.09 sec)

The table looks like this:

mysql> SHOW CREATE TABLE test;
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                                |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test | CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=791 DEFAULT CHARSET=latin1 |
+-----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

After server reboot

The first thing I tested was to reboot the server. After that everything was good and back to normal! But after a couple of days the slow queries was back.


As mysql has been much faster before and even after a reboot, for a
couple of days, I think that there must be some sort of problem or
should I accept that a mysql query takes about 0,1 seconds to exec

Problem found. It has nothing to do with MySQL/InnoDB. The problem was in the RAID controller battery that were going throuh "relearning" cycle and then the write-Back policy is disabled and Write-Through is enabled and as a result writes become very slow. If you have a Dell server and similar symtoms, take a look at the syslog and the battery.

Jan 13 17:34:21 Server Administrator: Storage Service EventID: 2188  The controller write policy has been changed to Write Through.:  Battery 0 Controller 0

For more information: http://agiletesting.blogspot.se/2011/09/slow-database-check-raid-battery.html

Maybe this question should be renamed and moved to Server Fault.

StackExchange Database Administrators Q#56062, answer score: 2