Jump to content
Search In
  • More options...
Find results that contain...
Find results in...
  • Sign Up

Проблема с медленными запросами, проходивших особенно долгое время и создающих дополнительную нагрузку на сервер


Recommended Posts

Проблема с медленными запросами, проходивших особенно долгое время и создающих дополнительную нагрузку на сервер.

Ничего не менял уже давно, но тут вдруг за последние два дня выросла нагрузка на сервер, и хостер (sweb) отключил базу.

Около 5к товаров и 20 категорий. Версия OC 1.5.3.1 

Подскажите где и что копать.

Пока залил копию на денвер. Был модуль сортировки товаров по атрибутам, пока его отключил, и отключил отображение количества товаров в категориях.

на хостинге sweb параметр long_query_time равен 15

Как проверить на денвере, изменилась ли ситуация? Можно уже залить базу на хостинг и проверять?

 

Примеры медленных запросов от саппорта sweb которые возникли пару дней назад:

 

 

# User@Host: zesstru[zesstru] @ localhost []
# Query_time: 16.643319 Lock_time: 0.000404 Rows_sent: 1 Rows_examined:
15548
use zesstru;
SET timestamp=1369147421;
SELECT COUNT(DISTINCT p.product_id) AS total FROM prefdbproduct p LEFT JOIN
prefdbproduct_description pd ON (p.product_id = pd.product_id) LEFT JOIN
prefdbproduct_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN
prefdbproduct_attribute atra_4 ON (p.product_id = atra_4.product_id) LEFT JOIN
prefdbproduct_to_category p2c ON (p.product_id = p2c.product_id) WHERE
pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND
p2s.store_id = '0' AND (atra_4.language_id = '1' AND atra_4.attribute_id = '4'
AND atra_4.text IN ('20.0-29.9мм')) AND p2c.category_id = '77';

# User@Host: zesstru[zesstru] @ localhost []
# Query_time: 17.647776 Lock_time: 0.000323 Rows_sent: 1 Rows_examined:
15566
use zesstru;
SET timestamp=1369147338;
SELECT COUNT(DISTINCT p.product_id) AS total FROM prefdbproduct p LEFT JOIN
prefdbproduct_description pd ON (p.product_id = pd.product_id) LEFT JOIN
prefdbproduct_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN
prefdbproduct_to_category p2c ON (p.product_id = p2c.product_id) WHERE
pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND
p2s.store_id = '0' AND (p2c.category_id = '51');

# User@Host: zesstru[zesstru] @ localhost []
# Query_time: 17.171950 Lock_time: 0.000557 Rows_sent: 30 Rows_examined:
15648
use zesstru;
SET timestamp=1369147338;
SELECT p.product_id, (SELECT AVG(rating) AS total FROM prefdbreview r1 WHERE
r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS
rating FROM prefdbproduct p LEFT JOIN prefdbproduct_description pd ON
(p.product_id = pd.product_id) LEFT JOIN prefdbproduct_to_store p2s ON
(p.product_id = p2s.product_id) LEFT JOIN prefdbproduct_attribute atra_5 ON
(p.product_id = atra_5.product_id) LEFT JOIN prefdbproduct_to_category p2c ON
(p.product_id = p2c.product_id) WHERE pd.language_id = '1' AND p.status = '1'
AND p.date_available <= NOW() AND p2s.store_id = '0' AND (atra_5.language_id =
'1' AND atra_5.attribute_id = '5' AND atra_5.text IN ('Античная латунь')) AND
(p2c.category_id = '77') AND (atra_5.language_id = '1' AND atra_5.attribute_id
= '5' AND atra_5.text IN ('Античная латунь')) GROUP BY p.product_id ORDER BY
p.sort_order ASC LIMIT 0,30;

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...

Important Information

On our site, cookies are used and personal data is processed to improve the user interface. To find out what and what personal data we are processing, please go to the link. If you click "I agree," it means that you understand and accept all the conditions specified in this Privacy Notice.