Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

enter image description here

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?

share|improve this question
Have you enabled/checked the slow log? What kind of queries are the cause of this? Many simple statements/queries or a few complex ones? – ypercube Sep 17 '12 at 7:50
it's a combination - i've checked the slow query log and there are a huge amount of queries in there the moment we enter this cycle. some queries 10 seconds, some 1.5 minutes. Those queries take next to no time to execute now though? – Luc Sep 17 '12 at 7:53
Only SELECTS or UPDATE/DELETE/INSERT statements as well? – ypercube Sep 17 '12 at 7:53
both. The UPDATES are just session table stuff, the SELECTS are all sorts of selects from across the whole website. News, ForumDiscussions, Event information, Statistics - a combination of all of those. It doesn't appear that there are row locking issues because the SHOW PROCESS LIST just reports those queries are executing. – Luc Sep 17 '12 at 7:56
Are the primary keys auto_increment in the tables? (or you have GUIDs as primary keys)? – ypercube Sep 17 '12 at 8:03
show 4 more comments

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.