MySQL 5.6 - таблица блокируется, даже когда используется ALGORITHM=inplace

Я управляю следующим ALTER команда для базы данных MySQL 5.6 для большой таблицы с 60 миллионами строк:

ALTER TABLE `large_table` ADD COLUMN `note` longtext NULL, 
ALGORITHM=INPLACE, LOCK=NONE;

Несмотря на указание обоих ALGORITHM=INPLACE а также LOCK=NONEтаблица блокируется и, по сути, закрывает приложение до завершения миграции.

Я проверил, что таблица действительно была заблокирована, проверив значение In_use столбец на выходе из SHOW OPEN TABLES команда. Было установлено 1,

Из того, что я собираю в документации MySQL, эта операция не должна блокировать таблицу. И, как предполагается, MySQL не выполнит команду, если она не сможет работать без блокировки. Я обновил базу данных до MySQL 5.7, чтобы посмотреть, будет ли она лучше, но я столкнулся с той же проблемой на 5.7.

Это ожидаемое поведение? Как мне узнать, что здесь происходит не так?

Источник

1 ответ

Я предполагаю, что вы не делали какой-то другой DDL на этом столе в то же время?

На будущее:

8.0.12 имеет ALTER TABLE .. ALGORITHM=INSTANT за ADD COLUMN, См. Обсуждение, Справочник ALTER и Справочник по DDL.

Следующие ограничения применяются, когда алгоритм INSTANT используется для добавления столбца:

  • Добавление столбца нельзя объединить в одном выражении с другими действиями ALTER TABLE, которые не поддерживают ALGORITHM=INSTANT.
  • Столбец может быть добавлен только как последний столбец таблицы. Добавление столбца в любую другую позицию среди других столбцов не поддерживается.
  • Столбцы нельзя добавить в таблицы, использующие ROW_FORMAT=COMPRESSED.
  • Столбцы нельзя добавить в таблицы, содержащие индекс FULLTEXT.
  • Столбцы нельзя добавлять во временные таблицы. Временные таблицы поддерживают только ALGORITHM=COPY.
  • Столбцы нельзя добавлять в таблицы, которые находятся в табличном пространстве словаря данных.
  • Пределы размера строки не оцениваются при добавлении столбца. Однако ограничения размера строки проверяются во время операций DML, которые вставляют и обновляют строки в таблице.

В один оператор ALTER TABLE можно добавить несколько столбцов.

Если вы не можете обновить, подумайте о Percona pt-online-schema-change или новый, конкурирующий продукт gh-ost (который использует binlog).

Источник

У меня также были проблемы с блокировкой MySQL 5.6, даже когда ALGORITHM=INPLACE, LOCK=NONE;используется. Сначала я бы проверил следующее:

Проверить ограничения на таблице

Предложение ALTER TABLE LOCK=NONE не разрешено, если для таблицы установлены ограничения ON...CASCADE или ON...SET NULL.

Источник:https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html

Есть ли у таблицы отношения внешнего ключа?

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

Источник:https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html

И прочтите о блокировке метаданных здесь:https://dev.mysql.com/doc/refman/5.6/en/metadata-locking.html

Сначала измените таблицы из старого формата времени

Если вы создали свои таблицы из более ранней версии MySQL 5.6 с полями DATETIME или TIMESTAMP, их необходимо обновить до нового формата MySQL 5.6.

Таблицы InnoDB, созданные до MySQL 5.6, не поддерживают ALTER TABLE ... ALGORITHM=INPLACE для таблиц, которые включают временные столбцы (DATE, DATETIME или TIMESTAMP) и не были перестроены с помощью ALTER TABLE ... ALGORITHM=COPY. В этом случае операция ALTER TABLE ... ALGORITHM=INPLACE возвращает следующую ошибку:

ОШИБКА 1846 (0A000): ALGORITHM=INPLACE не поддерживается. Причина: невозможно изменить тип столбца INPLACE. Попробуйте ALGORITHM=COPY.

Источник:https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-limitations.html

Проверьте, есть ли в таблице разбиение

Секционирование меняет способ применения правил изменения таблиц. Проверить статус разделения таблицы

show table status;

Ищите Engine, не равный InnoDB

Источники:https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

Наконец, как сказал Рик Джеймс в своем ответе, обновление с 5.6 до 8.0 может быть вариантом, поскольку оно обеспечивает другие улучшения.

Источник
Другие вопросы по тегам