1

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?

2
  • Additional information request, please. Any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: A) SELECT COUNT(*) FROM information_schema.tables; B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; not SHOW STATUS, just STATUS; G) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. Dec 25, 2021 at 20:54
  • Could you also post the last 400 lines of your error log for analysis? Dec 25, 2021 at 21:06

1 Answer 1

0

Mystery resolved. It was the facebookexternalhit bot (facebookexternalhit/1.1 +http //www.facebook.com/externalhit_uatext.php)".

Overall, that site is receiving huge amounts of bot traffic, but usually hits are distributed equally in time. In case of FB, the site was receiving a large number of hits in short periods of time. Thousands of hits during 1-3 min periods, every 5 hours.

After applying rate limiting to that one particular bot, downtimes are no longer happening and there are no spikes on DB usage.

Your Answer

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

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