mysql high cpu usage due to IO disk
enabling caching in memory decreasing cpu usage by allocating memory.
debugging mysql cpu usage
ome other things you can check would be to run the following query while the CPU load is high:
SHOW PROCESSLIST;
This will show you any queries that are currently running or in the queue to run, what the query is and what it’s doing (this command will truncate the query if it’s too long, you can use SHOW FULL PROCESSLIST to see the full query text).
adding index to tables also reduces the cpu load
heavy weight code like cron.php event schedules also triggers the high cpu load. timeout is the best way stopping high cpu time.
enable mysql slow query logs
if more than 1 seconds or prescribed milli seconds it log to the mysql slow quey logs, you can analyze later. my case all WordPress dashboard activies like updating plugins post update triggering more thena 1 seconds slow query log.
dont; stop or restart mysql while in high cpu usage
database maybe corrupted, i faced the same issue, later realized stop its client like php or php-fpm
after stopping php-fpm in nginx mysql cpu usage goes to zero.
how to limit mysql cpu usgae
for managed sql services there is a rule 100MB for 1 connection.
for 151 default max connections it will be 15100MB or 15GB ram or 16gb ram 8 cores cpu. as rough estimation.
so we can limit max connections,
we can limit memory allocation so it automatically limits the cpu usage.
and some tweaks ion mysql.conf file.
note: this page will be updated on regular baisi just remember me as “raazkumar.com” or raazkumar.com mysql”