Whenever we send off a newsletter with say 10+ links to specific products, our server ends up overloading and our website ends up going down for 5-10 minutes until the server comes back up.
The newsletter/email blast is sent off to around 4000~ customers using godaddy. I reached out to my support and they found some logs;
I noticed that several queries which took more than 1 second to execute were logged in the MySQL slow log at 15:56 UTC time, for example:
and the logs posted;
# Time: 2024-09-26T15:56:16.957652Z
# User@Host: USER[HOST] @ localhost [] Id: 262056
# Schema: SHOP Last_errno: 0 Killed: 0
# Query_time: 3.999498 Lock_time: 0.000002 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0 Bytes_sent: 1039
SET timestamp=1727366172;
SELECT `main_table`.* FROM `cleversoft_megamenus` AS `main_table` WHERE (`is_active` = '1') AND (`identifier` = 'products');
# Time: 2024-09-26T15:56:16.957666Z
# User@Host: USER[HOST] @ localhost [] Id: 262074
# Schema: SHOP Last_errno: 0 Killed: 0
# Query_time: 4.091669 Lock_time: 0.000021 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0 Bytes_sent: 146
SET timestamp=1727366172;
SELECT `catalog_category_flat_store_1`.`entity_id` FROM `catalog_category_flat_store_1` WHERE (entity_id='650');
# Time: 2024-09-26T15:56:16.957667Z
# User@Host: USER[HOST] @ localhost [] Id: 262072
# Schema: SHOP Last_errno: 0 Killed: 0
# Query_time: 4.091260 Lock_time: 0.000013 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0 Bytes_sent: 520
SET timestamp=1727366172;
SELECT `indexer_state`.* FROM `indexer_state` WHERE (`indexer_state`.`indexer_id`='catalog_category_flat');
# Time: 2024-09-26T15:56:17.053093Z
# User@Host: USER[HOST] @ localhost [] Id: 262081
# Schema: SHOP Last_errno: 0 Killed: 0
# Query_time: 2.895144 Lock_time: 0.000002 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Bytes_sent: 86
SET timestamp=1727366174;
SELECT `cp`.`page_id` FROM `cms_page` AS `cp`
INNER JOIN `cms_page_store` AS `cps` ON cp.page_id = cps.page_id WHERE (cp.identifier = 'news-updates') AND (cps.store_id IN (0, '1')) AND (cp.is_active = 1) ORDER BY `cps`.`store_id` DESC
LIMIT 1;
# Time: 2024-09-26T15:56:17.053102Z
# User@Host: USER[HOST] @ localhost [] Id: 262068
# Schema: SHOP Last_errno: 0 Killed: 0
# Query_time: 4.187082 Lock_time: 0.000017 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Bytes_sent: 1685
SET timestamp=1727366172;
SELECT `main_table`.*, `default_option_title`.`title` AS `default_title`, `store_option_title`.`title` AS `store_title`, IF(store_option_title.title IS NULL, default_option_title.title, store_option_title.title) AS `title`, `default_option_price`.`price` AS `default_price`, `default_option_price`.`price_type` AS `default_price_type`, `store_option_price`.`price` AS `store_price`, `store_option_price`.`price_type` AS `store_price_type`, IF(store_option_price.price IS NULL, default_option_price.price, store_option_price.price) AS `price`, IF(store_option_price.price_type IS NULL, default_option_price.price_type, store_option_price.price_type) AS `price_type` FROM `catalog_product_option` AS `main_table`
INNER JOIN `catalog_product_entity` AS `cpe` ON cpe.entity_id = main_table.product_id
INNER JOIN `catalog_product_option_title` AS `default_option_title` ON default_option_title.option_id = main_table.option_id
LEFT JOIN `catalog_product_option_title` AS `store_option_title` ON store_option_title.option_i..;
# Time: 2024-09-26T15:56:17.053110Z
# User@Host: USER[HOST] @ localhost [] Id: 262070
# Schema: SHOP Last_errno: 0 Killed: 0
# Query_time: 4.187014 Lock_time: 0.000006 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0 Bytes_sent: 1685
SET timestamp=1727366172;
SELECT `main_table`.*, `default_option_title`.`title` AS `default_title`, `store_option_title`.`title` AS `store_title`, IF(store_option_title.title IS NULL, default_option_title.title, store_option_title.title) AS `title`, `default_option_price`.`price` AS `default_price`, `default_option_price`.`price_type` AS `default_price_type`, `store_option_price`.`price` AS `store_price`, `store_option_price`.`price_type` AS `store_price_type`, IF(store_option_price.price IS NULL, default_option_price.price, store_option_price.price) AS `price`, IF(store_option_price.price_type IS NULL, default_option_price.price_type, store_option_price.price_type) AS `price_type` FROM `catalog_product_option` AS `main_table`
INNER JOIN `catalog_product_entity` AS `cpe` ON cpe.entity_id = main_table.product_id
INNER JOIN `catalog_product_option_title` AS `default_option_title` ON default_option_title.option_id = main_table.option_id
LEFT JOIN `catalog_product_option_title` AS `store_option_title` ON store_option_title.option_i..;
# Time: 2024-09-26T15:56:17.053452Z
# User@Host: USER[HOST] @ localhost [] Id: 262071
# Schema: SHOP Last_errno: 0 Killed: 0
# Query_time: 4.187416 Lock_time: 0.000014 Rows_sent: 12 Rows_examined: 24 Rows_affected: 0 Bytes_sent: 1345
SET timestamp=1727366172;
SELECT `catalog_product_entity_tier_price`.`value_id` AS `price_id`, `catalog_product_entity_tier_price`.`website_id`, `catalog_product_entity_tier_price`.`all_groups`, `catalog_product_entity_tier_price`.`customer_group_id` AS `cust_group`, `catalog_product_entity_tier_price`.`value` AS `price`, `catalog_product_entity_tier_price`.`qty` AS `price_qty`, `catalog_product_entity_tier_price`.`percentage_value` FROM `catalog_product_entity_tier_price` WHERE (website_id = 0) AND (entity_id = '9378') ORDER BY `qty` ASC;
I am at a bit of a loss as to what to do next/with this information. Are there any glaring issues that could be bogging the server down from the information given?
The complete catalog has around 12000 products, using a flat catalog and category system.. on M2.3.5 and php7.3