High CPU usage

from the Artful MySQL Tips List


Mainly there are three ways MySQL CPU usage becomes problematic ...

1 You have a table in memory that needs an index for what you are asking of it, so you're doing full table scans in memory. Fix that with a covering index.

2 You're GROUPing or ORDERing in memory, and the sort is small enough to avoid spillover to disk. Avoid that by doing it in the client or by widening the scope of the covering index.

3 Code in stored procedures, stored functions, stacked views. You'll need to re-jigger that approach.

More general things to check ...

CPU usage spikes, or is continuously high?

What does top say? An i/o bottleneck will show higher cpu%; high mem usage will leave free mem low.

Run perf.

If show processlist shows many locked queries, consider increasing max_connections but watch out for mem load.

Check thread configuration starting with ...

show variables like '%threads%';

If global status var ratios ...

Select_range_check / Uptime_since_flush_status 
Select_scan / Uptime_since_flush_status
... > 0, optimise those queries.

Suss out abusive processes and remove them.

Useful links ...

https://stackoverflow.com/questions/1282232/mysql-high-cpu-usage helpful

https://dba.stackexchange.com/questions/131477/innodb-intermittent-cpu-spikes-on-a-large-database

https://dba.stackexchange.com/questions/46752/mysql-5-5-high-cpu-usage

Last updated 16 Mar 2021


Return to the Artful MySQL Tips page