Убей в себе государство (slonik_v_domene) wrote,
Убей в себе государство
slonik_v_domene

Category:

Как расшардить MyISAM/InnoDB таблицу в 100M записей

Вот такая вот задачка: надо таблицу в 100 миллионов записей побить на N маленьких. Зачем? Затем, что большая таблица совершенно неуправляемая - ни ALTER не сделать, ни забекапить по-человечески. А если там индекс не дай бог развалится, то пиши пропало - ни в жизнь оно никогда не прочекается.


1. Считаем MD5(Primary Key) из таблицы.
От этого MD5 нам потребуется 3 последние цифры: последние две однозначно указывают имя таблицы в кластере, а еще одна – имя сервера в кластере. Поскольку функция MD5 имеет практически линейное распределение, учетные данные пользователей равномерно разносятся по таблицам.
3858f62230ac3c915f300c664312c63f
Кластер: 6
Таблица: 3f


Использование 16 кластеров (0 - F) по 256 таблиц в каждом (00 - FF) дает 16 * 256 = 4096 таблиц. При текущем количестве записей 100 миллионов, в каждой таблице находится около 25 000 записей. При росте количества пользователей до 200 миллионов, количество записей в каждой таблице не превысит 50 000.
В случае, если использовать не 16 кластеров, а 8, количество записей в таблицах соответственно удвоится и составит 50 и 100 тысяч. Для четырех кластеров записей будет 100 и 200 тысяч на одну таблицу.
Теперь все это без проблем бэкапится практически "на лету" с любого slave. Кроме того, нагрузка на каждую таблицу снижается прямо пропорционально количеству таблиц.

Это теория.

На практике первый вопрос: как перетащить данные из одной большой таблицы во много маленьких? Второй - консистентность старой БД и новой

Собственно, этому посвящены пункты 2 - 4.

2. Пишем уровень абстракции от БД.
Если у вас используется мега-ОРМ, в котором вы не можете на лету менять имя таблицы - вы сам себе неудачник. Если же ОРМ не совсем заумный и туда можно запихать изменение имени таблицы "на лету", а еще лучше - вовсе нет никакого ОРМ, задача имеет простое решение: ищем ВСЕ места, где есть INSERT/UPDATE/DELETE и дублируем все изменения сразу в старую и новую систему.
Если проект написан не через жопу (у меня это так), сделать все можно обычным грепом.
После того как все это сделали, поднимаем новую схему с большим количеством таблиц (структура таблиц одинакова, отличаются только имена: в старой системе table, а в новой - table_00 .. table_FF) и запускаем полученную "репликацию".
Все хорошо, новые данные пишутся в таблицы.
Осталась ровно одна проблема: перенести старые данные из огромной таблицы.

3. Переносим данные.
Если в таблицу регулярно идут INSERT/UPDATE/DELETE, то на таком количестве данных любая выборка типа SELECT * FROM table LIMIT ... OFFSET .... приводит к тому что таблица намертво лочится, а проект начинает сосать.
Поэтому есть два варианта:

3.1 Выборка по Primary Key с указанием LIMIT:
Выбираем порцию данных по условию WHERE PK > PK_start, запоминаем предыдущую позицию (в PK_start), и так - до тех пор, пока все не перенесется.

3.2 Если в таблице ОЧЕНЬ кривой Primary Key или его нет вовсе (бывает и такое), нам поможет механизм MySQL HANDLER. Фактически, HANDLER - это низкоуровневый итератор по таблице. Все, что надо сделать - открыть обработчик по таблице
HANDLER table OPEN AS h1
HANDLER h1 READ FIRST

а затем - повторить действие
HANDLER h1 READ NEXT LIMIT 1000
столько раз, сколько потребуется для того чтобы выгрести все данные. У вытаскиваемыех данных по вышеописанной схеме считаем MD5(PK) и вставляем в таблицы посредством INSERT.
По окончании процедуры не забваем закрыть обработчик.
HANDLER h1 CLOSE

4. Консистентность.
Мы вставляем данные в новые таблицы посредством REPLACE. Если у нас уже есть запись в таблице (а это возможно, т.к. в п.2 мы сделали репликацию), REPLACE данные не изменит. То есть, новые записи УЖЕ синхронизированны, и мы только добавляем старые.

5. Кофе-брейк
После того как все данные перетащили, все протестировали, через API переключаем SELECT'ы на новые таблицы. Ура, миграция окончена, можно выпить кофе.

6. Как сделать лучше?
Очевидно, что закладываться на то что более никаких миграций не потребуется, мягко говоря, неправильно. Поэтому есть смысл не просто 1:1 копировать названия полей из старой таблицы в новые, а добавить еще одно поле типа char[3] (char[4], char[5] ...), куда .... прааавильно, записать используемую часть MD5(PK). Тогда в следующий раз мы сможем не считать MD5(PK), что даст в ряде случав неиллюзорный выигрыш по скорости.

7. На что обратить внимание?
Даже не пытайтесь вставлять данные в таблицу с наличествующим PRIMARY KEY, да и вообще любыми другими индексами - результат будет крайне плачевным по скорости из-за огромного количества seek-ов по диску. Сначала надо создать таблицу без индексов, смигрировать в нее данные и только после этого создать индексы.

8. Вместо пост-скриптума.
8.1 Найти проектанта хуева, что просрал все полимеры момент, когда надо было дробить данные и дать ему в морду за такое количество записей в одной таблице.

8.2 Все успешно перенесено. 100M записей отпроцессилось за неполные три дня; в среднем скорость от 500 до 1000 запросов в секунду. Да, во время миграции была деградация скорости, но три дня на такое - все равно приемлемо.


Update, спасибо Андрею Ф.:
Можно возразить, что если мы умножим на кол-во таблиц и получаем (сюрприз!), что общая нагрузка на систему в целом не изменилась.
А на самом деле нагрузка падает, потому что есть множество нелинейных процессов:
- банальная скорость поиска в B-tree вырастет, пусть даже и немного - логарифмически.
- можно поднять несколько инстансов на сервере и не упираться в один пул соединений
- альтер будет блокировать только часть стейтментов
- репликационный трафик в запросах в секунду на 1 ноду будет ниже, т.к. реплицироваться будут разные ноды, а не одна большая
Tags: brain-damage, mysql, рабочее
Subscribe

  • Post a new comment

    Error

    Anonymous comments are disabled in this journal

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

  • 71 comments