Как работать с базой на 2 миллиарда записей?

Question

[Muskat12]

Сейчас есть SQLITE БД на 2.3 миллиарда строк. SELECTом искать в ней что-то очень долго, а с учетом того, что этих селектов от 100 за одну операцию, то это вообще вечность. Думаю перейти на PostgreSQL. Смысл есть? Если, нет то посоветуйте другие варианты решения проблемы.

Comments

[Muskat12]

Индексы создал, но поиск это существенно не ускорило, плюс мне кажется они не нужны особо. Таблица выглядит так:

id username balance

Разве индексы тут спасут?)

[mayton2019]

Вовсе не факт что переход на PG улучшит ситуацию. Я-бы не стал ничего делать до тех пор пока не станет ясно что мы хотим улучшать и как.

Есть идея что index-organized tables помогут. Это гибрид таблицы и инедкса (2в одном). И если автор ищет только по id то тогда можно на этом сыграть уменишив количество IOPS. Такой род таблиц поддерживается в Oracle и MSSQL.

Есть идея что если у автора нет требований по ACID то можно брать NoSQL системы. Но я-бы сначала все таки исчерпал возможности классических систем.

По поводу 100 селектов за операцию. Есть идея - делать пакетные выборки. Тоеть заранее подготовить список ID-шников и искать по SELECT .... id in (?,?,?.....). Это сэкономит на количестве сетевых roundtrips.

Answer 1

[Ипатьев]

Какой-то странный ответ выбран решением.
По количеству "фич" SQLite может поспорить со многими "настоящими" базами данных. Я уверен, что автор этого заявления не знает и 5% фич что Sqlite, что Postgres, что mysql.
Непонятно, за счёт чего "Postgresql конечно будет сложнее". Запросы практически те же самые, код тоже особо не изменится. Понятно, что надо будет собственно установить постгрес или там mysql, но это разовое действие, а сама работа с Postgresql ничуть не сложнее чем с sqlite.

Главное отличие sqlite от "нормальных" баз данных не в какой-то воображаемой "простоте", а в том что она встроенная. То есть запускается вместе со скриптом, и вместе с ним же умирает. И памяти ей доступно ровно столько, сколько скрипту. Из чего следует, что индексы, хотя и могут помочь, но их приходится заново считывать в память при каждом запуске скрипта.

В то время как классические DBMS являются постоянно висящими в памяти демонами. У которых все индексы, соответственно, тоже висят в памяти. То есть при обработке запроса система может не сделать вообще ни одного обращения к диску. За счет чего достигается огромный прирост производительности. Надо только следить за тем, чтобы хватало памяти, выделенной для индексов.

Comments

[Pan Propan]

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

[mayton2019]

Pan Propan, я-бы задал этот вопрос отдельно для SQLite.

Answer 2

[gsaw]

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

Postgresql конечно будет сложнее, но там и фич больше. Можно шардинг настроить, да и само engine может лучше запросы оптимизировать.

Comments

[Muskat12]

Спасибо за ответ, можно ли попросить у вас какой то список того, что можно почитать для оптимизации всего этого чуда?

[Александр]

Я бы начал с нормирования, далее повесил индексы на нужные поля.

[Muskat12]

Александр,
Индексы создал, но поиск это существенно не ускорило, плюс мне кажется они не нужны особо. Таблица выглядит так:

id username balance

Разве индексы тут спасут?)

[ThunderCat]

Muskat12, оптимизации чего? sqlite так то не предназначена для больших нагрузок. Правильно настроенный индексы, скорее всего, дадут некоторый прирост, но все равно это не будет полноценным решением. А если вы о постгрес - то у вас ее еще нет, что вы будете оптимизировать то?

[ThunderCat]

Muskat12,

Разве индексы тут спасут?)

индексы везде спасут, если по ним осуществляется условный поиск. Просто профит на таком объеме и на текущей бд будет несущественным.

[gsaw]

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

Кстати подумалось. А может sqllite базу разбить на более мелкие? На каждого пользователя свою базу к примеру, или по месяцам. Или что там у вас критерием. Что то, что в каждом запросе участвует и поможет разбить таблицу на более мелкие. Тоже своеобразный горизонтальный шардинг. В postgres это делается практически из коробки, с sqlite надо будет вручную этим заниматься.

Вот к примеру как на postgres

https://www.postgresql.org/docs/current/ddl-partit...

Ну или не прямо по каждому пользователю, а для начала на 26 по каждой букве алфавита. Или как они там еще пользователи могут по группам разбиться. По странам к примеру

[Александр]

Muskat12, судя по таблице, нужно вернуться к вопросу проектирования.
у вас 2,4млрд уникальных username ? или для одного юзера может быть 100 записей о балансе? тогда как вы выбираете нужную ? (последняя?) .
Таблица балансов напрашивается как минимум добавление поля даты, а для деления можно на каждый месяц свою таблицу.

[Muskat12]

Александр, 2.4 лярда уникальных юзернеймов)

[mayton2019]

ThunderCat, вот интересно пишут на сайте SQLite

An SQLite database is normally stored in a single ordinary disk file. However, in certain circumstances, the database might be stored in memory.

Это очень хитрое и лукавое пояснение по системе хранения. Не прикопаешся. Вроде и дисковая БД но вроде и может лежать в мемори. Есть у меня подозрение что все проблемы перформанса SQLite надо просто искать в функциях самой операционной системы. Если она использует memory mapped files то получается что эта система самоустраняется от решения вопросов дискового кеша и оптимизации. Дескыть операционка сама позаботится. Мне кажется что слабое место в SQlite лежит где-то здесь. Почему другие dbms не пошли по такому позорному и легкому пути? Почему у них хардкорная работа с файлом и отдельно с памяью?

[Vitsliputsli]

mayton2019, не хитрое, имеется ввиду, что вы можете создать базу не на диске, а прямо в памяти на время работы приложения. Поэтому и есть указание, что обычно все же пишем на диск.
Классические СУБД предпочитают сами управлять буфером, т.к. только они знают приоритеты. Выдавленная из буфера страница с данными может и не понадобится, а вот страница с индексом уже критично. ОС на это пофиг, ей надо - она займет, не разбираясь.
И справедливости ради, это актуально для данных так или иначе помещающихся в оперативку, если же данных с которыми работаем в разы больше, то читаем диск, как это делают колоночные СУБД.
Т.е. под каждую задачу свой инструмент.

[mayton2019]

Vitsliputsli, давайте прикинем размер сегмента данных

id username balance - кортеж неизвестной длины. Но пускай для простоты id будет int, username будет в среднем 30 символов (однобайтных) и balance будет тоже int числом с fixed point арифметикой.
Тогда размер кортежа = sizeof(int) + 30 + sizeof(int) = 38 байт.

2.3 миллиарда * 38 = 81 Гигабайт

Это только сегмент данных без учота индексов. Не кисло так.

Вот так. Если я ошибаюсь - пускай автор откомментирует.

[Vitsliputsli]

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

[Muskat12]

mayton2019, с учетом индексов вышла база на 400гб :)

[mayton2019]

Muskat12, смотри. Дизайн системы (а не только таблицы должен исходит от юзкейса). Ты писал

SELECTом искать в ней что-то очень долго, а с учетом того, что этих селектов от 100 за одну операцию, то это вообще вечность.

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

Невозможно придумать таблицу которая была бы всем запроса "мила". Затюнишь под OLTP - пострадает аналитика. И наоборот.