Mysqd load 100% in CPU. How fix it?


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

This sql cant load db

$sql = "SELECT
(select count(id) from deviceevents as de where de.device = deviceevents.device and LIKE '%kill app%') as countInstalls,
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


Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

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.

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