Mysql – theSQL running on 100% CPU both on RDS and EC2

amazon ec2amazon-rdsMySQLmysql-5.5

How do we troubleshoot an issue with Amazon wherein my database queries (PHP/MySQL) are eating 100% CPU most of the day?

Traffic=200 connections average

mySQL version = 5.5.40

The following specific query seems to be the culprit with each query being sorted. (Most of the time, the status is Sorting Result).

SELECT reports . * , domains.name AS dom_name, publisher.pub_name
FROM reports, domains, publisher
WHERE reports.domain_id = domains.dom_id
AND publisher.pub_id = reports.pub_id
AND reports.rep_type =  'N'
AND reports.pub_id
IN ( 3 ) 
AND reports.rep_status =  'Y'
ORDER BY reports.rep_pub_date DESC 
LIMIT 1200 , 100

The database contains roughly 100 tables, and the biggest table is reports with roughly 3 GB size and 2,45,00 rows.

Based on the developers demands, we downgraded from being on MySQL 5.6 on RDS to mySQL 5.5 on EC2, situation remains the same. (4 CPU/15 GB RAM)

How do we troubleshoot the issue? Is it related to queries or is it inherently slow on EC2 and thereby we need to upgrade the hardware?

Best Answer

Your question is about troubleshooting, not about 'fixing' the CPU issue.

Nearly all CPU issues can be traced to bad/missing indexes or poorly written queries.

Do you have the slowlog turned on? With long_query_time = 2? Can you run pt-query-digest? Well, probably you said no to all of those. Pursue them for future use.

Can you do SHOW FULL PROCESSLIST? Probably. Look at the queries that don't say "Sleep" and don't look like they have something to do with replication. They will probably be the naughty queries.

Show us the naughtiest query, together with SHOW CREATE TABLE for the table(s) that it uses. Also provide EXPLAIN SELECT ... so we can see how the query is being performed.

If you are lucky, we will say "add INDEX(...)" or "change the WHERE clause in this way...". If you are less lucky, the solution could be more complex.

(None of the specs you mentioned -- ram, cores, #tables, table size, etc., are important to the question. The version might be important because the solution might involve a 'new' feature.)

Since you have provided the SELECT, I can make some guesses and proceed without the rest of the data.

Keeping junk in the table and filtering them out hurts performance (='N', ='Y'), but I will live with that for the moment.

INDEX(rep_type, rep_status, pub_id, rep_pub_date) might make things run a lot faster. But... The speedup will only work (I think) when the IN clause has only one item.

OFFSET 1200 means that 1200 rows have to be fetched, then tossed. Let's work on decreasing that overhead...

(Note that JOIN...ON is a better way to write JOINs.)

Oops, I need the PRIMARY KEY of reports; I'll assume it is rep_id.

SELECT  r2.* , d.name AS dom_name, p.pub_name
    FROM  
      ( SELECT  rep_id
            FROM  reports
            WHERE  rep_type = 'N'
              AND  pub_id IN ( 3 )
              AND  rep_status = 'Y'
            ORDER BY  rep_pub_date DESC
            LIMIT  1200 , 100 
      ) r1
    JOIN  reports AS r2  USING(rep_id)
    JOIN  domains AS d   ON d.dom_id = r2.domain_id
    JOIN  publisher AS p ON p.pub_id = r2.pub_id;

Some notes:
* The subquery looks only at reports, and does not have to haul around 1200 rows to be thrown away.
* Once the subquery is finished, the JOINs are doing only 100 rows each.
* This format will benefit from the above index.
* When the IN has multiple items, the index will work even better for the subquery than the original query. But still not 'perfectly'.
* My index works well only because it handles all of the WHERE clause, plus all of the ORDER BY.

To get even better performance for "pagination" than you can get through OFFSET, see my blog: mysql.rjweb.org. That blog also points out two bugs you possibly don't know you currently have.

Another issue... Do users really go as far as the 13th page? Or is this a search engine crawler causing you grief?

Related Question