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...
JOIN...ON is a better way to write JOINs.)
Oops, I need the
PRIMARY KEY of
reports; I'll assume it is
SELECT r2.* , d.name AS dom_name, p.pub_name
( SELECT rep_id
WHERE rep_type = 'N'
AND pub_id IN ( 3 )
AND rep_status = 'Y'
ORDER BY rep_pub_date DESC
LIMIT 1200 , 100
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;
* 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
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?