Mysql – UPDATE query periodically is slower by factor of 5000x

innodbMySQLupdate

I run a site (Magento) which have a problem with a specific UPDATE query.

Under heavy load, this query runs ~200 times/minute, and takes in average 1 ms (stdev 0.2 ms). However, at intervals of 1-2 hours, the query starts to take a lot of time to complete, 5-35 seconds. Restarting the database server (mysql) does not stop the issue, nor does restarting php-fpm. The only thing that restores the normal state is rebooting the server.

The query looks rather innocent to me:

UPDATE {table} SET {54 columns are set} WHERE {primary key = value}

The table is ~5.000-20.000 rows big. I have tried to check memory usage of mysql, and there is plenty of available ram and htop gives Mysql memory usage as 7.5G and mysqltuner says InnoDB data size is 5.9 G with a buffer pool of 10G. Mysqltuner also says open file limit used is 0% (8 of 5k).

Do you have any ideas what I can investigate to find the issue?

Best Answer

Since your server is under heavy load try to increase its mysql thread concurrency.

Try to set:

innodb_thread_concurrency at 0 (infinite)

innodb_read_io_threads at 64 (Maximum)

innodb_write_io_threads at 64 (Maximum)

Sources:

https://dba.stackexchange.com/a/2948/33029

http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_thread_concurrency