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.

  • 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. Jul 10 '20 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. Jul 10 '20 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 Jul 11 '20 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. Jul 11 '20 at 11:58
  • Apologies did I miss a result in the above Pastebin pastebin.com/CQNZQcBT Jul 11 '20 at 13:35

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.


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.


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.

  • 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. Oct 17 '20 at 15:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.