Первоначально Написал Эндрю Мур
Изменения в таблице MySQL могут прервать производственный трафик, что приведет к ухудшению качества обслуживания клиентов или, в худшем случае, к потере дохода. Не все администраторы баз данных, разработчики, syadmins знают MySQL достаточно хорошо, чтобы избежать этой ловушки. Администраторы баз данных обычно сталкиваются с такими прерываниями работы при работе со сценариями обновления, которые затрагивают как приложение, так и базу данных, или если неопытный инженер admin / dev выполняет изменение схемы, не зная, как работает MySQL внутри.
Истины
* Прямые блокировки таблиц MySQL ALTER на время изменения (до 5.6)
* Онлайн DDL в MySQL 5.6 не всегда онлайн и может вызывать блокировки
* Даже с изменением схемы Percona Toolkit pt-online-schema-существует несколько рабочих нагрузок, которые могут блокировка опыта
Здесь, в команде Percona MySQL Managed Services, мы призываем наших клиентов работать с нами при планировании и выполнении миграций схемы. Мы стремимся к тому, чтобы мы использовали лучший метод, доступный в данных обстоятельствах. Наши намерения избегать блокировок при выполнении DDL для больших таблиц гарантируют, что бизнес может продолжаться как обычно, в то время как мы стремимся улучшить время отклика или добавить функциональность приложения. Суть в том, что бизнес, полагающийся на доступ к своим данным, не может позволить себе не работать во время основных торговых часов.
Многие из управляемых нами установок по-прежнему ниже MySQL 5.6, что требует от нас поиска обходных путей, чтобы минимизировать количество сбоев, которые может вызвать миграция. Это может повлечь за собой продвижение подчиненного устройства или изменение схемы с помощью инструмента «онлайн-изменения схемы». MySQL версии 5.6, по-видимому, решает эту проблему путем уменьшения числа сценариев, когда таблица перестраивается и блокируется, но еще не охватывает все возможные ситуации, например, при изменении типа данных столбца необходимо полное перестроение таблицы. Тема 5.6 «Online Schema Change» подробно обсуждалась в прошлом году в посте « Изменения схемы — что нового в MySQL 5.6? Пшемыслав Малковский
С появлением новых функциональных возможностей в MySQL 5.7 мы ожидаем неблокирующих операций DDL, таких как; ОПТИМИЗИРУЙТЕ ТАБЛИЦУ и ПЕРЕИМЕНУЙТЕ ИНДЕКС. ( Подробнее )
Лучший совет для пользователей MySQL 5.6 — изучить матрицу, чтобы ознакомиться с ситуациями, когда лучше всего взглянуть за пределы MySQL, чтобы выполнить изменения схемы. Хорошей новостью является то, что мы находимся на верном пути к решению этой проблемы.
По правде говоря, блокирующее изменение обычно будет оставаться незамеченным на таблице 30 МБ, и мы склонны использовать прямое изменение в этой ситуации, но на таблице 30 ГБ или 300 ГБ мы планируем кое-что сделать. Если есть период времени, когда активность низкая, и это разрешает блокировку таблицы, то иногда лучше выполнить в этом окне. Хотя мы часто реагируем на новые операторы SQL или новые проблемы с производительностью, и требуется аварийный индекс, чтобы уменьшить нагрузку на мастер, чтобы уменьшить время отклика.
К pt-osc или нет к pt-osc?
Как уже упоминалось, pt-online-schema-change является неотъемлемой частью нашего рабочего процесса. Обычно это правильный путь, но у нас все еще есть случаи, когда, например, pt-online-schema-change нельзя использовать; когда таблица уже использует триггеры. Важно напомнить себе о шагах, которые проходит pt-online-schema-change для завершения своей работы. Давайте посмотрим на исходный код, чтобы определить их;
[moore @ localhost] $ egrep ‘Шаг’ pt-online-schema-change # Шаг 1: Создать новую таблицу. # Шаг 2: Измените новую пустую таблицу. Это должно быть очень быстро, # Шаг 3: Создайте триггеры для регистрации изменений в исходной таблице и <- (блокировка метаданных) # Шаг 4: Скопируйте строки. # Шаг 5: Переименовать таблицы: orig -> old, new -> orig <- (блокировка метаданных) # Шаг 6: Обновить ограничения внешнего ключа, если есть дочерние таблицы. # Шаг 7: Бросить старый стол.
[moore@localhost]$egrep'Step'pt-online-schema-change # Step 1: Create the new table. # Step 2: Alter the new, empty table. This should be very quick, # Step 3: Create the triggers to capture changes on the original table and <--(metadata lock) # Step 4: Copy rows. # Step 5: Rename tables: orig -> old, new -> orig <--(metadata lock) # Step 6: Update foreign key constraints if there are child tables. # Step 7: Drop the old table.
Я выбрал шаги 3 и 5 сверху, чтобы выделить источник потенциального простоя из-за блокировок, но шаг 6 также вызывает озабоченность, поскольку внешние ключи могут иметь вложенные действия и должны учитываться при планировании этих действий, чтобы избежать связанных таблицы были перестроены с прямым изменением неявно. Есть несколько способов приблизиться к таблице с ограничениями ссылочной целостности, и они подробно описаны в документации по pt-osc. Хорошим подготовительным шагом является рассмотрение структуры вашей таблицы, включая ограничения и то, как рябь изменений может повлиять на таблицы вокруг нее. ,
Недавно нас предупредили об инциденте после того, как клиент с высокой параллельной и транзакционной рабочей нагрузкой запустил стандартный скрипт изменения схемы pt-online для большой таблицы. Это показалось им нормальным, и через несколько часов наш пейджер был уведомлен о том, что на этом клиенте достигнут предел max_connections. Так что же происходит? Когда pt-online-schema-change достигло шага 5, он попытался получить блокировку метаданных, чтобы переименовать оригинал и теневую таблицу, однако это не было сразу предоставлено из-за открытых транзакций, и поэтому потоки начали стоять в очереди за командой RENAME. Фактическое влияние, которое это имело на клиентское приложение, было простоем. Не удалось установить новые подключения, и все существующие потоки ожидали команды RENAME.
Блокировка метаданных
Введена в 5.5.3 на уровне сервера. Когда транзакция начинается, она получает блокировку метаданных (независимо от механизма хранения) для всех таблиц, которые она использует, а затем освобождает их по завершении своей работы. Это гарантирует, что ничто не может изменить определение таблицы, пока транзакция открыта.
С некоторой дальновидностью и планированием мы можем избежать этих ситуаций с нестандартными параметрами pt-osc, а именно — nodrop-new-table и -no-swap-tables. Эта комбинация оставляет таблицу теней и триггеры на месте, чтобы мы могли инициировать атомное RENAME, когда позволяет загрузка.
РЕДАКТИРОВАТЬ: начиная с версии 2.2 percona-toolkit у нас есть новая переменная –tries, которая вместе с –set-vars была развернута для охвата этого сценария, где различные операции pt-osc могут блокировать ожидание блокировки метаданных. Поведение pt-osc (–set-vars) по умолчанию заключается в установке следующих переменных сеанса при подключении к серверу;
wait_timeout = 10000
innodb_lock_wait_timeout = 1
lock_wait_timeout = 60
при использовании –tries мы можем детально определить операцию, количество попыток и интервал ожидания между попытками. Эта комбинация гарантирует, что pt-osc своевременно прекратит собственный сеанс ожидания, чтобы избежать накопления потока, и предоставит нам цикл, чтобы попытаться получить нашу блокировку метаданных для триггеров | rename | fk management;
–Tries swap_tables: 5: 0.5, drop_triggers: 5: 0.5
Документация находится здесь http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change–tries
Это показывает, что даже при использовании такого инструмента, как pt-online-schema-change, важно понимать предостережения, представленные в решении, которое вы считаете наиболее адекватным. Чтобы определить направление движения, используйте блок-схему, чтобы убедиться, что вы учитываете некоторые предостережения об изменении схемы MySQL. Обязательно ознакомьтесь с рекомендуемым результатом, хотя, поскольку есть неизведанные области, такие как дисковое пространство, загрузка ввода-вывода, которые не показаны на диаграмме.
Выбор правильного варианта DDL
Убедитесь, что вы знаете, какой эффект окажет ALTER TABLE на вашу платформу, и выберите правильный метод, соответствующий вашему времени безотказной работы. Иногда это означает задержку изменения до периода более легкого использования или использования инструмента, который позволит избежать удержания стола заблокированным на время операции. Прямой ALTER иногда является ответом, например, когда на столе установлены триггеры.
— В большинстве случаев pt-osc — это именно то, что нам нужно.
— Во многих случаях pt-osc необходим, но способ его использования требует настройки.
— В некоторых случаях pt-osc не является подходящим инструментом / методом, и мы должны рассмотреть Собственная блокировка ALTER или использование аварийного переключения для манипулирования изменениями на всех хостах в кластере реплик.
Если вы хотите узнать больше о том, как избежать простоев, пожалуйста, настройтесь на мой вебинар в среду, 19 ноября, в 10:00 по тихоокеанскому времени. Он называется «Советы из окопов: руководство по предотвращению простоев для чрезмерно расширенных администраторов баз данных». Зарегистрироваться! (Если вы пропустите это, не волнуйтесь: поймайте запись и загрузите слайды с той же самой страницы регистрации .)