1

We have several web servers and MySql Server. Once in a while there is a huge query that being requested from one of the web servers (which means it comes from frontend).

We have a full page cache enabled. With redis support. So this kind of query is very strange. A especially when it comes from frontend.

The query is too long and it can take up to 20 sec to complete. When it occurs on several servers in parallel it can break down the whole system and website will fall.

The query goes like this:

`SELECT t_d.entity_id, t_d.attribute_id, t_d.value AS default_value,t_s.value AS store_value, IF(t_s.value_id IS NULL, t_d.value, t_s.value) AS value FROMcatalog_product_entity_varchar AS t_d\n LEFT JOIN catalog_product_entity_varcharAS t_s ON t_s.attribute_id = t_d.attribute_id AND t_s.entity_id = t_d.entity_id AND t_s.store_id = 2 WHERE (t_d.entity_type_id = 4) AND (t_d.entity_id IN (2,000 of products id comes here))` 

,and this query is being joined 5 or 6 times. Each time with 2,000 products

How can I tell where does this query comes from? How can I disable this dynamic query or whatever it is??

Thanks in advance

4
  • how many products do you have on your Magento store? Dec 2, 2013 at 14:20
  • 4k total. 2k are simple and the rest are configurable. Dec 3, 2013 at 15:04
  • Could this query be for a sitemap?
    – parhamr
    Dec 10, 2013 at 0:53
  • no. my sitemap is off. and this kind of query occurs when loading category (latest finding) . when the category is not cached of coures. Dec 12, 2013 at 13:08

1 Answer 1

1

First, if you aren't running New Relic, you probably should. It can help provide data & insight.

This is likely coming from Mage_Catalog_Model_Resource_Collection_Abstract::_getLoadAttributesSelect(). This may be the result of a category load or a configurable product load.

2
  • but does this kind of big query is normal? or there's something wrong out there... Dec 3, 2013 at 15:08
  • These large queries are possible under certain not-rare circumstances. Based on your simple/configurable ratio, I'm wondering if you are not suffering from some inefficiencies in how configurable options are determined.
    – benmarks
    Dec 3, 2013 at 15:32

Your Answer

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

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