0

We are currently experiencing an issue were our site runs fine for about 3 hours, but then our MySQL server crashes as a result of one query being run up to 300 times at once.

The query is the following.

SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_category_product_index_store1` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id=10
 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)
 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) WHERE (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) = '4') AND (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = '1')

The only variation is the category ID. Reindexing catalog_category_products does fix the issue for a short period, but eventually, the same problem persists.

Our MYSQL is running on an r4.x2large instance running MariaDB 10.2

Any help or feedback would be much appreciated.

Improve this question
9
  • Additional information request. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 2.6 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional very helpful info, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. Commented Jul 10, 2020 at 20:36
  • Please post TEXT results of A) SHOW CREATE TABLE catalog_product_entity; B) SHOW CREATE TABLE catalog_category_product_index_store1; C) SHOW CREATE TABLE catalog_product_entity_int; D) SHOW INDEX FROM catalog_product_entity; E) SHOW INDEX FROM catalog_category_product_index_store1; F) SHOW INDEX FROM catalog_product_entity_int; and Welcome To magento.stackexchange.com. Commented Jul 10, 2020 at 21:22
  • Thanks appreciated the response, our server is running on SSD's and here is the data requested pastebin.com/CQNZQcBT imagebin.ca/v/5SzqAm0z4nR0 Commented Jul 11, 2020 at 0:02
  • Thank you for the htop posted and SSD detail. Please consider posting additional details requested for analysis. I can see from the htop report your 8 cpu's were all busy with mysqld. Commented Jul 11, 2020 at 11:58
  • Apologies did I miss a result in the above Pastebin pastebin.com/CQNZQcBT Commented Jul 11, 2020 at 13:35

3 Answers 3

Reset to default
1

To avoid data-type conversions, try this query, please

SELECT SQL_NO_CACHE COUNT(DISTINCT e.entity_id) FROM catalog_product_entity AS e INNER JOIN catalog_category_product_index_store1 AS cat_index ON cat_index.product_id=e.entity_id AND cat_index.store_id=1 AND cat_index.category_id=10 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) 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) WHERE (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) = 4) AND (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = 1)

and compare timings to completion for selecting the same data with the single quotes removed on 6 equal selectors.

Please post timings before and after to select the ~ 162450 distinct count.

Workload analysis in process, give me another day, please for config suggestions to improve performance.

Improve this answer
0

Rate Per Second = RPS

Suggestions to consider for your RDS Parameters group

innodb_flush_neighbors=2  # from 1 to reduce innodb_buffer_pool_pages_dirty of 38,718 faster
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used for function
read_rnd_buffer_size=64K  # from 256K to reduce handler_read_rnd_next RPS of 104,614
read_buffer_size=512K  # from 128K to reduce handler_read_next RPS of 329,982

You will find these configuration changes will REDUCE CPU busy significantly.

For additional assistance, please view my profile, Network profile for contact info.

Your SHOW FULL PROCESSLIST indicated ~ 130 of the processes were stalled at SENDING DATA SELECT COUNT(DISTINCT .... let's determine why, please.

Improve this answer
0

After a few hours digging I've realised that I have followed the recommendation of query_cache_type = 0 from MySQLTunerScript as that feature would be removed in Mysql 8.0

By setting query_cache_type = 1 and tuning the query_cache_size value we have now removed the issue.

Improve this answer
1
  • Lachlan, If any of the comments were helpful, please upvote them. If any Answer was helpful, please Upvote and if it was the BEST answer, mark Accepted. Commented Oct 17, 2020 at 15:27

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.