0

We are facing huge MYSQL CPU usage. While investigating we found that there are MySQL sleep connections during the CRON run. Is there any way to reduce the CPU usage and the sleep connections. Please check the attached screenshots.

enter image description here

enter image description here

We found the following suggestions:

Magento 2.4 Database Connection Usage

We have a large catalog and large number of tier prices, approx. 2000000. Is this suggestion suitable to fix our problem. Please suggest a better solution if you have any.

Thanks.

| Id | User | Host | db | Command | Time | State |Info | Progress | +---------+----------------------------+-----------------+ | 2777768 | user_user_live_245 | 127.0.0 | user_user_live | Sleep | 2704 | | NULL | 0.000 | | 2777774 | user_user_live_245 | 127.0.0.1:3 | user_user_live | Query | 0 | Sending data | UPDATE catalog_product_index_price_temp AS i INNER JOIN catalog_product_index_website AS cpiw ON cpiw.website_id = i.website_id INNER JOIN catalogrule_product_price AS cpp ON cpp.product_id = i.entity_id AND cpp.customer_group_id = i.customer_group_id AND cpp.website_id = i.website_id AND cpp.rule_date = cpiw.website_date SET i.final_price = LEAST(final_price, IFNULL(cpp.rule_price, i.final_price)), i.min_price = LEAST(min_price, IFNULL(cpp.rule_price, i.min_price)) WHERE (i.entity_id IN (13, 14, 18, 383, 387, 395, 399, 410, 414, 416, 418, 419, 423, 424, 429, 431, 433, 436, 437, 447, 448, 449, 450, 451, 452, 453, 454, 459, 461, 463, 472, 473, 474, 475, 481, 482, 486, 487, 488, 489, 490, 491, 492, 494, 509, 510, 511, 513, 516, 518, 558, 559, 560, 561, 565, 566, 568, 582, 584, 585, 587, 588, 589, 594, 595, 596, 597, 598, 599, 600, 601, 602, 603, 604, 605, 606, 607, 608, 609, 610, 611, 612, 613, 614, 615, 616, 617, 618, 619, 620, 621, 622, 623, 624, 625, 626, 627, 628, 629, 630)) | 0.000 | | 2782841 | user_user_live_245 | localhost | user_user_live | Query | 0 | starting | SHOW FULL PROCESSLIST | 0.000 | | 2782875 | user_user_live_245 | 127.0.0.1:64063 | user_user_live | Sleep | 0 | | NULL | 0.000 |

Improve this question
9
  • Please post TEXT results of this query - SELECT 'SLEEPING time use', COUNT(*),SUM(time) FROM information_schema.processlist WHERE command="Sleep";
    – Wilson Hauck
    Commented Mar 15 at 20:42
  • 1
    SLEEPING time use | COUNT(*) | SUM(time) | +-------------------+----------+-----------+ | SLEEPING time use | 3 | 12116 |
    – Jasvir Singh
    Commented Mar 18 at 4:10
  • Since there are only 3 averaging more than one hour each, could you post TEXT results of SELECT * from information_schema.processlist WHERE command="Sleep" \G;
    – Wilson Hauck
    Commented Mar 18 at 11:58
  • 1
    COMMAND | TIME | STATE | INFO | TIME_MS | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID | Sleep | 0 | | NULL | 22.296 | 0 | 0 | 0.000 | 81496 | 6436936 | 0 | 1607888880 | NULL | 46285 | Sleep | 157 | | NULL | 157966.443 | 0 | 0 | 0.000 | 81496 | 81496 | 0 | 1607823893 | NULL | 46735 |
    – Jasvir Singh
    Commented Mar 18 at 12:07
  • 1
    Hi, I updated the question with the full response to the command.
    – Jasvir Singh
    Commented Mar 19 at 3:36

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.