Magento 1: MySQL failing on flat catalog / locked tables

I’m running quite old Magento 1.9.2 instance, since 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 server stops responding. DB server has 32GB of RAM and 16 CPUs. Incidents are happening 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 MySQL process list during one of the incidents. Here’s the list of tables in use (top 30):

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 spotted any UPDATES or INSERTS. The most significant part of them are 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 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 which may give more insights on that?