0

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

UPDATE

After running another newsletter, and the server going down, looking into the access_log, a lot of it looks like normal traffic, however right before server went down, one thing that stood out was;

2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/u/l/ultras1.jpg HTTP/2.0" 200 8909 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/c/o/convmsdida4k.jpg HTTP/2.0" 200 6486 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/d/a/da.jpg HTTP/2.0" 200 8340 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmd-convntrmbasdian_3_.jpg HTTP/2.0" 200 6993 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmd-bmursaevfeyecup.jpg HTTP/2.0" 200 5306 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/v/i/vidhubsmartcp.jpg HTTP/2.0" 200 6555 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/1/1/113.jpg HTTP/2.0" 200 9329 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/s/d/sdiaudio.jpg HTTP/2.0" 200 7749 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/7/i/7inhdr.jpg HTTP/2.0" 200 18052 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmd-dv-rfl-audif.jpg HTTP/2.0" 200 7558 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/d/a/davincikeyboard1_1.jpg HTTP/2.0" 200 10395 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmd-hdl-smtwscopeduo4k2-1.jpg HTTP/2.0" 200 11587 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/u/p/updowncrosshd.jpg HTTP/2.0" 200 8731 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/p/k/pktcincam4k.jpg HTTP/2.0" 200 11919 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/a/t/atemminipro.jpg HTTP/2.0" 200 13253 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmd-swatemminisbpr.jpg HTTP/2.0" 200 9184 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/u/l/ultrahdmini.jpg HTTP/2.0" 200 7529 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/u/s/us4kminifront.jpg HTTP/2.0" 200 8388 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/s/d/sdi-hdmi-6g.jpg HTTP/2.0" 200 12009 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/c/o/convcmic_sh03g_wpsu_sdi_to_hdmi_3g_wpsu_1.jpg HTTP/2.0" 200 10473 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmccass-shade.jpg HTTP/2.0" 200 5600 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmd-bdlkdvqdhdmi4k.jpg HTTP/2.0" 200 11990 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/h/d/hdl-multip6g-16.jpg HTTP/2.0" 200 5707 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/t/e/teranex8k.jpg HTTP/2.0" 200 8649 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmd-hyperdavida035-1.jpg HTTP/2.0" 200 12809 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/p/s/psu12a.jpg HTTP/2.0" 200 8072 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/v/i/vidhubmastercp.jpg HTTP/2.0" 200 5946 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmd-hyperdavida037-1.jpg HTTP/2.0" 200 18763 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/4/b4.jpg HTTP/2.0" 200 5516 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/v/i/viewfinder1.jpg HTTP/2.0" 200 12932 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmd-swpaneladv2me.jpg HTTP/2.0" 200 15403 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/h/y/hyperdeckcontroller1.jpg HTTP/2.0" 200 13486 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/s/k/skbolts_1.jpg HTTP/2.0" 200 7515 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/c/o/convmcsaud4k.jpg HTTP/2.0" 200 6144 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/d/bdlkstudio4k.jpg HTTP/2.0" 200 11482 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/m/i/minirec4k.jpg HTTP/2.0" 200 8289 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmd-swatemminibpriso.jpg HTTP/2.0" 200 14439 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/a/u/auded.jpg HTTP/2.0" 200 15158 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/s/w/swtalgpi8.jpg HTTP/2.0" 200 4929 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/m/u/multiview4.jpg HTTP/2.0" 200 8493 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/b/m/bmd-psupply-12v30w.jpg HTTP/2.0" 200 9580 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/m/u/multiview4hd-1.jpg HTTP/2.0" 200 8398 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/d/e/deckli.jpg HTTP/2.0" 200 18724 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/5/i/5inhdr.jpg HTTP/2.0" 200 15218 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/c/o/convcmic_hs03g_hdmi_to_sdi_3g-3.jpg HTTP/2.0" 200 10695 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/s/d/sdidist.jpg HTTP/2.0" 200 7608 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT‮
2603:9001:3c00:7c08:b932:44b1:5975:3f94 XXXXX.com - [08/Nov/2024:15:16:37 +0000] "GET /cache/5b85ee3076fdd136ac49a93981b58561/a/t/atem1advanced1.jpg HTTP/2.0" 200 14546 "https://XXXXXX.com/xxxx.html" "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36" | TLSv1.3 | - - 0.000 - 0 NC:000000 UP:-DT

All of those requests in the same second seems like a lot (no other ip reacted that way in the access logs).. is there anything we could do to combat something like this?

Improve this question

2 Answers 2

Reset to default
0

if you are sending a lot of emails, this means that the DB will be hogged by that process to be able to send the emails. This is due to the reading and locking required to send the emails.

The information that you have is what queries are slow, but not why they get stuck. Usually this happens because the DB is locking the records to write the results of the email sending process.

Some questions to troubleshoot this:

  • Do you use an external service to do the email sending? Can you improve on that?
  • Can you move this process to a lower priority triggering the process with the "nice" command?
  • Can you batch the email sending or use the background workers to send the emails?
Improve this answer
10
  • We send our emails through what was madmimi, which is now Go daddy email marketing. So the only things that actually touch our website/server, are the links within the emails that will either point to a page on the website or a specific product page (example being like a shop now button on the email for a product which when clicked brings you to that product page) .. if we have over say 10 of those, this issue always happens, any less, it seems to be able to handle it. Could it possibly be bots spamming the server the second the emails are received?
    – ne0nlight
    Commented Nov 5 at 14:22
  • Are you sure that the service is not requesting the template to be generated from within Magento which might cause the spike in ussage? (never used madmimi). You should be able to deduct that from the access_log of the server on the timeframe that the system is under load
    – Serfe
    Commented Nov 7 at 18:45
  • That shouldn't be the case, we custom create the email template and the only things pointing to our server whatsoever would be say a link to our homepage, and the product page links, maybe just a couple images at times
    – ne0nlight
    Commented Nov 7 at 22:11
  • As stated before, to know which URL are been crawled, you need to have access to the access_log file from your control panel. there you should see the user agent of every request and confirm if the bots are the ones responsible for the high server usage. Also, might be the moment when the server is running the backup on the database and site which might lead to that.
    – Serfe
    Commented Nov 8 at 13:44
  • Sure thing, we are sending out a similar newsletter momentarily that has plenty of links pointing to the product pages, so for all intents and purposes .. this will likely take our server down. However at that point I'll be able to check the access_log as to what was going on from there. Will report back soon!
    – ne0nlight
    Commented Nov 8 at 14:37
0

I think you would need to optimise the email content itself to get any observable difference on server load. It would highly depend on what are you sending in the email blast.

If the the email content is not personalised based on the customer I would recommend generating a static html of the the content (even if the content itself is dynamically generated, if the email content doesn't vary based on customer you should use a single static html once it's generated). This way the only load on the server will be of sending the static html as email instead of every email querying DB for the same thing over and over.

Also regarding the access.log, you should closely look into the urls and check if the urls are the media and content used in the emails themselves. If it's that, it's actually a good thing as customers are opening your emails and that maybe generating those logs.

Improve this answer

Your Answer

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

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