High CPU usage

from the Artful MySQL Tips List


There are three main 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 issues 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.

If none of the above leads to a solution, consider installing the Percona Monitoring and Management Tool; see here for an illustrative case study.

Other 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

Query CPU time

Version 8.0.28 adds optional performance_schema tracking of CPU time spent on a query, see "Performance Schema Notes" at https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-28.html

Last updated 18 Jan 2022


Return to the Artful MySQL Tips page