Innodb Slow queries since convert from MyISAM

a few days ago we converted some write intensive tables from MyISAM to InnoDB hoping to have a better performance due the better locking system of InnoDB, but instead of gain performance, we start to see simple queries on slow log.

for exemple, the bellow query took 3.6 sec to run:

# Time: 130402  7:24:07
# User@Host: iron[iron] @ localhost []
# Query_time: 3.596235  Lock_time: 0.000033 Rows_sent: 0  Rows_examined: 1
SET timestamp=1364883847;
UPDATE `cookies` SET `lastSelectedLanguage`="english" WHERE  `cookieID`="27276286";

see bellow the table structure:

mysql> describe cookies;
+----------------------+------------------+------+-----+---------+----------------+
| Field                | Type             | Null | Key | Default | Extra          |
+----------------------+------------------+------+-----+---------+----------------+
| cookieID             | bigint(20)       | NO   | PRI | NULL    | auto_increment |
| containerID          | int(10) unsigned | NO   | MUL | NULL    |                |
| dtCreated            | datetime         | NO   |     | NULL    |                |
| lastSelectedLanguage | varchar(31)      | YES  |     | NULL    |                |
+----------------------+------------------+------+-----+---------+----------------+

to run a select(not using cache) with the same WHERE clause it run in 0 sec

mysql> SELECT SQL_NO_CACHE * FROM `cookies` WHERE `cookieID`="27276286";
+----------+-------------+---------------------+----------------------+
| cookieID | containerID | dtCreated           | lastSelectedLanguage |
+----------+-------------+---------------------+----------------------+
| 27276286 |           6 | 2013-04-02 06:23:52 | english              |
+----------+-------------+---------------------+----------------------+
1 row in set (0.00 sec)

The server is a 16 core cpu's:

```
...
processor : 15
vendor_id : GenuineIntel
cpu family : 6
model : 44
model name : Intel(R) Xeon(R) CPU E5620 @ 2.40G

I suspect your slow UPDATES occur due to your high innodb_max_dirty_pages_pct. This is a very good article on how InnoDB handles checkpoints and dirty page flushing, but the gist of my recommendation is to lower innodb_max_dirty_pages_pct to 60 or 70 and see if that helps.

Unfortunately, I suspect you are running native InnoDB in 5.1 and not the InnoDB plugin. This will limit your ability to tune your checkpoints.

StackExchange Database Administrators Q#39044, answer score: 3