Модуль многоуровневой навигации для версии 1.4.11 грузит сильно базу

lightman555

lightman555

Гуру форума
Регистрация
7 Мар 2013
Сообщения
375
Реакции
65
Сегодня стал недоступен сайт на какой то время из-за нагрузки на базу (хостинг на vps)
Служба поддержки написала:
По истории видим только то, что в указанный периода была высокая нагрузка на MySQL.
Его процессы использовали около 90% ресурсов.
 
запросы которые грузили базу на другом хостинге были такого вида:
SET timestamp=1409984287;
SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name
FROM `ps_category_product` cp
INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)
INNER JOIN ps_product p ON (p.id_product = cp.id_product)
INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4
AND psi.price_min <= 288000 AND psi.price_max >= 176)
WHERE
c.nleft >= 5
AND c.nright <= 6
AND c.active = 1
AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049)
GROUP BY p.id_manufacturer;

SET timestamp=1409984285;
SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,
COUNT(DISTINCT p.id_product) nbr,
lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name
FROM ps_feature_product fp
INNER JOIN ps_product p ON (p.id_product = fp.id_product)
LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)
INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))
LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)
LEFT JOIN ps_layered_indexable_feature_lang_value lifl
ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)
LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl
ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)
INNER JOIN `ps_layered...
SET timestamp=1409984285;
SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,
COUNT(DISTINCT p.id_product) nbr,
lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name
FROM ps_feature_product fp
INNER JOIN ps_product p ON (p.id_product = fp.id_product)
LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)
INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))
LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)
LEFT JOIN ps_layered_indexable_feature_lang_value lifl
ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)
LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl
ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)
INNER JOIN `ps_layered...
SET timestamp=1409984284;
SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,
COUNT(DISTINCT p.id_product) nbr,
lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name
FROM ps_feature_product fp
INNER JOIN ps_product p ON (p.id_product = fp.id_product)
LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)
INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))
LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)
LEFT JOIN ps_layered_indexable_feature_lang_value lifl
ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)
LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl
ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)
INNER JOIN `ps_layered...
SET timestamp=1409984284;
SELECT fl.name feature_name, fp.id_feature, fv.id_feature_value, fvl.value,
COUNT(DISTINCT p.id_product) nbr,
lifl.url_name name_url_name, lifl.meta_title name_meta_title, lifvl.url_name value_url_name, lifvl.meta_title value_meta_title , psi.price_min, psi.price_max, m.name
FROM ps_feature_product fp
INNER JOIN ps_product p ON (p.id_product = fp.id_product)
LEFT JOIN ps_feature_lang fl ON (fl.id_feature = fp.id_feature AND fl.id_lang = 6)
INNER JOIN ps_feature_value fv ON (fv.id_feature_value = fp.id_feature_value AND (fv.custom IS NULL OR fv.custom = 0))
LEFT JOIN ps_feature_value_lang fvl ON (fvl.id_feature_value = fp.id_feature_value AND fvl.id_lang = 6)
LEFT JOIN ps_layered_indexable_feature_lang_value lifl
ON (lifl.id_feature = fp.id_feature AND lifl.id_lang = 6)
LEFT JOIN ps_layered_indexable_feature_value_lang_value lifvl
ON (lifvl.id_feature_value = fp.id_feature_value AND lifvl.id_lang = 6)
INNER JOIN `ps_layered...
SET timestamp=1409984277;
SELECT p.`id_product` id_product
FROM `ps_product` p
INNER JOIN `ps_layered_price_index` psi
ON
(
psi.price_min >= 176
AND psi.price_max <= 288000
AND psi.`id_product` = p.`id_product`
AND psi.`id_currency` = 4
)
INNER JOIN ps_category_product cp
ON p.id_product = cp.id_product
INNER JOIN ps_category c ON (c.id_category = cp.id_category AND
c.nleft >= 5
AND c.nright <= 6
AND c.active = 1)
WHERE 1 AND p.`active` = 1 AND p.id_product IN (SELECT `id_product` FROM `ps_feature_product` fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050) GROUP BY id_product;

SET timestamp=1409984274;
SELECT p.`id_product` id_product
FROM `ps_product` p
INNER JOIN `ps_layered_price_index` psi
ON
((psi.price_min < 176 AND psi.price_max > 176)
OR
(psi.price_max > 288000 AND psi.price_min < 288000))
AND psi.`id_product` = p.`id_product`
AND psi.`id_currency` = 4
INNER JOIN ps_category_product cp
ON p.id_product = cp.id_product
INNER JOIN ps_category c ON (c.id_category = cp.id_category AND
c.nleft >= 5
AND c.nright <= 6
AND c.active = 1)
WHERE 1 AND p.`active` = 1 AND p.id_product IN (SELECT `id_product` FROM `ps_feature_product` fp WHERE fp.`id_feature_value` = 217049) GROUP BY id_product;

SET timestamp=1409984269;
SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name
FROM `ps_category_product` cp
INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)
INNER JOIN ps_product p ON (p.id_product = cp.id_product)
INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4
AND psi.price_min <= 61038 AND psi.price_max >= 3335)
WHERE
c.nleft >= 3
AND c.nright <= 4
AND c.active = 1
AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050)
GROUP BY p.id_manufacturer;

SET timestamp=1409984268;
SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name
FROM `ps_category_product` cp
INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)
INNER JOIN ps_product p ON (p.id_product = cp.id_product)
INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4
AND psi.price_min <= 61038 AND psi.price_max >= 3335)
WHERE
c.nleft >= 3
AND c.nright <= 4
AND c.active = 1
AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050)
GROUP BY p.id_manufacturer;

SET timestamp=1409984268;
SELECT m.name, COUNT(DISTINCT p.id_product) nbr, m.id_manufacturer , psi.price_min, psi.price_max, m.name
FROM `ps_category_product` cp
INNER JOIN `ps_category` c ON (c.id_category = cp.id_category)
INNER JOIN ps_product p ON (p.id_product = cp.id_product)
INNER JOIN ps_manufacturer m ON (m.id_manufacturer = p.id_manufacturer)
INNER JOIN `ps_layered_price_index` psi ON (psi.id_product = p.id_product AND psi.id_currency = 4
AND psi.price_min <= 61038 AND psi.price_max >= 3335)
WHERE
c.nleft >= 3
AND c.nright <= 4
AND c.active = 1
AND p.active = 1 AND p.id_product IN (SELECT id_product FROM ps_feature_product fp WHERE fp.`id_feature_value` = 217049 OR fp.`id_feature_value` = 217050)
GROUP BY p.id_manufacturer;
 
хостер предлагает установить memcached - это поможет?

и сложно ли его настроить в админке?
В админке магазина нужно по идее только перключить галочку и все (с учетом что уже кеш установлен на серваке)?
вот скриншот:
Для просмотра ссылки Войди или Зарегистрируйся
 
В общем активировал я этот мемкешед Для просмотра ссылки Войди или Зарегистрируйся
стало по лучше но все же тема актуальна до сих пор
так же интересно как сделать чтобы так же как и с ценой появился еще ползунок возможности выбирать по количеству лампочек (в данном моем случае) - было бы очень удобно для клиентов
 
Нужно как здесь сделать еще один ползунок аналогичный как у цены Для просмотра ссылки Войди или Зарегистрируйся
кто поможет?
 
Войдите или зарегистрируйтесь для ответа.
Назад
Сверху