Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

MySQL Using 100% CPU
 
hazzy99
Jr. Member
 
Total Posts:  1
Joined:  2012-04-16
 

Hi Guys,

My Ubuntu 12.04 64Bit server is running Magento 1.7 and MySQL database 5.5.31-0ubuntu0.12.04.1.

It is a twin core server and MySQL seems to be using 100% of one of the cores. Looking at the history of the CPU usage shows me this seems to be running all the time. Not just when there is website activity.

I have used SHOW FULL PROCESSLIST to show the MySQL processes and i see a very long running process with this SQL statement.

SELECT `e`.*, IF(at_status.value_id 0at_status.valueat_status_default.value) AS `status`, IF(at_visibility.value_id 0at_visibility.valueat_visibility_default.value) AS `visibility`, `at_price`.`value` AS `price`, IF(at_name.value_id 0at_name.valueat_name_default.value) AS `name`, IF(at_short_description.value_id 0at_short_description.valueat_short_description_default.value) AS `short_description`, `stock`.`qty`, `stock`.`is_in_stock`, `stock`.`manage_stock`, `stock`.`use_config_manage_stock`, `url`.`request_path`, `categories`.*, GROUP_CONCAT(categories_index.category_id) AS `categories_ids`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `price_index`.`final_priceFROM `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_product_website` AS `product_websiteON product_website.product_id e.entity_id AND product_website.website_id '1'
 
INNER JOIN `catalog_product_entity_int` AS `at_status_defaultON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = '96') AND `at_status_default`.`store_id` = 0
 LEFT JOIN 
`catalog_product_entity_int` AS `at_statusON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = '96') AND (`at_status`.`store_id` = 1)
 
INNER JOIN `catalog_product_entity_int` AS `at_visibility_defaultON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = '102') AND `at_visibility_default`.`store_id` = 0
 LEFT JOIN 
`catalog_product_entity_int` AS `at_visibilityON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = '102') AND (`at_visibility`.`store_id` = 1)
 
INNER JOIN `catalog_product_entity_decimal` AS `at_priceON (`at_price`.`entity_id` = `e`.`entity_id`) AND (`at_price`.`attribute_id` = '75') AND (`at_price`.`store_id` = 0)
 
INNER JOIN `catalog_product_entity_varchar` AS `at_name_defaultON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = '71') AND `at_name_default`.`store_id` = 0
 LEFT JOIN 
`catalog_product_entity_varchar` AS `at_nameON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = '71') AND (`at_name`.`store_id` = 1)
 
INNER JOIN `catalog_product_entity_text` AS `at_short_description_defaultON (`at_short_description_default`.`entity_id` = `e`.`entity_id`) AND (`at_short_description_default`.`attribute_id` = '73') AND `at_short_description_default`.`store_id` = 0
 LEFT JOIN 
`catalog_product_entity_text` AS `at_short_descriptionON (`at_short_description`.`entity_id` = `e`.`entity_id`) AND (`at_short_description`.`attribute_id` = '73') AND (`at_short_description`.`store_id` = 1)
 
LEFT JOIN `cataloginventory_stock_item` AS `stockON stock.product_id=e.entity_id
 LEFT JOIN 
`core_url_rewrite` AS `urlON url.product_id=e.entity_id AND url.category_id IS NULL AND is_system=AND ISNULL(options) AND url.store_id=1
 LEFT JOIN 
`catalog_category_product` AS `categoriesON categories.product_id=e.entity_id
 INNER JOIN 
`catalog_category_product_index` AS `categories_indexON categories_index.category_id=categories.category_id AND categories_index.product_id=categories.product_id AND categories_index.store_id=AND categories_index.category_id NOT IN (111,112,113,114,115,116,117,118,119,120,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,143,144,145,146,147,148,152,153,154,155,156,157,158,159,160,161,162,163,166,167,168,169,170,171,173,178,179,180,181,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,456,457,503,587,588,589,590,591,592,593,594,600,601,602,608)
 
LEFT JOIN `catalog_product_index_price` AS `price_indexON price_index.entity_id=e.entity_id AND customer_group_id=AND price_index.website_id=1 WHERE (IF(at_status.value_id 0at_status.valueat_status_default.value) = '1') AND (`e`.`type_idIN('simple')) AND (IF(at_visibility.value_id 0at_visibility.valueat_visibility_default.valueIN('1''2''3''4')) AND (at_price.value '10') AND (`e`.`skuIS NOT NULL) AND (IF(at_name.value_id 0at_name.valueat_name_default.valueIS NOT NULL) AND (IF(at_short_description.value_id 0at_short_description.valueat_short_description_default.valueIS NOT NULLGROUP BY `e`.`entity_idLIMIT 1500 OFFSET 3000

I have searched the Magento source code to see if i can find out what this originates from without success. Any help appreciated.

Hazzy.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Nematis
Jr. Member
 
Avatar
Total Posts:  26
Joined:  2008-08-03
Perpignan
 

Hi,

I have exactly the same problem with this query.

I use the Data Feed Manager module from Wyomind. It’s this module which creates this sql query.

Do you have resolved the problem ? How ?

Thanks.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Wyomind
Jr. Member
 
Avatar
Total Posts:  26
Joined:  2010-11-09
 

Hi All,

Thanks for posting your messages.
The reason of this issue is quite obscure and seems to be rather due to a mysql bug or a Magento issue with some index.

As you noticed the following request doesn\’t end and dramatically increase the CPU usage :

SELECT `e`.*, IF(at_status.value_id 0at_status.valueat_status_default.value) AS `status`, IF(at_visibility.value_id 0at_visibility.valueat_visibility_default.value) AS `visibility`, `at_price`.`value` AS `price`, IF(at_name.value_id 0at_name.valueat_name_default.value) AS `name`, IF(at_short_description.value_id 0at_short_description.valueat_short_description_default.value) AS `short_description`, `stock`.`qty`, `stock`.`is_in_stock`, `stock`.`manage_stock`, `stock`.`use_config_manage_stock`, `url`.`request_path`, `categories`.*, GROUP_CONCAT(categories_index.category_id) AS `categories_ids`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `price_index`.`final_priceFROM `catalog_product_entity` AS `e`
 
INNER JOIN `catalog_product_website` AS `product_websiteON product_website.product_id e.entity_id AND product_website.website_id \'1\'
 
INNER JOIN `catalog_product_entity_int` AS `at_status_defaultON (`at_status_default`.`entity_id` = `e`.`entity_id`) AND (`at_status_default`.`attribute_id` = \'96\') AND `at_status_default`.`store_id` = 0
 LEFT JOIN 
`catalog_product_entity_int` AS `at_statusON (`at_status`.`entity_id` = `e`.`entity_id`) AND (`at_status`.`attribute_id` = \'96\') AND (`at_status`.`store_id` = 1)
 
INNER JOIN `catalog_product_entity_int` AS `at_visibility_defaultON (`at_visibility_default`.`entity_id` = `e`.`entity_id`) AND (`at_visibility_default`.`attribute_id` = \'102\') AND `at_visibility_default`.`store_id` = 0
 LEFT JOIN 
`catalog_product_entity_int` AS `at_visibilityON (`at_visibility`.`entity_id` = `e`.`entity_id`) AND (`at_visibility`.`attribute_id` = \'102\') AND (`at_visibility`.`store_id` = 1)
 
INNER JOIN `catalog_product_entity_decimal` AS `at_priceON (`at_price`.`entity_id` = `e`.`entity_id`) AND (`at_price`.`attribute_id` = \'75\') AND (`at_price`.`store_id` = 0)
 
INNER JOIN `catalog_product_entity_varchar` AS `at_name_defaultON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = \'71\') AND `at_name_default`.`store_id` = 0
 LEFT JOIN 
`catalog_product_entity_varchar` AS `at_nameON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = \'71\') AND (`at_name`.`store_id` = 1)
 
INNER JOIN `catalog_product_entity_text` AS `at_short_description_defaultON (`at_short_description_default`.`entity_id` = `e`.`entity_id`) AND (`at_short_description_default`.`attribute_id` = \'73\') AND `at_short_description_default`.`store_id` = 0
 LEFT JOIN 
`catalog_product_entity_text` AS `at_short_descriptionON (`at_short_description`.`entity_id` = `e`.`entity_id`) AND (`at_short_description`.`attribute_id` = \'73\') AND (`at_short_description`.`store_id` = 1)
 
LEFT JOIN `cataloginventory_stock_item` AS `stockON stock.product_id=e.entity_id
 LEFT JOIN 
`core_url_rewrite` AS `urlON url.product_id=e.entity_id AND url.category_id IS NULL AND is_system=AND ISNULL(options) AND url.store_id=1
 LEFT JOIN 
`catalog_category_product` AS `categoriesON categories.product_id=e.entity_id
 INNER JOIN 
`catalog_category_product_index` AS `categories_indexON categories_index.category_id=categories.category_id AND categories_index.product_id=categories.product_id AND categories_index.store_id=AND categories_index.category_id NOT IN (111,112,113,114,115,116,117,118,119,120,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,143,144,145,146,147,148,152,153,154,155,156,157,158,159,160,161,162,163,166,167,168,169,170,171,173,178,179,180,181,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,456,457,503,587,588,589,590,591,592,593,594,600,601,602,608)
 
LEFT JOIN `catalog_product_index_price` AS `price_indexON price_index.entity_id=e.entity_id AND customer_group_id=AND price_index.website_id=1 WHERE (IF(at_status.value_id 0at_status.valueat_status_default.value) = \'1\') AND (`e`.`type_idIN(\'simple\')) AND (IF(at_visibility.value_id 0at_visibility.valueat_visibility_default.valueIN(\'1\'\'2\'\'3\'\'4\')) AND (at_price.value \'10\') AND (`e`.`skuIS NOT NULL) AND (IF(at_name.value_id 0at_name.valueat_name_default.valueIS NOT NULL) AND (IF(at_short_description.value_id 0at_short_description.valueat_short_description_default.valueIS NOT NULLGROUP BY `e`.`entity_idLIMIT 1500 OFFSET 3000

Whereas this one shouldn\’t:

SELECT `e`.*, IF(at_status.value_id > 0, at_status.value, at_status_default.value) AS `status`, IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) AS `visibility`, `at_price`.`value` AS `price`, IF(at_name.value_id > 0, at_name.value, at_name_default.value) AS `name`, IF(at_short_description.value_id > 0, at_short_description.value, at_short_description_default.value) AS `short_description`, `stock`.`qty`, `stock`.`is_in_stock`, `stock`.`manage_stock`, `stock`.`use_config_manage_stock`, `url`.`request_path`, `categories`.*, GROUP_CONCAT(categories_index.category_id) AS `categories_ids`, `price_index`.`min_price`, `price_index`.`max_price`, `price_index`.`tier_price`, `price_index`.`final_price` FROM `catalog_product_entity` AS `e`
INNER JOIN `catalog_product_website` AS `product_website` ON product_website.product_id = e.entity_id AND product_website.website_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` = \’96\’) 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` = \’96\’) AND (`at_status`.`store_id` = 1)
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` = \’102\’) 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` = \’102\’) AND (`at_visibility`.`store_id` = 1)
INNER JOIN `catalog_product_entity_decimal` AS `at_price` ON (`at_price`.`entity_id` = `e`.`entity_id`) AND (`at_price`.`attribute_id` = \’75\’) AND (`at_price`.`store_id` = 0)
INNER JOIN `catalog_product_entity_varchar` AS `at_name_default` ON (`at_name_default`.`entity_id` = `e`.`entity_id`) AND (`at_name_default`.`attribute_id` = \’71\’) AND `at_name_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_varchar` AS `at_name` ON (`at_name`.`entity_id` = `e`.`entity_id`) AND (`at_name`.`attribute_id` = \’71\’) AND (`at_name`.`store_id` = 1)
INNER JOIN `catalog_product_entity_text` AS `at_short_description_default` ON (`at_short_description_default`.`entity_id` = `e`.`entity_id`) AND (`at_short_description_default`.`attribute_id` = \’73\’) AND `at_short_description_default`.`store_id` = 0
LEFT JOIN `catalog_product_entity_text` AS `at_short_description` ON (`at_short_description`.`entity_id` = `e`.`entity_id`) AND (`at_short_description`.`attribute_id` = \’73\’) AND (`at_short_description`.`store_id` = 1)
LEFT JOIN `cataloginventory_stock_item` AS `stock` ON stock.product_id=e.entity_id
LEFT JOIN `core_url_rewrite` AS `url` ON url.product_id=e.entity_id AND url.target_path NOT LIKE \’Êtegory%\’ AND is_system=1 AND ISNULL(options) AND url.store_id=1
LEFT JOIN `catalog_category_product` AS `categories` ON categories.product_id=e.entity_id
INNER JOIN `catalog_category_product_index` AS `categories_index` ON categories_index.category_id=categories.category_id AND categories_index.product_id=categories.product_id AND categories_index.store_id=1 AND categories_index.category_id NOT IN (111,112,113,114,115,116,117,118,119,120,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,143,144,145,146,147,148,152,153,154,155,156,157,158,159,160,161,162,163,166,167,168,169,170,171,173,178,179,180,181,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,456,457,503,587,588,589,590,591,592,593,594,600,601,602,608)
LEFT JOIN `catalog_product_index_price` AS `price_index` ON price_index.entity_id=e.entity_id AND customer_group_id=0 AND price_index.website_id=1 WHERE (IF(at_status.value_id > 0, at_status.value, at_status_default.value) = \’1\’) AND (`e`.`type_id` IN(\’simple\’)) AND (IF(at_visibility.value_id > 0, at_visibility.value, at_visibility_default.value) IN(\’1\’, \’2\’, \’3\’, \’4\’)) AND (at_price.value > \’10\’) AND (`e`.`sku` IS NOT NULL) AND (IF(at_name.value_id > 0, at_name.value, at_name_default.value) IS NOT NULL) AND (IF(at_short_description.value_id > 0, at_short_description.value, at_short_description_default.value) IS NOT NULL) GROUP BY `e`.`entity_id` LIMIT 1500 OFFSET 3000

The only difference is url.target_path NOT LIKE \’Êtegory%\’ instead of url.category_id IS NULL

The issue seems to happen only when there are a important number of url permanent redirection in the core_url_rewrite table,
that is why with the previous version of the module we suggested to apply the following workaround :
http://www.wyomind.com/data-feed-manager-magento.html?directlink=faq#How_to_improve_my_datafeed_generation_process

With the last versions of the module the above sql query is now applied and avoid any issue.

Cheers.
Pierre.

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top