UPDATE query periodically is slower by factor of 5000x

Question

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?

Answer 1

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

Answer 2

Since only cold reboot help, first what need to check (as already suggested) - all external processes, cron and etc.

All look like - some process take more and more memory, forgotten return it back to OS.

next what good to check - innodb_log_file_size and innodb_log_buffer_size (of course if all tables InnoDB)

SHOW GLOBAL STATUS LIKE 'innodb_log_waits';

This is could be problem of slowdown, but generally - any restart of MySQL must resolve the problem without full server restart.

To be 100% sure - check if some of big tables not in InnoDB (it possible of course) in this case MySQL will manage and InnoDB memory, which could be free, and system cache, which will be shared with other processes.

Answer 3

How much RAM in the VPS? If it is less than 10G, that is the problem. If needed, decrease innodb_buffer_pool_size to 70% of available RAM.

What version of MySQL? 5.1(?) had a problem like this -- the transaction rate would drop to 0/sec periodically. Percona's innodb plugin was the solution back then. (It has been fixed in all new versions.) If needed, upgrade.

Otherwise, let's see SHOW VARIABLES; and SHOW GLOBAL STATUS; after it has been up for at least a day.