Как ускорить вставку данных в таблицу с 500 млн записей?

Question

[DamskiyUgodnik]

Всем привет!
Столкнулся с такой проблемой, есть необходимость держать таблицу на 500 млн записей. Таблица MyISAM. Структура таблицы:

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `group_id` int(11) NOT NULL DEFAULT '0',
  `email` varchar(255) DEFAULT NULL,
  `reg_time` timestamp NULL DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD KEY `email` (`email`),
  ADD KEY `group_id` (`group_id`),
  ADD KEY `reg_time` (`reg_time`);

ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

В данные момент решил сделать тест и просто сгенерить синтетические данные, так вот при вставке после 150 млн записей INSERT запросы стали отрабатывать нереально долгое время либо вообще не отрабатывать. Данные добавляю запросами вида:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

По железу: обычный домашний комп (amd 4 ядра, 22 гига памяти), ubuntu, mysql из коробки без доп манипуляций с ним.
Интересует в какую сторону копать?

Comments

[spaceatmoon]

Оффтоп: для чего вам 22ГБ ОЗУ на домашнем компе?

Рассматривали MariaDB?

[Developer]

spaceatmoon, памяти мало не бывает

[spaceatmoon]

1 2, сижу со своих 4ГБ

[DamskiyUgodnik]

spaceatmoon,
Просто получилось взять ооочень за недорого, решил не отказываться :) как сказали выше, памяти мало не бывает. MariaDB пока нет, тут уже спортивный интерес на сколько муторно запустить это в MySql и вообще возможно ли на текущем железе.

[Saboteur]

spaceatmoon, нынче в моде 32гб, а с 6-м поколением интелов и 64гб можно.
Удобно. Можно запустить пару виртуалок, можно сделать рамдиск и компилить на нем. И вообще...

Answer 1

[AlexHell]

Тут пишут про "не лучшее решение" а для каких задач? Автор скажите как пользоваться планируете! Для чтения много запросов? Пропорция чтения / записи какая? 80 чтений, 20 записи? Тогда индексы не удалять точно. Да и вообще вредные советы в духе "вставить пакетно без индексов".. угу, а потом ждать пока индексы построятся по этим миллионам? А если у человека записи идут постоянные, т.е это не 100% чтений, ему что удалять каждый раз индексы, вставлять данные, и заного индексы создавать? Удалять индексы можно 1 раз, для огромного пакета и при отсутствии последующих вставок в течении продолжительного времени, иначе пересоздавать и удалять - не вариант.

Далее, советовали поменять на InnoDB - полностью поддерживаю. MyISAM очень привередлив и может легко корруптится (пруф сейчас не найду), и рекомендуется уж большие то базы (а особенно важные, даже не большие, но большие особенно) точно держать в InnoDB или xtraDB (MariaDB улучшенная версия InnoDB). Там восстановление после сбоев адекватное. По скорости работы надо проводить конкретные замеры для вашей нагрузки (чтений, записи, вашего железа), чтобы еще найти момент на котором MyISAM будет быстрее, что не факт. А восстановление после сбоев дорогого стоит.

Что касается основного подхода: держите индексы в ОЗУ, впрочем Mysql сам это и делает, когда выделяете достаточно оперативки. в MyISAM опции погуглите для задания (если на нем останетесь). А для InnoDB нужно задавать следующие параметры
innodb_buffer_pool_size=1024M
innodb_log_buffer_size=4M
innodb_log_file_size=48M
innodb_log_files_in_group = 2
по их настройке есть целые статьи и книги (от Зайцева в оригинале найдите если нужны подробности). От себя скажу что innodb_buffer_pool_size основная опция для держания всего в ОЗУ, если не умещаются индексы, данные, т.е. по замерам идет подкачка на диск смотрите read/write по дискам.. под linux iostat -dx 5 ; vmstat 5 ; iotop в помощь
innodb_log_buffer_size и innodb_log_file_size задается от размера вставок, чтобы не копились в оперативке слишком много или мало - влияет на сброс лога на диск, читайте подробности и настраивайте по своей нагрузке на запись, точные цифры никто не скажет (правило настройки есть в книге и статьях).
innodb_flush_log_at_trx_commit - доп опция, читайте что делает, может пригодиться, но для надежности лучше default.

Если есть достаточное железо т.е. ОЗУ и диски в raid 10, то InnoDB (xtraDB) обеспечат вам адекватную вставку в 500млн таблицу с вашей несложной структурой. И чтение из нее обеспечат.

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

Хотя по вашей базе я не вижу где тут прямо очень часто надо что-то менять. Новые юзеры часто добавляются? Данные меняются каких полей и как часто? Может не всю таблицу привели и там еще что-то?

Answer 2

[Denis]

убирайте ключи с полей

Comments

[DamskiyUgodnik]

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

[Developer]

DamskiyUgodnik, нужно делать шардинг и держать данные в разных базах

Answer 3

[d-stream]

1. bulk insert
2. при гарантированной корректности данных - немного может поспособствовать вначале влитие данных в таблицу без индексов, а уже потом alter с установкой индексов

Answer 4

[vanyamba-electronics]

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

Сама таблица должна иметь вид:

CREATE TABLE `users` (
  `id` int(11) PRIMARY KEY AUTOINCREMENT,
  `group_id` int(11) DEFAULT NULL,
  `email_id` int(11) DEFAULT NULL,
  `reg_time` timestamp NULL DEFAULT NULL
);

С таблицей e-mail-ов примерно та же схема, только varchar(255) надо заменить на char(63) или char(127), чтобы под текст выделялось место определённого размера в файле таблицы.

Comments

[AlexHell]

"только varchar(255) надо заменить на char(63) или char(127), чтобы под текст выделялось место определённого размера в файле таблицы. " -- поддерживаю, лучше статичного размера, но имеет смысл (для автора) только когда все поля в row будут статичные (нельзя varchar одно поле, а другое char, если вдруг столкнотесь).

А вот "Я бы попробовал заполнить всю таблицу пустыми записями, и при регистрации модифицировал бы их запросом UPDATE. " ИМХО вредный совет. ВО 1х как проверять наличие такого юзера в базу? Фейк записи надо отделать, индекс по какому-то полю .. email не пустой? Во 2х все ID предусмотреть заранее невозможно, плюс у него же в будущем будет всеравно вставка.
В том числе будет перезапись email, group_id, reg_time - в момент перезадания из фейк юзера в реальные параметры.

В 3х " и не происходило копирование файла при добавлении новых записей." это вообще про что? Копирование при добавлении разьве происходит обычно? Помоему в конец дописывает, если нет удаленных фрагментов в середине data файлов. А если и есть - в середину перепишет.
Да и индексы придется перестраивать по email так и так.

Answer 5

[Dimonchik]

развлекайся

лень перечитывать, но нет ничего быстрее как вставка load data infile в myisam без индексов (индексы потом тоже вечность будут делаться, обычно в этом месте ее конвертят в InnoDB перед индексацией)

если в статье не так - буду признателен

PS на 500млн мускуль не лучшее решение, но тут уж от задачи и квалификации