first-programmer
@first-programmer
Backend software engineer

Как правильно работать с большим количеством данных?

Всем привет)

Раньше никогда не работал в фирмах где имеется большой объем данных.

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

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

Все это нужно дергать по api из другого нашего сервиса.

Получается мы делаем такие запросы -
- получение данных с учетом пагинации, это 10 записей
- получение данных об общем количестве записей для построения пагинации, это SELECT count(id)
- Потом дергается запрос для получения данных на экспорт (можно подумать как отрефакторить, чтобы не при загрузке страницы дергались данные, а только во время экспорта) - все данные с учетом фильтров. Пока ограничил максимально 10000 записей, но по хорошему нужно наверно миллионы для статистики.
- Запросы на каждый выпадающий список в фильтрах - SELECT distinct field_name
- Запрос при фильтрации и сортировке - SELECT * FROM some_table WHERE field_name LIKE '%value%'

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

Получается что нужно куча раз обработать запросы к таблице с миллионами данных. Сейчас отваливается по таймауту при фильтрации, но не понятно как рефакторить. Если по id делать фильтр, то более или менее, а если по другим полям то отваливается пока. Полей много, разные даты, guid, названия проектов, данные из поля типа json, цены.

На все индексы не поставишь, тем более что один индекс может добавить гигов 5-10 к весу.

Кто работал с такими объемами данных, как это вообще делать, чтобы все шустро работало.

На сервере postgresql.
  • Вопрос задан
  • 1291 просмотр
Решения вопроса 2
FanatPHP
@FanatPHP
Чебуратор тега PHP
Не хочется ругаться, но вопрос очень бессвязный и в нем перемешаны реальные проблемы с нелепыми фантазиями.

И проблема тут не в незнании как работать с большими базами данных, а в неумении работать с БД в целом.

Про идею "всем индекс не поставишь" надо сразу забыть. Там где индекс нужен, он должен стоять без вариантов. Другое дело что тупо натыкать индексов по всем полям, по которым идёт поиск - это тоже глупость. Индекс в запросе может использоваться только один, и индексы по второму-третьему полю уже будут бесполезны. Надо анализировать запросы и, возможно, делать составные индексы.

Детсадовский запрос вида like '%...%' - это отдельный ужас. Надо смотреть на полнотекстовый поиск. А лучше вообще его избегать. На крайний случай использовать внешние поисковые сервисы типа эластика. И только не говори что этот лайк у тебя идёт по полю типа джейсон или "через запятую"

Но самый конечно ужас - это select distinct для фильтров. То есть неумение проектировать бд на самом базовом уровне, непонимание самых начальных принципов реляционных бд, нормализации. Вот с этих принципов и надо начать. В потом уже хвататься за большие объемы. Очевидно, что поля по которым ты собрался делать "distinct" - это должны быть отдельные таблицы, от которых в основной таблице будет просто id. поле размером в 4 байта.

Непонятно, откуда взялись фантазии про гигабайтные индексы, кстати. Большая часть полей в нормальной бд - это не больше десятка байт. То есть индекс - это десятки мегабайт, а не "гигабайты".

В общем, куда лучше бы смотрелись здесь не абстрактные рассуждения про большие объёмы, а конкретный запрос, который "отваливается". С обязательным результатом EXPLAIN

А ответ на абстрактный вопрос "как работать с большими объемами" очень простой: точно так же, как с небольшими. Реляционные бд изначально проектировались под большие размеры. То есть надо просто уметь работать с бд. Читать про реляционную модель, нормализацию, индексы, оптимизацию запросов.

Конкретно для грида надо смотреть в сторону Эластика/Сфинкса. В смысле чтобы не только для полнотекстового поиска, а чтобы все фильтры, которые есть выборке, были забиты в поисковый индекс. И все выборки - через поисковый сервис, а не через прямой запрос к базе
Ответ написан
@rPman
Избавляйся от ... where fld like '%...%' это самые плохие по скорости запросы, хотя не всегда это возможно без увеличения размера базы, вообще полнотекстовый поиск можно отдавать на откуп стороннему приложению (это может расширить возможности фильтрации), считай это еще одной формой индекса, хотя лучше данные по другому хранить. Я встречал ситуации когда в varchar хранили значения справочника из десятка значений, но делали по нему like %% что грузило базу прилично, когда как сделать простой комбобокс на порядок эффективнее и удобнее - поиск по подстроке лучше по справочнику делать на клиенте.

Без индексов скорее всего никуда, если в запросе идет фильтрация по полю, то если не создать на него индекс, этот запрос и будет создавать нагрузку

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

Частный случай прогноза, не делать select ... limit ... на каждую страницу, а при смене фильтра запрашивать весь диапазон, но только идентификаторы, сохранив их либо в таблице на сервере либо на бакэенде, генерирующим запросы вида select ... id in (...), последнее если количество записей не больше тысяч. Кстати именно постраничная навигация с прогруженным списком id самая шустрая

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

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

p.s. диск на сервере hdd или ssd? если не хватает денег на большой ssd, настрой маленький ssd как кеш к медленному hdd (linux bcache), даже когда данные полностью не влезают в кеш, это дает заметный прирост, потому что hdd начинает менее случайно двигаться, плюс добавляется буферизация записи (данные записываются на ssd и лежат там пока hdd не освободится), так же дает эффект ускорения записи вынос журнала файловой системы (если ext4) на ssd (требуется пара гигабайт от силы).

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

Но мне больше нравится резервирование на лету (настроить онлайн репликацию, пусть и на слабый, бакап сервер)
Ответ написан
Пригласить эксперта
Ответы на вопрос 4
shurshur
@shurshur
70 Гб - это вообще не гигантский объём. Люди оперируют террабайтами и даже больше. Главная проблема не в объёме таблицы, а в том, чтобы не читать её целиком (full scan) при выполнении запроса. И вот тут главная фигня: одно только условие like '%слово%' в любом случае требует просмотреть каждую строку, значит, будет full scan. Обычные индексы по этому полю строить бесполезно. Есть всякие полнотекстовые, но в общем случае их тоже надо правильно готовить, чтобы работало приемлемо. Решение может зависеть от задачи. Например, если это ключевые слова в виде текстовой строки с пробелами или иными разделителями, то их можно вынести в отдельную таблицу отдельными строками и проиндексировать там, полнотекстовый поиск тут будет излишним.
Ответ написан
batyrmastyr
@batyrmastyr
- получение данных об общем количестве записей для построения пагинации, это SELECT count(id)

1. count(*), а не count(id)
2. если вас не сильно интересует абсолютно точное значение для миллионов результатов, то делаете оценку количества, начать проще с EXPLAIN <текст запроса> вы можете получить оценку количества результатов. Мы для себя решили, что если по оценке меньше 50 000 строк, то вслед за этим делаем обычный SELECT count(*) для получения точного количества.
Потом дергается запрос для получения данных на экспорт

1. Пожалуй, вам от этого нужно избавляться в первую очередь. Нажал человек на кнопку "экспортировать" - экспортируете, а до этого и дёргаться нет смысла. Фильтры можно получить либо при клике, либо из заголовка referer
2. Если вам нужно абсолютно все данные, то ставите задание на экспорт в очередь и выполняете его в отдельном процессе, сохраняете в файл. Для пользователя рисуете прогресс выполнения и выводите его в нажатую пользователем кнопку, хотя можно тупо на отдельной странице выводить список "заказанных" выгрузок и ссылки на скачивание.
Запросы на каждый выпадающий список в фильтрах - SELECT distinct field_name

Можно с какой-то периодичностью выгружать выхлоп таких запросов в материализованное представление / справочную таблицу / ENUM. Для обновления таких справочников "в реальном времени" можно повесить триггер на вставку в основную таблицу который будет делать INSERT INTO dictionary (value, column_oid) ON CONFLICT / ALTER TYPE ADD VALUE IF NOT EXISTS
После чего в основной таблице заводите рядом поле под идентификатор в справочнике и индексируете уже его.
Запрос при фильтрации и сортировке - SELECT * FROM some_table WHERE field_name LIKE '%value%'

1. если у вас значения длинные (от 8 - 10 символов), то стоит попробовать триграммные индексы. Но на коротких значениях они могут замедлить поиск раза в полтора-два.
2. Полнотекстовый поиск. В частности есть поиск лексемы по префиксу ts_tsquery('сло:*') (быстро найдёт и "слово" и "словарь", но не найдёт "однословное")
3. Для полей по которым вы сделаете словари лучше делать поиск через словарь SELECT * FROM table WHERE column_dictionary_id IN (SELECT id FROM dictionary WHERE value LIKE '%текст%'). В словаре у вас наверняка на порядок - три меньше значений, а несколько сотен или тысяч значений в IN постгрес нормально пережуёт.
Полей много, разные даты, guid, названия проектов, данные из поля типа json, цены.

Активнее используйте функциональные и частичные индексы.
Например, у нас есть кадастровые номера. Триграммный индекс по ним весит 56 мбайт, а BTREE по номерам урезанным до кадастровых кварталов - 15 мбайт, в поиске к "cadastre_id LIKE '11:22:333333:1%'" добавился "AND to_quarter(cadastre_id) = '11:22:333333'", но сам поиск получается на порядок быстрее (~5 мсек вместо 50 - 70).
Главное не забывайте о стоимости этих самых функций - индекс по to_quarter может строиться всего в 1,5 раза дольше нефункционального, если делать LEFT(cadastre, -(position(':' IN reverse(cadastre))), а может и в 100 раз, если использовать регулярку.
На все индексы не поставишь, тем более что один индекс может добавить гигов 5-10 к весу.

Если ещё не обновились, то обновляйтесь на 13-ю версию, там размер BTREE индексов уменьшили в 3 раза. Ну и посмотрите, возможно вам где-то нужны GIST, GIN или BRIN индексы.
Ответ написан
@ComodoHacker
Похоже, вы доросли до вакансии "Разработчик БД". То есть вам нужен человек, хорошо знающий работу СУБД (в вашем случае PostgreSQL) и оптимизацию ее производительности.
Ответ написан
dimonchik2013
@dimonchik2013
совет, который уже дан - ничего не стоит
это тибе
https://github.com/mkabilov/pg2ch
( ну и сам Клихаус, если вдруг не дошло)

а вообще - книжка
https://dmkpress.com/catalog/computer/databases/97...

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

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы
10 мая 2021, в 04:00
1400 руб./в час
10 мая 2021, в 03:06
120000 руб./за проект
10 мая 2021, в 03:04
40000 руб./за проект