Утилита для изменения структуры таблицы без блокировки запросов pt-online-schema-change
Использование утилиты
При необходимости добавить к таблице в MySQL новые индексы, колонки, удалить их, поменять тип таблицы или иным образом изменить её, таблица может оказаться заблокированной для выполнения других запросов. В ряде случаев блокировки таблицы можно избежать, используя опции ALGORITHM и LOCK запроса ALTER TABLE: MySQL 5.7 Reference Manual / Online DDL Operations.
Однако чаще бывает проще воспользоваться утилитой pt-online-schema-change, которая позволяет выполнить практически любое преобразование таблицы без возникновения блокировки других запросов. У утилиты, однако, есть несколько ограничений:
- она не работает с таблицами, у которых имеются триггеры,
- не работает с таблицами без первичного или уникального ключей.
Дело в том, что для преобразования таблицы утилита создаёт новую таблицу с нужной структурой и копирует в неё данные из имеющейся таблицы. А чтобы отслеживать изменения в исходных данных, происходящие в процессе их копирования, утилита создаёт на исходной таблице триггеры. Кратковременная блокировка исходной таблицы происходит только в момент, когда исходная и новая таблицы меняются местами.
Для тестового запуска утилиты нужно вызвать её с опцией --dry-run:
$ pt-online-schema-change --dry-run --alter "ADD INDEX (field_id)" D=db,t=tbl
Опции --alter передаётся выполняемое выражение без ключевых слов ALTER TABLE и без указания имени таблицы. Последними передаются настройки подключения к базе данных, где буквы перед знаком "равно" имеют следующий смысл:
- D - имя базы данных,
- t - имя таблицы,
- h - IP-адрес сервера MySQL или его доменное имя,
- u - имя учётной записи на сервере MySQL,
- p - пароль учётной записи,
- P - номер TCP-порта сервера MySQL для подключения.
Если при тестовом запуске утилита не выдала ошибок, то можно запустить утилиту в режиме исполнения, заменив опцию --dry-run на опцию --execute.
Перед выполнением работы утилита выполняет большое количество различных проверок структуры таблицы и настроек сервера, а в процессе выполнения работы отслеживает показатели нагрузки на сам сервер и обнаруженные реплики.
Например, утилита может отказаться работать, если обнаружит, что на сервере имеются настройки, которые ограничивают список баз данных, для которых ведётся запись в журнал репликации. Чтобы отключить эту проверку, нужно воспользоваться опцией --no-check-replication-filters.
Также утилита может отказаться работать, если для выполнения копирования данных ей придётся воспользоваться индексом, выборка по которому происходит медленно или такой индекс отсутствует вовсе. Для отключения этой проверки можно воспользоваться опцией --no-check-plan.
Если в процессе работы утилиты будут превышены определённые показатели нагрузки на сервер, то утилита может отменить выполнение операции. Например, длительная операция может отмениться из-за большого количества активных потоков на завершающем этапе, когда таблицы нужно поменять местами. По умолчанию утилита считает допустимым не более 50 активных потоков, а для изменения этого лимита может потребоваться указать соответствующую опцию, например, в описанном случае это будет опция --critical-load Threads_running=100.
Если к таблице привязаны триггеры, то утилита тоже отказывается работать. Однако в случае сервера версии 5.7 и выше возможно создание нескольких триггеров, соответствующих одной и той же таблице и операции, что позволяет утилите оставить уже имеющиеся триггеры и добавить свои собственные. Если изменения в структуре таблицы не нарушат работу имеющихся триггеров, то можно указать утилите опцию --preserve-triggers и корректно изменить структуру таблицы с привязанными к ней триггерами.
Утилита для своей работы уменьшает на уровне сеанса значение переменной innodb_lock_wait_timeout до одной секунды. Если в конце переноса данных не удалось поменять таблицы местами и удалить триггеры, а утилита завершилась с ошибкой 'Lock wait timeout exceeded', то можно попробовать вернуть этой переменной значение по умолчанию, равное 50 секундам. Для этого можно добавить в командную строку опцию --set-vars innodb_lock_wait_timeout=50.
Если в других таблицах существуют внешние ключи, ссылающиеся на эту таблицу, то нужно выполнить корректную обработку ограничений в зависимых таблицах. Поскольку исходная таблица будет переименована, то все имеющиеся внешние ключи будут ссылаться на исходную старую таблицу, а при её удалении внешние ключи в других таблицах перестанут работать. Чтобы избежать проблем при изменении внешних ключей, можно добавить в командную строку опцию --alter-foreign-keys-method=rebuild_constraints. Эта опция предписывает сначала удалить все внешние ключи, зависящие от старой таблицы, а после переименования новой таблицы создать внешние ключи заново.
Обходные пути
Иногда обработка таблицы с помощью утилиты pt-online-schema-change может занять слишком много времени, поскольку при использовании этой утилиты происходит копирование всех данных таблицы в другую таблицу:
2023-11-24T12:45:59 Copying approximately 34075768 rows...
Copying `mgate`.`sms_submit`: 0% 22:00:12 remain
Copying `mgate`.`sms_submit`: 0% 1+17:24:08 remain
Copying `mgate`.`sms_submit`: 0% 2+12:58:05 remain
Copying `mgate`.`sms_submit`: 0% 2+21:34:45 remain
Copying `mgate`.`sms_submit`: 0% 3+05:28:24 remain
Copying `mgate`.`sms_submit`: 0% 3+14:09:07 remain
Copying `mgate`.`sms_submit`: 0% 3+22:28:31 remain
Copying `mgate`.`sms_submit`: 0% 4+06:18:21 remain
В отдельных случаях бывает возможно воспользоваться неблокирующим запросом изменения таблицы на месте, без копирования данных. В приведённом выше случае удалось выполнить необходимые изменения в таблице за несколько минут:
mysql> ALTER TABLE sms_submit ADD INDEX provider_id_message_id (provider_id, message_id), ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (2 min 59.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
Точно таким же образом можно вернуть в файловую систему не занятое место из таблицы, расположенной в отдельном файле:
mysql> ALTER TABLE lh_audits ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (19 min 30.75 sec)
Records: 0 Duplicates: 0 Warnings: 0