Статьи

Последствия изменений блокировки метаданных в MySQL 5.5

Этот пост принадлежит Ovais Tariq из блога MySQL Performance.

Чтобы точно понять, что такое блокировка метаданных, прочитайте этот раздел в руководстве по MySQL .

Давайте начнем с рассмотрения поведения блокировки метаданных до MySQL 5.5.3.

Поведение блокировки метаданных до MySQL 5.5.3

До MySQL 5.5.3 оператор, открывший таблицу, удерживал только блокировки метаданных до конца оператора, а не до конца транзакции. Это означало, что транзакция не была в действительности изолированной, поскольку один и тот же запрос мог возвращать разные результаты, если выполнялся дважды и если между вызовами запроса выполнялся DDL. Позвольте мне привести пример с помощью простого тестового примера, в котором я добавлю новый столбец в таблицу, когда транзакция в режиме изоляции REPEATABLE-READ активна.

session1 > select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

session1 > start transaction;
Query OK, 0 rows affected (0.00 sec)  

session1 > select * from test where id=1;
+----+------+
| id | x    |
+----+------+
|  1 | foo  |
+----+------+
1 row in set (0.00 sec)

session2 > ALTER TABLE test add column c char(32) default 'dummy_text';
Query OK, 2 rows affected (0.57 sec)
Records: 2  Duplicates: 0  Warnings: 0

session1 > select * from test where id=1;
Empty set (0.00 sec)

session1 > rollback;
Query OK, 0 rows affected (0.00 sec)  

session1 > start transaction;
Query OK, 0 rows affected (0.00 sec)  

session1 > select * from test where id=1;
+----+------+------------+
| id | x    | c          |
+----+------+------------+
|  1 | foo  | dummy_text |
+----+------+------------+
1 row in set (0.00 sec)

И вы можете видеть, как нарушается изоляция, потому что SELECT не был повторяем, хотя был использован уровень изоляции транзакции REPEATABLE-READ. Такое поведение версий до 5.5 также означает, что запросы могут быть записаны в другом порядке, чем двоичный журнал, нарушающий семантику блокировки и противоречащий концепциям сериализации. Например, посмотрите на следующую выдержку из двоичного журнала в случае, когда транзакция UPDATE смешана с ALTER:

# at 536
#130201 11:21:03 server id 1  end_log_pos 658   Query   thread_id=8     exec_time=0     error_code=0
SET TIMESTAMP=1359714063/*!*/;
ALTER TABLE test add column id_2 int(11) default 0 after id
/*!*/;
# at 658
#130201 11:21:39 server id 1  end_log_pos 726   Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1359714099/*!*/;
BEGIN
/*!*/;
# at 726
# at 773
#130201 11:21:35 server id 1  end_log_pos 773   Table_map: `test`.`test` mapped to number 17
#130201 11:21:35 server id 1  end_log_pos 829   Update_rows: table id 17 flags: STMT_END_F

BINLOG '
L5cLURMBAAAALwAAAAUDAAAAABEAAAAAAAEABHRlc3QABHRlc3QAAwMD/gL+CQY=
L5cLURgBAAAAOAAAAD0DAAAAABEAAAAAAAEAA///+AIAAAAAAAAAA2JhcvgCAAAAAAAAAANob3A=
'/*!*/;
### UPDATE test.test
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=0 /* INT meta=0 nullable=1 is_null=0 */
###   @3='bar' /* STRING(9) meta=65033 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
###   @2=0 /* INT meta=0 nullable=1 is_null=0 */
###   @3='hop' /* STRING(9) meta=65033 nullable=1 is_null=0 */
# at 829
#130201 11:21:39 server id 1  end_log_pos 856   Xid = 85
COMMIT/*!*/;

Обратите внимание, как ALTER регистрируется до ОБНОВЛЕНИЯ, потому что ALTER не блокировал ожидание транзакции для принятия

По причинам, описанным выше, реализация блокировки метаданных была изменена, начиная с MySQL 5.5.3. Давайте посмотрим, как это работает сейчас.

Поведение блокировки метаданных, начиная с MySQL 5.5.3

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

session1 > start transaction;
Query OK, 0 rows affected (0.00 sec)

session1 > select * from test order by id;
+----+------+
| id | x    |
+----+------+
|  1 | foo  |
|  2 | bar  |
+----+------+
2 rows in set (0.00 sec)

session2 > ALTER TABLE test add column c char(32) default 'dummy_text';

session3 > show processlist;
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------+
| Id | User     | Host      | db   | Command | Time | State                           | Info                                                        |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------+
|  1 | msandbox | localhost | test | Sleep   |  140 |                                 | NULL                                                        |
|  2 | msandbox | localhost | test | Query   |    3 | Waiting for table metadata lock | ALTER TABLE test add column c char(32) default 'dummy_text' |
|  3 | msandbox | localhost | test | Query   |    0 | NULL                            | show processlist                                            |
+----+----------+-----------+------+---------+------+---------------------------------+-------------------------------------------------------------+
3 rows in set (0.00 sec)

Вы можете видеть, как блокирует ALTER, потому что транзакция в session1 все еще открыта, и как только транзакция в session1 закрыта, ALTER успешно проходит:

session1 > rollback;
Query OK, 0 rows affected (0.00 sec)

session2 > ALTER TABLE test add column c char(32) default 'dummy_text';
Query OK, 2 rows affected (46.77 sec)
Records: 2  Duplicates: 0  Warnings: 0

Давайте посмотрим, где ALTER провел большую часть своего времени:

session2 > show profiles;
+----------+-------------+-------------------------------------------------------------+
| Query_ID | Duration    | Query                                                       |
+----------+-------------+-------------------------------------------------------------+
|        1 | 46.78110075 | ALTER TABLE test add column c char(32) default 'dummy_text' |
+----------+-------------+-------------------------------------------------------------+
1 row in set (0.00 sec)

session2 > show profile for query 1;
+------------------------------+-----------+
| Status                       | Duration  |
+------------------------------+-----------+
| starting                     |  0.000060 |
| checking permissions         |  0.000003 |
| checking permissions         |  0.000003 |
| init                         |  0.000005 |
| Opening tables               |  0.000045 |
| System lock                  |  0.000006 |
| setup                        |  0.000016 |
| creating table               |  0.168283 |
| After create                 |  0.000061 |
| copy to tmp table            |  0.165808 |
| rename result table          | 46.446738 |
| end                          |  0.000035 |
| Waiting for query cache lock |  0.000003 |
| end                          |  0.000006 |
| query end                    |  0.000003 |
| closing tables               |  0.000008 |
| freeing items                |  0.000016 |
| cleaning up                  |  0.000004 |
+------------------------------+-----------+
18 rows in set (0.00 sec)

Поэтому ALTER ожидал блокировок метаданных сразу после того, как таблица с новой структурой была создана и заполнена данными, но до того, как старая таблица была заменена новой. Обратите внимание, что ALTER — это многошаговый процесс, старая таблица блокируется в режиме совместного использования, а затем выполняется нечто подобное следующим шагам: создается новая таблица с новой структурой, а затем выполняется INSERT INTO new_table SELECT * FROM old_table и затем RENAME old_table в tmp_table, new_table в old_table и, наконец, DROP tmp_table.
Давайте посмотрим другой пример, на этот раз пытаясь переименовать:

session2 > RENAME TABLE test to test_2;

session3 > show processlist;
+----+----------+-----------+------+---------+------+---------------------------------+-----------------------------+
| Id | User     | Host      | db   | Command | Time | State                           | Info                        |
+----+----------+-----------+------+---------+------+---------------------------------+-----------------------------+
|  1 | msandbox | localhost | test | Sleep   |   49 |                                 | NULL                        |
|  2 | msandbox | localhost | test | Query   |   35 | Waiting for table metadata lock | RENAME TABLE test to test_2 |
|  3 | msandbox | localhost | test | Query   |    0 | NULL                            | show processlist            |
+----+----------+-----------+------+---------+------+---------------------------------+-----------------------------+
3 rows in set (0.00 sec)

И вы можете видеть, что RENAME также заблокирован, потому что транзакция, которая получила доступ к таблице «test», все еще открыта.

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

Давайте посмотрим еще один интересный побочный эффект блокировки метаданных.

Когда ALTER может сделать таблицу недоступной?

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

session1 > start transaction;
Query OK, 0 rows affected (0.00 sec)

session1 > select * from test_2 order by id;
+----+------+------------+
| id | x    | c          |
+----+------+------------+
|  1 | foo  | dummy_text |
|  2 | bar  | dummy_text |
+----+------+------------+
2 rows in set (0.00 sec)

session6 > ALTER TABLE test_2 DROP COLUMN c;

session7 > select * from test_2 order by id;
session8 > select * from test_2 order by id;
session9 > select * from test_2 order by id;
session10 > select * from test_2 order by id;

session3 > show processlist;
+----+----------+-----------+------+---------+------+---------------------------------+----------------------------------+
| Id | User     | Host      | db   | Command | Time | State                           | Info                             |
+----+----------+-----------+------+---------+------+---------------------------------+----------------------------------+
|  1 | msandbox | localhost | test | Sleep   |  403 |                                 | NULL                             |
|  3 | msandbox | localhost | test | Query   |    0 | NULL                            | show processlist                 |
|  6 | msandbox | localhost | test | Query   |  229 | Waiting for table metadata lock | ALTER TABLE test_2 DROP COLUMN c |
|  7 | msandbox | localhost | test | Query   |  195 | Waiting for table metadata lock | select * from test_2 order by id |
|  8 | msandbox | localhost | test | Query   |  180 | Waiting for table metadata lock | select * from test_2 order by id |
|  9 | msandbox | localhost | test | Query   |  169 | Waiting for table metadata lock | select * from test_2 order by id |
| 10 | msandbox | localhost | test | Query   |   55 | Waiting for table metadata lock | select * from test_2 order by id |
+----+----------+-----------+------+---------+------+---------------------------------+----------------------------------+
7 rows in set (0.00 sec)

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

session10 > show profile for query 1;
+----------------------+------------+
| Status               | Duration   |
+----------------------+------------+
| starting             |   0.000058 |
| checking permissions |   0.000006 |
| Opening tables       | 213.028481 |
| System lock          |   0.000009 |
| init                 |   0.000014 |
| optimizing           |   0.000002 |
| statistics           |   0.000005 |
| preparing            |   0.000006 |
| executing            |   0.000001 |
| Sorting result       |   0.000002 |
| Sending data         |   0.000040 |
| end                  |   0.000003 |
| query end            |   0.000002 |
| closing tables       |   0.000003 |
| freeing items        |   0.000007 |
| logging slow query   |   0.000002 |
| cleaning up          |   0.000002 |
+----------------------+------------+
17 rows in set (0.00 sec)

И вы можете видеть, как запрос провел почти все время ожидания в состоянии «Открытие таблиц». Теперь это поведение в отношении ALTER, делающего таблицу недоступной в некоторых случаях, на самом деле не задокументировано, и поэтому я сообщил об ошибке: http://bugs.mysql.com/bug.php?id=67647

Блокировка метаданных ведет себя иначе для запросов, которые обслуживаются из Query Cache, посмотрим, что произойдет в этом случае.

Блокировка метаданных и кеш запросов

Как блокировка метаданных ведет себя с query_cache? Это важный вопрос. Если Query Cache включен и SELECT может обслуживаться из Query Cache, тогда SELECT не будет блокировать ALTER, даже если ALTER ожидает блокировки метаданных. Зачем? Потому что в таком случае не нужно выполнять операцию открытия таблицы. Давайте посмотрим на этот сценарий через тестовый пример:

session1 > start transaction;
Query OK, 0 rows affected (0.00 sec)

session1 > select * from test_2 order by id;
+----+------+
| id | x    |
+----+------+
|  1 | foo  |
|  2 | bar  |
+----+------+
2 rows in set (0.00 sec)

session6 > RENAME TABLE test_2 to test;

session10 > select * from test_2 order by id;
+----+------+
| id | x    |
+----+------+
|  1 | foo  |
|  2 | bar  |
+----+------+
2 rows in set (0.00 sec)

session3 > show processlist;
+----+----------+-----------+------+---------+------+---------------------------------+-----------------------------+
| Id | User     | Host      | db   | Command | Time | State                           | Info                        |
+----+----------+-----------+------+---------+------+---------------------------------+-----------------------------+
|  1 | msandbox | localhost | test | Sleep   |   22 |                                 | NULL                        |
|  3 | msandbox | localhost | test | Query   |    0 | NULL                            | show processlist            |
|  6 | msandbox | localhost | test | Query   |    3 | Waiting for table metadata lock | RENAME TABLE test_2 to test |
| 10 | msandbox | localhost | test | Sleep   |   37 |                                 | NULL                        |
+----+----------+-----------+------+---------+------+---------------------------------+-----------------------------+
4 rows in set (0.00 sec)

Запрос выполняется без каких-либо блокировок, пока RENAME все еще ожидает блокировки метаданных. Давайте посмотрим информацию профилирования для этого запроса:

session10 > show profile for query 11;
+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000018 |
| Waiting for query cache lock   | 0.000003 |
| checking query cache for query | 0.000007 |
| checking privileges on cached  | 0.000003 |
| checking permissions           | 0.000005 |
| sending cached result to clien | 0.000011 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000002 |
+--------------------------------+----------+
8 rows in set (0.00 sec)

Вы можете видеть, что операция по открытию таблицы не была выполнена и, следовательно, не ожидание.

Изменяет ли что-либо факт, что таблица уже открыта, а объект таблицы находится в table_cache, что-либо относительно блокировок метаданных.

Блокировка метаданных и кеш таблиц

Независимо от того, получает ли соединение доступ к таблице, которая уже находится в кэше таблиц, любой запрос к таблице, ожидающей операции DDL, будет блокироваться. Зачем? Потому что MySQL видит, что старые записи в кэше таблиц должны быть признаны недействительными, и любой запрос, который обращается к таблице, должен будет повторно открыть измененную таблицу, и в кэше таблиц появятся новые записи. Давайте посмотрим на это явление в действии:

session6 > ALTER TABLE test add column c char(32) default 'dummy_text';
Query OK, 2 rows affected (59.80 sec)

session10 > show status like 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 30    |
| Opened_tables | 0     |
+---------------+-------+
2 rows in set (0.00 sec)

session10 > select * from test order by id;
+----+------+------------+
| id | x    | c          |
+----+------+------------+
|  1 | foo  | dummy_text |
|  2 | bar  | dummy_text |
+----+------+------------+
2 rows in set (53.78 sec)

session10 > show status like 'Open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   | 30    |
| Opened_tables | 1     |
+---------------+-------+
2 rows in set (0.00 sec)

session10 > show profile for query 18;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000059 |
| checking permissions |  0.000010 |
| Opening tables       | 53.786685 |
| System lock          |  0.000009 |
| init                 |  0.000012 |
| optimizing           |  0.000003 |
| statistics           |  0.000007 |
| preparing            |  0.000006 |
| executing            |  0.000001 |
| Sorting result       |  0.000004 |
| Sending data         |  0.000033 |
| end                  |  0.000003 |
| query end            |  0.000002 |
| closing tables       |  0.000004 |
| freeing items        |  0.000009 |
| logging slow query   |  0.000002 |
| cleaning up          |  0.000002 |
+----------------------+-----------+
17 rows in set (0.00 sec)

Как вы можете видеть, запрос SELECT по-прежнему блокируется, и счетчик состояния Opened_tables также увеличивается после завершения запроса.

Так много для объяснения, теперь давайте посмотрим на последствия.

последствия

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

Есть и другие интересные последствия для приложений, использующих версии MySQL до 5.5:

  • Я помню случай клиента, когда есть подчиненный подчиненный, который ежедневно выполняет длительную транзакцию, эта транзакция имеет тенденцию выполняться часами. Теперь каждый день одну из таблиц переименовывали и меняли местами, и именно эта таблица считывалась из длительной транзакции. Поскольку ведомое устройство пыталось выполнить запрос на переименование, оно просто блокировало бы ожидание завершения длительной транзакции, что заставляло бы ведомое устройство задерживаться на часы, ожидающие завершения транзакции, поскольку вы знаете, что ведомое устройство является однопоточным, поэтому это не может действительно применить любое другое событие. Это никогда не было проблемой, когда приложение использовало MySQL версии <5.5 в качестве хранилища данных.
  • На этот раз был еще один интересный случай, когда Active MQ использует MySQL в режиме HA. В режиме HA есть два сервера Active MQ, оба пытаются выполнить нечто подобное следующей последовательности событий:
    session1 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB;
    Query OK, 0 rows affected (0.09 sec)
    
    session1 > insert into t1 values(null);
    Query OK, 1 row affected (0.21 sec)
    
    session1 > start transaction;
    Query OK, 0 rows affected (0.00 sec)
    
    session1 > select * from t1 where i=1 for update;
    +---+
    | i |
    +---+
    | 1 |
    +---+
    1 row in set (0.00 sec)
    
    session2 > CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB;

    При использовании MySQL 5.1 второй CREATE сразу же завершится ошибкой с ошибкой «ОШИБКА 1050 (42S01): таблица« t1 »уже существует», но из-за того, как работает блокировка метаданных в 5.5, это больше не так, второй CREATE будет просто заблокировать с непредвиденными последствиями. Обходной путь здесь должен был бы установить переменную lock_wait_timeout в очень низкое значение и затем выполнить CREATE TABLE, это обеспечит немедленный сбой CREATE (однако по другой причине):

    session2 > set session lock_wait_timeout=1;CREATE TABLE t1(i int(11) not null auto_increment primary key) ENGINE=InnoDB;

    Тем не менее, я чувствую, что CREATE TABLE должен завершиться ошибкой в ​​таком случае, когда таблица уже существует, и нет другого DDL, такого как таблица DROP, ожидающая выполнения на той же таблице, и поэтому я сообщил об ошибке: http: // bugs.mysql.com/bug.php?id=67873