Frequently slow queries while in the 'Update' state

I have two tables that I update very frequently, one with a direct UPDATE .. and another INSERT .. ON DUPLICATE KEY UPDATE ... Normally these queries are instant but at times will take 0.1s to 1+ seconds and then be instant again for a few seconds.

Another thing to note, I have two MySQL servers (in the same private network). All select queries (barring one or two) are executed on the slave and the inserts seen here are of course on the master. The slowdowns are most apparent when the database is under a large load. Lastly, I am using xtradb (to see if it helped) but the same behaviour occurred while on just plain MySQL 5.5 InnoDB.

-- CustomData
+-----------+---------+------+-----+---------+-------+
| Field     | Type    | Null | Key | Default | Extra |
+-----------+---------+------+-----+---------+-------+
| Server    | int(11) | NO   | PRI | NULL    |       |
| Plugin    | int(11) | NO   | PRI | NULL    |       |
| ColumnID  | int(11) | NO   | PRI | NULL    |       |
| DataPoint | int(11) | NO   |     | NULL    |       |
| Updated   | int(11) | NO   | MUL | NULL    |       |
+-----------+---------+------+-----+---------+-------+

Ex. of a couple queries:

```
mysql> insert into CustomData (Server, Plugin, ColumnID, DataPoint, Updated) VALUES ( 52707, 1, 1, 0, 1327093596) on duplicate key update DataPoint = 0 , Updated = 1327093596 ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into CustomData (Server, Plugin, ColumnID, DataPoint, Updated) VALUES ( 52707, 1, 1, 0, 1327093596) on duplicate key update DataPoint = 0 , Updated = 1327093596 ;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into CustomData (Server, Plugin, ColumnID, DataPoint, Updated) VALUES ( 52707, 1, 1, 0, 1327093596) on duplicate key update DataPoint = 0 , Updated = 1327093596 ;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into CustomData (Server, Plugin, ColumnID, DataPoint, Updated) VALUES ( 52707, 1, 1, 0, 1327093596) on duplicate key update DataPoint = 0 , Updated

You need to make some adjustments in your InnoDB setup
ADJUSTMENT #1 : Bigger InnoDB Redo Logs

Since I I do not see innodb_log_file_size, I assume you have the default of 5M. Since your innodb_buffer_pool_size = is 4G, you need 1G redo logs.
ADJUSTMENT #2 : Have InnoDB uses all CPUs

Out of the box, InnoDB does not use all CPUs. I wrote a post long ago about how InnoDB LEFT UNCONFIGURED may work faster in older versions. I also wrote posts about multicore engagement for InnoDB:

• How do I properly perform a MySQL bake-off?

• Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB?

• Percona vs MySQL

• Multi cores and MySQL Performance

With these things said, here are the adjustments to make

cp /etc/my.cnf /etc/my.cnf_old

Add this setting to /etc/my.cnf

[mysqld]
innodb_log_file_size = 1G
innodb_io_capacity = 20000
innodb_read_io_threads = 5000
innodb_write_io_threads = 5000

Next, run these steps

service mysql stop
mv /var/log/mysql/ib_logfile0 /var/log/mysql/ib_logfile0_old 
mv /var/log/mysql/ib_logfile1 /var/log/mysql/ib_logfile1_old
service mysql start

Now, all cores with assist InnoDB and there is much more room for transaction isolation

Give it a Try !!!

StackExchange Database Administrators Q#17068, answer score: 4