MySQL не в состоянии ALTER TABLE, который активно записывается в

В одном из вариантов использования моего приложения у меня есть два одновременных соединения MySQL:

  • один активно пишет в таблицу с именем T (на самом деле, постоянно обновляя одну строку в этой таблице), и
  • еще один выполняет DDL для той же таблицы (ALTER TABLE, добавляя 8 новых столбцов и расширяя один столбец из varchar(80) в varchar(2000)). Ожидается, что DDL в конечном итоге завершится.

Столбцы в UPDATE DML не зависит от DDL.

Таблица содержит только одну строку UPDATE "D).

Анализ

То, что я наблюдаю, когда запускается интеграционный тест, охватывающий этот вариант использования, - это время ожидания теста (таблица так активно записывается, что DDL никогда не завершается), но только для MySQL 5.7. Обычно тест завершается менее чем за 30 секунд на нашем оборудовании (что действительно происходит для MySQL 5.6 и 8.0), но для MySQL 5.7 даже 200 секунд недостаточно. Я экспериментировал с разными ALGORITHM а также LOCK значения (см. 13.1.8 Синтаксис ALTER TABLE), без удачи.

Когда я профилирую свое приложение (случай MySQL 5.7), я замечаю, что 99% процессорного времени тратится на чтение из сокета (то есть ожидание ответа MySQL о том, что таблица была изменена), но экземпляр базы данных является своего рода черным коробка для меня - конечно, у меня есть performance_schema включен и может выполнять запросы к нему, но я не знаю, какую именно информацию я ищу.

Синтез

В то же время мне не удалось свести проблему к минимальному самодостаточному модульному тесту - единственное, что я наблюдаю, это увеличение времени тестирования в 3–10 раз для MySQL 5.7 по сравнению с другими версиями MySQL, но DDL этого не делает. висеть вечно

Все версии MySQL являются стандартными версиями для Windows или Debian Linux, загруженными с https://www.mysql.com/, с минимальными изменениями my.cnf или официальные изображения Docker.

Вопросы:

  1. Действительно ли технически возможно для MySQL отложить выполнение ALTER TABLE DDL навсегда? Или то, что я наблюдаю, это просто очень загруженный экземпляр базы данных? Возможно ли
    • просить, чтобы ALTER TABLE выполняется непрерывно, то есть база данных возвращает ошибку, если превышено определенное время ожидания, или
    • форсировать все другие соединения, которые потенциально могут поставить даже SHARED заблокировать таблицу или некоторые ее строки для приостановки, чтобы они не вмешивались во время выполнения DDL?
  2. При работе с исходным тайм-аутом интеграционного теста, как я могу дополнительно диагностировать ситуацию со стороны MySQL?
Источник

1 ответ

Решение

TL;DR - совершить ваши транзакции, чтобы разблокировать ALTER TABLE.

Да, ALTER TABLE может блокировать в течение длительного времени. Это может показаться навсегда. На самом деле это значение lock_wait_timeout, которое по умолчанию составляет 31536000 секунд или 365 дней.

В MySQL для операторов DDL, таких как ALTER TABLE, требуется эксклюзивная блокировка метаданных для таблицы. Цель состоит в том, чтобы убедиться, что вы не изменяете таблицу одновременно из двух одновременных сеансов.

Операторы DML, такие как SELECT, INSERT, UPDATE, DELETE, также содержат "общие" блокировки метаданных. Совместно используемые блокировки могут удерживаться несколькими сеансами одновременно, но блокировать монопольные блокировки, потому что монопольные блокировки требуют, чтобы они были единственными, чтобы удерживать любой тип блокировки на столе.

Документация гласит:

Этот подход к блокировке подразумевает, что таблица, используемая транзакцией в рамках одного сеанса, не может использоваться в инструкциях DDL другими сеансами, пока транзакция не завершится.

Назначение операторов DML, содержащих блокировку метаданных, состоит в том, чтобы они могли сохранить свое представление таблицы с возможностью повторного чтения, не беспокоясь о том, что другой сеанс выполняет DROP TABLE или ALTER TABLE, чтобы поставить под угрозу их представление таблицы. Эта блокировка необходима, потому что MySQL не имеет версионных метаданных ( они постепенно работают над этим).

Это означает, что транзакция, которая запустила простой SELECT и не зафиксировала, заблокирует DROP TABLE или ALTER TABLE, которые требуют изменения блокировки.

Есть некоторый нюанс с введением онлайн DDL.

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

Онлайн DDL не распространяется на каждый тип операции ALTER TABLE; некоторые все еще требуют эксклюзивных замков. Изменение типа данных, например, как вы делаете, требует эксклюзивной блокировки. См. Online DDL Overview для подробностей.

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