Подбор похожих новостей тормозит

[Хоббит 35]

Привет, подбор похожих новостей тормозит, такой запрос: 

SELECT id, date, short_story, xfields, title, category, alt_name, MATCH (title, short_story, full_story, xfields) AGAINST ('BLA-BLA-BLA-BLA-BLA-BLA-BLA') as score FROM dle_post WHERE category = '1' AND MATCH (title, short_story, full_story, xfields) AGAINST ('BLA-BLA-BLA-BLA-BLA-BLA-BLA') AND id != 600000 AND approve=1 ORDER BY score DESC LIMIT 5;

...выполняется дольше 10-30 секунд, почти всегда получаю ошибку 504, если попытаюсь открыть только что созданный новость. Если убрать тег {related-news} то таких проблем не будет. 

Включен функция "Искать похожие публикации только в тех же категориях, что и сама новость". Если отключить данную фишку то БД вообще падает.  Мульти категории выключены, у меня категории используется так: 

Категория 1 - Лучшие моменты фильмов (1000 новостей).

Категория 2 - Лучшие моменты сериалов (5000 новостей). 

Категория 3 и далее - 570 000+ новостей.

Есть такая идея: создать отдельную таблицу, и туда записывать ID новостей каждой категории в отдельную колонку, и потом подключить систему Sphinx для релевантного подбора. Это хотя бы уменьшит круг подбора, что думаете?

Никаких модификации нет, это тест на чистом движке DLE 14.

Изменено 4 февраля 2021 пользователем Хоббит

[MSK 289]

1 час назад, Хоббит сказал:

подключить систему Sphinx

У меня тоже давно есть желание, но пока так и не взялся...

1 час назад, Хоббит сказал:

что думаете?

Ну как костыль - можно создавать кеш похожих новостей по крону вне двига для ВСЕХ новостей.
Обновлять кэш после добавления/редактирования новостей с учетом настроек поиска похожих...
Это немного затратно, но не повлияет на комфорт посетителя.

 

[Хоббит 35]

11 минут назад, MSK сказал:

Ну как костыль - можно создавать кеш похожих новостей по крону вне двига для ВСЕХ новостей.
Обновлять кэш после добавления/редактирования новостей с учетом настроек поиска похожих...
Это немного затратно, но не повлияет на комфорт посетителя.

Допустим у меня в других 570 000 новостях уже указаны ID новостей, тогда ваша идея не целесообразно получается. 

Странная логика работы этого модуля, зачем сканировать все 570к новостей, и потом отбирать новости из 1 категории для формирования 5 похожих новостей? Нельзя сразу начать со второго шага, то есть поиска новостей из 1 категорий? 

[MSK 289]

25 минут назад, Хоббит сказал:

Допустим у меня в других 570 000 новостях уже указаны ID новостей, тогда ваша идея не целесообразно получается. 

Как это нецелесообразно?

А если добавили новость, которая является более ПОХОЖЕЙ, чем те, которые уже есть в ранее добавленных :)
На то это и обновляемый кэш :)

[Хоббит 35]

34 минуты назад, MSK сказал:

Как это нецелесообразно?

А если добавили новость, которая является более ПОХОЖЕЙ, чем те, которые уже есть в ранее добавленных :)
На то это и обновляемый кэш :)

Вы слишком глубоко думаете :) Разве DLE обновляет кэш похожих новостей при добавлении более ПОХОЖЕЙ новости? 

[MSK 289]

2 минуты назад, Хоббит сказал:

Вы слишком глубоко думаете

Я не думаю, я знаю :)
Именно это уже подумали за нас, нам думать надо при разработке модификаций :)

3 минуты назад, Хоббит сказал:

Разве DLE обновляет кэш похожих новостей при добавлении более ПОХОЖЕЙ новости? 

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

clear_cache( array('news_', 'tagscloud_', 'archives_', 'calendar_', 'topnews_', 'rss', 'stats') );

и при редактировании

clear_cache( array('news_', 'full_'.$item_db[0], 'comm_'.$item_db[0], 'tagscloud_', 'archives_', 'related_', 'calendar_', 'rss', 'stats') );

 

[celsoft 6 094]

04.02.2021 в 13:38, Хоббит сказал:

Есть такая идея: создать отдельную таблицу, и туда записывать ID новостей каждой категории в отдельную колонку

Непонятно зачем именно для Sphinx делать. Но в DLE такая таблица с данными уже есть )).

04.02.2021 в 13:38, Хоббит сказал:

и потом подключить систему Sphinx для релевантного подбора. Это хотя бы уменьшит круг подбора, что думаете?

Sphinx конечно искать будет Быстрее MySQL.

[Хоббит 35]

11 часов назад, celsoft сказал:

Непонятно зачем именно для Sphinx делать. Но в DLE такая таблица с данными уже есть )).

dle_post_extras_cats? Если требуется отфильтровать ID, и собрать новости с первой категории (cat_id = 1) разве не придется сканировать всю таблицу? Это же то же самое как запрос в первом посте, не? Я хочу пропустить первый шаг: составить готовый список ID новостей из первой категории, и чтобы Sphinx сразу искал среди этого списка, что думаете? Разве это не избавит от лишнего сканирования всей таблицы, допустим если новости из первой категории меньше 10 000?

12 часов назад, celsoft сказал:

Sphinx конечно искать будет Быстрее MySQL.

Насколько быстро? Сейчас процесс подбора похожих новостей у меня длится около 10-15 секунд (с админки, аякс)

[MSK 289]

5 минут назад, Хоббит сказал:

чтобы Sphinx сразу искал среди этого списка, что думаете?

сначала искать должен сфинкс

6 минут назад, Хоббит сказал:

у меня длится около 10-15 секунд (с админки, аякс)

а может дело в сервере?

[Хоббит 35]

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

а может дело в сервере?

Типа не хватает мощности? Почти всегда 80% мощности пустует.

Процессор:    Intel(R) Core(TM) i9-9900K CPU @ 3.60GHz (16 cores)
Оперативная память:    18654 / 31853 MiB
Файл подкачки (swap)    4095 MiB
Средняя загрузка (1, 5, 15 мин):    1.12 1.35 1.67

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

сначала искать должен сфинкс

Это как? Я не совсем понимаю логику работы данного движка :(

[celsoft 6 094]

1 час назад, Хоббит сказал:

Если требуется отфильтровать ID, и собрать новости с первой категории (cat_id = 1) разве не придется сканировать всю таблицу?

Это поле содержит индексы. Зачем таблицу то сканировать?

[Хоббит 35]

12 минут назад, celsoft сказал:

Это поле содержит индексы. Зачем таблицу то сканировать?

Ах вот оно как, отлично 😉

[Mr. Bot 26]

11 часов назад, Хоббит сказал:

Типа не хватает мощности? Почти всегда 80% мощности пустует.

Да это не показатель ничуть, можно пару настроек криво сделать или например при ваших объёмах оставить по дефолту, то будет тоже самое.
При 600К новостей явно нужен тюнинг под ваш тип контента.

Хотя слишком быстрым он даже на SSD не будет, вот NVMe SSD уже раметно бодрее, а если у вас HDD, то вполне возможно и 10-30 секунд при разной степени фрагментированности.

[Хоббит 35]

11 часов назад, Mr. Bot сказал:

Да это не показатель ничуть, можно пару настроек криво сделать или например при ваших объёмах оставить по дефолту, то будет тоже самое.
При 600К новостей явно нужен тюнинг под ваш тип контента.

Хотя слишком быстрым он даже на SSD не будет, вот NVMe SSD уже раметно бодрее, а если у вас HDD, то вполне возможно и 10-30 секунд при разной степени фрагментированности.

Тюнинг сервера делал сис. админ, уже 3 года следит за сайтом поэтому проблемы со стороны сервера очень маловероятны. SSD m.2 

[Хоббит 35]

@celsoft будет ли в будущем улучшения и оптимизация related news?

Сейчас понятно что при достижении 300-600к новостей будут проблемы. 

Считаю данную модуль довольно важным, как никак это вроде бы кольцевая перелинковка 

Изменено 10 февраля 2021 пользователем Хоббит

[celsoft 6 094]

3 часа назад, Хоббит сказал:

будет ли в будущем улучшения и оптимизация related news?

Он максимально возможно оптимизирован. Это все что может предоставить MySQL, а требований к дополнительному серверному ПО у нас не будет, т.к. это не каждому доступно