Mysql грузит процессор на 100%, как нормализовать работу?

Question

[MosVasil]

Процессор перманентно загружен на 100% процессом 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): 53.6 us, 0.3 sy, 0.0 ni, 45.7 id, 0.2 wa, 0.0 hi, 0.2 si, 0.0 st
KiB Mem : 8010676 total, 4730600 free, 1348236 used, 1931840 buff/cache
KiB Swap: 8191996 total, 8191996 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 core
9913 root 20 0 0 0 0 S 0.3 0.0 0:00.11 kworker/0:3
1 root 20 0 43232 3672 2440 S 0.0 0.0 0:04.38 systemd
2 root 20 0 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)

в таблице orgs_list 107957 записей
Что можно сделать чтобы нормализовать работу сервера?

Answer 1

[Fortop]

Настроить корректно ваши запросы к БД.

• Проверить их через explain
  
• Добавить индексы где требуется
  
• Вынести агрегаты типа COUNT, SUM в key-value хранилища типа redis/memcache и читать их значения оттуда.
  

Comments

[MosVasil]

EXPLAIN SELECT * FROM `orgs_list` ORDER BY `last_id` ASC
Выдал такой результат, подскажите как трактовать?

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orgs_list ALL NULL NULL NULL NULL 107957 Using filesort

[Fortop]

MosVasil:

вы пытаетесь выполнить сортировку по полю где нет индекса.
простейший случай попробуйте добавить индекс на last_id

А еще лучше покажите результат

show create table orgs_list;

[MosVasil]

"CREATE TABLE `orgs_list` (
`last_id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(600) NOT NULL,
`short` varchar(200) NOT NULL,
`inn` bigint(10) NOT NULL,
`kpp` bigint(9) NOT NULL,
`ogrn` bigint(13) NOT NULL,
`date_ogrn` date DEFAULT NULL,
`region` varchar(200) DEFAULT NULL,
`adress` varchar(200) DEFAULT NULL,
`post_index` varchar(15) DEFAULT NULL,
`name_nalogorgan` varchar(200) DEFAULT NULL,
`adress_nalogorgan` varchar(200) DEFAULT NULL,
`nomer_nalogorgan` varchar(45) DEFAULT NULL,
`director_name` varchar(200) DEFAULT NULL,
`director_surname` varchar(200) DEFAULT NULL,
`director_fathername` varchar(200) DEFAULT NULL,
`director_inn` bigint(12) DEFAULT NULL,
`director_type` varchar(45) DEFAULT NULL,
`director_nametype` varchar(45) DEFAULT NULL,
`director_ogrnip` varchar(45) DEFAULT NULL,
`director_job` varchar(45) DEFAULT NULL,
`director_phone` varchar(45) DEFAULT NULL,
`capital` varchar(200) DEFAULT NULL,
`email` varchar(200) DEFAULT NULL,
`date` date DEFAULT NULL,
`status_code` varchar(45) DEFAULT NULL,
`status_name` varchar(200) DEFAULT NULL,
`status_date` date DEFAULT NULL,
`manage_name` varchar(200) DEFAULT NULL,
`manage_ogrn` varchar(45) DEFAULT NULL,
`manage_kpp` varchar(45) DEFAULT NULL,
`manage_inn` varchar(45) DEFAULT NULL,
`city` varchar(45) DEFAULT NULL,
`fcc_reg_nomer` varchar(45) NOT NULL,
`fcc_date` date NOT NULL,
`fcc_code` varchar(45) NOT NULL,
`fcc_name` varchar(200) NOT NULL,
`pension_reg_nomer` varchar(45) NOT NULL,
`pension_code` varchar(45) NOT NULL,
`pension_date` date NOT NULL,
`pension_name` varchar(200) NOT NULL,
PRIMARY KEY (`last_id`),
KEY `inn` (`inn`),
KEY `last_id` (`last_id`),
KEY `region` (`region`),
KEY `director_surname` (`director_surname`,`director_inn`,`manage_ogrn`),
KEY `kpp` (`kpp`)
) ENGINE=MyISAM AUTO_INCREMENT=107958 DEFAULT CHARSET=utf8"

[MosVasil]

индекс на last_id стоит primary, так же есть еще 5 индексов в этой таблице.
но результат все тот же
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orgs_list ALL NULL NULL NULL NULL 100 Using filesort

[Fortop]

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE orgs_list ALL NULL NULL NULL NULL 107957 Using filesort

нет тут доступных индексов для использования

Вот как ведет себя БД в случае их наличия

MariaDB [*]> explain SELECT * FROM transaction ORDER BY uuid ASC ;
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+-------+
| id   | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows  | Extra |
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+-------+
|    1 | SIMPLE      | transaction | index | NULL          | PRIMARY | 108     | NULL | 50537 |       |
+------+-------------+-------------+-------+---------------+---------+---------+------+-------+-------+
1 row in set (0.06 sec)

[Fortop]

MosVasil: вам ENGINE=MyISAM крайне нужен?

[Fortop]

MosVasil:
Ну и еще

COUNT(orgs2.last_id) AS countcompanys, COUNT(orgs3.last_id)

это ведь join

Так что оригинальный запрос несколько далек от простого select