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

Тема в разделе "PrestaShop", создана пользователем lightman555, 10 сен 2014.

Информация :
Хочешь в разделе без проблем общаться - прочти правила, чтобы потом с форумом на время не прощаться. Читать обязательно!
Внимание! Читайте внимательно правила!Предупреждения выдаются без всяких вопросов. Нарушил - получил. Будьте Внимательнее! Постинг нескольких постов подряд приравнивается к НАРУШЕНИЮ! Будьте вежливы и соблюдайте правила ;-)
Модераторы: ZiX
  1. lightman555

    lightman555

    Регистр.:
    7 мар 2013
    Сообщения:
    206
    Симпатии:
    7
    Сегодня стал недоступен сайт на какой то время из-за нагрузки на базу (хостинг на vps)
    Служба поддержки написала:
    По истории видим только то, что в указанный периода была высокая нагрузка на MySQL.
    Его процессы использовали около 90% ресурсов.
     
  2. lightman555

    lightman555

    Регистр.:
    7 мар 2013
    Сообщения:
    206
    Симпатии:
    7
    запросы которые грузили базу на другом хостинге были такого вида:
    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;
     
  3. lightman555

    lightman555

    Регистр.:
    7 мар 2013
    Сообщения:
    206
    Симпатии:
    7
    хостер предлагает установить memcached - это поможет?

    и сложно ли его настроить в админке?
    В админке магазина нужно по идее только перключить галочку и все (с учетом что уже кеш установлен на серваке)?
    вот скриншот:
    https://yadi.sk/i/NXs9j1habNpYM
     
  4. lightman555

    lightman555

    Регистр.:
    7 мар 2013
    Сообщения:
    206
    Симпатии:
    7
    В общем активировал я этот мемкешед https://yadi.sk/i/tGnPZG5cbq9vP
    стало по лучше но все же тема актуальна до сих пор
    так же интересно как сделать чтобы так же как и с ценой появился еще ползунок возможности выбирать по количеству лампочек (в данном моем случае) - было бы очень удобно для клиентов
     
  5. lightman555

    lightman555

    Регистр.:
    7 мар 2013
    Сообщения:
    206
    Симпатии:
    7
    Нужно как здесь сделать еще один ползунок аналогичный как у цены https://yadi.sk/i/JHQAyQbCbqJwP
    кто поможет?