We are running MySQL 5.6 on Windows Server 2008r2.
Every 30 minutes it runs very slowly for around 40 seconds and then goes back to normal for another 30 minutes. It is happening like clockwork with each ‘hang’ being 30 minutes after the last one finished.
Any ideas? We are stumped and don’t know where next to look.
Background / things we have ruled out below.
Thanks.
• Our initial thoughts were a locking query but we have eliminated this.
• The slow query log shows affected queries but with zero lock time.
• General logs show nothing (as an aside, is there a way to increase the logging level to get it to log when it is flushing the cache etc? What does MySQL run every 30 minutes?)
• When it is running slowly, it is still running but even simple queries like Select ‘Hello World’; take over a second to run.
• All MySQL operations run slowly at the time in question including monitoring tools and especially making new connections. InnoDB and MyISAM are equally affected.
• We have switched from using the SAN array to using local SSD and it has made no difference ruling out disk / spindles.
• The machine has Sophos Endpoint Protection but this is not scanning anything on the database drives.
• It is as if the machine is maxed out but local performance monitoring does show any unusual system metrics. CPU, disk queue, disk throughput, memory, network activity etc. are all flat.
• The machine is a VM running on VMware. Hypervisor monitoring is not showing any performance issues – but I am not convinced it is granular enough to pick up a 30 second spike.
• We have tried adjusting MySQL settings like the InnoDB cache size, log size etc and this has made no difference.
• The server runs nothing other than a couple of MySQL instances.
• The other instances are unaffected - as far as we can tell.