Magento CE 1.7.02 - MySQL getting inundated with the same request over and over [closed]

Question

We have a dedicated server with an extension called M2ePro installed.We have approx 20,000 ebay listings and 10,000 Amazon listings.

We have 2500 products in our catalog.

The problem we are having is MySql.It is getting inundated with the same request over and over again which never gets completed.

| 11686 | t******b_magento | localhost | t******b_magento | Query | 984 | Sending data | SELECT `e`.`entity_id`, `ur`.`request_path` AS `url` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_website` AS `w` ON e.entity_id=w.product_id
LEFT JOIN `core_url_rewrite` AS `ur` ON e.entity_id=ur.product_id AND ur.category_id IS NULL AND ur.store_id='1' AND ur.is_system=1
INNER JOIN `catalog_product_entity_int` AS `t1_visibility` ON e.entity_id=t1_visibility.entity_id AND t1_visibility.store_id=0
LEFT JOIN `catalog_product_entity_int` AS `t2_visibility` ON t1_visibility.entity_id = t2_visibility.entity_id AND t1_visibility.attribute_id = t2_visibility.attribute_id AND t2_visibility.store_id=''
INNER JOIN `catalog_product_entity_int` AS `t1_status` ON e.entity_id=t1_status.entity_id AND t1_status.store_id=0
LEFT JOIN `catalog_product_entity_int` AS `t2_status` ON t1_status.entity_id = t2_status.entity_id AND t1_status.attribute_id = t2_status.attribute_id AND t2_status.store_id='' WHERE (w.website_id='1') AND (t1_visibility.attribute_id='95') AND ((IF(t2_visibility.value_id > 0, t2_visibility.value, t1_visibility.value)) IN(3, 2, 4)) AND (t1_status.attribute_id='89') AND ((IF(t2_status.value_id > 0, t2_status.value, t1_status.value)) IN(1)) |

I am not sure is this a M2epro issue or Magento.Our frontend of our website does not show up because of this MySQL overload

Any Suggestions would be helpful.

Answer 1

It looks like it is the product sitemap code as the code you have shown looks like it is built from the following collection model located in the sitemap module (Mage/Sitemap/Model/Resource/Catalog/Product.php::getCollection)

Maybe someone is hitting your sitemap a lot. You would be able to see this in your server access logs.

Failing that it would be worth seeing what other queries are running at the same time running the following as it may be some other query that is blocking;

show full processlist

Also would be worth running the query you mention preceeding it with an explain that might identify an issue with that query like a missing index