0

I have a website running Magento 1.9 which is flooding mysql with slow queries like this

SELECT COUNT(DISTINCT e.entity_id) FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(3, 4) AND cat_index.category_id = '2' WHERE (`e`.`entity_id` IN(78661, 78664, 78668, 78659, 78660, 78658, 78657, 78656, ...about 1000 more ids)

I'm assuming this has to do with caregory and layered navigation so I disabled the layered navigation counts globally. It helped mitigate the problem but it's still not quite solved so I want to takle it at the source.

Could someone help me understand when these queries are run and why this is happening?

Another example

SELECT `trofodosia_boyrtsas_idx`.`value`, COUNT(trofodosia_boyrtsas_idx.entity_id) AS `count` FROM `catalog_product_entity` AS `e`
 INNER JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id = e.entity_id AND price_index.website_id = '1' AND price_index.customer_group_id = 0
 INNER JOIN `catalog_category_product_index` AS `cat_index` ON cat_index.product_id=e.entity_id AND cat_index.store_id='1' AND cat_index.visibility IN(3, 4) AND cat_index.category_id = '2'
 INNER JOIN `catalog_product_index_eav` AS `trofodosia_boyrtsas_idx` ON trofodosia_boyrtsas_idx.entity_id = e.entity_id AND trofodosia_boyrtsas_idx.attribute_id = '321' AND trofodosia_boyrtsas_idx.store_id = '1' WHERE (`e`.`entity_id` IN(78661, 78664, ...more ids)
GROUP BY `trofodosia_boyrtsas_idx`

They seem to be fairly common in all magento 1.9 running instances and connected to the layered navigation and pagination.

Is there something I could change on mysql server to improve this? Is this behaviour something documented in Magento 1.9?

PS: Due to the amount of attributes I can't enable flat catalog because it hits the max row limit in mysql.

13
  • trofodosia_boyrtsas, is there some kind of custom module / third party module that works with those query? I've never seen anything like: "trofodosia_boyrtsas_idx" Jan 13 at 14:11
  • This is just an attribute that probably gets indexed by amasty improved layered navigation.
    – gabtzi
    Jan 13 at 14:46
  • However I think I found the root cause is not within Magento but with the underlying MariaDB Server, it seems in 10.2.35-bionic it has a bug which causes it to slow down queries with a lot of IN clauses. I'm currently investigating this path futher
    – gabtzi
    Jan 13 at 14:48
  • Happened to me that i had to remove as much as i can in one project the amount of queries that had an IN inside them, with a catalog of more than 50k / 100k products those queries kills the DB Jan 13 at 14:50
  • Please consider posting EXPLAIN SELECT .....; for your slow query and SHOW CREATE TABLE (tbl_name); for each table referenced. It is likely you have unnecessary quotes around numeric search values, leading to time wasted on datatype manipulation. Jan 21 at 13:30
0

In case anyone else searches for the same issue apparently it's a bug in mysql 5.6 and affects mariadb 10.2.35-bionic as well. When you have many static values in an IN() clause like magento does for categories and layered navigation it will cause extreme stress on the mysql server calculating the statistics to optimize the query.

I've tested the same query using different Maria DB versions

  • 10.2.36 (0.058sec)
  • 10.2.35-bionic (72sec)
  • 10.3.26-bionic (0.27sec)
  • 10.5.x (0.53sec)

And only 10.2.35-bionic was affected using docker containers.

To test if you're affected you can run the following test which was found in MySQL bugtracker (linking to pastebin because it's too big for an answer)

https://pastebin.com/x9AziWXF

0

In order to optimize query on Product Description Page try to remove inner join with associated product which makes a lot difference to load product data.

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.