Форумчане, нужна помощь.
Сделали сайт, товаров до сотни, начали наполнять и вросла нагрузка на БД в сотни раз до 300к cp при лимите хостинга 2,5к. Отключил фильтр стало 250к cp
В логах два типа медленных запросов:
SELECT p.`manufacturer_id`, 1 AS total FROM `oc_product` p JOIN `oc_product_to_category` p2c ON (p.`product_id` = p2c.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` IN (87) AND (pov.`quantity` > 0)) tofv0 ON (p.`product_id` = tofv0.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` IN (152) AND (pov.`quantity` > 0)) tofv1 ON (p.`product_id` = tofv1.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` IN (195) AND (pov.`quantity` > 0)) tofv2 ON (p.`product_id` = tofv2.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` IN (202) AND (pov.`quantity` > 0)) tofv3 ON (p.`product_id` = tofv3.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` IN (292) AND (pov.`quantity` > 0)) tofv4 ON (p.`product_id` = tofv4.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` IN (408) AND (pov.`quantity` > 0)) tofv5 ON (p.`product_id` = tofv5.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` IN (518) AND (pov.`quantity` > 0)) tofv6 ON (p.`product_id` = tofv6.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` IN (524) AND (pov.`quantity` > 0)) tofv7 ON (p.`product_id` = tofv7.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` IN (527) AND (pov.`quantity` > 0)) tofv8 ON (p.`product_id` = tofv8.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` IN (536) AND (pov.`quantity` > 0)) tofv9 ON (p.`product_id` = tofv9.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` IN (537) AND (pov.`quantity` > 0)) tofv10 ON (p.`product_id` = tofv10.`product_id`) WHERE p2c.`category_id` IN (17) AND p.`status` = 1 GROUP BY p.`manufacturer_id`\G
SELECT SQL_CALC_FOUND_ROWS p.* ,pd.* , NULL AS special ,p.`stock_status_id` AS stock_status , p.`price` AS price_sort ,0 AS rating,0 AS reviews FROM `oc_product` p LEFT JOIN `oc_product_description` pd ON (p.`product_id` = pd.`product_id`) JOIN `oc_product_to_category` p2c ON (p.`product_id` = p2c.`product_id`) JOIN `oc_product_option_value` pov ON (p.`product_id` = pov.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` = 152 AND (pov.`quantity` > 0)) tofv1 ON (p.`product_id` = tofv1.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` = 175 AND (pov.`quantity` > 0)) tofv2 ON (p.`product_id` = tofv2.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` = 202 AND (pov.`quantity` > 0)) tofv3 ON (p.`product_id` = tofv3.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` = 204 AND (pov.`quantity` > 0)) tofv4 ON (p.`product_id` = tofv4.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` = 249 AND (pov.`quantity` > 0)) tofv5 ON (p.`product_id` = tofv5.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` = 299 AND (pov.`quantity` > 0)) tofv6 ON (p.`product_id` = tofv6.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` = 356 AND (pov.`quantity` > 0)) tofv7 ON (p.`product_id` = tofv7.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` = 362 AND (pov.`quantity` > 0)) tofv8 ON (p.`product_id` = tofv8.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` = 439 AND (pov.`quantity` > 0)) tofv9 ON (p.`product_id` = tofv9.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` = 519 AND (pov.`quantity` > 0)) tofv10 ON (p.`product_id` = tofv10.`product_id`) JOIN (SELECT DISTINCT pov.`product_id` FROM `oc_product_option_value` pov WHERE pov.`option_id` = 16 AND pov.`option_value_id` = 534 AND (pov.`quantity` > 0)) tofv11 ON (p.`product_id` = tofv11.`product_id`) WHERE p2c.`category_id` = 17 AND pov.`option_id` = 16 AND pov.`option_value_id` = 88 AND (pov.`quantity` > 0) AND (pov.`quantity` > 0) AND p.`status` = 1 AND pd.`language_id` = 1 GROUP BY p.`product_id` ORDER BY (p.`quantity` > 0) DESC, LCASE(pd.`name`) LIMIT 0,15\G
Отключил две категории на сайте, которые начали наполнять (экипировка и запчасти) и нагрузка упала до нуля
Что может генерировать такую нагрузку и куда копать?
ocStore 2.3.0.2
