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

[Yoskaldyr]

Ну в общем сабж.

Что можно для этого использовать? Железо может быть любое, количество памяти тоже любое.
Важно что список постоянно обновляется/дополняется. И важно чтобы результат запроса был минимальным (до 0.1 сек по возможности).

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

 

[WMix]

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

 

[grigori]

в начале 2010х в sotmarket было порядка 40 млн товарных позиций в одной базе mysql без шардинга с нормализованной EAV структурой данных, и фасетный, то есть, обычный фильтр по нескольким характеристикам работал на обычных запросах с десятком join, с оптимизацией по индексам, и даже без кеширования

ну, на основном домене был sphinx, там 100500 запросов в секунду было, но в общем случае эта база все выдавала напрямую без тормозов, с пангинацией и сортировкой по разным полям

это было на 3 архитектуры процессоров назад (5-6 поколений), и никаких ssd/nvme, если у тебя тормозит - проблема не в mysql

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

 

[WMix]

у нас таблица цен около 200м. без фильтров на "обьект" (грубо говоря предварительный фильтр) до 2-3к записей, есть пагинация на scrollen ну и фильтры, их не то что много но есть и функция (то что без индекса), все на базе mysql. select 0.0х секунд обычно
железка правда дорогая, на локалке только усеченный вариант работает

 

[Yoskaldyr]

Я в курсе что там самое страшное - это не просто пагинация, а пагинация + сортировка.

Почему не мускуль или подобное:
- потому что нельзя сделать индекс для всех вариантов фильтра/сортировки - ну не для этого btree индексы.
- потому что есть фильтры которые могут включать очень большое количество от основного второе

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

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

 

[Yoskaldyr]

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

 

[Yoskaldyr]

@grigori Не напоминай того звиздеца, что был в сотмаркете - видел, помню. Да контора большая, но чаще там не было даже минимальной оптимизации, т.е. не верю что там было 0.0х с фасетным поиском одного большого списка. Учитывая ты сам говорил что там брали количеством программистов, а не качеством. Не спорю могло быть, но только если было какое-то предварительное ограничение списка (например по категориям)

 

[WMix]

ну что тут посоветовать, без примера сложно судить. и соглашусь, что пример без базы не раскроет проблему. хотя взгляд на explain часто помогает. в любом случае кэш поможет в выборке, но потеря в записи/обновлении кэша, как в скорости так и в сложности.
нужен пример

 

[grigori]

@grigori Не напоминай того звиздеца, что был в сотмаркете - видел, помню. Да контора большая, но чаще там не было даже минимальной оптимизации, т.е. не верю что там было 0.0х с фасетным поиском одного большого списка.

чушь какая-то
я говорю про свой код, я тюнил запросы
у проекта были большие проблемы с денормализацией, и я несколько месяцев занимался базой вместе с DBA, мне еще премию за это дали,
в моем приложении запросы с 5-7 join отрабатывали где-то за 0.1
и нет, команду не раздували, про количество я говорил в контексте Oracle - вот там да, настоящий enterprise

самое страшное - это не просто пагинация, а пагинация + сортировка.

Почему не мускуль или подобное:
- потому что нельзя сделать индекс для всех вариантов фильтра/сортировки - ну не для этого btree индексы.
- потому что есть фильтры которые могут включать очень большое количество от основного второе

не так и страшно, я лично писал такое на основе mysql с репликацией

 

[grigori]

в любом случае кэш поможет в выборке, но потеря в записи/обновлении кэша, как в скорости так и в сложности.

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

 

[Yoskaldyr]

не так и страшно, я лично писал такое на основе mysql с репликацией

проблема в том что есть физические лимиты, которые просто никак не преодолеть. Основная проблема что будет дофига и больше данных копироваться во временные таблицы, а потом уже там сортироваться, а после этого скипаться на нужную страницу. И хорошо если при фильтрации останется всего около 100К - это хороший случай, тогда все пройдет относительно быстро. Но можно отхватить когда после фильтра будет около 90% всех записей, а это больше 10 лямов и их надо скопировать во временную таблицу, а потом отсортировать (особенно весело когда сортировка сразу по 2-м полям одновременно).

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

SELECT attachment_id FROM `xf_attachment` LIMIT 15000000, 10

+---------------+
| attachment_id |
+---------------+
|      17434659 |
|      17434660 |
|      17434661 |
|      17434662 |
|      17434663 |
|       8607405 |
|      17434671 |
|      17434672 |
|      17434673 |
|      17434674 |
+---------------+
10 rows in set (2.62 sec)

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

10 rows in set (0.28 sec)

Если же добавить в первый запрос сортировку то сразу получаем уже

10 rows in set (3.29 sec)

P.S. Не надо писать что база не протюнена и т.п. Памяти дофига, под буфера выделено более чем. Проблема именно в таких списках и в пагинации.

 

[Yoskaldyr]

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

А и еще для уточнения некоторые данные по которым может фильтроваться имеют low cardinality (т.е. жопа в плане сильного уменьшения списков)

 

[WMix]

Если же добавить в первый запрос сортировку то сразу получаем уже

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

SELECT count(*) FROM ...; # все записи
+-----------+
| count(*)  |
+-----------+
| 119926512 |
+-----------+
1 row in set (55.70 sec)

SELECT count(*) FROM ... where ...; # на 1 клиента (у нас и таких запросов нет, это тоже много)
+----------+
| count(*) |
+----------+
|     6643 |
+----------+
1 row in set (0.01 sec)

SELECT SQL_NO_CACHE * FROM .. where ...  order by ... limit 6000, 10;
10 rows in set (0.02 sec)

 

[флоппик]

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

 

[fixxxer]

А и еще для уточнения некоторые данные по которым может фильтроваться имеют low cardinality (т.е. жопа в плане сильного уменьшения списков)

Если данных не очень много (читай помещаются на одном сервере), то postgresql+jsonb+gin вполне достаточно для большинства задач такого рода.

 

[Yoskaldyr]

денормализацию данных для разных возможных сортировок и/или фильров

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

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

Ну так и я о чем же. Я просто привел пример самого простого запроса с пагинацией и показал типичный пример тормозов, которые принципиально не вылечить никакой оптимизацией запросов. Вернее есть пару хаков, в таблице только идшник, а все остальное джойнится, но все равно но в лучшем случае будет 0.5 сек (это на вскидку исходя из относительных размеров строки таблицы на которой делал запрос и строки из одного идшника). В реальности (ведь надо хотя бы пару полей в таблице минимум) даже со всеми хаками и т.п. быстрее секунды на таких объемах не получить.

postgresql+jsonb+gin

главный вопрос - нормально переварит неадекватную пагинацию за вменяемое время?

 

[grigori]

SELECT attachment_id FROM `xf_attachment` LIMIT 15000000, 10

+---------------+
10 rows in set (2.62 sec)

и это без сортировки без каких либо фильтров, attachment_id - примари индекс.

P.S. Не надо писать что база не протюнена и т.п. Памяти дофига, под буфера выделено более чем. Проблема именно в таких списках и в пагинации.

1. если с базой что-то не так, то почему не стоит про это писать?

дай EXPLAIN ANALYZE для этого запроса и структуру таблицы

давай для сравнения сделаем просто таблицу на 20 млн записей с PK типа int64, и проверим скорость select limit 10 offset 15m
а то еще окажется, что attachment_id там типа string uuid


2. обрати внимание, что в пангинациях почти везде ограничение на 1000 записей - гугл, яндекс, амазон, сайты магазинов
какой у тебя business case, в котором нужен offset 15M? экспорт всего каталога?

 

[c0dex]

1. если с базой что-то не так, то почему не стоит про это писать?

дай EXPLAIN ANALYZE для этого запроса и структуру таблицы

давай для сравнения сделаем просто таблицу на 20 млн записей с PK типа int64, и проверим скорость select limit 10 offset 15m

2. обрати внимание, что в пангинациях почти везде ограничение на 1000 записей - гугл, яндекс, амазон, сайты магазинов
какой у тебя business case, в котором нужен offset 15M? экспорт каталога?

Могу привести свой пример, такие скажем 5ток копеек, у меня в одной из таблиц сейчас 120047424 строк, это некий лог изменений сущностей, если сделать limit 120000000, 10, то мы будем ждать (10 total, Query took 61.6894 seconds).

Но если делать те выборки что надо, там выходит по 0.5-1 секунде на выбор. Просто так пагинировать такие объемы данных имхо редко когда надо, кто долистает до 100500 страницы?

Мне кажется что у @Yoskaldyr нет полного ТЗ

 

[fixxxer]

главный вопрос - нормально переварит неадекватную пагинацию за вменяемое время?

Ну, чем больше оффсет, тем хуже, но если там не стопицот миллионов, должно быть терпимо.

В принципе, если offset прямо становится проблемой, всегда есть интерфейсный хак с load more

Ещё как-то, помню, делал кэширование кусками по 100 с запасом "на следующие пару страниц", чего хватало для типичного поведения пользователя.

 

[grigori]

дело в том, что когда мы работаем со 120 млн записей, это не бывают оперативные данные,
это всегда или стабильный исторический массив, или набор каталогов нескольких разных источников

у меня был случай, когда условный Cannon выгружал свой каталог на 100500 запчастей, а Toshiba на другие 100500 запчастей, и мне все это надо было выгружать в 100 тысяч одновременных соединений,
но это разные каталоги, базу можно шардировать, и 12 млн не лежат в одной таблице
а когда лежат - они не обновляются каждый день, просто потому что в реальном мире никто не может менять миллион товарных позиций в неделю