Как организовать пагинацию, если БД и поисковая машина — это раздельные сервисы?

Question

[imaginationunicorn]

Доброго всем времени.

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

Подробности:
MySQL-сервер (MariaDB 10.3+), в котором хранятся записи вида Документ с огромным набором параметров, по которым можно их отфильтровать - это и many-to-many отношения (фильтрация по нескольким ID других моделей), и "больше чем/меньше чем" по столбцу, IS TRUE/FALSE, сравнение строк и так далее. У всех Документов есть особенность - каждому из них соответствует текстовое содержимое, по содержимому которого и необходимо одновременно с поиском по параметрам выполнять фильтрацию. Содержимое хранится в Sphinx / Manticore (поисковые машины). Проблема - организация пагинации.

К примеру, необходимо сделать выборку документов по запросу:
атрибут1 IN (1,2,3)
атрибут2 = 'example_string'
атрибут3 IS TRUE
атрибут4 > 500
запрос полнотекстового поиска "укрепляющий эфирный шампунь с экстрактом масел жожоба"

Что ожидается: отфильтрованный по всем параметрам список документов, отсортированный по релевантности, заданной поисковой машиной.

Проблема: пагинация.

В текущем варианте из поисковой машины забираются все айдишники и позже добавляются в запрос базы данных вида WHERE IN (список ID) AND ORDER BY FIELD(id, список ID). Проблема в том, что по любому запросу из поисковой машины находится больше 100 000 документов и при попытке их вернуть поисковая машина выполняет запрос довольно долго (1-2 секунды). После этого еще и сама БД с таким уточнением работает дольше, чем если бы эти данные просто joinились с другой таблицей.

Обратный вариант - брать отфильтрованный список ID из базы данных и формировать условие в поисковой машине WHERE IN (список ID) тоже не вариант - опять низкая скорость обработки (да и кол-во их там переваливает за 100 000 на запрос).

Какие варианты пробовались:
- FEDERATED / FEDERATEDX таблица - не удалось "приготовить", нестабильная работа и на любой чих-ошибку не найти ответ в сети
- SphinxSE таблица - аналогично, отмечен разработчиками как устаревший и неподдерживаемый вариант
- заносить все параметры для фильтрации в индекс поисковой машины - ужасный вариант, так как поисковая машина выполняет фильтрацию по обычным своим полям очень медленно, плюс встает необходимость содержать актуальными две копии данных - в БД и поисковой машине
- PostgreSQL с полнотекстовым поиском - слабый поиск по русским словам и очень малое количество информации по тому, как использовать

Документов в базе в общем около 100 миллионов. Нужна скорость. Почему считаю, что она может быть? Потому что любой сложный запрос со стороны БД выполняется менее чем за 0.15 с, в поисковой машине - тоже около 0.15с. А соединить результаты их фильтрации черт знает как. Пробовался еще вариант "вычислять пересечения ID из двух результатов", но тут поисковая машина становится узким местом - не умеет она быстро отдавать все найденные ID, если их число переваливает за 1000 (кто пользуется Sphinx / Manticore, должен знать эту особенность).

Ограничение: поисковая машина должна быть обязательно Sphinx / Manticore. Остальные - болезненно некачественный поиск по тому содержимому, что есть в документах.

Comments

[d'Ivan]

В текущем варианте из поисковой машины забираются все айдишники и позже добавляются в запрос базы данных вида WHERE IN (список ID) AND ORDER BY FIELD(id, список ID).

1. Каков может быть размер списка ID?
2. В запросе используется LIMIT?

Answer 1

[Boris Köln]

Все атрибуты надо индексировать / фильтровать поисковой машиной.
sphinxsearch.com/docs/current.html#attributes

Comments

[imaginationunicorn]

В тексте вопроса указано, что поисковая машина справляется куда медленнее с фильтрацией по атрибутам, поскольку они хранятся в индексе поисковой машины неиндексированными. Все, для чего годится информация в индексе поисковой машины - добавочные фасеточные запросы. Плюс требуется содержать полную копию данных в индексе поисковой машины, по которым требуется фильтрация и сортировка - а это банальное удвоение занимаемого места на жестком диске / SSD (при 100 миллионах документов это очень ощутимые объемы).

Еще один крайне неприятный момент - это чрезвычайно медленный UPDATE в real-time индексе SPHINX'a / Manticore - от 0.06 до 0.25 с на одну строчку (это даже если просто меняется значение одного атрибута). Поддерживать строки в индексе поисковой машины в актуальном состоянии становится проблематичным (а обновления частые)

[Boris Köln]

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

Скорость дополнительного поиска по атрибутам - не самая лучшая, но явно лучше, чем 100К ID перегонять в SQL и фильтровать там.

Проблема актуальности поисковых данных - да, есть.

[imaginationunicorn]

Boris Korobkov, на данный момент атрибутов 43 (из них 14 MVA с кол-вом внутри до 110) - и уже проверено, что полнотекстовый индекс (он, кстати, достаточно слабый - индексируются слова без словоформ и большим количеством стоп-слов) с атрибутами и без них различаются по объему на 63%. Не удвоенние, но ощутимо.

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

Единственная реальная проблема - это обновление данных в этом индексе (обновлений много и они регулярные). Оно попросту не успевает выполняться, 16 воркеров на сервере с 24 ядрами отведены сфинксу - и все равно не успевает. Пересматриваем архитектуру, важность некоторых атрибутов, но все равно пока что слабо работает поиск.

[Boris Köln]

imaginationunicorn, нормальный вариант один: искать и фильтровать одним движком. Каким именно: решайте сами в зависимости от исходных данных.
Как использовать PostgreSQL с полнотекстовым поиском - есть документация и примеры. Русская морфология работает неплохо. При жалении можно потом прикрутить триграммы.

[Fortop]

imaginationunicorn, прошардируйте индекс.

Причём не только средствами сфинкса, но и через приложение. Поместите его (сфинкс) на 2,4,8,16 серверов и выполняйте обновления распределяя их по шардам.

[Arris]

Еще, возможно, вам помогут percolate-индексы (Manticore)

Answer 2

[rPman]

Добавление тысяч идентификаторов в sql запрос вида where in (...) это плохая практика, что получается, если вы сначала заполняете in nmemory таблицу идентификаторами (из полнотекстового запроса внешней базы), а потом уже делаете фильтрацию по вашим атрибутам из sql базы, приджойнив это временную табличку?

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

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

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

Answer 3

[batyrmastyr]

1. Попробуйте искать целиком в сфинксе используя распределённые индексы.
index rss {
type = distributed
agent_persistent = 127.0.0.1:9312:rss1
...
agent_persistent = 127.0.0.1:9312:rssN
agent_persistent = 127.0.0.1:9312:rssUpdates
}
В первые N частей закидываете все документы более-менее равномерно их распределив, например, по датам создания или номерам и индексируете редко, раз в сутки или неделю.
В последний rssUpdates выбираете только документы изменившиеся с последней индексации, прописываете sql_query_killlist чтобы исключить из результатов устаревшие копии документов из других индексов.

2.1. Точное число результатов довольно быстро можно узнать так из SHOW META:
"SELECT ... WHERE <условия без сортировки> LIMIT 1; SHOW META like 'total_found';" и смотрите результат второго запроса.

2.2 Дальше делаете "честные запросы" - фильтрацию, сортировки для получения нужной страницы, но с указанием max_matches: "SELECT .. WHERE ... LIMIT ... OPTION max_matches=страница * размер страницы". В типичной ситуации, когда пользователь угомонится на первых страницах, скорость значительно увеличится.

3. В третьем сфинксе появился CREATE INDEX для обычных, не текстовых, столбцов. Может помочь.

4. В Постгресе сейчас есть "обёртка сторонних данных" и через неё можно стучаться, например, к mysql. К Сфинксу стучаться не пробовал, но стоит попробовать схему "данные в постгресе, полнотекстовый индекс из сфинкса подключаем через JOIN".

Answer 4

[ManticoreSearch]

У всех Документов есть особенность - каждому из них соответствует текстовое содержимое, по содержимому которого и необходимо одновременно с поиском по параметрам выполнять фильтрацию

А в Sphinx / Manticore какой функциональности не хватает, чтобы фильтровать сразу сразу и по полнотексту, и по всему остальному? Можно пример?

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

Можно пример запроса по "поисковая машина выполняет фильтрацию по обычным своим полям очень медленно". Не пойму что вы имеете в виду под "обычными своими полями" и почему вдруг получается медленно.
Как по мне - этот вариант наиболее оптимальный в вашем случае. Фильтровать по полнотексту в одном месте и неполнотексту в другом и при этом оперировать сотнями тысяч документов возможно, но не всегда может получиться добиться хорошей производительности, т.к. всё будет упираться в то, что отфильтрованное в одном месте нужно будет передать в другое. А в случае, если делать всё в одном месте это происходит автматически.

плюс встает необходимость содержать актуальными две копии данных - в БД и поисковой машине

Так а сейчас вы разве это уже не делаете? Просто нужно дополнительно с id/full-text field синхронизировать и другие поля, по которым хочется в итоге фильтровать. Если вы имеете в виду, что сложно синхронизировать обновления документов, то для plain индексов есть main+delta для таких случаев (https://play.manticoresearch.com/maindelta/) , для RT - зависит от приложения.