Jump to content
Search In
  • More options...
Find results that contain...
Find results in...
  • Sign Up
  • entries
    41
  • comments
    403
  • views
    8,727

c 12секунд до 300мс. Почему ваши категории могут тормозить ?


Yoda

1,458 views

Привели мне пациента...

500к товаров

7к уников в день

150к записей в таблице order.

 

Вобщем не ларек.

И вот на категории в 50-60к товаров этот не ларек генерится 12 секунд!

 

Не ну а че... Это  ж опенкарт... Это ж не годится для больших магазинов. Никто не смог помочь. Как обычно вот эти сказки школотронов от программизма.

 

В среднем страницы загружаются  2-4 сек, делаем быстро.все решаем, получаем 200-400мс, но на больших категориях все равно дичь.

 

Смотрим запросы находим вот такое прекрасное, да еще и дважды инициализируемое:

 

        $sql = "SELECT p.product_id,
            (SELECT Count(op.order_id) AS popular
            FROM   oc_order_product op
                            LEFT JOIN `oc_order` o
                                        ON ( op.order_id = o.order_id )
            WHERE  op.product_id = p.product_id
                            AND Adddate(o.date_added, INTERVAL 30 day) < Now()
                            AND o.order_status_id > '0'
            GROUP  BY op.product_id
            ORDER  BY popular DESC) AS popular
            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 p2s.store_id = '0'
                    AND cp.path_id = '". (int)$category_id ."'
            GROUP  BY p.product_id
            ORDER  BY ( p.quantity > 0 ) DESC,
                        popular DESC,
                        Lcase(pd.name) DESC,
                        p.date_added DESC
            LIMIT  0, 3 ";

Ржавый фак и Винни-Пух.
Это просто какая то жестяная жесть, джоин на джоин на джоин, при чем наборы 60 к товаров, 300 категорий и порядка 10-20к заказов.

И сложная сортировка-группировка этого всего по разным таблицам, да еще и по предвычисляемому полю  p.quantity > 0 все те школотроны, которые в гугле прочитали страшно умное слово индексы, тут сразу такие присели... При таких запросах индексы в принципе не могут полноценно работать.

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

 

А теперь вопрос знатокам....
А что же делать ?
Как оптимизировать эти процессы?

 

Ну кеш вы скажете понятно, но ведь кеш у нас так или иначе должен прогрется для всех категорий, рано или поздно он протухнет, и все равно кому то из клиентов попадется тухлая страница на 10-12сек, да и там не одна не две жирные категории. 7 секунд или 12.. Разницы особой нету.
 

Вобщем задачка со звездочкой. Как сохранить полностью логику этого запроса без изменений базовых таблицы движка и отдать быстро эти данные холодными без всяких кешей ?

Если что, мы с 6 сек на этом реализации, получили 0.18 мс. 

 

  • +1 1

20 Comments


Recommended Comments

Цитата

Как сохранить полностью логику этого запроса без изменений базовых таблицы движка и отдать быстро эти данные холодными без всяких кешей ?

"Загадка от Жака Фреско. На размышление 30 секунд".

 

Решение в лоб - обновление кеша по cron раз в сутки, 10 сек отработает глубокой ночью - не страшно.

 

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

 

Я не гуру, но может вместо "LIMIT 0, 3" написать "LIMIT 3"? Это даст какое то различие? Интересно было бы узнать.

 

Цитата

А теперь вопрос знатокам...

Точно не ко мне:-D

Link to comment

Ну как минимум первое выпилить NOW() из-запроса. Какая нам разница это было прям сейчас или сегодня или вчера или неделю, если выборка за целый месяц. И записать this->now = 'd-m-y 00:00:00' или как там, навскидку не вспомню.

Если у нас не мультимагазин выпилить p2s вместе с его LEFT JOIN

Link to comment
Quote

Если что, мы с 6 сек на этом реализации, получили 0.18 мс. 

подобное время возможно при использовании предварительно вычисленных агрегатов, например. Как заметили выше - это самое очевидное и простое решение. При работе с ними, весь этот запрос превращается во что-то вроде select product_id as popular from agregate_table where category_id\path_id = ?.

Как альтернативный вариант - использование иных хранилищ, взамен mysql (на мой взгляд сложнее + так же требует синхронизации данных, как и агрегаты - пересоздания)

 

ради спортивного интереса чуть переписал этот кривенький запрос:

а) вычисление популярных товаров из секции select можно вынести в отдельный джоин

б) подсчет количества заказов для каждого товара категории - это не совсем корректный признак его популярности, в то время как сумма купленного количества товара должна быть более точной характеристикой. Потому Count(op.order_id) AS popular заменил на SUM(op.quantity) AS popular. На скорость это не влияет.

Spoiler

SELECT SQL_NO_CACHE
  p.product_id
FROM oc_category_path cp
  JOIN oc_product_to_category p2c
    ON (cp.category_id = p2c.category_id)
  JOIN oc_product p
    ON (p2c.product_id = p.product_id)
  JOIN oc_product_description pd
    ON (p.product_id = pd.product_id)
  JOIN oc_product_to_store p2s
    ON (p.product_id = p2s.product_id)
  JOIN (SELECT
      op.product_id,
      SUM(op.quantity) AS popular
    FROM oc_order_product op
      JOIN `oc_order` o
        ON (op.order_id = o.order_id)
    WHERE o.order_status_id > '0' AND Adddate(o.date_added, INTERVAL 30 day) < Now()
    GROUP BY op.product_id) popular
    ON p.product_id = popular.product_id
WHERE pd.language_id = '1'
AND p.status = '1'
AND p2s.store_id = '0'
-- подставить свои значения или выполнить так, типа, для вообще всех товаров, что бы посложнее 
-- AND p2c.category_id = ?
-- AND cp.path_id = ?
GROUP BY p.product_id
ORDER BY (p.quantity > 0) DESC,
popular.popular DESC,
LCASE(pd.name) DESC,
p.date_added DESC
LIMIT 0, 3;

 

Результаты на живом примере:

при 380к активных и включенных товаров в oc_products и 40к+ заказов в oc_order время выполнения этого запроса без привязки к какой-либо категории чуть менее 0.4сек.

 

С уточнением категории, разумеется, будет быстрее: с потенциальным минимумом, равным времени выполнения запроса из джоина с вычислением популярных товаров (на моих данных около 0.2сек). Если же этот джоин с вычислением popular заменить предварительно посчитанной таблицей, то итоговое время выполнения запроса будет еще меньше: ~0.01сек для категории с 1к товаров, например или около 0.2сек для определения топ3 среди всех 380к товаров.

Link to comment
12 часов назад, niger сказал:

Ну как минимум первое выпилить NOW() из-запроса

Вы адепт секты "кеш mysql"?

Так я вам открою военную тайну. Использование кеша мускуля, это забивание гвоздей микроскопом.

Link to comment
5 минут назад, Vladzimir сказал:

Вы адепт секты "кеш mysql"?

Так я вам открою военную тайну. Использование кеша мускуля, это забивание гвоздей микроскопом.

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

  • +1 2
Link to comment
3 минуты назад, Shureg сказал:

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

А по существу? Я делал десяток тестов, которые всегда показывали, что включение кеша мускуля увеличивает время генерации страницы на 15-20%.

Да и текущая реализация в опенкарте делает использование кеша мускуля безсмысленным.

Link to comment
1 минуту назад, Vladzimir сказал:

А по существу? Я делал десяток тестов, которые всегда показывали, что включение кеша мускуля увеличивает время генерации страницы на 15-20%.

Да и текущая реализация в опенкарте делает использование кеша мускуля безсмысленным.

Ну, во-первых, реализацию можно подправить. Во-вторых, "врожденный" кэш у мускула все равно есть, если только вы его не убивали специально настройками сервера, хоть и несколько иной природы. В третьих, замедление возможно только в одном случае - если у вас кэш резко уменьшил доступную мускулу память на запросы. В-общем, если вы возьмете дефолтный ОС на шаред хостинге и врубите ему кэш mysql побольше - скорее всего, ничего полезного действительно не будет. Но варианты разные есть.

Link to comment
1 минуту назад, Shureg сказал:

Ну, во-первых, реализацию можно подправить. Во-вторых, "врожденный" кэш у мускула все равно есть, если только вы его не убивали специально настройками сервера, хоть и несколько иной природы. В третьих, замедление возможно только в одном случае - если у вас кэш резко уменьшил доступную мускулу память на запросы. В-общем, если вы возьмете дефолтный ОС на шаред хостинге и врубите ему кэш mysql побольше - скорее всего, ничего полезного действительно не будет. Но варианты разные есть.

Т.е. получается именно экономия на спичках. И кеш кешу рознь.Но мой коммент был именно к

Цитата

Ну как минимум первое выпилить NOW() из-запроса

Прироста это не даст, от слова совсем.

Link to comment
4 минуты назад, Shureg сказал:

Ну, во-первых, реализацию можно подправить. Во-вторых, "врожденный" кэш у мускула все равно есть, если только вы его не убивали специально настройками сервера, хоть и несколько иной природы. В третьих, замедление возможно только в одном случае - если у вас кэш резко уменьшил доступную мускулу память на запросы. В-общем, если вы возьмете дефолтный ОС на шаред хостинге и врубите ему кэш mysql побольше - скорее всего, ничего полезного действительно не будет. Но варианты разные есть.

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

Link to comment
7 минут назад, Vladzimir сказал:

Прироста это не даст, от слова совсем.

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

Edited by Shureg
Link to comment
5 минут назад, Vladzimir сказал:

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

Для "всей таблицы" это не так уж "глобально".  ))

Link to comment
1 минуту назад, Shureg сказал:

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

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

Link to comment
16 минут назад, Vladzimir сказал:

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

Ничего парадоксального, я уже написал, в дефолтном ОС собственный кэш mysql почти бесполезен из-за вездесущих NOW, и только жрет память. Но кто сказал, что запросы надо использовать "как есть".

Link to comment
8 часов назад, 100napb сказал:

Результаты на живом примере:

А теперь еще замените

AND Adddate(o.date_added, INTERVAL 30 day) < Now()

на

AND o.date_added > '2020-01-25 00:00:00'

 

  • +1 1
Link to comment
1 hour ago, SooR said:

А теперь еще замените

решил посмотреть как изменятся конкретные цифры. Спасибо. Спасибо потому, что

Во-1: я немного ошибся в результатах на живом примере, которые приводил выше. они были приведены для более тяжелого варианта запроса, в котором было отключено уточнение по дате и запрос, по сути, считал вообще все заказы *смущенный смайл*. Ну на коленке же тестируем, ради интереса... Зато! С уточнением по дате, как и было в изначальном условии задачи, запрос работает даже без промежуточных таблиц быстрее: менее 0.1сек. Не так уж и плохо, хотя с агрегатом можно еще быстрее.

Во-2: от варианта записи даты через adddate-interval или явно, результат 0.1сек прям заметно не меняется. хотя я согласен - должно быть быстрее, если указывать дату явно, как Вы и уточнили.

 

 

Edited by 100napb
Link to comment

@100napb , у меня ваш запрос выполняется за 0.890, тогда как оригинальный 0.609 (с указанием path_id)

Link to comment

@SooR

¯\_(ツ)_/¯

могу лишь такое сравнение привести со своей стороны

Spoiler

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

 

806555891_.png.aa8018d7112de43322f36e1c6369d27d.png

 

 

 а с промежуточной таблицей? хотя это уже почти агрегат... с ней не интересно

Spoiler

на случай если лень писать

 

CREATE TABLE IF NOT EXISTS aggr_table (
  PRIMARY KEY (product_id)
) AS (SELECT
   op.product_id, COUNT(op.order_id) AS popular
  FROM oc_order_product op
    LEFT JOIN `oc_order` o
      ON (op.order_id = o.order_id)
  WHERE Adddate(o.date_added, INTERVAL 30 day) < Now()
  AND o.order_status_id > '0'
  GROUP BY op.product_id);

 

 

+

 

SELECT SQL_NO_CACHE
  p.product_id, popular.popular
FROM oc_category_path cp
  JOIN oc_product_to_category p2c
    ON (cp.category_id = p2c.category_id)
  JOIN oc_product p
    ON (p2c.product_id = p.product_id)
  JOIN oc_product_description pd
    ON (p.product_id = pd.product_id)
  JOIN oc_product_to_store p2s
    ON (p.product_id = p2s.product_id)
  JOIN aggr_table as popular
    ON p.product_id = popular.product_id
WHERE pd.language_id = '1'
AND p.status = '1'
AND p2s.store_id = '0'
AND cp.path_id = 18
GROUP BY p.product_id
ORDER BY (p.quantity > 0) DESC,
popular.popular DESC,
LCASE(pd.name) DESC,
p.date_added DESC
LIMIT 0, 3; 

Edited by 100napb
Link to comment

Мда уж, почитал ваш холивар.
Смешно.

 

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

 

Второй тупочет ножкой зачем-то про нативный кеш mysql. Не особо понимая, что кеш ничем не помогает, когда у тебя 3000 запросов по 0.001сек, это 3 сек, и кешируй их не кешируй, тут только работать в сторону уменьшения количества запросов. 

Еще один хочет что-то выпилить, и предлагает кэпские решения, которые итак понятны, но ни на что не влияют, так как проблема всей этой конструкции, в том, что на 100 000 товаров производится 100 000 подзапросов count, по полю другой таблицы, в которой 140 000 записей, и вот в этом  самая большая проблема.


В том что каждый раз, на каждую категорию производится вот этот вот COUNT(order_id). 

 

Ну и здесь есть несколько вариантов решений:

1 - просто забить и сделать именно для этих результатов длинный кеш, скажем на сутки. Но у нас 300-400 пользователей получат долгую загрузку страницы, больше 5-6 сек.

2 - Запустить плановую агрегацию, крон, который будет перебирать значения и считать их раз в сутки под каждую категорию. Но у нас есть проблема, запрос 6 секунд, на 300  категорий, это 1800 секунд у нас занято ядро сервера, раз в сутки, которое будет просто в холостую гонять эти каунты.

3 - Cамая пожалуй верная реализация, это сделать промежуточную PIVOT таблицу, в которую мы посчитаем общее количество продаж по каждому товару за последний месяц, одним длинным запросов в 5-7 секунд. А потом уже будем выгребать данные из нее, учитывая что у нас в ней сразу есть все поля как для сортировки так и для группировки, mysql сделает это очень быстро с использованием составных индексов, на прототипе у меня вышло, что то около 0.18сек, вместо наших исходных шести. И вот потом уже можно эти данные кешировать в самом движке, либо прогреть их в какую-то агрегатную таблицу, и оттуда забирать дополнительным запросом, но так или иначе, мы сэкономим ресурс сервера (проект нагруженный), и отдадим пользователям быстрые страницы, ровно с теми данными, которые изначально нам были необходимы.

 

Хейтеры, в бой, можете начинать рассказывать какие вы умные, а я тупорылый! 

  • +1 3
Link to comment
В 27.02.2021 в 12:31, Yoda сказав:

 


Еще один хочет что-то выпилить, и предлагает кэпские решения, которые итак понятны, но ни на что не влияют, так как проблема всей этой конструкции, в том, что на 100 000 товаров производится 100 000 подзапросов count, по полю другой таблицы, в которой 140 000 записей, и вот в этом  самая большая проблема.


 

 

Хейтеры, в бой, можете начинать рассказывать какие вы умные, а я тупорылый! 

"не то что бы я хейтер, но"

 

если товаров не 100 000, а только 1000 - тогда уменьшения кеши помогает ?

 

 

Link to comment
В 07.06.2021 в 21:20, NazarVen сказал:

"не то что бы я хейтер, но"

 

если товаров не 100 000, а только 1000 - тогда уменьшения кеши помогает ?

 

 

 

Непонятный текст.

Link to comment

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.