d34dr00t
By:
d34dr00t

Mysqd load 100% in CPU. How fix it?

December 5, 2015 2.4k views
MySQL Monitoring Server Optimization PHP DigitalOcean LEMP Nginx

Hi,

I cant understand why select in db load cpu in 100%/ this is problem.

This sql cant load db

$sql = "SELECT
device,
battery,
timestamp,
events,
(select count(id) from deviceevents as de where de.device = deviceevents.device and de.events LIKE '%kill app%') as countInstalls,
temperature
FROM deviceevents
where id in (SELECT max(id) FROM `deviceevents` group by device) ORDER BY timestamp ASC";

root@screenlog:~# top

top - 11:21:08 up 3 days, 2:44, 1 user, load average: 4.73, 4.08, 2.43
Tasks: 78 total, 2 running, 76 sleeping, 0 stopped, 0 zombie
%Cpu(s): 99.7 us, 0.3 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem: 1017872 total, 945804 used, 72068 free, 96220 buffers
KiB Swap: 102396 total, 0 used, 102396 free. 658612 cached Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8874 mysql 20 0 892308 68756 7632 S 99.7 6.8 34:39.57 mysqld
23871 root 20 0 24932 1588 1116 R 0.3 0.2 0:00.04 top
1 root 20 0 33476 2800 1440 S 0.0 0.3 0:04.11 init

2 Answers

You can easily find answers about this on the web, for example:
HERE

Depending on how complex your queries are, how many queries you're making, and how much load, MySQL will start to use more and more resources. I recommend you check out MySQL Tuner. It will give you some recommendations on what you can do to optimize MySQL.

Another common problem with MySQL CPU being high is some of your tables may have fragments in them. The tuner script will also help you with this.

Have another answer? Share your knowledge.