2

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?

4
  • Since only rebooting helps, have you checked outside factors. Not sure what platform you are on but you could use task manager or top to see if there is no antivirus locking your files. Specifically since you state it are 1-2 hour intervals – Tom V Nov 3 '15 at 12:04
  • I have only been able to check a few outside factors. The server is a Ubuntu 14.04.3 LTS server running on a 32xCPU core VMWare VPS. There is no antivirus of any sort locking the files. I haven't been able to look into I/O usage during these freezes, but cpu utilization doesn't differ from when the load is normal and the unix load says around 22-28 both before these freezes and during them. – Aron Cederholm Nov 3 '15 at 14:30
  • Run this and make sure there are no scheduled jobs interfering: find /var/spool/cron/lastrun/ /var/spool/cron/crontabs/ /etc/cron.d/ /etc/crontab -type f -not -name '.*' -exec cat {} + | grep -v -E '^#|^MAILTO|cron\.(hourly|daily|weekly|monthly)|scrub_areca|puppet agent|collect_pkg_list|run-crons|monitorcf.sh|ae_scan_linux.sh' | grep --color=never -E '[0-9]+ ' – naschoff Nov 3 '15 at 15:32
  • Apart from a few nightly cron jobs I have four entries, a ubuntu php session cleaner running 09,39 * * * *, a magento cron.sh running * * * * *, and these two I don't know about: 5-55/10 * * * * root command -v debian-sa1 > /dev/null && debian-sa1 1 1 59 23 * * * root command -v debian-sa1 > /dev/null && debian-sa1 60 2 – Aron Cederholm Nov 3 '15 at 15:47
2
+25

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

0

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.

0

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.

3
  • 128G RAM. Mysql 5.5.44. – Aron Cederholm Dec 7 '15 at 8:51
  • SSDs? Or conventional disks? – Rick James Dec 7 '15 at 21:34
  • It's a VPS, so I can't tell. – Aron Cederholm Dec 9 '15 at 9:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.