Проблемы ALTER TABLE тормозит база данных Mysql и варианты решения

Привет, Вы узнаете о том , что такое alter table, Разберем основные их виды и особенности использования. Еще будет много подробных примеров и описаний. Для того чтобы лучше понимать что такое alter table , настоятельно рекомендую прочитать все из категории Базы данных, знаний и хранилища данных. Big data, СУБД и SQL и noSQL.

Миграция таблиц MySQL - это хорошо известная проблема, и с 2009 года ее решают с помощью онлайн-инструментов для изменения схемы. Растущие быстро развивающиеся продукты часто требуют изменений в структуре базы данных. Добавление / изменение / удаление столбцов и индексов и т. д. Блокируют операции с поведением MySQL по умолчанию. Мы проводим такие изменения схемы несколько раз в день и хотим минимизировать воздействие на пользователя.

Перед тем, как проиллюстрировать gh-ost, давайте рассмотрим существующие решения и причины, по которым стоит использовать новый инструмент.

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

В мире MySQL уже есть несколько технологий, которые помогают справиться с этой проблемой: online DDL, pt-online-schema-change, gh-ost, JSON поля. Репликация и синхронные кластеры (InnoDB Cluster, Galera) позволяют работать с отличиями в схемах на узлах и обновлять кластер постепенно узел за узлом.

Решение проблемы ALTER TABLE с использованием триггеров

Рассмотрим самый простой способ обхода проблем тормозов при выполнении ALTER TABLE высоконагруженного сервера MySql на основе триггеров.

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

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

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

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

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

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

Выполняем

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

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

Можно вообще не трогать таблицу раз уж на то пошло, а сделать отдельную `users_activity`:

CREATE TABLE `users_lastvisits` (
  `user_id` int(11) NOT NULL,
  `time_avtivity` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Теперь можно во всех запросах где нужен time_avtivity делать 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

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

Другие способы зменения таблиц, Онлайн-миграции схемы

Сегодня изменение схемы онлайн стало возможным с помощью следующих трех основных опций:

• Перенос схемы на реплику, клонирование / применение на других репликах, продвижение отрефакторированной реплики в качестве нового мастера
• Используйте MySQL Online DDL для InnoDB
• Используйте инструмент миграции схемы. Наиболее распространены сегодня pt-online-schema-change и OSC от Facebook ; Также найдены LHMи оригинальные oak-online-alter-table инструменты.

Другие варианты включают непрерывное обновление схемы с помощью кластера Galera и другие механизмы хранения, отличные от InnoDB. В GitHub мы используем общую архитектуру мастер-реплик и надежный движок InnoDB.

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

• Миграция реплики приводит к операционным накладным расходам, что требует большего количества хостов, более длительных сроков доставки и более сложного управления. Изменения применяются явно к конкретным репликам или поддеревьям топологии. Такие соображения, как выход из строя хостов, восстановление хоста из более ранней резервной копии, вновь подготовленные хосты, требуют строгой системы отслеживания изменений для каждого хоста. Для изменения может потребоваться несколько итераций, а значит, больше времени. Повышение реплики до уровня мастера приводит к кратковременному отключению. Сложнее координировать множественные изменения, происходящие одновременно. Обычно мы вносим несколько изменений схемы в день и хотим избавиться от накладных расходов на управление, в то же время признавая, что это решение используется.
• Онлайн-DDL MySQL для InnoDB находится «в сети» только на сервере, на котором он вызван. Поток репликации сериализует, alterчто вызывает задержку репликации. Попытка запустить его индивидуально для каждой реплики приводит к значительным накладным расходам на управление, упомянутым выше. DDL не прерывается; его остановка на полпути приводит к долгому откату или к повреждению словаря данных. Это не играет «хорошо»; он не может дросселировать или останавливаться при высокой нагрузке. Это обязательство по выполнению операции, которая может истощить ваши ресурсы.
• Мы используем pt-online-schema-change уже много лет. Однако по мере роста объема и трафика мы сталкиваемся со все большим количеством проблем, и многие миграции рассматриваются как «рискованные операции». Некоторые миграции можно будет выполнять только в непиковые часы или в выходные дни; другие постоянно вызывают сбой MySQL.
  Все существующие онлайн-инструменты для изменения схемы используют MySQL triggersдля выполнения миграции, и в этом заключается несколько проблем.

Что плохого в миграции на основе триггеров?

Все инструменты онлайн-изменения схемы работают аналогичным образом: они создают призрачную таблицу по подобию вашей исходной таблицы, переносят эту таблицу, пока она пуста, медленно и постепенно копируют данные из исходной таблицы в призрачную таблицу, одновременно распространяя текущие изменения. (любой INSERT, DELETE, UPDATE применительно к вашей таблице ) к призрачной таблице. Когда инструмент убедится, что таблицы синхронизированы, он заменяет исходную таблицу призрачной таблицей.

Инструменты наподобие pt-online-schema-change, LHMи oak-online-alter-table используют синхронный подход, при котором каждое изменение в таблицу транслирует сразу, используя то же самое место сделки, в зеркальном изменения на призрак таблицы. Инструмент Facebook использует асинхронный подход к записи изменений в таблицу журнала изменений, затем повторяет это и применяет изменения к призрачной таблице. Все эти инструменты используют триггеры для определения текущих изменений в вашей таблице.

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

Использование триггеров в целом и миграции на основе триггеров в частности страдают от следующих причин:

• Триггеры, являющиеся хранимыми подпрограммами, представляют собой интерпретируемый код. MySQL не компилирует их заранее. Подключаясь к пространству транзакции вашего запроса, они добавляют накладные расходы на анализатор и интерпретатор к каждому запросу, действующему в вашей перенесенной таблице.
• Блокировки: триггеры используют то же пространство транзакций, что и исходные запросы, и пока эти запросы конкурируют за блокировки в таблице, триггеры независимо друг от друга конкурируют за блокировки в другой таблице. Это особенно остро стоит при синхронном подходе. Конфликт блокировок напрямую связан с параллелизмом записи на ведущем устройстве. Мы столкнулись с почти или полной блокировкой в ​​производственной среде, что привело к тому, что таблица или вся база данных стали недоступными из-за конфликта блокировок.
  Другой аспект триггерных блокировок - это блокировки метаданных, которые им требуются при создании или уничтожении. Мы наблюдали задержки от многих секунд до минуты при попытке удалить триггеры из занятой таблицы в конце операции миграции.
• Без приостановки: когда нагрузка на мастер становится высокой, вы хотите ограничить или приостановить отложенную миграцию. Однако решение на основе триггера не может этого сделать. Хотя он может приостановить операцию копирования строки, он не может приостановить триггеры. Удаление триггеров приводит к потере данных. Таким образом, триггеры должны работать на протяжении всего переноса. Мы видели, что на загруженных серверах, даже когда онлайн-операция дросселируется, мастер отключается из-за нагрузки триггеров.
• Параллельные миграции: мы или другие могут быть заинтересованы в возможности запускать несколько одновременных миграций (в разных таблицах). Учитывая вышеупомянутые накладные расходы на триггер, мы не готовы запускать несколько одновременных миграций на основе триггеров. Нам неизвестно, чтобы кто-то делал это на практике.
• Тестирование: мы можем поэкспериментировать с миграцией или оценить ее нагрузку. Миграции на основе триггеров могут имитировать миграцию только на репликах с помощью репликации на основе операторов и далеки от представления истинной миграции главного компьютера, учитывая, что рабочая нагрузка на реплике является однопоточной (это всегда имеет место для каждой таблицы, независимо от многопоточности). используется технология потоковой репликации).

призрак

gh-ost расшифровывается как GitHub's Online Schema Transmogrifier / Transfigurator / Transformer / Thingy

gh-ost является:

• Без срабатывания
• Легкий
• Приостановлено
• Динамически управляемый
• Проверяемый
• Проверяемый
• Заслуживающий доверия

Без срабатывания

gh-ostне использует триггеры. Он перехватывает изменения в данных таблицы, отслеживая двоичные журналы. Поэтому он работает в асинхронном режиме, применяя изменения к призрачной таблице через некоторое время после того, как они были зафиксированы.

gh-ostожидает двоичных журналов в формате RBR (репликация на основе строк); однако это не означает, что вы не можете использовать его для миграции мастера, работающего с SBR (репликация на основе операторов). Фактически, мы именно так и поступаем. gh-ostс удовольствием читает двоичные журналы из реплики, которая переводит SBR в RBR, и с радостью перенастраивает реплику для этого.

Легкий

Не используя триггеры, gh-ost отделяет рабочую нагрузку миграции от общей основной рабочей нагрузки. Он не учитывает параллелизм и конкуренцию запросов, выполняемых в перенесенной таблице. Изменения, применяемые такими запросами, упрощаются и сериализуются в двоичном журнале, где gh-ostони подбираются для применения в gh-ost таблице. Фактически, gh-ost также сериализует записи копии строки вместе с записью событий двоичного журнала. Таким образом, мастер наблюдает только одно соединение, которое последовательно записывает в таблицу призраков . Это не сильно отличается от ETL.

Приостановлено

Поскольку все записи контролируются gh-ost, и поскольку чтение двоичных журналов является в первую очередь асинхронной операцией, gh-ostможет приостановить все записи на ведущее устройство при регулировании. Регулирование подразумевает отсутствие копирования строк на главном сервере и никаких обновлений строк. gh-ostсоздает внутреннюю таблицу отслеживания и продолжает записывать в эту таблицу события пульса даже при регулировании в незначительных объемах.

gh-ost делает еще один шаг вперед и предлагает несколько элементов управления регулированием:

• Нагрузка: знакомая функция для пользователей pt-online-schema-change, можно установить пороговые значения для метрик MySQL, таких какThreads_running=30
• Задержка репликации: gh-ostимеет встроенный механизм контрольных сигналов, который он использует для проверки задержки репликации; вы можете указать контрольные реплики или gh-ostв первую очередь неявно использовать реплику, к которой вы ее подключаете.
• Запрос: вы можете представить запрос, который решает, должно ли сработать дросселирование. Подумайте SELECT HOUR(NOW()) BETWEEN 8 and 17.

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

• Отметить файл: коснитесь файла, и gh-ostначнется регулирование. Удалите файл, и он возобновит работу.
• Пользовательская команда: динамически подключиться к сети gh-ost(см. Ниже) и дать ей команду начать регулирование.

Динамически управляемый

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

gh-ostслушает запросы через файл сокета unix и (настраивается) через TCP. Вы можете давать gh-ostинструкции даже во время миграции. Вы можете, например:

• echo throttle | socat - /tmp/gh-ost.sock чтобы начать троттлинг. Точно так же вы можетеno-throttle
• Параметры выполнения изменений: chunk-size=1500, max-lag-millis=2000, max-load=Thread_running=30 приведены примеры в инструкции gh-ostпринимает , что изменение его поведения.

Проверяемый

Кроме того, тот же интерфейс может быть использован , чтобы спросить gh-ostо статусе . gh-ostрада сообщить о текущем прогрессе, основных параметрах конфигурации, идентификаторах задействованных серверов и многом другом. Поскольку эта информация доступна через сеть, она дает отличную видимость текущей операции, которую в противном случае вы могли бы найти сегодня только при использовании общего экрана или дополнительных файлов журналов.

Проверяемый

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

gh-ostпоставляется со встроенной поддержкой тестирования с помощью --test-on-replica: он позволяет вам запустить миграцию на реплике, так что в конце миграции gh-ostостановит реплику, заменит таблицы, отменит обмен и оставит вас с обеими таблицами на месте и синхронно репликация остановлена. Это позволяет вам изучить и сравнить две таблицы на досуге.

Вот как мы тестируем gh-ost в производственной среде на GitHub: у нас есть несколько назначенных производственных реплик; они не обслуживают трафик, а вместо этого проводят непрерывный тест миграции для всех таблиц. Каждая из наших производственных таблиц, размером от пустого до нескольких сотен ГБ, переносится с помощью тривиального оператора, который на самом деле не изменяет ее структуру ( engine=innodb). Каждая такая миграция заканчивается остановкой репликации. Мы берем полную контрольную сумму всех данных таблицы как из исходной таблицы, так и из таблицы- призрака, и ожидаем, что они будут идентичны. Затем мы возобновляем репликацию и переходим к следующей таблице. Известно, что каждая из наших производственных таблиц прошла несколько успешных миграций через gh-ost реплику.

Заслуживающий доверия

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

• Тестируем gh-ost на репликах; мы выполнили тысячи успешных миграций, прежде чем впервые опробовать его на мастерах. Вы тоже можете. Перенесите свои реплики и убедитесь, что данные не повреждены. Мы хотим, чтобы вы это сделали!
• По мере выполнения gh-ostи, если вы подозреваете, что нагрузка на главный компьютер увеличивается, продолжайте и инициируйте регулирование. Коснитесь файла. echo throttle. Посмотрите, как нагрузка на вашего мастера вернулась к норме. Просто зная, что вы можете это сделать, вы обретете душевное спокойствие.
• Миграция начинается, и в ETA говорится, что она закончится в 2:00am? Вас беспокоит финальный переход, когда таблицы меняются местами, и вы хотите остаться? Вы можете указать , gh-ostчтобы отложить отрез по сравнению с использованием файла флаг. gh-ostзавершит копирование строки, но не переворачивает таблицы. Вместо этого он будет продолжать применять текущие изменения, синхронизируя призрачную таблицу. Когда вы придете в офис на следующий день, снимите файл с флажком или echo unpostponeв него gh-ost, и нарезка будет сделана. Нам не нравится, что наше программное обеспечение заставляет нас наблюдать за его поведением. Вместо этого он должен освободить нас, чтобы мы могли делать то, что делают люди.
• Говоря об ETA, --exact-rowcount вы будете улыбаться. Заплатите начальную цену за лонг SELECT COUNT(*)на вашей таблице . gh-ost получит точную оценку объема работы, которую ему необходимо выполнить. Он будет эвристически обновлять эту оценку по мере выполнения миграции. Хотя время ETA всегда может меняться, процент прогресса становится точным. Если, как и мы, вас укусила миграция, в которой говорилось, что вы 99% останавливаетесь на час, заставляя вас кусать ногти, вы оцените изменение.

режимы работы gh-ost

gh-ost работает, подключаясь к потенциально нескольким серверам, а также подключаясь как реплика для потоковой передачи двоичных событий журнала непосредственно с одного из этих серверов. Существуют различные режимы работы, которые зависят от ваших настроек, конфигурации и места, где вы хотите выполнить миграцию.

а. Подключиться к реплике, перейти на мастер

Это режим, gh-ostожидаемый по умолчанию. gh-ostисследует реплику, ползет вверх, чтобы найти хозяина топологии, а также подключится к нему. Миграция будет:

• Чтение и запись строковых данных на мастере
• Прочитать двоичные журналы событий на реплике, применить изменения к мастеру
• Изучите формат таблицы, столбцы и ключи, подсчитайте строки на реплике
• Чтение внутренних событий журнала изменений (например, пульса) из реплики
• Переключатель (таблицы переключателей) на мастере

Если ваш мастер работает с SBR, это тот режим, с которым можно работать. Реплика должна быть настроена с включенными двоичными журналами ( log_bin, log_slave_updates) и должна иметь binlog_format=ROW( gh-ostможно применить последнее для вас).

Однако даже с RBR мы предлагаем, чтобы это режим работы с наименьшим вмешательством в мастерскую.

б. Подключиться к мастеру

Если у вас нет реплик или вы не хотите их использовать, вы все равно можете работать непосредственно с мастером. gh-ostвсе операции будут делать прямо на мастере. Вы все равно можете попросить его учитывать задержку репликации.

• Ваш мастер должен создавать двоичные журналы в формате RBR.
• Вы должны одобрить этот режим через --allow-on-master.

c. Перенести / протестировать на реплике

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

• --migrate-on-replicaуказывает на gh-ostто, что он должен перенести таблицу непосредственно на реплику. Он будет выполнять фазу переключения даже во время репликации.
• --test-on-replicaуказывает, что миграция предназначена только для тестирования. Перед тем, как произойдет переключение, репликация останавливается. Таблицы меняются местами, а затем меняются местами: исходная таблица возвращается на свое исходное место.
  Обе таблицы остаются с остановленной репликацией. Вы можете изучить эти два и сравнить данные.

ALTER TABLE tbl_name
    [alter_option [, alter_option] ...]
    [partition_options]

alter_option: {
    table_options
  | ADD [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | ADD [COLUMN] (col_name column_definition,...

продолжение следует...

Продолжение:

Часть 1 Проблемы ALTER TABLE тормозит база данных Mysql и варианты решения
Часть 2 Параметры таблицы - Проблемы ALTER TABLE тормозит база данных Mysql
Часть 3 Требования к производительности и пространству - Проблемы ALTER TABLE тормозит
Часть 4 Отказ от и импорт табличных пространств InnoDB - Проблемы ALTER