We've been struggling with this issue for months.
This week we upped our RDS instance to the highest performing instance and although the occurrences have reduced, we're still seeing our database suddenly hit 100% CPU Utilization. This seems to come out of nowhere, sometimes at 2am, sometimes at midday.
I've ruled out a DOS - our site access logs show normal traffic.
I've ruled out
memcached sudden death as hits and misses continue at a normal rate.
SHOW PROCESSLIST while we have issues reports about 500 queries in the queue. If I kill them off or restart the server, they keep coming back. Eventually out of nowhere our server goes back to normal CPU utilization. Sometimes this takes up to 3 hours.
Our bad performing queries typically take 0.02 seconds to execute when the server is normal but while we're in this 100% CPU state, those queries never finish executing.
Does anybody know anything about MySQL query optimization? Could it be the server is deciding to use different indexes all of a sudden, which puts it into a spiral?