Оптимизация SQL запросов

[tarvolok]

Здравствуйте, помогите кто чем может :) слишком большой отклик  у некоторых запросов к базе sql, сервер выделенный виртуальный, трафика нет (отладка магаза) но при этому на открытие страницы уходит под 5-7 секунд, установил панель отладки которая показала большой отклик для таких вот запросов:

23 	

SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_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 IN (521)

/var/www/tarvolok/data/www/site.com/catalog/model/catalog/product.php (545)	0.20478 s
24 	

SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_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 IN (519)

/var/www/tarvolok/data/www/site.com/catalog/model/catalog/product.php (545)	0.21754 s
25 	

SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_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 IN (517)

/var/www/tarvolok/data/www/site.com/catalog/model/catalog/product.php (545)	0.22328 s
26 	

SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_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 IN (73)

/var/www/tarvolok/data/www/site.com/catalog/model/catalog/product.php (545)	0.21217 s
27 	

SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_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 IN (75)

/var/www/tarvolok/data/www/site.com/catalog/model/catalog/product.php (545)	0.19892 s
28 	

SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_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 IN (74)

/var/www/tarvolok/data/www/site.com/catalog/model/catalog/product.php (545)	0.19897 s
29 	

SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_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 IN (482)

/var/www/tarvolok/data/www/site.com/catalog/model/catalog/product.php (545)	0.19218 s
30 	

SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_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 IN (484)

/var/www/tarvolok/data/www/site.com/catalog/model/catalog/product.php (545)	0.18633 s

ускорялок никаких не ставил, размер таблицы oc_product_description 60Мб, таблицы oc_product 7Мб (32 000 товаров) остальные запросы выполняются за тысячные доли секунды.  Если у кого есть мысли куда копать - подскажите, всем откликрувшимся спасибо.

[Vladzimir]

Отключите в админке подсчет товаров в категории.

[tarvolok]

Отключите в админке подсчет товаров в категории.

Естественно, подсчёт отключён, я вот думаю а остаются ли запросы к базе относительно подсчёта этих товаров, например, что значит этот кусочек кода: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product ?

[Vladzimir]

Какие еще модули установлены? Где-то идет обращение к модели где идет подсчет товаров.

[tarvolok]

Отключил все модули, все вкумод удалил, всё равно запросы остаются.

[deim]

А сколько у вас категорий на сайте? Мы недавно на такое натыкались у товарища noVe https://opencartforum.com/topic/23340-uskorit-rabotu-saita-na-ocstore-1541-poriadka-50-000-tovarov/

Может быть и вам поможет.

Кстати, ссылку бы дали ;)

[DAN]

Естественно, подсчёт отключён, я вот думаю а остаются ли запросы к базе относительно подсчёта этих товаров, например, что значит этот кусочек кода: SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_product ?

Как вариант это может быть подсчет для pagination

[tarvolok]

сайт находится на домене office-dom.com там сейчас активирован плагин для отладки, и все модули выключены.

 

Как вариант это может быть подсчет для pagination

Что за pagination? его возможно отключить без последствий?

[deim]

Что за pagination? его возможно отключить без последствий?

Это постраничная навигация в категориях и т.д.

[DAN]

 

Что за pagination? его возможно отключить без последствий?

Возможно, комментировать в controller и template все что связано с pagination. Но тогда пропадет навигация по страницам в категориях и т.д. А это уже с последствиями :-)  для юзабилити.

[Vladzimir]

Вы себе однозначно поставили левую сборку, ибо в коде есть

<a href="[вц]">модули opencart</a>

Змінено 25 листопада 2013 користувачем freelancer
варез

[tarvolok]

Спасибо, друзья что откликнулись, решение найдено! И оно, как обычно, довольно банально. На скрине и в логах длинных запросов можно прочитать что там идёт запрос на вывод определённых категорий, путём не сложных дидуктивных рассуждений я понял что это иды тех самых категорий которые у меня должны выводиться в главном меню, что горизонтальное, однако даже при выключенном в дизайне меню эти запросы всё равно создаются и сильно нагружают базу.

Решение: Каталог/категории/изменить/данные и снять галочку с чекбокса "главное меню".

 

 

 

Вы себе однозначно поставили левую сборку, ибо в коде есть

<a href="http://myopencart.net/forum/files/">модули opencart</a>

 

Где именно этот код? На главной я не нашёл, возможно он относился к конкретному модулю. Дистриб уже перелопачен так, что стандартным его не назвать...

[Vladzimir]

office-dom.com/index.php?route=product/category&path=480_485 в исходном коде страницы.

[tarvolok]

Да, действительно помимо всего прочего ещё и инфицирован файл response.php, видимо последствия апрельских атак.  Восстановил его из оригинального дистрибутива, благодарю за наводку.

[acer]

Спасибо, друзья что откликнулись, решение найдено! И оно, как обычно, довольно банально. На скрине и в логах длинных запросов можно прочитать что там идёт запрос на вывод определённых категорий, путём не сложных дидуктивных рассуждений я понял что это иды тех самых категорий которые у меня должны выводиться в главном меню, что горизонтальное, однако даже при выключенном в дизайне меню эти запросы всё равно создаются и сильно нагружают базу.

Решение: Каталог/категории/изменить/данные и снять галочку с чекбокса "главное меню".

И что же это за решение проблемы?

Если категория должна быть в главном меню, то зачем снимать галочку "Главное меню"? )

[Ilari]

Решение: Каталог/категории/изменить/данные и снять галочку с чекбокса "главное меню".

 

Присоединяюсь к последнему вопросу, что это за решения, может мы его неправильно поняли?

 

У меня тоже такая проблема, почему-то сайт генерирует оч большую нагрузку, до 100к cp,

Может вы подскажете? хостинг прислал проблемный запрос:

SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_category_path cp LEFT JOIN oc_product_to_category p2c ON (cp.category_id = p2c.category_id) LEFT JOIN oc_product p ON (p2c.product_id = p.product_id) LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '63'

 

exaplin запроса:

[email protected][artilari_olive8e]> explain SELECT COUNT(DISTINCT p.product_id) AS total FROM oc_category_path cp LEFT JOIN oc_product_to_category p2c ON (cp.category_id = p2c.category_id) LEFT JOIN oc_product p ON (p2c.product_id = p.product_id) LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOIN oc_product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND cp.path_id = '63'; +----+-------------+-------+------------+--------+---------------------+---------+---------+----------------------------------------+-------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------------+---------+---------+----------------------------------------+-------+----------+--------------------------+ | 1 | SIMPLE | p2s | NULL | index | PRIMARY | PRIMARY | 8 | NULL | 20056 | 10.00 | Using where; Using index | | 1 | SIMPLE | p | NULL | eq_ref | PRIMARY | PRIMARY | 4 | artilari_olive8e.p2s.product_id | 1 | 5.00 | Using where | | 1 | SIMPLE | pd | NULL | eq_ref | PRIMARY | PRIMARY | 8 | artilari_olive8e.p2s.product_id,const | 1 | 100.00 | Using index | | 1 | SIMPLE | p2c | NULL | ref | PRIMARY,category_id | PRIMARY | 4 | artilari_olive8e.p2s.product_id | 3 | 100.00 | Using where; Using index | | 1 | SIMPLE | cp | NULL | eq_ref | PRIMARY | PRIMARY | 8 | artilari_olive8e.p2c.category_id,const | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+---------------------+---------+---------+------

[ArtemPitov]

По поводу подсчета, он просто так не отключает, нужно править еще категории если я не ошибаюсь 

[deim]

Ilari, какая версия движка? сколько товаров, сколько категорий?

[druzhkov]

С этими SELECT(COUNT(...)) в опенкарте действительно проблемы. Мы у клиента лечили установкой кеширования: http://e-45.ru/articles/%D0%9E%D0%BF%D1%82%D0%B8%D0%BC%D0%B8%D0%B7%D0%B0%D1%86%D0%B8%D1%8F-opencart-1_5

В принципе, процедура несложная: смотрим в профайлере тормозные запросы, ищем код, оборачиваем в кеш, потом следующий и т.д.

[Vladzimir]

С этими SELECT(COUNT(...)) в опенкарте действительно проблемы. Мы у клиента лечили установкой кеширования: http://e-45.ru/articles/%D0%9E%D0%BF%D1%82%D0%B8%D0%BC%D0%B8%D0%B7%D0%B0%D1%86%D0%B8%D1%8F-opencart-1_5

В принципе, процедура несложная: смотрим в профайлере тормозные запросы, ищем код, оборачиваем в кеш, потом следующий и т.д.

Кэширование - хорошо, но не тогда когда у вас есть сортировка по наличию.

[druzhkov]

Кэширование - хорошо, но не тогда когда у вас есть сортировка по наличию.

 

Как можно заметить, ключ кеша равен md5 от всех поступивших в метод параметров, в том числе и от сортировки. Соответственно, для разных сортировок будут разные кеши.

[Vladzimir]

Как можно заметить, ключ кеша равен md5 от всех поступивших в метод параметров, в том числе и от сортировки. Соответственно, для разных сортировок будут разные кеши.

А откуда вы узнаете что она изменилась? Сортировка имелась ввиду что запросом такие товары сортируются вперед в наличии назад нет в наличии.

[ArtemPitov]

@Vladzimir, 

md5(http_build_query($data)); 

@druzhkov, Интересное решения, нужно будет поэкспериментировать  

[Vladzimir]

@Vladzimir, 

md5(http_build_query($data)); 

@druzhkov, Интересное решения, нужно будет поэкспериментировать  

И что это даст? data всегда будет одна и та же. А вот результат запроса всегда плавает.

[ArtemPitov]

И что это даст? data всегда будет одна и та же. А вот результат запроса всегда плавает.

$data['sort'] всегда разная будет и будет генерировать разный хеш 

 

мы же используем http_build_query