17.12.2 Online DDL Performance and Concurrency — MySQL 9.2

17.12.2 Производительность и одновременность онлайн DDL

Онлайн DDL улучшает несколько аспектов работы MySQL:

Приложения, которые обращаются к таблице, реагируют быстрее, поскольку запросы и операции DML к таблице могут выполняться во время операции DDL. Снижение блокировок и ожидания ресурсов сервера MySQL приводит к большей масштабируемости, даже для операций, которые не участвуют в операции DDL.
Мгновенные операции изменяют только метаданные в словаре данных. В фазе выполнения операции может быть кратковременно взята эксклюзивная блокировка метаданных на таблице. Данные таблицы не затрагиваются, что делает операции мгновенными. Разрешены одновременные операции DML.
Онлайн операции избегают операций ввода-вывода на диск и циклов процессора, связанных с методом копирования таблицы, что сводит к минимуму общую нагрузку на базу данных. Минимизация нагрузки помогает поддерживать хорошую производительность и высокую пропускную способность во время операции DDL.
Онлайн операции считывают меньше данных в пул буфера, чем операции копирования таблиц, что уменьшает удаление часто используемых данных из памяти. Удаление часто используемых данных может привести к временному падению производительности после операции DDL.

Оператор LOCK

По умолчанию MySQL использует минимальное количество блокировок во время операции DDL. Оператор LOCK может быть указан для операций на месте и некоторых операций копирования, чтобы при необходимости обеспечить более строгую блокировку. Если оператор LOCK указывает менее строгий уровень блокировки, чем разрешено для конкретной операции DDL, операция завершается с ошибкой. LOCK операторы описаны ниже в порядке возрастания строгости:

LOCK=NONE:

Разрешает одновременные запросы и операции DML.

Например, используйте этот оператор для таблиц, связанных с регистрацией клиентов или покупками, чтобы избежать недоступности таблиц во время длительных операций DDL.
LOCK=SHARED:

Разрешает одновременные запросы, но блокирует операции DML.

Например, используйте этот оператор для таблиц хранилища данных, где вы можете отложить операции загрузки данных до завершения операции DDL, но запросы не могут откладываться на длительное время.
LOCK=DEFAULT:

Разрешает максимальную одновременность (одновременные запросы, DML или оба). Пропуск оператора LOCK эквивалентен указанию оператора LOCK=DEFAULT.

Используйте этот оператор, если не ожидаете, что уровень блокировки по умолчанию оператора DDL вызовет проблемы доступности таблицы.
LOCK=EXCLUSIVE:

Блокирует одновременные запросы и операции DML.

Используйте этот оператор, если основная цель - завершить операцию DDL как можно быстрее, и одновременный доступ для запросов и DML не требуется. Вы также можете использовать этот оператор, если сервер должен быть простаивающим, чтобы избежать неожиданных обращений к таблице.

Онлайн DDL и блокировки метаданных

Онлайн операции DDL можно рассматривать как состоящие из трех фаз:

Фаза 1: Инициализация

На стадии инициализации сервер определяет уровень допустимой одновременности во время операции, принимая во внимание возможности хранилища данных, операции, указанные в операторе, и указанные пользователем ALGORITHM и LOCK параметры. На этой стадии берется общая, модернизируемая блокировка метаданных для защиты текущего определения таблицы.
Фаза 2: Выполнение

На этой стадии оператор подготавливается и выполняется. Модернизация общей блокировки метаданных до эксклюзивной зависит от факторов, оцененных на стадии инициализации. Если требуется эксклюзивная блокировка метаданных, она берется только на короткое время во время подготовки оператора.
Фаза 3: Сохранение определения таблицы

На стадии сохранения определения таблицы общая блокировка метаданных модернизируется до эксклюзивной, чтобы удалить старое определение таблицы и сохранить новое. После предоставления блокировка метаданных имеет кратковременную эксклюзивность.

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

Следующий пример демонстрирует онлайн-операцию DDL, ожидающую эксклюзивную блокировку метаданных, и то, как ожидающая блокировка метаданных блокирует последующие транзакции в таблице.

Сессия 1:

mysql> CREATE TABLE t1 (c1 INT) ENGINE=InnoDB;
mysql> START TRANSACTION;
mysql> SELECT * FROM t1;

Оператор SELECT сессии 1 берет общую блокировку метаданных на таблице t1.

Сессия 2:

mysql> ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE;

Онлайн-операция DDL в сессии 2, для которой требуется эксклюзивная блокировка метаданных на таблице t1 для сохранения изменений определения таблицы, должна ожидать завершения или отката транзакции сессии 1.

Сессия 3:

mysql> SELECT * FROM t1;

Оператор SELECT в сессии 3 заблокирован, ожидая предоставления эксклюзивной блокировки метаданных, запрошенной операцией ALTER TABLE в сессии 2.

Вы можете использовать SHOW FULL PROCESSLIST для определения того, ожидают ли транзакции блокировку метаданных.

mysql> SHOW FULL PROCESSLIST\G
...
*************************** 2. row ***************************
     Id: 5
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 44
  State: Waiting for table metadata lock
   Info: ALTER TABLE t1 ADD COLUMN x INT, ALGORITHM=INPLACE, LOCK=NONE
...
*************************** 4. row ***************************
     Id: 7
   User: root
   Host: localhost
     db: test
Command: Query
   Time: 5
  State: Waiting for table metadata lock
   Info: SELECT * FROM t1
4 rows in set (0.00 sec)

Информация о блокировке метаданных также предоставляется через схему производительности в таблице metadata_locks, которая содержит сведения о зависимостях блокировок метаданных между сессиями, блокировке метаданных, которую ожидает сессия, и сессии, которая в данный момент удерживает блокировку метаданных. Более подробную информацию см. в Разделе 29.12.13.3, «Таблица metadata_locks».

Производительность онлайн DDL

Производительность операции DDL в значительной степени определяется тем, выполняется ли операция мгновенно, на месте и перестраивает ли она таблицу.

Для оценки относительной производительности операции DDL вы можете сравнить результаты, используя ALGORITHM=INSTANT, ALGORITHM=INPLACE и ALGORITHM=COPY. Оператор также можно выполнить, включив old_alter_table, чтобы принудительно использовать ALGORITHM=COPY.

Для операций DDL, которые изменяют данные таблицы, вы можете определить, выполняет ли операция DDL изменения на месте или выполняет копию таблицы, посмотрев значение “затронутые строки”, отображаемое после завершения команды. Например:

Изменение значения по умолчанию столбца (быстро, не влияет на данные таблицы):
```
Query OK, 0 rows affected (0.07 sec)
```
Добавление индекса (требует времени, но 0 rows affected показывает, что таблица не копируется):
```
Query OK, 0 rows affected (21.42 sec)
```
Изменение типа данных столбца (требует значительного времени и требует перестроения всех строк таблицы):
```
Query OK, 1671168 rows affected (1 min 35.54 sec)
```

Перед выполнением операции DDL на большой таблице проверьте, быстрая она или медленная следующим образом:

Создайте клон структуры таблицы.
Заполните клонированную таблицу небольшим количеством данных.
Выполните операцию DDL на клонированной таблице.
Проверьте, равно ли значение “затронутые строки” нулю. Значение, отличное от нуля, означает, что операция копирует данные таблицы, что может потребовать специального планирования. Например, вы можете выполнить операцию DDL во время запланированного простоя или на каждом реплике-сервере по одному.

Примечание

Для лучшего понимания обработки MySQL, связанной с операцией DDL, проверьте таблицы Performance Schema и INFORMATION_SCHEMA, связанные с InnoDB перед и после операций DDL, чтобы увидеть количество физических чтений, записей, выделений памяти и так далее.

События стадии схемы производительности могут использоваться для отслеживания прогресса ALTER TABLE. См. Раздел 17.16.1, «Отслеживание прогресса ALTER TABLE для таблиц InnoDB с использованием Performance Schema».

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