ALTER очень больших таблиц в MySQL

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

    Дано: таблица размером в несколько десятков гигабайт данных. Задача — изменить структуру таблицы.

    Сразу забегу в перед, работать метод будет только на транзакционных таблицах. Если у вас MyISAM-таблица на десятки гигабайт, то тут как в том анекдоте — «разбирайтесь сами со своими проблемами». Пример будет приведен для InnoDB таблицы.

    Предположим что структура нашей таблицы такая:

    CREATE TABLE `users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `email` varchar(40) NOT NULL DEFAULT '',
      `password_hash` char(32) NOT NULL DEFAULT '',
      `registration_date` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    Мы хотим добавить в эту таблицу поле last_login.

    Какие у нас есть варианты.

    В лоб


    ALTER TABLE `users` ADD COLUMN `last_login` int(11) NOT NULL DEFAULT 0;

    Вариант прекрасно работет на мелких проектах где размер таблиц редко превышает 50 000 записей. Нам вариант не подходит т.к. ALTER будет выполнятся слишком долго и все это время таблица будет заблокирована как на запись так и на чтение. Соответственно сервис нужно будет останавливать на это время.

    Включаем мозг


    Можно вообще не трогать таблицу раз уж на то пошло, а сделать отдельную `users_lastvisits`:
    CREATE TABLE `users_lastvisits` (
      `user_id` int(11) NOT NULL,
      `last_login` int(11) NOT NULL DEFAULT '0',
      PRIMARY KEY (`user_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

    И все таки — нужно добавить поле


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

    Мой способ заключается в следующем


    Создаем новую таблицу с конечной структурой, делаем на первой таблицы триггеры, которые будут логировать все изменения, одновременно с этим начинаем переливать данные из первой таблицы во вторую, а по окончании «вливаем» изменившиеся данные и переименовываем таблицы.

    Итак, подготавливаем 2 таблицы — первая с нужной структурой, вторая для логирования изменений.
    CREATE TABLE `_users` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `email` varchar(40) NOT NULL DEFAULT '',
      `password_hash` char(32) NOT NULL DEFAULT '',
      `registration_date` int(11) NOT NULL DEFAULT '0',
      `lastvisit` int(11) NOT NULL DEFAULT 0,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    CREATE TABLE `users_updated_rows` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `row_id` int(11) NOT NULL DEFAULT '0',
      `action` enum('updated','deleted') NOT NULL DEFAULT 'updated',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    Ставим триггеры:
    DELIMITER ;;
    
    CREATE TRIGGER users_after_delete AFTER DELETE ON users FOR EACH ROW
    BEGIN
      INSERT INTO users_updated_rows VALUES (0, OLD.id, 'deleted');
    END;;
    
    CREATE TRIGGER users_after_insert AFTER INSERT ON users FOR EACH ROW
    BEGIN
      INSERT INTO users_updated_rows VALUES (0, NEW.id, 'updated');
    END;;
    
    CREATE TRIGGER users_after_update AFTER UPDATE ON users FOR EACH ROW
    BEGIN
      IF (OLD.id != NEW.id) THEN
        INSERT INTO users_updated_rows VALUES (0, OLD.id, 'deleted');
      END IF;
      INSERT INTO users_updated_rows VALUES (0, NEW.id, 'updated');
    END;;
    
    DELIMITER ;

    Теперь начинаем переливку. Для этого надо открыть 2 соединения с базой. В одном будет идти собственно переливка, в другом нужно будет ненадолго заблокировать таблицу на запись.
    mysql> LOCK TABLES users WRITE;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> -- триггеры уже должны быть установлены
    mysql> TRUNCATE users_updated_rows;
    Query OK, 0 rows affected (0.17 sec)
    
    mysql> -- в другой консоли пускаем переливку
    mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users;
    
    mysql> -- снова в первой консоли
    mysql> UNLOCK TABLES;

    Все, теперь пока таблица переливается у нас есть время подумать как будем вливать изменившиеся с момента начала переливки данные. Тут вообщем то ничего сложного — скрипт приводить я не буду, нужно просто брать по одной записи из таблицы users_updated_rows в том порядке, в котором они добавлялись (сортировать по первичному ключу) и обновлять или удалять её в таблице _users;

    Итак, переливка таблицы уже закончилась, нужно вливать остальные данные. Запускаем скрипт. Скрипт должен работать постоянно и обновлять все записи которые добавляются в лог, когда он перельет все данные нужно переименовать таблицы:
    mysql> TRUNCATE users_updated_rows;
    Query OK, 0 rows affected (0.16 sec)
    mysql> RENAME TABLE users TO __users, _users TO users;
    Query OK, 0 rows affected (0.11 sec)

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

    Если все сделать правильно данные не будут потеряны и перерывов в работе сервиса практически не будет. Что нам и требовалось. Таким же способом можно перелить данные на другой сервер, поменяется только способ переливки. Вместо
    mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users;

    нужно перелить через mysqldump:
    $ mysqldump -h host1 db users --single-transaction -ecQ | pv | mysql -h host2

    Таким способом мне удалось перелить без остановки работы сервисов на другой сервер таблицу размером в 60Gb и 400 млн строк где то за 12 часов.

    Кстати, велосипед уже изобретен Facebook и называется Online Schema Change for MySQL.

    Similar posts

    AdBlock has stolen the banner, but banners are not teeth — they will be back

    More
    Ads

    Comments 55

      +3
      А просто создать копию таблицы — не?
        +2
        копия такой таблицы так будет создаваться в течении многих часов, нагружая сервер, хотя не будет блокировать работу с таблицей вроде как
          0
          Ну, по сути, в топике именно такое решение — заблокировать один сервер, чтобы он обновился, потом — второй.
            0
            Нет, на момент переливки ничего не блокируется. Работа с первой таблицей как шла так и идет. Можно вообще без лока обойтись — но потеряется ещё немного данных — но впринципе количество незначительное.
              0
              Смотря как переливать. Если INSERT INTO (SELECT FROM ...), то исходная таблица (по которой SELECT) залочится на запись на всё время копирования. Какой-нибудь LIMIT 1000 там обязателен.
                0
                Ну тогда можно сделать скрипт, START TRANSACTION и пачками…
                  0
                  Не факт, что оно не встрянет при COMMIT. Транзакции не нужны, т.к. целевая таблица во время копирования никем больше не используется. А исходная и так залочиться для вставок и апдейтов.
                    0
                    mysqldump по идее делает START TRANSACTION и COMMIT если указать --single-transaction
                      0
                      mysqldump не делает INSERT INTO (SELECT FROM), он делает только SELECT FROM. SELECT FROM не лочит исходную таблицу на вставки, это делает именно связка INSERT INTO (SELECT FROM).
                        0
                        Я о том и говорю — можно сделать скрипт который перельет так же как и mysqldump
        +3
        >> Можно поднять master-slave репликацию, сделать ALTER на slave-сервере а потом поменять их местами.

        Самое правильное решение этой «проблемы». Потеря данных в таком случае сведена к минимуму
          0
          вот бы подробности такого процесса
          0
          Немного непонятно место с блокировкой/разблокировкой таблицы, зачем это делать?
            0
            чтобы на момент «переливки» данных из одной таблицы в другую, уже перенесенные данные не изменялись и не добавлялись в старой таблице.
              –1
              Бред, тогда весь топик ни о чем, это вариант в абзаце «В лоб».
                +2
                почему бред? у вас таблица заблокирована только на запись, на чтение блокировка не ставится, данные доступны для чтения, триггеры отрабатывают и записывают все изменения в другую таблицу из которой потом мы можем подтянуть недостающие изменения
                  +1
                  Блокируется ненадолго не несколько секунд только на момент запуска переливки, чтобы запись в лог и переливка началась в один момент, а не получилось например так что началась переливка, обновилось 1000 записей и только потом началась запись в лог.
                    0
                    errors сыплются, если код не подготовлен заранее к такому сценарию
                +1
                Если этого не сделать, то до начала копирования уже кто-то может внести изменения, которые отобразятся в users_updated_rows, т.е. мы будем считать, что там что-то изменилось, а эти изменения произошли ещё до начала копирования. Хотя чем это отличается от тех изменений, которые будут в ещё не скопированных полях (которые тоже будут залогированы, но при этом скопированы уже изменёнными) не совсем понятно.
                +3
                А вообще это классический способ изменения структуры больших таблиц при невозможности организации master-slave репликации
                  +1
                  Объясните, а почему при изменении записей блокируется вся таблица? Разве нет блокировки на уровне записей, а не таблиц?
                    0
                    Извиняюсь, подумал меняются данные в таблице, а не сама таблица. Но это навело на другое решение:

                    1. Сначала добавляется NULL колонка (вроде как происходит мгновенно, ибо гигабайты записей не затрагиваются)
                    2. Делается UPDATE с блокировкой на уровне записей для установки нужного значения по-умолчанию.
                    3. Добавляется NOT NULL CONSTRAINT.
                    4. Добавляется DEFAULT CONSTRAINT.
                      +1
                      > 1. Сначала добавляется NULL колонка (вроде как происходит мгновенно, ибо гигабайты записей не затрагиваются)

                      точно? )
                        0
                        Точно что? Что мгновенно? Перед тем как писать проверил на таблице с 1m записей — мгновенно.
                          0
                          Странно, а какая версия? Я пробовал на 5.3 на таблице в 50 млн — не мгновенно.
                            0
                            10-я версия.
                              0
                              Да, ниже уже выяснили, что это на MS SQL Server.
                    +2
                      0
                      Да, и фейсбук на неё ссылается — но как говорится пока не сделаешь сам не поймешь как оно работает :)
                      0
                      Часто у себя используем, очень удобно и нам и пользователям, которые этого даже и не замечают)
                        0
                        А если в таблице есть foreign keys?
                          0
                          Не вижу проблемы
                            0
                            Просто у Фейсбука написано:
                              0
                              No foreign keys should exist. Otherwise an error is returned.
                                0
                                Мм, да наверное потому что данные в таблице на которую ссылается изменяемая тоже может меняться. Вообще в таких таблицах лучше внешние ключи особо не использовать.
                            +5
                            ALTER TABLE `users` ADD COLUMN `last_login` int(11) NOT NULL DEFAULT 0;

                            Расскажу, как это мы делаем на Oracle (в таблицах сотни миллионов записей). Возможно, и в MySQL аналогично, но я не уверен, не проверял:

                            Шаг 1. ALTER TABLE users ADD(last_login INT);

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

                            Шаг 2. Делается скрипт, который выполняет UPDATE порциями, обрабатывая по N записей за коммит.

                            UPDATE users SET last_login=0 WHERE id between… AND ...;

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

                            Шаг 3. Вот теперь настало время выполнить ALTER TABLE users MODIFY(last_login DEFAULT 0 NOT NULL);

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

                            P.S. Если у кого есть желание, можете проверить на MySQL, было бы интересно посмотреть результат.
                              +3
                              Понимаете, какая штука… В MySQL практически любое изменение схемы таблиц производится так:
                              1. Лочится таблица.
                              2. Создается ее копия, уже с новой структурой (во временной таблице).
                              3. Исходная таблица удаляется, новая — переименовывается.

                              Это происходит даже тогда, когда вы делаете ADD FOREIGN KEY (операция, вообще не изменяющая данный или число/структуру колонок), и даже (о ужас!) когда добавляете новый элемент в ENUM-поле. То же самое и при создании индекса: пересоздается ВСЯ таблица, сколько бы данных в ней ни было. Так устроена InnoDB, и это происходит с самого его создания, а конца-края такому поведению не видно.

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

                              Все это имеет громадное значение при интенсивной скорости разработки, когда структура базы данных меняется каждую неделю. Поэтому нередко приходится видеть гроздь таблиц, создававшихся только из-за того, что добавлять новые колонки было дорого/неудобно.
                                0
                                Насчет InnoDB — это новая для меня информация, спасибо.

                                Если не трудно, не могли бы Вы пожалуйста дать ссылки, где можно почитать об этом более подробно? (я сходу не нашел)
                                  0
                                  Вот, только что нашел в блогах: mysqlha.blogspot.com/2008/07/ddl-twice-as-fast.html

                                  Prior to 5.1, most DDL operations to a table (add/drop column, add/drop index) are implemented by copying the table and rebuilding all indexes. MySQL docs are rather quiet about this. The handler interface in 5.1 provides methods that allow this to not be done. Adding an index with the InnoDB 5.1 pluging creates the index without copying the table. Not only is this fast, but the index has less fragmentation because it is created with the output from a sort (InnoDB 5.1 includes merge sort).
                                  July 14, 2008 7:02 AM

                                  Может быть все же в более новых версиях MySQL/InnoDB, созданных после 2008 года, данная проблема была устранена (как минимум с ADD FOREIGN KEY)? Ведь декларировалось же Fast Index Creation в MySQL 5.5/5.6 (или ранее в MySQL 5.1 + InnoDB plugin), и т.д…

                                  Ссылка: dev.mysql.com/doc/refman/5.5/en/innodb-create-index-overview.html

                                  Вы не занимались ли этим вопросом? Или то, что Вы написали выше, касается лишь старых версий MySQL? (Сам я сейчас не могу проверить, нет MySQL под рукой).
                                    +1
                                    > Шаг 1. ALTER TABLE users ADD(last_login INT);
                                    попробовал на большой таблице на MySQL 5.3 — мускул ушел думать. Т.е. скорее всего переливка.

                                    Знаю что начиная с 5.1 ENUM мгновенно добавляет новое значение а модификация существующего и удаление так же долго.
                                      0
                                      Спасибо за эксперимент.

                                      Интересно было бы повторить это же самое с последним MySQL 5.5. Если будет тот же результат, то это весьма веский и однозначный аргумент не использовать MySQL в больших проектах. В частности, этот аргумент может быть использован при ответе на типичный вопрос заказчика «а почему не MySQL?». А то обычно это довольно сложно объяснить, без конкретных примеров.
                                        +1
                                        Мы используем MySQL по 2-м причинам:
                                        1. Так исторически сложилось и переписать тонны кода под другую БД очень долго и дорого.
                                        2. Оракл и Postgre никто просто не знает так как MySQL. Мы обновляли MySQL с 5.1 до 5.3 и ещё месяц находили косяки. При переходе на тот же Оракл просто будем смотреть на него и не понимать чего оно не работает.

                                        Поэтому имеем то что имеем и выкручиваемся как можем. Вообщем то недостатки MySQL — это последнее на что мы жалуемся.
                                          0
                                          Да, я понимаю. Например, переход на Oracle с любой СУБД всегда очень болезненен, одна лишь эквивалентность пустой строки '' и NULL чего стоит. Ну да ладно, это оффтопик уже.

                                          По теме. Только что прошел по ссылке в конце Вашей статьи на решение от Facebook, прочитал, осознал. Еще раз спасибо, это была новая информация для меня. Время от времени приходится использовать и MySQL в работе, теперь буду использовать и эту информацию.
                                            0
                                            и все таки раз у вас такие большие объемы данных, то я бы рекомендовал переход на Oracle как стратегическое решение, которое избавит вас от разных других проблем больших баз. Хотя в принципе я вас могу понять — у нас кроме Oracle никто других баз практически не знает и миграция на mySql кажется неподъемной задачей )))
                                              0
                                              Оракл не панацея. Я с ним никогда не работал но разве он горизонтально масштабируется?
                                                0
                                                СУБД oracle для больших баз и нагрузок подходит как нельзя лучше. Поддерживается партиционирование, кластеризация и многое другое. Обзор см habrahabr.ru/blogs/oracle/120003/.
                                    0
                                    Собственно написал о том же выше в рамках MS SQL — все работает.
                                      0
                                      А, у Вас MS SQL. Ну тогда может быть.
                                    0
                                    Я для таких целей делал другую таблицу с такой же структурой + новым полем, и правильным значением для автоинкремента. После этого менял таблицы местами и не спеша переливал старые данные. Ни потерь данных, ни проблем при этом не было. Хотя кончено данные переливались достаточно долго.
                                      0
                                      Это есть у вас туда только вставки идут, а если выборки старых данных?
                                        0
                                        Этой информацией пользуемся только внутри компании, так что пару часов — терпимо.
                                        Хотя конечно вы правы.
                                      +1
                                      Если выполнять перелив с помощью insert ignore, то можно не делать лок исходной таблицы.
                                      Записи, которые проапдетятся/добавятся во время перелива в основной таблице, попадут в копируемую таблицу с помощью триггеров и insert ignore их пропустит.
                                        0
                                        Мда, у нас есть таблица, миллиард записей, терабайт в объёме. Если у вас 60 гиг лилось 12 часов — мы месяц проторчим :)
                                          –1
                                          Да… насколько проще работать с древовидными не SQL структурами при изменении — для того что бы добавить параметр (столбец) — достаточно 1 строки и 1 секунды…
                                            0
                                            Все таблицы в MySQL на движке InnoDB на сколько я понимаю являются кластерными в терминологии Oracle, т.е. неким их подобием, именно по этой причине на каждой таблице будет PK даже если вы его не заказывали, по которому будет сформирован кластер. По сему добавление колонки вызывает существенное изменение самой структуры таблицы а не только словарей.
                                            Способ описанный выше в реально сложной ситуации помогет не сильно, особенно если одаренные разработчики навешали констрейнтов на эту горячую таблицу и сделали кучу индексов, но автор молодец, мы такую таблицу мигрировали ручками… по частям… так ничего и не предумав для автоматизации процесса :) в итоге плюнули остановили сервис и просто добавили поле :) ну постояли часок другой… ну ничего страшного… бывает
                                            Помнится во избежании данной ситуации — мы просто создавали новую таблицу — пихали в нее новой поле и ссылку на большую. Решение кривое, но гарантированно работает без простоя.

                                            Only users with full accounts can post comments. Log in, please.