Медленные запросы

[Mila80]

Здравствуйте. Помогите пожалуйста, кто может, Долго грузятся прайсы с товарами, больше 12 000 шт., при загрузке большого кол-ва выкидывает с админки с ошибкой 504 Gateway Time-out.

Обнаружила медленные запросы, больше недели с одного IP ежедневно по 6-8 шт., время 6-9 секунд.

Один из:

SELECT (IF (LCASE(pd.name) LIKE '%goodyear ultra grip ice 2%', 60, 0) + IF (LCASE(pd.name) LIKE'goodyear%', 80, 0) + IF (LCASE(pd.name) LIKE '%goodyear%', 8, 0) + IF (LCASE(pd.name) LIKE 'ultra%', 80, 0) + IF (LCASE(pd.name) LIKE '%ultra%', 8, 0) + IF (LCASE(pd.name) LIKE 'grip%', 80, 0) + IF (LCASE(pd.name) LIKE'%grip%', 8, 0) + IF (LCASE(pd.name) LIKE 'ice%', 80, 0) + IF (LCASE(pd.name) LIKE '%ice%', 8, 0) + IF(LCASE(pd.name) LIKE '2%', 80, 0) + IF (LCASE(pd.name) LIKE '%2%', 8, 0) + IF (LCASE(pd.description) LIKE'%goodyear ultra grip ice 2%', 20, 0) + IF (LCASE(pd.description) LIKE '%goodyear%', 2, 0) + IF(LCASE(pd.description) LIKE '%ultra%', 2, 0) + IF (LCASE(pd.description) LIKE '%grip%', 2, 0) + IF(LCASE(pd.description) LIKE '%ice%', 2, 0) + IF (LCASE(pd.description) LIKE '%2%', 2, 0) + IF (LCASE(pd.tag)LIKE '%goodyear%', 9, 0) + IF (LCASE(pd.tag) LIKE '%ultra%', 9, 0) + IF (LCASE(pd.tag) LIKE '%grip%', 9, 0) + IF(LCASE(pd.tag) LIKE '%ice%', 9, 0) + IF (LCASE(pd.tag) LIKE '%2%', 9, 0)) AS relevance, p.product_id FROMoc_product p LEFT JOIN oc_product_description pd ON (p.product_id = pd.product_id) LEFT JOINoc_product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN oc_product_attribute pa ON(p.product_id = pa.product_id) WHERE pd.language_id = '1' AND p.STATUS = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' AND ( (LCASE(pd.name) LIKE '%goodyear%') OR (LCASE(pd.name) LIKE'%ultra%') OR (LCASE(pd.name) LIKE '%grip%') OR (LCASE(pd.name) LIKE '%ice%') OR (LCASE(pd.name) LIKE'%2%') OR (LCASE(pd.description) LIKE '%goodyear%') OR (LCASE(pd.description) LIKE '%ultra%') OR(LCASE(pd.description) LIKE '%grip%') OR (LCASE(pd.description) LIKE '%ice%') OR (LCASE(pd.description)LIKE '%2%') OR (LCASE(pd.tag) LIKE '%goodyear%') OR (LCASE(pd.tag) LIKE '%ultra%') OR (LCASE(pd.tag) LIKE'%grip%') OR (LCASE(pd.tag) LIKE '%ice%') OR (LCASE(pd.tag) LIKE '%2%') OR (LCASE(pa.text) LIKE'%goodyear%' AND pa.language_id = '1' ) OR (LCASE(pa.text) LIKE '%ultra%' AND pa.language_id = '1' ) OR(LCASE(pa.text) LIKE '%grip%' AND pa.language_id = '1' ) OR (LCASE(pa.text) LIKE '%ice%' AND pa.language_id = '1' ) OR (LCASE(pa.text) LIKE '%2%' AND pa.language_id = '1' ) OR (LCASE(p.model) LIKE '%goodyear%') OR(LCASE(p.model) LIKE '%ultra%') OR (LCASE(p.model) LIKE '%grip%') OR (LCASE(p.model) LIKE '%ice%') OR(LCASE(p.model) LIKE '%2%') OR (LCASE(p.sku) = 'goodyear') OR (LCASE(p.sku) = 'ultra') OR (LCASE(p.sku) = 'grip') OR (LCASE(p.sku) = 'ice') OR (LCASE(p.sku) = '2') OR (LCASE(p.upc) = 'goodyear') OR (LCASE(p.upc) = 'ultra') OR (LCASE(p.upc) = 'grip') OR (LCASE(p.upc) = 'ice') OR (LCASE(p.upc) = '2') OR (LCASE(p.ean) = 'goodyear') OR (LCASE(p.ean) = 'ultra') OR (LCASE(p.ean) = 'grip') OR (LCASE(p.ean) = 'ice') OR (LCASE(p.ean)= '2') OR (LCASE(p.jan) = 'goodyear') OR (LCASE(p.jan) = 'ultra') OR (LCASE(p.jan) = 'grip') OR (LCASE(p.jan) = 'ice') OR (LCASE(p.jan) = '2') OR (LCASE(p.isbn) = 'goodyear') OR (LCASE(p.isbn) = 'ultra') OR (LCASE(p.isbn) = 'grip') OR (LCASE(p.isbn) = 'ice') OR (LCASE(p.isbn) = '2') OR (LCASE(p.mpn) = 'goodyear') OR (LCASE(p.mpn) = 'ultra') OR (LCASE(p.mpn) = 'grip') OR (LCASE(p.mpn) = 'ice') OR (LCASE(p.mpn) = '2') ) GROUP BY p.product_id ORDER BY relevance DESC, LCASE(pd.name) ASC LIMIT 0,30

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	p	NULL	ALL	PRIMARY	NULL	NULL	NULL	10089	3.33	Using where; Using temporary; Using filesort
1	SIMPLE	p2s	NULL	eq_ref	PRIMARY,store_id	PRIMARY	8	diacar_db.p.product_id,const	1	100.00	Using index
1	SIMPLE	pd	NULL	eq_ref	PRIMARY,language_id	PRIMARY	8	diacar_db.p.product_id,const	1	100.00	NULL
1	SIMPLE	pa	NULL	ref	PRIMARY	PRIMARY	4	diacar_db.p.product_id	749	100.00	Using where

 

 

Вроде как, у некоторых таблиц не используются индексы и грубо сканирование идет. ПОМОГИТЕ установить правильно индексы или другие возможные варианты.

 

 

Что проделано:

Заказано на хостинке Memcache - не помогло.

Отключено Подсчет кол-ва товаров;

Отключено filterpro - не помогло.

 

 

[markimax]

Запросы такого плана LIKE '%goodyear%',  не делают выборку по индексам а шерстят всю таблицу перебором (вам это explain и показал)

Спойлер

 

1

SIMPLE

p

NULL

ALL

PRIMARY

NULL

NULL

NULL

10089

3.33

Using where; Using temporary; Using filesort

 

 

 

Так что вам индексы здесь не помогут

Нужна полная оптимизация плюс установка Сфинкса

Готовьте бюджет ~ 9900 рублей (мин)

[sv2109]

да, запросы по вхождению вида "%слово%" работают очень медленно, так как в этом случае не используются индексы, особенно если поиск происходит по фразе из нескольких слов, особенно если у вас магазин на 12000 товаров. 
У меня есть модуль поиск специально для больших сайтов, он специально написан чтобы решить эту проблему, если будут вопросы - пишите в ЛС отвечу

 

[Mila80]

15 минут назад, markimax сказал:

 

Так что вам индексы здесь не помогут

Нужна полная оптимизация плюс установка Сфинкса

Готовьте бюджет ~ 9900 рублей (мин)

 

Cпасибо. Сама попробую, если нет - тогда....А то, что с одного ай пи идут запросы, не критично?

[markimax]

2 минуты назад, Mila80 сказал:

 

Cпасибо. Сама попробую, если нет - тогда....А то, что с одного ай пи идут запросы, не критично?

 

Кто ищет что то или кто то (конкуренты) сознательно ДдоСит узкое место вашего магазина чтобы его положить

[sv2109]

2 minutes ago, Mila80 said:

 

Cпасибо. Сама попробую, если нет - тогда....А то, что с одного ай пи идут запросы, не критично?

6-8 в день это не много, если бы сотни тогда можно было бы подозревать какой-то парсинг или сбор информации конкурентами или еще что. 
а 6-8 в день спокойно может дать какой-то посетитель для поиска нужного товара. 

[Mila80]

10 минут назад, sv2109 сказал:

6-8 в день это не много, если бы сотни тогда можно было бы подозревать какой-то парсинг или сбор информации конкурентами или еще что. 
а 6-8 в день спокойно может дать какой-то посетитель для поиска нужного товара. 

Да, 6-8 запросов, по 2 одинаковым товарам, ежедневно с одного ай пи. Удалила их.

Но, я не думаю, что из-за этого страдает скорость админ-панели при импорте. Так, что скорее всего нужна оптимизация.

[freelancer]

1 час назад, markimax сказал:

 

Так что вам индексы здесь не помогут

а как же FULLTEXT INDEX ?

[sv2109]

50 minutes ago, freelancer said:

а как же FULLTEXT INDEX ?

он имеет свои недостатки, я поэтому его в своих модулях не использую, например в нем нету возможность нормально настроить релевантность по какой-то формуле
например он также не поддерживает поиск по вхождению внутри отдельного слова (то есть поиск будет идти по началу слова)
в нем нет поддержки русской морфологии
и так далее. 

[freelancer]

Только что, sv2109 сказал:

он имеет свои недостатки, я поэтому его в своих модулях не использую, например в нем нету возможность нормально настроить релевантность по какой-то формуле
например он также не поддерживает поиск по вхождению внутри отдельного слова (то есть поиск будет идти по началу слова) и так далее. 

 

как же вы тогда ищите без LIKE и без match against? если это не коммерческая тайна разумеется

[Mila80]

15 минут назад, freelancer сказал:

а как же FULLTEXT INDEX ?

Спасибо, с полнотекстовым поиском буду разбираться.

Странность в другом. Кол-во товара на сайте не менялось пару лет, никаких дополнений не вносилось. Кол-во посетителей возросло незначительно. Как могут 6-8 медленных запросов влиять так сильно на загрузку/обновление товаров на сайте. Я их вместе связала лишь с тем, что появились они в одно время (запросы и проблемы в импорте). 

Edited April 20, 2018 by Mila80

[markimax]

45 минут назад, freelancer сказал:

а как же FULLTEXT INDEX ?

Никак
При Like %... выборка идет без учета индексов - перебором всех записей в таблице

match against  тоже тормоза на больших базах
Полнотекстовый поиск в MySQL это тормоза еще те

Сфинкс частично решает проблему, если грамотно настроить

[sv2109]

31 minutes ago, freelancer said:

 

как же вы тогда ищите без LIKE и без match against? если это не коммерческая тайна разумеется

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

 

30 minutes ago, Mila80 said:

 Как могут 6-8 медленных запросов влиять так сильно на загрузку/обновление товаров на сайте. Я их вместе связала лишь с тем, что появились они в одно время (запросы и проблемы в импорте). 

если поиск работает медленно то это создает нагрузку на сервер и если в тоже самое время вы делаете импорт товаров, то он естественно тоже тормозит, так как сервер точно в это же время обрабатывает сложный поисковый запрос + добавляется еще нагрузка от импорта, который тоже может создавать приличную нагрузку, это накладывается одно на другое и получаем ошибку 504, то есть серверу не хватает времени чтобы обработать запрос. Решений несколько: установить более быстрый модуль поиска, установить более мощный сервер, увеличить время выполнения скриптов на сервере.

[Mila80]

2 часа назад, sv2109 сказал:

Решений несколько: установить более быстрый модуль поиска, установить более мощный сервер, увеличить время выполнения скриптов на сервере.

Сервер мощный, время выполнения скриптов увеличено. Установка более быстрого модуля поиска - это хорошо, только боюсь, что ошибка в чем-то другом. Т.к. ранее писала, существенных изменений на сайте не проводилось. 

[freelancer]

тормоза могут быть связаны с другой нагрузкой на базу или процессор в то же время.

не давно был такой случай, страницы открывались долго. как выяснилось по логам - выгрузка в яндекс маркет

[freelancer]

вы этот запрос выполните на своём сервере и посмотрите сколько времени он отрабатывает

[chukcha]

Вообще -то запрос немного не туда-сюда

Допусти он медленный

Но нагрузка и медленная работа совсем не связана с этим запросом (или подобным)

Если уж разбирать запрос, то спрашивается, зачем искать по "2" , релевантность в таком случае практически нулевая

 

Кто автор такого запроса?
 

[Mila80]

2 часа назад, freelancer сказал:

на своём сервере

Запрос занял 6.4658 сек. 

Edited April 20, 2018 by Mila80

[Mila80]

1 час назад, chukcha сказал:

Кто автор такого запроса?
 

Модуль поиска хоть и старой модификации, искал и ищет по сайту быстро, проблем не было еще неделю назад, а потом раз, и что-то пошло не так (существенных изменений на сайте не было) 

[chukcha]

CHECK TABLE

 

 https://dev.mysql.com/doc/refman/8.0/en/analyze-table.html

 

https://dev.mysql.com/doc/refman/8.0/en/optimize-table.html

 

 

 

[Mila80]

Спасибо всем, кто откликнулся - помогли. Медленные запросы прекратились после удаления модуля Поиска. Медленные запросы никак не связаны с ошибкой 504 при загрузке прайс-листов.

[freelancer]

@Mila80 не подскажите модуль, который был причиной таким медленным запросам?

[Mila80]

5 минут назад, freelancer сказал:

@Mila80 не подскажите модуль, который был причиной таким медленным запросам?

Поисковая система с морфологией и релевантностью (возможно самая первая версия этого модуля). Модуль куплен очень давно, работал исправно и не давал сбоев ранее. Спасибо автору модуля, что написал  и указал возможные причины сбоев, а именно рост сайта.

 

[sv2109]

34 minutes ago, freelancer said:

@Mila80 не подскажите модуль, который был причиной таким медленным запросам?

это мой модуль, у пользователя стояла очень старая версия этого модуля, купленная лет 5 назад + модуль был не настроен правильно, так как был поиск по очень многим полям и по вхождению (а это влияет на скорость) и мин. длина слова для поиска судя по запросу стояла 1 символ, это добавляет в запрос поиск по напр. "2" что имеет мало смысла. И так далее. 

[sv2109]

Just now, Mila80 said:

Поисковая система с морфологией и релевантностью (возможно самая первая версия этого модуля). Модуль куплен очень давно, работал исправно и не давал сбоев ранее. Спасибо автору модуля, что написал  и указал возможные причины сбоев, а именно рост сайта.

 

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