Принцип MySQL Online DDL и наступление на яму

MySQL

Принцип MySQL Online DDL и наступление на яму

DDL (язык определения данных) MySQL включает в себя такие операции, как добавление или вычитание полей, добавление или вычитание индексов. До MySQL 5.6 операция DDL MySQL будет скопирована в соответствии с исходной таблицей и соответствующим образом изменена.Например, конкретный процесс DDL для таблицы A выглядит следующим образом:

  1. Создайте новую таблицу B в соответствии с определением таблицы A
  2. Запись блокировки на таблицу A
  3. Выполнение операций, указанных в DDL, над таблицей B.
  4. Скопируйте данные из A в B
  5. Снимите блокировку записи A
  6. удалить таблицу А
  7. Переименовать таблицу B в A

В процессе 2-4, если объем данных таблицы A относительно велик, процесс копирования в таблицу B будет занимать много времени и занимать дополнительное место для хранения. Кроме того, поскольку операция DDL занимает блокировку записи в таблице A, и DDL, и DML в таблице A будут заблокированы и не могут быть обслужены.

Поэтому MySQL 5.6 добавляет Online DDL, который позволяет выполнять операции DDL без прерывания служб базы данных.

использование

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

Параметры ALGORITHM и LOCK могут быть указаны в операторе ALTER для указания способа выполнения DDL и способа управления DML во время DDL соответственно.

  1. ALGORITHM=INPLACE означает, что во время выполнения DDL не происходит копирования таблицы, и DML разрешено выполнять одновременно во время процесса (INPLACE не требует большого количества дисковых операций ввода-вывода и ЦП, таких как COPY, что снижает нагрузку на базу данных). , В то же время это уменьшает использование пула буферов, чтобы избежать проблем с производительностью, вызванных большим удалением исходного кеша запросов в пуле буферов).

    Если установлено ALGORITHM=COPY, DDL будет выполняться способом, предшествующим MySQL 5.6, с использованием копирования таблиц, что блокирует все DML в процессе. Кроме того, вы также можете установить ALGORITHEM=DAFAULT, чтобы разрешить MySQL выбирать метод выполнения на основе принципа максимально возможного обеспечения одновременных операций DML.

  2. LOCK=NONE означает, что операции DML не заблокированы, и все операции DML разрешены во время процесса DDL. Кроме того, существуют EXCLUSIVE (удерживает эксклюзивные блокировки, блокирует все запросы, подходит для сценариев, когда DDL необходимо завершить как можно быстрее или сервисная библиотека простаивает), SHARED (разрешает SELECT, но блокирует INSERT UPDATE DELETE, подходит для данных склады и т.д., допускающие задержку ввода данных при записи) и DEFAULT (в зависимости от типа DDL выбираем значение LOCK по принципу обеспечения максимального параллелизма)

Однако не все DDL-операции могут быть выполнены INPLACE.Справочное руководство по MySQL — онлайн-операции DDLПосмотреть в.

Например, тип данных измененного столбца, показанный в таблице 14.10, не поддерживает INPLACE.

Operation In Place Rebuilds Table Permits Concurrent DML Only Modifies Metadata
Changing the column data type No Yes No No

В это время попробуйте изменить имя_столбца, исходный тип которого FLOAT, на INT.

ALTER TABLE tbl_name MODIFY COLUMN column_name INT, ALGORITHM=INPLACE, LOCK=NONE;

сообщит об ошибке

ERROR: 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

Процесс реализации

  1. Инициализация: вычислить допустимый параллелизм в процессе DDL в соответствии с механизмом хранения, указанными пользователем операциями, заданным пользователем АЛГОРИТМОМ и БЛОКИРОВКОЙ.В этом процессе будет получена общая блокировка метаданных для защиты определения структуры таблицы.
  2. Выполнение DDL: решите, следует ли обновить общую блокировку метаданных до монопольной блокировки метаданных (только на этапе подготовки оператора) в соответствии с ситуацией на первом этапе, а затем сгенерируйте оператор и выполните его. Блокировка общих метаданных во время выполнения гарантирует, что другие DDL не будут выполняться одновременно, но DML может выполняться в обычном режиме.
  3. Фиксация: Обновите общую блокировку метаданных до эксклюзивной блокировки метаданных, затем удалите старое определение таблицы и зафиксируйте новое определение таблицы.

Шаги в онлайн-процессе DDL, занимающие эксклюзивный MDL, выполняются очень быстро, поэтому блокировка операторов DML практически отсутствует.

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

Ступай на яму

Как упоминалось ранее, MDL необходимо получить во время выполнения Online DDL. MDL (блокировка метаданных) — это блокировка на уровне таблицы, введенная в MySQL 5.5, которая автоматически добавляется при доступе к таблице для обеспечения правильности чтения и записи. При выполнении операций DML над таблицей добавьте блокировки чтения MDL; при выполнении операций DDL добавьте блокировки записи MDL.

Для того, чтобы обеспечить параллельное выполнение DML в процессе выполнения DDL для больших таблиц, ранее использовался Online DDL с ALGORITHM=INPLACE, но риск тупиковой ситуации все еще остается.Проблема заключается в необходимости монопольного MDL во время выполнения онлайн-процесс DDL.

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

# Session 1
> START TRANSACTION;
> SELECT * FROM tbl_name;
# 正常执行

В это время, если сеанс 2 хочет выполнять операции DML, ему нужно только получить общий MDL, и он все еще может выполняться в обычном режиме.

# Session 2
> SELECT * FROM tbl_name;
# 正常执行

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

# Session 3
> ALTER TABLE tbl_name ADD COLUMN n INT;
# 阻塞

пройти черезshow processlistВы можете видеть, что операция ALTER ожидает MDL.

+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
| Id | User            | Host             | db   | Command | Time | State                           | Info            |
│----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
| 11 | root            | 172.17.0.1:53048 | demo | Query   |    3 | Waiting for table metadata lock | alter table ... |
+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+

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

# Session 4
> SELECT * FROM tbl_name;
# 阻塞

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

Есть две основные причины этой проблемы:

  1. Транзакция в сеансе 1 не была зафиксирована вовремя, что заблокировало DDL сеанса 3.
  2. Сеанс 3 Online DDL блокирует последующие DML и DDL

Что касается вопроса 1, многие ORM (например, pymysql) по умолчанию инкапсулируют пользовательские операторы в выполнение транзакции.Если клиентская программа прерывается и завершается, а транзакция не была зафиксирована или откатана, произойдет ситуация в сеансе 1. В это время вы можетеinfomation_schema.innodb_trxНайдите поток, соответствующий незавершенной транзакции, и принудительно завершите его

> SELECT * FROM information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 421564480355704
                 trx_state: RUNNING
               trx_started: 2020-07-21 01:49:41
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 9
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 0
          trx_lock_structs: 0
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 0
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: REPEATABLE READ
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
       trx_schedule_weight: NULL
1 row in set (0.0025 sec)

Вы можете видеть, что trx_mysql_thread_id, соответствующий транзакции, выполняемой сеансом 1, равен 9, а затем выполнитьKILL 9Транзакция в сеансе 1 может быть прервана.

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

Категории