Mysql loads the processor at 100%, how to normalize the work?

Question

MySQL

CPU permanently 100% loaded by mysql
top
top - 13:24:21 up 4:42, 4 users, load average: 1.83, 2.17, 1.47
Tasks: 131 total, 1 running, 130 sleeping, 0 stopped, 0 zombie
%Cpu ( s
)
free, 0 used. 6315452 AVAIL MEM
PID User PR NI VIRT RES SHR S% CPU% Mem
Time + Command 989 Mysql 20 0 1102844 125068 9580 S 100.0 1.6 177: 50.28 Mysqld
1300 ELASTIC + 20 0 3654932 903128 15380 s 7.7 11.3 29: 20.12 Java
2574 root 20 0 547868 16740 9468 S 0.3 0.2 0 :
07.21 0 0 0 S 0.0 0.0 0:00.01 kthreadd MariaDB [(none)]> show processlist; +-----+---------+-----------------+---------+----- ----+------+----------------------+--------------- -------------------------------------------------- -------------------------------------+----------+ | id | user | host | db | command | time | state | Info | progress |
+-----+---------+-----------------+---------+----- ----+------+----------------------+--------------- -------------------------------------------------- -------------------------------------+----------+
| 156 | elastic | localhost:47852 | elastic | query | 1313 | Copying to tmp table | SELECT `orgs_list`.*, COUNT(orgs2.last_id) AS countcompanys, COUNT(orgs3.last_id) AS countmanages FR | 0.000 |
| 181 | root | localhost | NULL | query | 0 | NULL | show processlist | 0.000 |
+-----+---------+-----------------+---------+----- ----+------+----------------------+--------------- -------------------------------------------------- -------------------------------------+----------+
2 rows in set (0.00 sec)
in the orgs_list table 107957 records
What can be done to normalize the server?

Answer 1

Set up your database queries correctly.

• Check them out via explain
  
• Add indexes where required
  
• Move aggregates like COUNT, SUM to key-value stores like redis/memcache and read their values ​​from there.