Mysql consume cpu 300-500%

[feerdispzoo]

From last days I see in top mysql consume cpu 300-500% and I've most issues in website (Magento 2) . Not possible connect via API etc due to hight consume cpu.

Directadmin everytime return message:

Warning: The system load average is 10.07​

Afer restart VPS, working fine for 1 minute, and back to the same. Below is logs.


I think there is some import to DB (open processes that cannot complete) and even after VPS reboot they keep coming back. Does anyone have an idea how I can kill all processes for this database?



Sending data SELECT DISTINCT `t`.`entity_id`, `t`.`attribute_id`, IF(t_store.value_id > 0, t_store.value, t_default.value) AS `value` FROM `catalog_product_entity_int` AS `t`
LEFT JOIN `catalog_product_entity_int` AS `t_store` ON t.entity_id=t_store.entity_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN `catalog_product_entity_int` AS `t_default` ON t.entity_id=t_default.entity_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (193, 206, 93, 207, 208, 190, 209, 210, 211, 83, 212, 213, 214, 215, 216, 226, 97, 217, 218, 219, 220, 136, 221, 99, 223, 224, 225)) AND (t.entity_id IN (16332, 16333, 16334, 16335, 16336, 16337, 16338, 16339, 16340, 16341, 16342, 16343, 16344, 16345, 16346, 16347, 16348, 16349, 16350, 16351, 16352, 16353, 16354, 16355, 16356, 16357, 16358, 16359, 16360, 16361, 16362, 16363, 16364, 16365, 16366, 16367, 16368, 16369, 16370, 16371, 16372, 16373, 16374, 16375, 16376, 16377, 16378, 16379, 16380, 16381, 16382, 16383, 16384, 16385, 16386, 16387, 16388, 16389, 16390, 16391, 16392, 16393, 16394, 16395, 16396, 16397, 16398, 16399, 16400, 16401, 16402, 16403, 16404, 16405, 16406, 16407, 16408, 16409, 16410, 16411, 16412, 16413, 16414, 16415, 16416, 16417, 16418, 16419, 16420, 16421, 16422, 16423, 16424, 16425, 16426, 16427, 16428, 16429, 16430, 16431)) UNION ALL SELECT DISTINCT `t`.`entity_id`, `t`.`attribute_id`, IF(t_store.value_id > 0, t_store.value, t_default.value) AS `value` FROM `catalog_product_entity_varchar` AS `t`
LEFT JOIN `catalog_product_entity_varchar` AS `t_store` ON t.entity_id=t_store.entity_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN `catalog_product_entity_varchar` AS `t_default` ON t.entity_id=t_default.entity_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (192, 73, 121)) AND (t.entity_id IN (16332, 16333, 16334, 16335, 16336, 16337, 16338, 16339, 16340, 16341, 16342, 16343, 16344, 16345, 16346, 16347, 16348, 16349, 16350, 16351, 16352, 16353, 16354, 16355, 16356, 16357, 16358, 16359, 16360, 16361, 16362, 16363, 16364, 16365, 16366, 16367, 16368, 16369, 16370, 16371, 16372, 16373, 16374, 16375, 16376, 16377, 16378, 16379, 16380, 16381, 16382, 16383, 16384, 16385, 16386, 16387, 16388, 16389, 16390, 16391, 16392, 16393, 16394, 16395, 16396, 16397, 16398, 16399, 16400, 16401, 16402, 16403, 16404, 16405, 16406, 16407, 16408, 16409, 16410, 16411, 16412, 16413, 16414, 16415, 16416, 16417, 16418, 16419, 16420, 16421, 16422, 16423, 16424, 16425, 16426, 16427, 16428, 16429, 16430, 16431)) UNION ALL SELECT DISTINCT `t`.`entity_id`, `t`.`attribute_id`, IF(t_store.value_id > 0, t_store.value, t_default.value) AS `value` FROM `catalog_product_entity_text` AS `t`
LEFT JOIN `catalog_product_entity_text` AS `t_store` ON t.entity_id=t_store.entity_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN `catalog_product_entity_text` AS `t_default` ON t.entity_id=t_default.entity_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (75, 76)) AND (t.entity_id IN (16332, 16333, 16334, 16335, 16336, 16337, 16338, 16339, 16340, 16341, 16342, 16343, 16344, 16345, 16346, 16347, 16348, 16349, 16350, 16351, 16352, 16353, 16354, 16355, 16356, 16357, 16358, 16359, 16360, 16361, 16362, 16363, 16364, 16365, 16366, 16367, 16368, 16369, 16370, 16371, 16372, 16373, 16374, 16375, 16376, 16377, 16378, 16379, 16380, 16381, 16382, 16383, 16384, 16385, 16386, 16387, 16388, 16389, 16390, 16391, 16392, 16393, 16394, 16395, 16396, 16397, 16398, 16399, 16400, 16401, 16402, 16403, 16404, 16405, 16406, 16407, 16408, 16409, 16410, 16411, 16412, 16413, 16414, 16415, 16416, 16417, 16418, 16419, 16420, 16421, 16422, 16423, 16424, 16425, 16426, 16427, 16428, 16429, 16430, 16431)) UNION ALL SELECT DISTINCT `t`.`entity_id`, `t`.`attribute_id`, IF(t_store.value_id > 0, t_store.value, t_default.value) AS `value` FROM `catalog_product_entity_decimal` AS `t`
LEFT JOIN `catalog_product_entity_decimal` AS `t_store` ON t.entity_id=t_store.entity_id AND t.attribute_id=t_store.attribute_id AND t_store.store_id = '4'
LEFT JOIN `catalog_product_entity_decimal` AS `t_default` ON t.entity_id=t_default.entity_id AND t.attribute_id=t_default.attribute_id AND t_default.store_id = 0 WHERE (t.attribute_id IN (77)) AND (t.entity_id IN (16332, 16333, 16334, 16335, 16336, 16337, 16338, 16339, 16340, 16341, 16342, 16343, 16344, 16345, 16346, 16347, 16348, 16349, 16350, 16351, 16352, 16353, 16354, 16355, 16356, 16357, 16358, 16359, 16360, 16361, 16362, 16363, 16364, 16365, 16366, 16367, 16368, 16369, 16370, 16371, 16372, 16373, 16374, 16375, 16376, 16377, 16378, 16379, 16380, 16381, 16382, 16383, 16384, 16385, 16386, 16387, 16388, 16389, 16390, 16391, 16392, 16393, 16394, 16395, 16396, 16397, 16398, 16399, 16400, 16401, 16402, 16403, 16404, 16405, 16406, 16407, 16408, 16409, 16410, 16411, 16412, 16413, 16414, 16415, 16416, 16417, 16418, 16419, 16420, 16421, 16422, 16423, 16424, 16425, 16426, 16427, 16428, 16429, 16430, 16431)) 0.000
3101 mall_m24 localhost:54956 mall_m24 Sleep 10558 NULL 0.000
3107 mall_m24 localhost:55008 mall_m24 Sleep 0 NULL 0.000
21201 flexly_dash localhost flexly_dash Sleep 10 NULL 0.000
22938 mall_m24 localhost:54700 mall_m24 Query 4360 Sending data SELECT `e`.*, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status`, IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS `visibility`, `at_weight`.`value` AS `weight` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '97') AND `at_status_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '97') AND (`at_status`.`store_id` = 1)
INNER JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '99') AND `at_visibility_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '99') AND (`at_visibility`.`store_id` = 1)
LEFT JOIN `catalog_product_entity_decimal` AS `at_weight` ON (`at_weight`.`entity_id` = `e`.`entity_id`) AND (`at_weight`.`attribute_id` = '82') AND (`at_weight`.`store_id` = 0) WHERE ((`e`.`type_id` = 'simple')) AND ((IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1')) AND ((at_weight.value < '10') OR (at_weight.value IS NULL)) ORDER BY `e`.`updated_at` DESC
LIMIT 100 0.000
23261 mall_m24 localhost:46172 mall_m24 Query 4256 Sending data SELECT `e`.*, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status`, IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS `visibility`, `at_weight`.`value` AS `weight` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_entity_int` AS `at_status_default` ON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '97') AND `at_status_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_int` AS `at_status` ON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '97') AND (`at_status`.`store_id` = 1)
INNER JOIN `catalog_product_entity_int` AS `at_visibility_default` ON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '99') AND `at_visibility_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_int` AS `at_visibility` ON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '99') AND (`at_visibility`.`store_id` = 1)
LEFT JOIN `catalog_product_entity_decimal` AS `at_weight` ON (`at_weight`.`entity_id` = `e`.`entity_id`) AND (`at_weight`.`attribute_id` = '82') AND (`at_weight`.`store_id` = 0) WHERE ((`e`.`type_id` = 'simple')) AND ((IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1')) AND ((at_weight.value < '10') OR (at_weight.value IS NULL)) ORDER BY `e`.`updated_at` DESC
LIMIT 100 0.000

 

[Richard G]

Oh Magento... yeah... might be an update cronjob that is running. Kill the cronjob and restart mysql/mariadb might work.

 

[cjd]

May also need some adjustments for mysql/mariadb, use MySQLTuner and see if some adjustments need to be made in the my.cnf

GitHub - major/MySQLTuner-perl: MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. - major/MySQLTuner-perl

github.com

Check how much free memory is available also. Low on ram can cause lots of strange issues, especially if you start using lots of virtual ram in swap, everything will slow right down and load can go really high.

May want to check your swapiness of your kernel also. I keep all my hosts at value of 1. System could be swapping well before necessary.

How to change the Swappiness of your Linux system

Swappiness is the kernel parameter that defines how much (and how often) your Linux kernel will copy RAM contents to swap. This parameters default va...

www.howtoforge.com

 

[Zhenyapan]

also you can from ssh connect to mysql, show processlist, check requests and their PIDs, kill this PIDs, or from phpmyadmin with da_admin credentials with comfort GUI do the same.

 

[feerdispzoo]

Thank you Guys. After restart mariadb and disable cron is 33%
713772 mysql 20 0 2183272 279928 23988 S 33.2 0.9 0:18.76 mysqld

After enable cron, is 500%+, I've disabled all imports etc. but still the same. I need to debug now directly in Magento what is goin on.

 

[Richard G]

You might also want to contact Magento support for this, they might have some good tips.

 

[johannes]

@chachagoldleaf4079 Are you the echo from @Zhenyapan `s posting here https://forum.directadmin.com/threads/mysql-consume-cpu-300-500.67092/post-352906 ?

I think there is no need to copywrite previous postings from others.

 

[Richard G]

First post and seeing the username, he's probably a spammer trying to bypass anti spam measures by posting something on-topic. Seen it before.
Reported him.