I'm running quite an old Magento 1.9.2 instance, for a couple of weeks, it's going offline periodically because of MySQL load. MySQL server CPU usage is going over 1000% (one thousand), all cores are busy and the server stops responding. DB server has 32GB of RAM and 16 CPUs. Incidents are happening a few times a day, I was very suspicious about cron tasks but none of them is correlated in time with downtimes.
I was able to capture the MySQL process list during one of the incidents. Here's the list of tables in use (top 30):
| Database | Table | In_use | Name_locked |
|---|---|---|---|
| zaxelos | catalog_product_flat_1 | 104 | 0 |
| zaxelos | mageworx_custom_options_option_view_mode | 88 | 0 |
| zaxelos | catalog_product_option | 81 | 0 |
| zaxelos | cataloginventory_stock_status | 51 | 0 |
| zaxelos | catalog_product_entity | 41 | 0 |
| zaxelos | eav_entity_attribute | 35 | 0 |
| zaxelos | catalog_product_bundle_option_value | 30 | 0 |
| zaxelos | catalog_product_entity_varchar | 28 | 0 |
| zaxelos | catalog_product_super_attribute | 24 | 0 |
| zaxelos | review | 23 | 0 |
| zaxelos | review_store | 23 | 0 |
| zaxelos | review_detail | 23 | 0 |
| zaxelos | zaxelos_cache | 20 | 0 |
| zaxelos | review_entity | 20 | 0 |
| zaxelos | zaxelos_productos_stock | 18 | 0 |
| zaxelos | zaxelos_dealbars | 18 | 0 |
| zaxelos | cataloginventory_stock_item | 17 | 0 |
| zaxelos | zaxelos_dynamicblocks_producthighlight | 17 | 0 |
| zaxelos | catalogrule_product_price | 16 | 0 |
| zaxelos | catalog_product_bundle_option | 15 | 0 |
| zaxelos | catalog_category_product_index | 15 | 0 |
| zaxelos | mageworx_custom_options_option_default | 14 | 0 |
| zaxelos | catalog_product_option_price | 14 | 0 |
| zaxelos | catalog_product_option_title | 14 | 0 |
| zaxelos | mageworx_custom_options_option_description | 14 | 0 |
| zaxelos | catalog_product_entity_media_gallery_value | 14 | 0 |
| zaxelos | mageworkshop_dr_purchase | 13 | 0 |
| zaxelos | catalog_category_entity | 13 | 0 |
| zaxelos | catalog_product_entity_group_price | 11 | 0 |
Example queries that are executed during the incidents are:
SELECT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`sku`, IF(( SELECT vm.view_mode AS required_options FROM catalog_product_option AS cpo LEFT JOIN mageworx_custom_options_option_view_mode AS vm ON cpo.option_id=vm.option_id AND (vm.store_id='1' OR vm.store_id=0) WHERE e.entity_id=cpo.product_id AND cpo.is_require=1 ORDER BY vm.store_id DESC LIMIT 0,1)='1','1','0') AS `required_options` FROM `catalog_product_flat_1` AS `e` WHERE (e.status = 1) AND (((e.sku LIKE 'YZB009%')))
SELECT `e`.`entity_id`, `e`.`type_id`, `e`.`attribute_set_id`, `e`.`entity_id`, `e`.`attribute_set_id`, `e`.`type_id`, `e`.`cost`, `e`.`created_at`, `e`.`gift_message_available`, `e`.`has_options`, `e`.`image_label`, `e`.`is_recurrgni`, `e`.`links_exist`, `e`.`links_purchased_separately`, `e`.`links_title`, `e`.`msrp`, `e`.`msrp_display_actual_price_type`, `e`.`msrp_enabled`, `e`.`name`, `e`.`news_from_date`, `e`.`news_to_date`, `e`.`open_amount_max`, `e`.`open_amount_min`, `e`.`price`, `e`.`price_type`, `e`.`price_view`, `e`.`recurrgni_profile`, `e`.`required_options`, `e`.`shipment_type`, `e`.`short_description`, `e`.`sku`, `e`.`sku_type`, `e`.`small_image`, `e`.`small_image_label`, `e`.`special_from_date`, `e`.`special_price`, `e`.`special_to_date`, `e`.`tax_class_id`, `e`.`thumbnail`, `e`.`thumbnail_label`, `e`.`updated_at`, `e`.`url_key`, `e`.`url_path`, `e`.`visibility`, `e`.`weight`, `e`.`weight_type`, `e`.`apparel_type`, `e`.`apparel_type_value`, `e`.`gender`, `e`.`gender_value`, `e`.`status`, `e`.`affirm_product_mfp`, `e`.`affirm_product_mfp_end_date`, `e`.`affirm_product_mfp_priority`, `e`.`affirm_product_mfp_start_date`, `e`.`affirm_product_mfp_type`, `e`.`affirm_product_promo_id`, `e`.`backorder_time`, `e`.`body_gender`, `e`.`body_gender_value`, `e`.`carat_weight_decimal`, `e`.`carat_weight_fraction`, `e`.`carat_weight_fraction_value`, `e`.`carat_weight_fraction_filter`, `e`.`carat_weight_fraction_filter_value`, `e`.`delivery_date_estimation`, `e`.`diamond_color`, `e`.`diamond_color_value`, `e`.`diamond_cut`, `e`.`diamond_quality`, `e`.`diamond_quality_value`, `e`.`diamond_type`, `e`.`diamond_type_value`, `e`.`family`, `e`.`family_value`, `e`.`family_name`, `e`.`gold_color`, `e`.`gold_color_value`, `e`.`gold_purity_color`, `e`.`gold_purity_color_value`, `e`.`gold_weight`, `e`.`head`, `e`.`head_value`, `e`.`karat`, `e`.`karat_value`, `e`.`number_of_diamonds`, `e`.`popularity_by_rating`, `e`.`popularity_by_reviews`, `e`.`popularity_by_sells`, `e`.`rgni_fit`, `e`.`rgni_width`, `e`.`rgni_width_nofilter`, `e`.`setting_type`, `e`.`set_breakdown`, `e`.`video1`, `e`.`video2`, `e`.`extended_description`, `e`.`description`, `e`.`image`, `e`.`color`, `e`.`color_value`, `e`.`googleshopping_exclude`, `e`.`manufacturer`, `e`.`manufacturer_value`, `e`.`res_number_of_rgnis`, `e`.`res_number_of_rgnis_value`, `e`.`rss`, `e`.`rss_value`, `e`.`rp`, `e`.`rp_value`, `e`.`esp`, `e`.`esp_value`, `e`.`sale`, `e`.`sale_value`, `e`.`deal_status`, `e`.`rgni_width_engmnt_rgni`, `e`.`rgni_width_engmnt_rgni_value`, `e`.`rgni_width_ladies_rgni_width`, `e`.`rgni_width_ladies_rgni_width_value`, `e`.`delivery`, `e`.`delivery_by`, `e`.`delivery_by_backorder`, `e`.`center_carat_weight`, `e`.`item_type`, `e`.`dynamic_stock_value`, `e`.`head_shape`, `e`.`head_shape_value`, `e`.`mstn_wttc`, `e`.`mstn_wttc_decimal`, `e`.`mstn_dimension`, `e`.`mstn_type`, `e`.`mstn_type_value`, `e`.`total_carat_weight`, `e`.`total_carat_weight_decimal`, `e`.`mstn_type_multi`, `e`.`rgni_fit_type_engmnt`, `e`.`rgni_fit_type_engmnt_value`, `e`.`rgni_fit_type_ladies`, `e`.`rgni_fit_type_ladies_value`, `e`.`rgni_fit_type_mens`, `e`.`rgni_fit_type_mens_value`, `e`.`rgni_width_mens_rgni_width`, `e`.`rgni_width_mens_rgni_width_value`, `e`.`engmnt_carat_total_weight`, `e`.`engmnt_carat_total_weight_value`, `e`.`head_shape_category`, `e`.`head_shape_category_value`, `e`.`ladies_carat_total_weight`, `e`.`ladies_carat_total_weight_value`, `e`.`mens_carat_total_weight`, `e`.`mens_carat_total_weight_value`, `e`.`has_active_deal`, `e`.`live_deal_status`, `e`.`deal_price`, `e`.`live_deal_value`, `e`.`rgni_highlights`, `e`.`extend_sync`, IF(( SELECT vm.view_mode AS required_options FROM catalog_product_option AS cpo LEFT JOIN mageworx_custom_options_option_view_mode AS vm ON cpo.option_id=vm.option_id AND (vm.store_id='1' OR vm.store_id=0) WHERE e.entity_id=cpo.product_id AND cpo.is_require=1 ORDER BY vm.store_id DESC LIMIT 0,1)='1','1','0') AS `required_options` FROM `catalog_product_flat_1` AS `e` WHERE (e.status = 1) AND (e.sku = 'YZB009KO') LIMIT 1
All queries captured during the incident are SELECTs, I didn't spot any UPDATES or INSERTS. The most significant part of them is related to flat catalog and options.
MySQL query cache limits are:
query_cache_size=512M
query_cache_limit=16M
Also, I've dumped index_process and cron_schedule during the incident - index and cron tasks were not running during the downtime.
I know it's very hard to give the exact answer in this kind of case.
What steps do I have to take in order to diagnose that?
Is there any MySQL monitoring tool that may give more insights on that?
