Огромная БД mySQL- что изучить?

Question

[Андрей]

Нужно сделать работоспособную базу огромных размеров - будет содержать в себе более 240 миллионов записей в одной таблице, и в несколько раз больше в другой. Сервер есть. С Базами Данных знаком на базовом уровне.
Что посоветуете изучить для быстрого старта?
Правильно ли делать такой проект на mySQl?

Comments

[Илья]

добавьте в вопрос ER модели (структуру таблиц), хотя бы текстом список столбцов в таблице перечислите, чтобы более конкретной была задача

[Андрей]

ответил ниже, пожалуйста гляньте, что бы не дублировать

Answer 1

[Илья]

В основном зависит все не от размера базы, а от того для чего вам эти данные, какая у них структура и как вы их будете обрабатывать.
Для начала, нужно определиться реляционная база или нет нужна.
Потом уже выбирать СУБД в зависимости от ваших задач.

Comments

[Андрей]

Думаю что мне нужна реляционная база.
Храниться будут записи о статусах пользователей в определенных системах. Вся информация в БД сводится к данным аккаунта(пара столбцов varchar(255)) и большому кол-ву значений 0/1(tinyint) отображающих определенные статусы.
Обрабатывать - буду извлекать в текстовые файлы те записи, которые попадают под определенные фильтры, это основное направление. Так же при добавлении новых записей буду проверять уникальность.

[RidgeA]

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

Answer 2

[Максим Кудрявцев]

Первое - составь схему своей БД. Третью нормальную форму знаешь? Приведи свои данные к ней, для начала.
Затем тебе нужно определить условия порций. Ознакомиться с этим можно вот здесь Если коротко - это позволяет хранить БД на более чем одном жестком диске и добавляет скорости, когда у тебя записей в таблице очень-очень много.

Затем тебе нужно понимать, какие операции у тебя происходят наиболее часто - чтение или запись. Исходя из этого выбирай механизм хранения(MyISAM или InnoDB) и оптимизируй настройки СУБД, в т.ч. кеш.

Вообще твой вопрос очень размыт. На него сложно дать однозначный ответ сейчас. Ты пишешь про 240 млн. записей. Хорошо, может быть для тебя это много, лет 5 назад я бы тоже испугался. Ты должен понять, что количество записей мало что играет. Необходимо понимать их структуру, состав, частоту обращений, характер этих обращений, размер и т.п. Выкини из головы цифру и сосредоточься на реализации. Также решай проблемы по мере их поступления. Оптимизировать что-либо стоит тогда, когда это уже работает. Оптимизировать нужно то, что понятно, а не какие-то абстрактные циферки несуществующей еще таблиц БД

Comments

[Илья]

Также решай проблемы по мере их поступления

Очень плохой совет особенно для архитектора.
Можно накосячить в проектировании, а потом думать как же мне провести оптимизацию это огромного куска говна и в итоге уйти на NoSQL.

Третью нормальную форму знаешь

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

[Андрей]

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

БД содержит в себе таблицы

accounts - в ней "столбцы" - ID(автоинкремент), email(varchar 255), password(varchar 255), login(varchar 255), и comment (TEXT). Для comment понимаю что нужно выносить в отдельную таблицу т.к. их будет
немного на общем фоне и это повысит производительность.

services - ID(автоинкремент), name (varchar 255), hard_level(tinyint), domain (varchar 255)

status - ID(автоинкремент), account_id(int 11), service_id(int 11), value(tinyint)

Как это все работает думаю понятно:
В таблицу accounts вносятся аккаунты.
В таблице services практически не меняются сервисы.
В таблице status при появлении информации вносится связка account_id - соответствующий ID аккаунта из acccounts, service_id - соответствующий ID сервиса, и значение 0/1. Для одного аккаунта может быть кол-во записей от нескольких, до полного кол-ва сервисов(160+)

Сейчас в базе есть 18млн тестовых записей в accounts, на основании которых я попробовал заполнить status. там начали получаться огромные значения ID, все лагает*. На реальном количестве аккаунтов 250млн+ - значения ID в таблице status станут просто огромными - грубо говоря что в районе 42500000000 - 12-значное число, что мне кажется соооовсем не здорово.

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

Фильтр может быть вроде: выбрать все аккаунты с +(1) на определенных сервисах(при этом на сервисы есть доп фильтр - по hard-level и domain), + еще к этому всему надо будет прикрутить фильтр по датам, упустил из виду, но в целом сути это не меняет.

*комп i7 3770, 8gb RAM, win7, установлен отдельно Apache и mySQL(не настраивал особо пока). сервер есть примерно такой же мощности, можно ставить любую ОС и делать что угодно.

[Андрей]

Сейчас читаю про noSQL - может и вправду имеет смысл делать с ними?

[Илья]

andrshpa: про таблицу `status`: я думаю id можно просто выпилить, т.к. это М:М таблица, либо сделать его строковым, типа "account_id:service_id" или вовсе составным по тому же принципу (account_id, service_id).

Все лагает при записи в status, или при просмотре через phpMyAdmin??))
Сколько запрос выполнялся?

Ну и собственно ключи по всем нужным полям у вас заданы?
Как по мне (опыта работы с настолько огромными бд у меня к сожалению нет), MySQL должен справляться с 18млн записей, к тому же здесь не так уж и много join'ов.

НУ и одно и туповатых решений: взять другой MySQL совместимый движок (Maria, Percona, ...)

[Андрей]

Илья:
//Все лагает при записи в status, или при просмотре через phpMyAdmin??))
//Сколько запрос выполнялся?

phpMyAdmin не пользуюсь, в db Forge Studio лагает, запрос на запись выполняется достаточно быстро, но вот запросы на получение больших выборок - неимоверно долго, точно дольше чем несколько десятков секунд. База status занимала на диске порядка 40 гб, будучи заполненной для 10 млн аккаунтов, меня это очень беспокоит.

Тип БД - innoDB. Возможно стоит перейти на maria? Но это вроде как не самый лучший вариант.

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

Так же надо учитывать - что 18 млн записей - даже не 10% от реальной рабочей нагрузки..

[Илья]

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

Попробуйте сделать запрос на выборку с LIMIT 1 (10, 100), много времени скорее всего занимает именно вывод а не сам запрос.

Тип БД - innoDB. Возможно стоит перейти на maria? Но это вроде как не самый лучший вариант.

MariaDB - это СУБД, InnoDB -это движок. Если верить оф. документации, а также стереотипам (здесь), то лучше использовать XtraDB.
Можно попробовать поизвращаться с ColumnStore.
Если говорить про Percona, то там тоже есть XtraDB, которая якобы "для работы с огромными базами" (но это не точно).

Answer 3

[Андрей]

Посматриваю в строну noSQL, в частности - Хранилища колонок, а именно Apache Cassandra.
Но совсем не уверен что это будет правильным выбором. Смущает отсутствие возможности делать привычные выборки, и так же то, что данная СУБД рассчитана больше на постоянное добавление в неё данных чем на работу с существующими.
Я несколько в замешательстве. С одной стороны, в некоторых аспекта noSQL очень привлекателен. Но как делать на нем подобные выборки //Фильтр может быть вроде: выбрать все аккаунты с +(1) на определенных сервисах(при этом на сервисы есть доп фильтр - по hard-level и domain), + еще к этому всему надо будет прикрутить фильтр по датам, упустил из виду, но в целом сути это не меняет.
я слабо представляю. Как в принципе делать их на реляционных базах данных - понятнее.

Более подробное описание задачи(дубль из моего коммента выше):
"БД содержит в себе таблицы
accounts - в ней "столбцы" - ID(автоинкремент), email(varchar 255), password(varchar 255), login(varchar 255), и comment (TEXT). Для comment понимаю что нужно выносить в отдельную таблицу т.к. их будет
немного на общем фоне и это повысит производительность.
services - ID(автоинкремент), name (varchar 255), hard_level(tinyint), domain (varchar 255)
status - ID(автоинкремент), account_id(int 11), service_id(int 11), value(tinyint)

Как это все работает думаю понятно:
В таблицу accounts вносятся аккаунты.
В таблице services практически не меняются сервисы.
В таблице status при появлении информации вносится связка account_id - соответствующий ID аккаунта из acccounts, service_id - соответствующий ID сервиса, и значение 0/1. Для одного аккаунта может быть кол-во записей от нескольких, до полного кол-ва сервисов(160+)

Сейчас в базе есть 18млн тестовых записей в accounts, на основании которых я попробовал заполнить status. там начали получаться огромные значения ID, все лагает*. На реальном количестве аккаунтов 250млн+ - значения ID в таблице status станут просто огромными - грубо говоря что в районе 42500000000 - 12-значное число, что мне кажется соооовсем не здорово.

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

Фильтр может быть вроде: выбрать все аккаунты с +(1) на определенных сервисах(при этом на сервисы есть доп фильтр - по hard-level и domain), + еще к этому всему надо будет прикрутить фильтр по датам, упустил из виду, но в целом сути это не меняет.

*комп i7 3770, 8gb RAM, win7, установлен отдельно Apache и mySQL(не настраивал особо пока). сервер есть примерно такой же мощности, можно ставить любую ОС и делать что угодно. "
"

Comments

[Илья]

Как ваши успехи? На чем в итоге нормально поднялась база?

[Андрей]

Илья: данный ответ был написан раньше вашего последнего комментария у предыдущему посту. Решил все таки делать на mySQL, точнее на mariaDB, взял версию 10.2.7 (стабильная и актуальная на данный момент), правда там используется движок innoDB, но обещают что он сейчас имеет такую же производительность как и XtraDB. Подробнее на офф.сайте.
Нереляционные БД все таки рассчитаны на намного более значительные объемы и несколько другие задачи - пугает то что там сложнее делать выборки по большому кол-ву параметров(в некоторых случаях - просто невозможно).
Благодарю за советы, я обязательно отпишу как только получу хороший результат, думаю это будет уже скоро.

Answer 4

[xmoonlight]

Научитесь тестировать производительность схемы базы данных и профилировать производительность запросов к этой базе.
mysql - более, чем достаточно.
Читайте ТОЛЬКО! оф. документацию!

Comments

[Андрей]

Благодарю за ответ!
Самое плохое в оф. документации - она на английском. Но этот совет я услышал, буду стараться, тем более уже сталкивался в других своих проектах с тем, что английский оф. мануал - самый лучший и результат получился только с ним.
Вопрос - придуманная мной схема БД(привожу ниже) правильная или её нужно менять в корне?

"БД содержит в себе таблицы
accounts - в ней "столбцы" - ID(автоинкремент), email(varchar 255), password(varchar 255), login(varchar 255), и comment (TEXT). Для comment понимаю что нужно выносить в отдельную таблицу т.к. их будет
немного на общем фоне и это повысит производительность.
services - ID(автоинкремент), name (varchar 255), hard_level(tinyint), domain (varchar 255)
status - ID(автоинкремент), account_id(int 11), service_id(int 11), value(tinyint)

Как это все работает думаю понятно:
В таблицу accounts вносятся аккаунты.
В таблице services практически не меняются сервисы.
В таблице status при появлении информации вносится связка account_id - соответствующий ID аккаунта из acccounts, service_id - соответствующий ID сервиса, и значение 0/1. Для одного аккаунта может быть кол-во записей от нескольких, до полного кол-ва сервисов(160+)"

Answer 5

[asd111]

https://www.percona.com/blog/2006/06/09/why-mysql-...

Comments

[Илья]

Статья 2006 года, неактуальная, да и к тому же "используйте индексы" и норм...

[asd111]

Илья, неактуальная из за того что 2006 года ? Или у вас есть что сказать по поводу данных советов:
1.Try to fit data set you’re working with in memory
2. Prefer full table scans to index accesses – For large data sets full table scans are often faster than RANGE scans and other types of index lookups.
3. Avoid joins to LARGE tables. Joining of large data sets using nested loops is very expensive. Try to avoid it.
По моему опыту советы вполне рабочие для mysql на данный момент для таблиц по 200-500 млн строк. И если делать JOIN с WHERE IN или BETWEEN на двух больших таблицах, то будет грустно, хоть и не очень :) Если делать просто JOIN по ID и не делать where in (1,2,3,4,1,23,1,23,12,3,13) то это совсем другое дело. Это как LIMIT offset начинает деградировать при больших значениях offset с 2006 года как было так и осталось.
Буду рад каким нибудь актуальным ссылкам по джоинам на больших таблицах с where in (12,3,12,3,12,3,12,3,12,3,12,3,12,3).