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
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.
Click below to sign up and get $100 of credit to try our products over 60 days!
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