14.13.2 Online DDL Performance and Concurrency — MySQL 5.7

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

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

Приложения, которые обращаются к таблице, реагируют быстрее, поскольку запросы и операции DML к таблице могут выполняться во время операции DDL. Снижение блокировок и ожидания ресурсов сервера MySQL приводит к большей масштабируемости, даже для операций, которые не участвуют в операции DDL.
Операции на месте избегают операций ввода-вывода на диск и циклов ЦП, связанных с копированием таблицы, что минимизирует общую нагрузку на базу данных. Минимизация нагрузки помогает поддерживать хорошую производительность и высокую пропускную способность во время операции 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;

Запрос сессии 1 SELECT берёт общую блокировку метаданных на таблице 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)

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

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

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

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

Для операций 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, чтобы увидеть количество физических чтений, записей, выделений памяти и т.д.

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

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