Глава 2. Мониторинг блокировок и взаимных исключений
Содержание
Мониторинг существенен для понимания того где в вашей системе возникают узкие места. Вам следует применять мониторинг как для определения источников состязательности и проверки того что вносимые вами изменения снижают такую состязательность.
Эта и две последующие главы предоставляют некий обзор мониторинга блокировок и взаимных исключений, мониторинг транзакций InnoDB и мониторинг общих транзакций в Схеме Производительности. Остаток этой книги показывает примеры того как вы можете применять такие ресурсы мониторинга для выявления и расследования состязательности. В частности, Главы 13 - 18 интенсивно пользуются мониторингом на протяжении обсуждения своих вариантов использования.
В этой главе вы изучите как вы можете выполнять мониторинг блокировок и взаимных исключений. Первичным ресурсом выступает Схема Производительности, которая и будет рассмотрена первой. Вторая половина этой главы обсуждает имеющиеся метрики состояния, мониторинг блокировок InnoDN и мониторинг взаимных исключений InnoDB.
![]() | Замечание |
---|---|
Не беспокойтесь если вы не знаете ещё что представляют собой разнообразные блокировки и взаимные исключения. Вы изучите их впоследствии на примерах применения источников мониторинга, обсуждаемых в этой главе. |
Схема Производительности (Performance) содержит основной источник сведений о доступных блокировках за исключением
взаимного блокирования. Но не только вы имеете возможность применять напрямую эти сведения о блокировках в своей
схеме Производительности; она также используется для двух относящихся к блокированию представлений в схеме
sys
. Кроме того, вы можете применять эту схему производительности для
исследования объектов синхронизации нижнего уровня, например, взаимных исключений (мьютексов). Прежде всего,
будет показано как можно изучать блокировки метаданных и таблиц.
Блокировки метаданных являются наиболее общими блокировками верхнего уровня, причём присутствует поддержка для
широкого диапазона блокировок простирающаяся от глобальных блокировок считывания до таких блокировок нижнего уровня
как для списка управления доступом (ACL, access control list). Мониторинг этих блокировок выполняется при помощи
таблицы metadata_locks
, которая содержит сведения относительно блокировок
уровня пользователя, блокировок метаданных и аналогичного. Для записи сведений должен быть разрешённым инструмент
wait/lock/metadata/sql/mdl
схемы Производительности (именно так обстоят
дела по умолчанию в MySQL 8). Ниже имеется пример того как вы можете разрешать инструменты.
Таблица metadata_locks
содержит 11 столбцов, которые суммируются в
Таблице 2-1:
Название столбца | Описание |
---|---|
|
Вид блокировки, которая осуществляет удержание, например,
|
|
Та схема данного объекта, к которой относится эта блокировка. |
|
Название блокируемого объекта. |
|
Для блокировок уровня столбца, Название блокируемого столбца. |
|
Адрес в памяти данного объекта. |
|
Значение уровня блокирования, например, совместное, исключительное или намерение. Дополнение A содержит исчерпывающий перечень возможных значений. |
|
Насколько долго удерживается эта блокировка. Допустимыми значениями
выступают |
|
Значение состояния этой блокировки Дополнительно к состояниям предоставленной (granted) и отложенной (pending), оно также может отображать что запрос на блокировку завершён по таймауту, был принесён в жертву (victim) и т.п.. |
|
То место в исходном коде, из которого была запрошена эта блокировка. |
|
Значение идентификатора потока схемы Производительности, из которого была выполнена эта блокировка. |
|
Значение идентификатора того события, которое запросило эту блокировку. |
Первичным ключом для данной таблицы выступает столбец OBJECT_INSTANCE_BEGIN
.
Листинг 2-1
показывает некий образец получения блокировок метаданных и выполнения запроса к таблице
metadata_locks
. У вас могут отличаться некоторые моменты.
Листинг 2-1. Пример использования таблицы metadata_locks
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 19 59 6
-- Connection 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
mysql> SELECT * FROM world.city WHERE ID = 130;
+-----+--------+-------------+-----------------+------------+
| ID | Name | CountryCode | District | Population |
+-----+--------+-------------+-----------------+------------+
| 130 | Sydney | AUS | New South Wales | 3276207 |
+-----+--------+-------------+-----------------+------------+
1 row in set (0.0005 sec)
mysql> SELECT *
FROM performance_schema.metadata_locks
WHERE OBJECT_TYPE = 'TABLE'
AND OBJECT_SCHEMA = 'world'
AND OBJECT_NAME = 'city'
AND OWNER_THREAD_ID = PS_CURRENT_THREAD_ID()\G
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: world
OBJECT_NAME: city
COLUMN_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2639965404080
LOCK_TYPE: SHARED_READ
LOCK_DURATION: TRANSACTION
LOCK_STATUS: GRANTED
SOURCE: sql_parse.cc:6162
OWNER_THREAD_ID: 59
OWNER_EVENT_ID: 10
1 row in set (0.0006 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.0006 sec)
Здесь вы можете наблюдать что это блокировка уровня таблицы для таблицы
world.city
.
Это блокировка на совместное считывание, поэтому прочие подключения могут получаться той жесамой блокировкой
в одно и то же время.
Если вы желаете разобраться почему некоторое подключение ожидает предоставления своему запросу на блокировку,
вам требуется выполнить запрос к таблице metadata_locks
относительной той
строки, в которой OBJECT_TYPE
, OBJECT_SCHEMA
и OBJECT_NAME
те же самые что и у задержанной блокировки, а также значением
LOCK_STATUS
выступает GRANTED
. То есть,
для выявления всех случаев отложенных блокировок и того что именно им препятствует, вам потребуется некий запрос,
который является самостоятельным присоединением (self-join) этой таблицы:
SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME,
w.OWNER_THREAD_ID AS WAITING_THREAD_ID,
b.OWNER_THREAD_ID AS BLOCKING_THREAD_ID
FROM performance_schema.metadata_locks w
INNER JOIN performance_schema.metadata_locks b
USING (OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME)
WHERE w.LOCK_STATUS = 'PENDING'
AND b.LOCK_STATUS = 'GRANTED';
Для получения дополнительных сведений относительно тех подключений, которые вовлечены в ожидание данной блокировки
вы можете, если пожелаете, осуществить присоединение прочих таблиц схемы Производительности, например,
events_statements_current
. В качестве альтернативы, как это будет обсуждено
позднее, для блокировок метаданных таблицы вы можете воспользоваться представлением
sys.schema_table_lock_waits
.
Менее часто применяемой является таблица table_handles
,
которая удерживает информацию относительно всех обработчиков открытой таблицы, включая то, какие именно блокировки
таблицы заблокированы в настоящий момент. Для подлежащих записи данных должен быть разрешён инструмент
wait/lock/table/sql/handler
(именно так устроено по умолчанию). Доступные
сведения аналогичны таблице metadata_locks
, а
Листинг 2-2
отображает некий образец явной блокировки на чтение в таблице world.city
.
У вас будут отличаться некоторые детали.
Листинг 2-1. Пример использования таблицы table_handles
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 21 61 6
-- Connection 1
mysql> LOCK TABLE world.city READ;
Query OK, 0 rows affected (0.0004 sec)
mysql> SELECT *
FROM performance_schema.table_handles
WHERE OBJECT_SCHEMA = 'world'
AND OBJECT_NAME = 'city'
AND OWNER_THREAD_ID = PS_CURRENT_THREAD_ID()\G
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: world
OBJECT_NAME: city
OBJECT_INSTANCE_BEGIN: 2639971828776
OWNER_THREAD_ID: 61
OWNER_EVENT_ID: 8
INTERNAL_LOCK: NULL
EXTERNAL_LOCK: READ EXTERNAL
1 row in set (0.0013 sec)
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.0004 sec)
Столбец INTERNAL_LOCK
содержит сведения о блокировках на уровне SQL, такие
как явные блокировки таблиц и таблиц не InnoDB, в то время как EXTERNAL_LOCK
содержит информацию о блокировках на уровне механизма хранения, включая явные блокировки таблиц для всех таблиц.
Для исследования содержимого блокировок вы не можете применять таблицу table_handles
,
в отличии от таблицы metadata_locks
(однако таблица
metadata_locks
также содержит содержит явные блокировки таблиц, как в данном
примере, а потому вы можете пользоваться ею).
Таблицы metadata_locks
и table_handles
связаны с блокировками самого верхнего уровня. Наш следующий шаг в дальнейшей гранулированности блокировок состоит в
блокировках данных, которые имеют их собственные таблицы.
Блокировка данных располагается на промежуточном уровне между блокировками метаданных и выполняемой синхронизацией объектов. Что в частности выполняют блокировки данных, так это то, что у вас имеется широкое разнообразие типов блокировок, таких как блокировки записей, блокировки промежутков, блокировки намерений вставки и т.п., которые взаимодействуют сложными способами, описанными в Главе 7. Это превращает таблицы мониторинга в особенно полезные для блокировок данных.
Сведения о блокировке данных расщепляются на две таблицы:
-
data_locks: Эта таблица содержит подробности блокированных таблиц и записей на уровне собственно InnoDB. Она показывает все удерживаемые в настоящий момент времени или отложенные блокировки.
-
data_lock_waits: Как и таблица data_locks, она отображает сведения по тому, какие потоки блокируют данный запрос, относящиеся к InnoDB блокировки, однако лишь той, которая дожидается своего предоставления.
Вы будете часто применять их в сочетании для поиска сведений относительно ожидающих блокировок.
MySQL 8 отслеживает некое изменение тем способом, которым работают эти таблицы мониторинга блокировок. В MySQL 5.7
и более ранних версиях эти сведения были доступны в двух особенных для InnoDB представлениях их схемы Информации
(Information), INNODB_LOCKS
и INNODB_LOCK_WAITS
.
Самые основные отличия состоят в том, что таблицы схемы Производительности созданы так, чтобы не зависеть от механизма
хранения и сведения относительно всех блокировок всегда доступны, в то время как в MySQL 5.7 и более ранних версиях
выставлялись лишь сведения относительно вовлечённых в ожидание захвата блокировок. Все эти блокировки всегда доступны
для исследования, превращая таблицы MySQL 8 в намного более полезные для изучения блокировок.
Таблица data_locks
является основной таблицей с подробными сведениями
относительно каждой из блокировок. Эта таблица имеет 15 столбцов, описанных в
Таблице 2-2:
Название столбца | Описание |
---|---|
|
Механизм хранения для этих данных. Что касается сервера MySQL, это всегда будет InnoDB. |
|
Значение внутреннего идентификатора конкретной блокировки, как она используется механизмом хранения. Вам не следует полагаться на то, что этот идентификатор обладает определённым форматом. |
|
Идентификатор той транзакции, которая специфична используемому механизму хранения.
Для InnoDB вы можете применять этот идентификатор для соединения со столбцом |
|
Тот идентификатор потока схемы Производительности, который сделал запрос на данную блокировку. |
|
Значение идентификатора события, которое осуществило запрос на эту блокировку.
Вы можете применять его для присоединения к некоторым имеющимся таблицам |
|
Схема того объекта, который выступает предметом, на который направлен запрос данной блокировки. |
|
Название объекта, выступающего предметом данного запроса блокировки. |
|
Для блокировок, вовлекающих разделы, название соответствующего раздела. |
|
Для запросов, вовлекающих подразделы, название соответствующего подраздела. |
|
Для вовлекающих в дело индексы блокировок, название соответствующего индекса.
Поскольку для InnoDB всё является индексом, название соответствующего индекса всегда устанавливается для блокировок
уровня записи в таблицах InnoDB. Когда блокируется соответствующая строка, этим значением будет
|
|
Значение адреса памяти данного запроса на блокировку. |
|
Значение уровня данного запроса на блокировку. Для InnoDB возможными
значениями выступают |
|
Значение применяемого режима блокировки. Оно включает будет ли она совместной или
исключительной блокировкой и соответствующие уточняющие подробности данной блокировки, например,
|
|
Является ли данная блокировка отложенной
( |
|
Сведения относительно блокируемых данных. Например, это может быть значение того индекса, для которого блокируется индекс записи. |
Первичным ключом данной таблицы выступает (ENGINE_LOCK_ID, ENGINE)
.
Некий образец овладения двумя блокировками и запроса к таблице data_locks
отображены в
Листинг 2-3.
У вас будут отличаться такие сведения, как значения идентификаторов и величины адресом памяти.
Листинг 2-3. Применение таблицы data_locks
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 23 64 6
-- Connection 1
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
mysql> SELECT *
FROM world.city
WHERE ID = 130
FOR SHARE;
+-----+--------+-------------+-----------------+------------+
| ID | Name | CountryCode | District | Population |
+-----+--------+-------------+-----------------+------------+
| 130 | Sydney | AUS | New South Wales | 3276207 |
+-----+--------+-------------+-----------------+------------+
1 row in set (0.0068 sec)
mysql> SELECT *
FROM performance_schema.data_locks
WHERE THREAD_ID = PS_CURRENT_THREAD_ID()\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2639727636640:3165:2639690712184
ENGINE_TRANSACTION_ID: 284114704347296
THREAD_ID: 64
EVENT_ID: 10
OBJECT_SCHEMA: world
OBJECT_NAME: city
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 2639690712184
LOCK_TYPE: TABLE
LOCK_MODE: IS
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 2639727636640:1926:6:131:2639690709400
ENGINE_TRANSACTION_ID: 284114704347296
THREAD_ID: 64
EVENT_ID: 10
OBJECT_SCHEMA: world
OBJECT_NAME: city
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 2639690709400
LOCK_TYPE: RECORD
LOCK_MODE: S,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 130
2 rows in set (0.0018 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.0007 sec)
В этом примере наш запрос получает некую блокировку намерения вставки (IS
)
для таблицы world.city
и некой совместно используемой записи
(S
), однако без промежутка, блокирования
(REC_NOT_GAP
) в соответствующем первичном ключе со значением 130.
Таблица data_lock_waits
является более простой, поскольку она содержит
значения базовых сведений относительно текущих вариантов предмета спора блокировки, показанного в
Таблице 2-3:
Название столбца | Описание |
---|---|
|
Тот механизм хранения, в котором происходит предмет спора за блокировку. |
|
Значение |
|
Значение |
|
Значение |
|
Значение |
|
Значение |
|
Значение |
|
Значение |
|
Значение |
|
Значение |
|
Значение |
Эта таблица не обладает первичным ключом. Самая основополагающая цель этой таблицы состоит в предоставлении простого
способа определения всех отложенных и блокированных запросов на блокировку, вовлечённых в соперничество блокирования.
Затем вы можете выполнить присоединение к таблице data_locks table
при помощи
столбцов REQUESTING_ENGINE_TRANSACTION_ID
и
BLOCKING_ENGINE_TRANSACTION_ID
, а также с прочими таблицами для получения
дополнительных сведений. Хорошим примером этого выступает представление
sys.innodb_lock_waits
.
До сих пор таблицы схемы Производительности обсуждались для блокировок, которые являются непосредственным результатом исполняемых операторов. Кроме того, имеются ожидания синхронизации нижнего уровня, которые важны для выполнения мониторинга в обстоятельствах с высокой степенью одновременности.
Ожидание синхронизации являются наиболее сложными для отслеживания по целому ряду причин. Они случаются очень часто, причём обычно на осень коротких промежутка=, и их мониторинг обладает высокими накладными расходами. Инструментарий для ожиданий синхронизации также не разрешены по умолчанию.
Ожидания синхронизации расщепляются на пять категорий:
-
cond: Применяемые в потоке условия для сигналов потокам.
-
mutex: Некая точка взаимного исключения, которая защищает части кода или прочие ресурсы.
-
prlock: Приоритетная блокировка считывания/ записи.
-
rwlock: Блокировка считывания/ записи, применяемая для ограничения одновременного доступа к особым переменным, например, для изменения системной переменной
gtid_mode
. -
sxlock: Совместная монопольная блокировка считывания/ записи. В настоящее время она применяется только в InnoDB, например, для улучшения величины масштабируемости выполняемых поисков B- дерева.
Названия инструментов ожиданий синхронизации начинаются с wait/synch/
с
последующим названием его категории, значения области, к которой относится это ожидание (например
sql
или innodb
) и собственно названия
этого ожидания. Например, взаимное исключение, ограждающее двойную запись в буфер InnoDB обладает названием
wait/synch/mutex/innodb/dblwr_mutex
.
Вы разрешаете необходимый инструментарий ожиданий синхронизации через установку значений колонок
ENABLED
и необязательной TIMED
в
таблице performance_schema.setup_instruments
для тех инструментов, которыми вы
желаете отслеживать. Кроме того, вам потребуется включить events_waits_current
не обязательные events_waits_history
и/ или
events_waits_history_long
в
performance_schema.setup_consumers
. Например, для отслеживания взаимного
исключения дублирующей записи в буфере InnoDB
mysql> UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES',
TIMED = 'YES'
WHERE NAME = 'wait/synch/mutex/innodb/dblwr_mutex';
Query OK, 1 row affected (0.0011 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> UPDATE performance_schema.setup_consumers
SET ENABLED = 'YES'
WHERE NAME = 'events_waits_current';
Query OK, 1 row affected (0.0005 sec)
Rows matched: 1 Changed: 1 Warnings: 0
В целом, лучше включать инструменты мониторинга синхронизации в особом файле настроек чтобы гарантировать надлежащую настройку с момента запуска MySQL:
[mysqld]
performance_schema_instrument = wait/synch/mutex/innodb/dblwr_mutex=ON
performance_schema_consumer_events_waits_current = ON
Затем перезапустите MySQL.
![]() | Предостережение |
---|---|
Будьте очень аккуратными с разрешением инструментария ожиданий синхронизаций и соответствующих потребителей в промышленных системах. Выполнение этого приводит к достаточно высоким накладным расходам, что в конечном счёте приведёт к выходу из строя. Чем больше вы их включаете, тем выше накладные расходы и тем больше вероятность того, что такой мониторинг мешает таким замерам, а потому полученные заключения не верны. |
Теперь вы можете выполнять мониторинг при помощи одной из таблиц
events_waits_%
:
-
events_waits_current: Текущие продолжающиеся или последние выполненные события ожиданий. Она требует включения соответствующего потребителя
events_waits_current
. -
events_waits_history: Последние десять (значение параметра
performance_schema_events_waits_history_size
) событий ожидания для каждого имеющегося потока. Это требует дополнительно к потребителюevents_waits_current
включения потребителяevents_waits_history
. -
events_waits_history_long: Самые последние 10 000 (значение параметра
performance_schema_events_waits_history_long_size
) события глобально, включая и те, которые имеются для больше не существующих потоков. Они требуют включения потребителяevents_waits_history_long
дополнительно к потребителюevents_waits_current
. -
events_waits_summary_by_account_by_event_name: События ожидания, сгруппированные по значениям учётных записей имени пользователя и названия хоста (также имеющего в схеме Производительности название действующих субъектов - actors).
-
events_waits_summary_by_host_by_event_name: События ожидания, сгруппированные по значению названия хоста той учётной записи, которая запускает это событие и название события.
-
events_waits_summary_by_instance: События ожидания, сгруппированные по названию событий, а также по адресам памяти (
OBJECT_INSTANCE_BEGIN
) своего объекта. Это полезно для событий с более чем одним экземпляром для мониторинга того будут ли эти ожидания равномерно распределены по экземплярам. Неким примером выступает таблица взаимных исключений кэша (wait/synch/mutex/sql/LOCK_table_cache
), которая обладает по одному объекту для экземпляра таблицы кэша (table_open_cache_instances
). -
events_waits_summary_by_thread_by_event_name: События ожидания для одновременно существующих потоков, сгруппированные по значениям идентификатора потока и названию события.
-
events_waits_summary_by_user_by_event_name: События ожидания, сгруппированные по значению имени пользователя той учётной записи, которая включила это событие и название события.
-
events_waits_summary_global_by_event_name: События ожидания, сгруппированные по значению названий событий. Эта таблица полезна для получения представления того, сколько времени тратится на ожидание для данного типа события.
Учитывая то, насколько коротко обычно проживает ожидание синхронизации и насколько часто они встречаются,
как правило, особенно полезными являются сводные таблицы для исследуемых при помощи схемы Производительности
ожиданий. Тем не менее, поскольку соответствующие инструменты ожидания по умолчанию не включены и они обладают
относительно высокими накладными расходами при своём мониторинге, обычно для взаимных исключений InnoDB и
семафоров применяются раздел соответствующего семафора монитора InnoDB или оператор
SHOW ENGINE INNODB MUTEX
, как это поясняется в этой главе. Основное исключение
составляют случаи когда вы желаете исследовать конкретную проблему состязательности.
Другой полезный способ применения схемы Производительности анализа блокировок состоит в запросе тех ошибок, которые встречаются в операторах.
Схема Производительности содержит несколько таблиц, которые могут применяться для исследования встречающихся ошибок. Поскольку отказ в получении некой блокировки либо по причине таймаута, либо некая взаимная блокировка включат ошибку, вы можете выполнять запрос для связанных с блокировками ошибках для выявления того какие операторы, учётные записи и тому подобного, что наиболее подвержено воздействию состязательности блокировок.
Для того чтобы увидеть что произошла некая определённая ошибка на индивидуальном уровне оператора вы можете
воспользоваться events_statements_current
,
events_statements_history
и
events_statements_history_long
. Первые две таблицы разрешены по умолчанию, в то
время как events_statements_history_long
требует включения потребителя
events_statements_history_long
.
Листинг 2-4
отображает некий пример таймаута ожидания блокировки и того как он показан в таблице
events_statements_history
.
Листинг 2-4. Пример ошибки блокировки в имеющихся таблицах операторов
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 63 179 6
-- 2 64 180 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
Query OK, 1 row affected (0.0011 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> SET SESSION innodb_lock_wait_timeout = 1;
Query OK, 0 rows affected (0.0003 sec)
Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 2> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
ERROR: 1205: Lock wait timeout exceeded; try restarting transaction
Connection 2> SELECT thread_id, event_id,
FORMAT_PICO_TIME(lock_time) AS lock_time,
sys.format_statement(SQL_TEXT) AS statement,
digest, mysql_errno,
returned_sqlstate, message_text, errors
FROM performance_schema.events_statements_history
WHERE thread_id = PS_CURRENT_THREAD_ID()
AND mysql_errno > 0\G
*************************** 1. row ***************************
thread_id: 180
event_id: 10
lock_time: 271.00 us
statement: UPDATE world.city SET Popul ... Population + 1 WHERE ID = 130
digest: 3e9795ad6fc0f4e3a4b4e99f33fbab2dc7b40d0761a8adbc60abfab02326108d
mysql_errno: 1205
returned_sqlstate: HY000
message_text: Lock wait timeout exceeded; try restarting transaction
errors: 1
1 row in set (0.0016 sec)
-- Connection 1
Connection 1> ROLLBACK;
Query OK, 0 rows affected (0.0472 sec)
-- Connection 2
Connection 2> ROLLBACK;
Query OK, 0 rows affected (0.0003 sec)
Имеется несколько моментов, которые стоит отметить в данном примере. Первый из них состоит в том, что значение времени блокировки составляет всего 1 микросекунду, несмотря на то, что это потребовало целую секунду перед тем как произошёл таймаут ожидания этой блокировки. Именно это, ожидание блокировки некой записи внутри InnoDB, не добавляется ко времени блокирования, о котором сообщает схема Производительности, поэтому мы не можем применять его для исследования состязательности блокировок уровня записей.
Второй момент состоит в том, что mysql_errno
,
returned_sqlstate
и message_text
содержат те же самые сведения об ошибке, ибо они возвращаются своему клиенту, что делает их удобными для запроса,
поскольку он также выполняется в таком случае. И третье, столбец errors
содержит некий счётчик общего числа встретившихся ошибок. Хотя этот счётчик и не сообщает ничего относительно
собственно природы этой ошибки, он полезен, так как в отличии от значений колонок со спецификой этой ошибки,
значение счётчика ошибок также присутствует в итоговых таблицах операторов, а потому вы можете воспользоваться им
при поиске того какой из операторов сталкивается с некой ошибкой каждого вида.
![]() | Совет |
---|---|
Это может быть полезным для регистрации встречающихся ошибок в соответствующем приложении. Затем вы можете, к примеру, выполнять анализ журнала своего приложения при помощи такой службы как Splunk для выработки отчётов, отображающих какие ошибки встречались и когда они стали проблемой. |
Представляющие интерес группа итоговых таблиц в этом контексте состоит из тех таблиц, которые суммируют ошибки. соответственно, имеются пять таких таблиц, сгруппированных по значениям учётной записи, хоста, потока, пользователя и глобальная:
mysql> SHOW TABLES FROM performance_schema LIKE '%error%';
+-------------------------------------------+
| Tables_in_performance_schema (%error%) |
+-------------------------------------------+
| events_errors_summary_by_account_by_error |
| events_errors_summary_by_host_by_error |
| events_errors_summary_by_thread_by_error |
| events_errors_summary_by_user_by_error |
| events_errors_summary_global_by_error |
+-------------------------------------------+
5 rows in set (0.0012 sec)
Например, для выборки статистических значений по таймаутам ожидания блокировок и взаимных блокировок:
mysql> SELECT *
FROM performance_schema.events_errors_summary_global_by_error
WHERE error_name IN ('ER_LOCK_WAIT_TIMEOUT',
'ER_LOCK_DEADLOCK')\G
*************************** 1. row ***************************
ERROR_NUMBER: 1205
ERROR_NAME: ER_LOCK_WAIT_TIMEOUT
SQL_STATE: HY000
SUM_ERROR_RAISED: 4
SUM_ERROR_HANDLED: 0
FIRST_SEEN: 2020-06-28 11:33:10
LAST_SEEN: 2020-06-28 11:49:30
*************************** 2. row ***************************
ERROR_NUMBER: 1213
ERROR_NAME: ER_LOCK_DEADLOCK
SQL_STATE: 40001
SUM_ERROR_RAISED: 3
SUM_ERROR_HANDLED: 0
FIRST_SEEN: 2020-06-27 12:06:38
LAST_SEEN: 2020-06-27 12:54:27
2 rows in set (0.0048 sec)
Хотя это и не помогает вам идентифицировать какие операторы столкнулись с произошедшими ошибками, это может помочь вам осуществлять мониторинг значения частоты с которой вы сталкиваетесь с этой ошибкой и, тем самым, определить становятся ли более частыми ошибки блокировки.
![]() | Совет |
---|---|
Таблица |
Все сведения в таблицах схемы Производительности являются сырыми данными, причём как отдельные события, так и
агрегированные. Часто когда вы исследуете проблемы блокировок или выполняете мониторинг для проблем блокировок,
более занятно определять имеются ли какие бы то ни было ожидания блокировок, либо получать некий отчёт тех событий
ожиданий, на которые тратится большая часть времени. Для таких сведений вам требуется применение схемы
sys
.
Схема sys
может рассматриваться как некая коллекция представлений, которая
служит отчётами по схеме Производительности и схеме Информации, а также в качестве различных функций и процедур утилит.
Для данного обсуждения основным средоточием выступают те два представления, которые берут сведения в таблицах
схемы Производительности и вернуть те пары блокировок, в которых одна из блокировок не может быть предоставлена по
причине другой блокировки. Таким образом, они отображают где имеются проблемы с ожиданием блокировок. Этими
двумя представлениями выступают innodb_lock_waits
и
schema_table_lock_waits
.
Представление innodb_lock_waits
использует представления
data_locks
и data_lock_waits
из
схемы Производительности для возврата всех вариантов ожиданий блокировок для блокировок записей InnoDB. Оно
отображает такие сведения как какое соединение блокировки пытается получить её и какие соединения и запросы в неё
вовлечены. Это представление также существует как x$innodb_lock_waits
,
когда вам требуются сведения без их форматирования.
Представление schema_table_lock_waits
работает аналогично, но применяет
таблицу metadata_locks
для возврата ожиданий блокировок, связанных с объектами
схемы. Эта информация также доступна без её форматирования в соответствующем представлении
x$schema_table_lock_waits
.
![]() | Совет |
---|---|
Также существуют некоторые представления, в которых перед названием соответствующего представления ставится
|
Главы 13 - 17 содержат примеры использования обоих представлений для исследования проблем блокировок.
Для представления состязательности на верхнем уровне вы также можете пользоваться счётчиками состояний и метриками InnoDB.
Существует ряд счётчиков состояния и метрик InnoDB, которые предоставляют сведения относительно блокировок. Они в целом применяются на самом глобальном уровне (экземпляра) и могут быть полезными для определения общего роста проблем блокировок.
Для счётчиков состояний и метрик InnoDB имеются два источника. Счётчики глобального состояния могут быть найдены в
таблице performance_schema.global_status
или в соответствующем операторе
SHOW GLOBAL STATUS
. Метрики InnoDB можно отыскать в представлении
information_schema.INNODB_METRICS
.
Метрики InnoDB аналогичны значениям переменных глобального состояния и способны предоставлять некоторые значительные
сведения по состоянию InnoDB. Значение столбца NAME
можно применять для
запроса значений метрик по названию. На момент написания этих строк имелось 313 метрик из которых 4 разрешены по
умолчанию. Также имеются и скрытые метрики, которые являются метриками latch
,
которые управляют будут ли собираться статистические данные ожиданий взаимных исключений. Эти метрики группируются по
подсистемам (столбцу SUBSYSTEM
), причём в колонке
COMMENT
для каждой метрики имеется описание того что замеряет эта метрика, а
значение типа метрики (счётчик, значение и т.п.) можно отыскать в столбце TYPE
.
Великолепный способ совместного отслеживания всех этих метрик состоит в использовании представления
sys.metrics
.
Листинг 2-5
отображает некий образец выборки такой метрики.
Листинг 2-5. Пример ошибки блокировки в имеющихся таблицах операторов
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 27 69 6
-- Connection 1
mysql> SELECT Variable_name,
Variable_value AS Value,
Enabled
FROM sys.metrics
WHERE Variable_name LIKE 'innodb_row_lock%'
OR Variable_name LIKE 'Table_locks%'
OR Variable_name LIKE 'innodb_rwlock_%'
OR Type = 'InnoDB Metrics - lock';
+-------------------------------+--------+---------+
| Variable_name | Value | Enabled |
+-------------------------------+--------+---------+
| innodb_row_lock_current_waits | 0 | YES |
| innodb_row_lock_time | 2163 | YES |
| innodb_row_lock_time_avg | 721 | YES |
| innodb_row_lock_time_max | 2000 | YES |
| innodb_row_lock_waits | 3 | YES |
| table_locks_immediate | 330 | YES |
| table_locks_waited | 0 | YES |
| lock_deadlock_false_positives | 0 | YES |
| lock_deadlock_rounds | 37214 | YES |
| lock_deadlocks | 1 | YES |
| lock_rec_grant_attempts | 1 | YES |
| lock_rec_lock_created | 0 | NO |
| lock_rec_lock_removed | 0 | NO |
| lock_rec_lock_requests | 0 | NO |
| lock_rec_lock_waits | 0 | NO |
| lock_rec_locks | 0 | NO |
| lock_rec_release_attempts | 24317 | YES |
| lock_row_lock_current_waits | 0 | YES |
| lock_schedule_refreshes | 37214 | YES |
| lock_table_lock_created | 0 | NO |
| lock_table_lock_removed | 0 | NO |
| lock_table_lock_waits | 0 | NO |
| lock_table_locks | 0 | NO |
| lock_threads_waiting | 0 | YES |
| lock_timeouts | 1 | YES |
| innodb_rwlock_s_os_waits | 12248 | YES |
| innodb_rwlock_s_spin_rounds | 19299 | YES |
| innodb_rwlock_s_spin_waits | 6811 | YES |
| innodb_rwlock_sx_os_waits | 171 | YES |
| innodb_rwlock_sx_spin_rounds | 5239 | YES |
| innodb_rwlock_sx_spin_waits | 182 | YES |
| innodb_rwlock_x_os_waits | 26283 | YES |
| innodb_rwlock_x_spin_rounds | 774745 | YES |
| innodb_rwlock_x_spin_waits | 12666 | YES |
+-------------------------------+--------+---------+
34 rows in set (0.0174 sec)
Метрики innodb_row_lock_%
,
lock_deadlocks
и lock_timeouts
представляют наибольший интерес. Метрики блокировки строк отображают сколько блокировок ожидается в данный момент
времени и статистические сведения относительно значений времени в миллисекундах, затрачиваемых на на ожидание
блокировок записей InnoDB. Соответственно, метрики lock_deadlocks
и
lock_timeouts
показывают значение числа взаимных блокировок и таймаутов
встречающихся ожиданий блокировок.
Если вы сталкиваетесь с состязательностью взаимного исключения или семафорами InnoDB, тогда для отслеживания
частоты происхождения и того сколько раундов тратится на ожидание полезны метрики
innodb_rwlock_%
.
Как вы можете видеть, не все эти метрики разрешены по умолчанию (это всё метрики InnoDB), а потому давайте
исследуем как возможно включать и отключать те метрики, которые поступают из представления
INNODB_METRICS
.
Метрики InnoDB могут настраиваться, а потому вы можете выбирать какие разрешены, а также вы может сбрасывать статистики. Вы включаете, запрещаете и сбрасываете значения метрик при помощи глобальных переменных системы:
-
innodb_monitor_disable: Отключает одну или более метрик.
-
innodb_monitor_enable: Разрешает одну или более метрик.
-
innodb_monitor_reset: Сбрасывает значение счётчика для одной или более метрик.
-
innodb_monitor_reset_all: Сбрасывает все статистики, включая сам счётчик, минимальное и максимальное значение для одной или более метрик.
По мере необходимости можно включать и отключать значения метрик с текущим состоянием, указанным в соответствующем
столбце STATUS
представлении INNODB_METRICS
.
Вы определяете значение названия соответствующей метрики или собственно название самой подсистемы, предваряемое
module_
в качестве значения к переменной
innodb_monitor_enable
или
innodb_monitor_disable
, а также вы можете применять
%
в качестве символа подстановки. Для воздействия на все метрики в качестве
особенного значения работает значение all
.
![]() | Замечание |
---|---|
Когда вы определяете некий модуль, он будет работать только если нет никакой метрики соответствующей этому
модулю. Примерами того, когда вы не можете определять такой модуль выступают
|
Листинг 2-6
отображает некий пример включения и применения всех метрик, совпадающих с
icp%
(которые являются метриками в подсистеме
icp
- ниспадающее условие индекса). После запроса этих метрик они снова
отключаются при помощи значения подсистемы в качестве аргумента. Величины значений
COUNT
зависят от вашей рабочей нагрузки, которой вы обладаете на момент
этого запроса.
Листинг 2-6. Применение представления INNODB_METRICS
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 32 74 6
-- Connection 1
mysql> SET GLOBAL innodb_monitor_enable = 'icp%';
Query OK, 0 rows affected (0.0003 sec)
mysql> SELECT NAME, SUBSYSTEM, COUNT, MIN_COUNT,
MAX_COUNT, AVG_COUNT,
STATUS, COMMENT
FROM information_schema.INNODB_METRICS
WHERE SUBSYSTEM = 'icp'\G
*************************** 1. row ***************************
NAME: icp_attempts
SUBSYSTEM: icp
COUNT: 0
MIN_COUNT: NULL
MAX_COUNT: NULL
AVG_COUNT: 0
STATUS: enabled
COMMENT: Number of attempts for index push-down condition checks
*************************** 2. row ***************************
NAME: icp_no_match
SUBSYSTEM: icp
COUNT: 0
MIN_COUNT: NULL
MAX_COUNT: NULL
AVG_COUNT: 0
STATUS: enabled
COMMENT: Index push-down condition does not match
*************************** 3. row ***************************
NAME: icp_out_of_range
SUBSYSTEM: icp
COUNT: 0
MIN_COUNT: NULL
MAX_COUNT: NULL
AVG_COUNT: 0
STATUS: enabled
COMMENT: Index push-down condition out of range
*************************** 4. row ***************************
NAME: icp_match
SUBSYSTEM: icp
COUNT: 0
MIN_COUNT: NULL
MAX_COUNT: NULL
AVG_COUNT: 0
STATUS: enabled
COMMENT: Index push-down condition matches
4 rows in set (0.0011 sec)
mysql> SET GLOBAL innodb_monitor_disable = 'module_icp';
Query OK, 0 rows affected (0.0004 sec)
Прежде всего, значения метрик включаются при помощи значения переменной
innodb_monitor_enable
; затем осуществляется выборка этих значений.
Дополнительно к этим отображаемым значениям также имеется некий набор столбцов со значением суффикса
_RESET
, которые сбрасываются только когда устанавливается значение системной
переменной innodb_monitor_reset
(только для значения счётчика) или для
innodb_monitor_reset_all
. Наконец, значения метрик снова отключаются.
![]() | Предостережение |
---|---|
Метрики обладают различными накладными расходами, а потому рекомендуется проверять свою рабочую нагрузку прежде чем включать метрики в промышленном применении. |
InnoDB на протяжении длительного времени обладал своим собственным монитором блокировок со сведениями о
блокировках, возвращаемых в выходных данных монитора InooDB. По умолчанию монитор InnoDB включает сведения о самой
последней ситуации взаимной блокировки, а также те блокировки, которые вовлечены в ожидания блокировок. После
включения параметра innodb_status_output_locks
(отключённого по умолчанию)
будут перечислены все блокировки; они аналогичны тому, чем вы обладаете в таблице
data_locks
схемы Производительности.
Чтобы продемонстрировать сведения о конкретной взаимной блокировке и транзакции вы можете создать взаимную блокировку при помощи шагов в Листинг 2-7.
Листинг 2-7. Пример создания взаимной блокировки
-- Connection Processlist ID Thread ID Event ID
-- --------------------------------------------------
-- 1 19 66 6
-- 2 20 67 6
-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
Query OK, 1 row affected (0.0008 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- Connection 2
Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 2> UPDATE world.city
SET Population = Population + 1
WHERE ID = 3805;
Query OK, 1 row affected (0.0008 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Connection 2> UPDATE world.city
SET Population = Population + 1
WHERE ID = 130;
-- Connection 1
Connection 1> UPDATE world.city
SET Population = Population + 1
WHERE ID = 3805;
2020-06-27 12:54:26.833760 1 [ERROR] mysqlsh.DBError ...
ERROR: 1213: Deadlock found when trying to get lock; try restarting transaction
-- Connection 2
Query OK, 1 row affected (0.1013 sec)
Rows matched: 1 Changed: 1 Warnings: 0
При помощи оператора SHOW ENGINE INNODB STATUS
вы способны вырабатывать
вывод монитора блокировок InnoDB. Листинг 2-8
отображает пример включения всех сведений о блокировках и выработки вывода своего монитора после исполнения операторов из
Листинга 2-7. (Применяемые в
Листинге 2-8 операторы включаются как исследование для
рабочей нагрузки Листинга 2-7 в модуле Python
concurrency_book
.) Полный вывод монитора InnoDB также доступен из репозитория
GitHub этой книги в файле listing_2_8.txt
.
Листинг 2-8. Вывод монитора InnoDB
-- Investigation #1
-- Connection 3
Connection 3> SET GLOBAL innodb_status_output_locks = ON;
Query OK, 0 rows affected (0.0005 sec)
-- Investigation #3
Connection 3> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2020-06-27 12:54:29 0x7f00 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 50 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2532 srv_active, 0 srv_shutdown, 1224 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 7750
OS WAIT ARRAY INFO: signal count 6744
RW-shared spins 3033, rounds 5292, OS waits 2261
RW-excl spins 1600, rounds 25565, OS waits 1082
RW-sx spins 2167, rounds 61634, OS waits 1874
Spin rounds per wait: 1.74 RW-shared, 15.98 RW-excl, 28.44 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2020-06-27 12:54:26 0x862c
*** (1) TRANSACTION:
TRANSACTION 296726, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 20, OS thread handle 29332, query id 56150 localhost ::1 root updating
UPDATE world.city
SET Population = Population + 1
WHERE ID = 130
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 259 page no 34 n bits 248 index PRIMARY of table `world`.`city` trx id 296726 lock_mode X locks rec but not gap
Record lock, heap no 66 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000edd; asc ;;
1: len 6; hex 000000048716; asc ;;
2: len 7; hex 020000015f2949; asc _)I;;
3: len 30; hex 53616e204672616e636973636f2020202020202020202020202020202020; asc San Francisco ; (total 35 bytes);
4: len 3; hex 555341; asc USA;;
5: len 20; hex 43616c69666f726e696120202020202020202020; asc California ;;
6: len 4; hex 800bda1e; asc ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 259 page no 7 n bits 248 index PRIMARY of table `world`.`city` trx id 296726 lock_mode X locks rec but not gap waiting
Record lock, heap no 44 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000082; asc ;;
1: len 6; hex 000000048715; asc ;;
2: len 7; hex 01000000d81fcd; asc ;;
3: len 30; hex 5379646e6579202020202020202020202020202020202020202020202020; asc Sydney ; (total 35 bytes);
4: len 3; hex 415553; asc AUS;;
5: len 20; hex 4e657720536f7574682057616c65732020202020; asc New South Wales ;;
6: len 4; hex 8031fdb0; asc 1 ;;
*** (2) TRANSACTION:
TRANSACTION 296725, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 19, OS thread handle 6576, query id 56151 localhost ::1 root updating
UPDATE world.city
SET Population = Population + 1
WHERE ID = 3805
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 259 page no 7 n bits 248 index PRIMARY of table `world`.`city` trx id 296725 lock_mode X locks rec but not gap
Record lock, heap no 44 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000082; asc ;;
1: len 6; hex 000000048715; asc ;;
2: len 7; hex 01000000d81fcd; asc ;;
3: len 30; hex 5379646e6579202020202020202020202020202020202020202020202020; asc Sydney ; (total 35 bytes);
4: len 3; hex 415553; asc AUS;;
5: len 20; hex 4e657720536f7574682057616c65732020202020; asc New South Wales ;;
6: len 4; hex 8031fdb0; asc 1 ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 259 page no 34 n bits 248 index PRIMARY of table `world`.`city` trx id 296725 lock_mode X locks rec but not gap waiting
Record lock, heap no 66 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000edd; asc ;;
1: len 6; hex 000000048716; asc ;;
2: len 7; hex 020000015f2949; asc _)I;;
3: len 30; hex 53616e204672616e636973636f2020202020202020202020202020202020; asc San Francisco ; (total 35 bytes);
4: len 3; hex 555341; asc USA;;
5: len 20; hex 43616c69666f726e696120202020202020202020; asc California ;;
6: len 4; hex 800bda1e; asc ;;
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------
Trx id counter 296728
Purge done for trx's n:o < 296728 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 283598406541472, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283598406540640, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283598406539808, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 283598406538976, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 296726, ACTIVE 3 sec
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 2
MySQL thread id 20, OS thread handle 29332, query id 56150 localhost ::1 root
TABLE LOCK table `world`.`city` trx id 296726 lock mode IX
RECORD LOCKS space id 259 page no 34 n bits 248 index PRIMARY of table `world`.`city` trx id 296726 lock_mode X locks rec but not gap
Record lock, heap no 66 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000edd; asc ;;
1: len 6; hex 000000048716; asc ;;
2: len 7; hex 020000015f2949; asc _)I;;
3: len 30; hex 53616e204672616e636973636f2020202020202020202020202020202020; asc San Francisco ; (total 35 bytes);
4: len 3; hex 555341; asc USA;;
5: len 20; hex 43616c69666f726e696120202020202020202020; asc California ;;
6: len 4; hex 800bda1e; asc ;;
RECORD LOCKS space id 259 page no 7 n bits 248 index PRIMARY of table `world`.`city` trx id 296726 lock_mode X locks rec but not gap
Record lock, heap no 44 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
0: len 4; hex 80000082; asc ;;
1: len 6; hex 000000048716; asc ;;
2: len 7; hex 020000015f296c; asc _)l;;
3: len 30; hex 5379646e6579202020202020202020202020202020202020202020202020; asc Sydney ; (total 35 bytes);
4: len 3; hex 415553; asc AUS;;
5: len 20; hex 4e657720536f7574682057616c65732020202020; asc New South Wales ;;
6: len 4; hex 8031fdb0; asc 1 ;;
...
-- Investigation #2
Connection 3> SET GLOBAL innodb_status_output_locks = OFF;
Query OK, 0 rows affected (0.0005 sec)
Дополнение A включает некий обзор тех разделов из которых состоит этот отчёт.
Рядом с верхом раздела LATEST DETECTED DEADLOCK
, который содержит
подробности тех транзакций и блокировок, которые вовлечены в самую последнюю взаимную блокировку и когда они
произошли. Когда с момента последнего перезапуска MySQL не произошло никаких взаимных блокировок, этот раздел можно
пропустить. Глава 16 содержит некий образец исследования
взаимных блокировок.
![]() | Замечание |
---|---|
Раздел взаимной блокировки в выводе монитора InnoDB содержит лишь сведения для тех взаимных блокировок, которые вовлечены в блокировки записей InnoDB. Для взаимных исключений, вовлечённых в блокировки не относящиеся к InnoDB, например, блокировки уровня пользователя ни имеется таких же сведений. |
Слегка ниже в этом выводе имеется раздел TRANSACTIONS
, который перечисляет
все транзакции InnoDB. Обратите внимание, что те транзакции, которые не удерживают никаких блокировок (т.е. чистые
запросы SELECT
), не включены. В этом примере имеется намерение исключительной
блокировки удержания таблицы world.city
и исключительных блокировок для
тех строк со значением первичного ключа равным 3805
(значение
80000edd
в сведениях для заблокированной записи для самого первого поля
означает строку со значением 0xedd
, что то же самое что и
3805
в десятичном представлении) и
130
(80000082
.
![]() | Совет |
---|---|
В настоящее время необходимые сведения о блокировках в выводе монитора InnoDB лучше получать из таблиц
|
Вы можете запросить сброс вывода своего монитора через каждые 15 секунд в stderr включая параметр
innodb_status_output
. Обратите внимание н то, что этот вывод весьма значителен,
а потому будьте готовы к тому, что ваш журнал регистрации ошибок быстро возрастёт когда вы его включите. Вывод
монитора InnoDB может также запросто завершиться сокрытием более серьёзных проблем. InnoDB к тому же делает возможным
автоматическую выдачу вывода монитора в журнал регистрации ошибок при применении определённых условий, например, когда
InnoDB испытывает сложности с поиском свободных блоков в своём пуле буфера или имеются длительные ожидания семафора.
Когда вы желаете быть уверенными в записях всех своих взаимных блокировок, вы можете включить имеющийся параметр
innodb_print_all_deadlocks
. Это вызывает вывод на печать сведений о
взаимных блокировках подобных выводам монитора InnoDB в установленный журнал регистрации ошибок всякий раз при
возникновении взаимных блокировок. Это также может оказаться полезным когда вам требуется расследовать взаимные
блокировки, однако это рекомендуется включать лишь по запросу во избежание того чтобы ваш журнал регистрации
ошибок становился слишком большим и потенциально скрывал бы прочие проблемы.
![]() | Предостережение |
---|---|
Будьте внимательны когда вы разрешаете обычные выводы своего монитора InnoDB или сведения обо всех взаимных блокировках. Эта информация запросто способна скрывать важные сообщения, зарегистрированные в вашем журнале ошибок. |
Самый верх вывода вашего монитора InnoDB включает сведения относительно ожиданий семафоров, которые выступают последней обсуждаемой категорией.
InnoDB применяет объекты взаимного исключения (более известные как мьютексы) и семафоры для охраны путей кода, к примеру, во избежание условий конкуренции при обновлении имеющегося пула буфера. В MySQL имеются три доступных ресурса для отслеживания взаимных исключений, с двумя из которых мы уже сталкивались. Наиболее общим инструментом выступают ожидания синхронизации в основной схеме Производительности; тем не менее, они не разрешены по умолчанию и могут вызывать проблемы с производительностью будучи включёнными. Этот раздел сосредотачивается на двух прочих ресурсах, являющихся особенными для InnoDB.
![]() | Замечание |
---|---|
При мониторинге InnoDB не существует чёткого отличия между мьютексами и семафорами. |
Как мы видели в своём предыдущей секции, получаемый вывод монитора InnoDB содержит некий раздел, который отображает кое- какие общие статистические сведения, а также пребывающие в данный момент в ожидании семафоры. Листинг 2-9 отображает пример такого раздела семафоров с происходящими ожиданиями. (Не тривиально вырабатывать по запросу ожидания семафоров, а потому не включены шаги воспроизведения. За неким образцом рабочей нагрузки вызывающей ожидания семафоров обратитесь к Главе 18.)
Листинг 2-9. Раздел монитора семафоров InnoDB
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 831
--Thread 28544 has waited at buf0buf.cc line 4637 for 0 seconds the semaphore:
Mutex at 000001F1AD24D5E8, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1228, lock var 1
--Thread 10676 has waited at buf0flu.cc line 1639 for 1 seconds the semaphore:
Mutex at 000001F1AD24D5E8, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1228, lock var 1
--Thread 10900 has waited at buf0lru.cc line 1051 for 0 seconds the semaphore:
Mutex at 000001F1AD24D5E8, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1228, lock var 1
--Thread 28128 has waited at buf0buf.cc line 2797 for 1 seconds the semaphore:
Mutex at 000001F1AD24D5E8, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1228, lock var 1
--Thread 33584 has waited at buf0buf.cc line 2945 for 0 seconds the semaphore:
Mutex at 000001F1AD24D5E8, Mutex BUF_POOL_LRU_LIST created buf0buf.cc:1228, lock var 1
OS WAIT ARRAY INFO: signal count 207
RW-shared spins 51, rounds 86, OS waits 35
RW-excl spins 39, rounds 993, OS waits 35
RW-sx spins 30, rounds 862, OS waits 25
Spin rounds per wait: 1.69 RW-shared, 25.46 RW-excl, 28.73 RW-sx
В том случае когда самое первое ожидание происходит в строке 4637 buf0buf.cc
,
которые относятся к имени файла и номеру строки исходного кода, из которых запрошено взаимное исключение. Значение
номера строки зависит от номера применяемого вами выпуска, а собственно компилятор/ платформа способны даже
изменять номер строки на единицу. Вот ссылка на buf0buf.cc
, которая содержит
приводимый ниже код в MySQL 8.0.21 около строки 4637 (номер строки предваряет каждую из строк):
4577 /** Inits a page for read to the buffer buf_pool. If the page is
4578 (1) already in buf_pool, or
4579 (2) if we specify to read only ibuf pages and the page is not an ibuf page, or
4580 (3) if the space is deleted or being deleted,
4581 then this function does nothing.
4582 Sets the io_fix flag to BUF_IO_READ and sets a non-recursive exclusive lock
4583 on the buffer frame. The io-handler must take care that the flag is cleared
4584 and the lock released later.
4585 @param[out] err DB_SUCCESS or DB_TABLESPACE_DELETED
4586 @param[in] mode BUF_READ_IBUF_PAGES_ONLY, ...
4587 @param[in] page_id page id
4588 @param[in] page_size page size
4589 @param[in] unzip TRUE=request uncompressed page
4590 @return pointer to the block or NULL */
4591 buf_page_t *buf_page_init_for_read(dberr_t *err, ulint mode,
4592 const page_id_t &page_id,
4593 const page_size_t &page_size, ibool unzip) {
...
4637 mutex_enter(&buf_pool->LRU_list_mutex);
...
Эта функция пробует считать страницу в свой пул буфера и в строке 4637 запрашивает взаимное исключение на общий
список LRU своего буферного пула. Это взаимное исключение создаётся в
buf0buf.cc:1228
(что также можно наблюдать в разделе семафоров). Именно
это взаимное исключение ожидают все, однако в различных частях своего исходного кода. Итак, это означает что
имеется состязательность, поддерживающая список самого последнего применяемого в соответствующем пуле буфера
InnoDB. (Конкретные ожидания в данном случае были созданы при наличии
innodb_buffer_pool_size = 5M
при выполнении одновременных запросов в некой
большой, почти в 2 GiB таблице.)
Таким образом, ы целом необходимо иметь ссылку на сам исходный код при изучении ожиданий семафоров. Даже в этих
обстоятельствах хорошей подсказкой того в какой части вашего кода имеется конкуренция является собственно название
файла, например, buf0buf.cc
относится к имеющемуся пулу буфера, а
buf0flu.cc
соответствует алгоритму сброса этого пула буфера.
Обсуждаемый раздел семафора полезен для наблюдения происходящих ожиданий, однако он мало применяется при мониторинге
по времени. Для этой цели лучшим выбором является выполнение мониторинга взаимного исключения самой InnoDB. Вы
осуществляете доступ к мониторингу мьютексов при помощи оператора
SHOW ENGINE INNODB MUTEX
:
mysql> SHOW ENGINE INNODB MUTEX;
+--------+------------------------------+------------+
| Type | Name | Status |
+--------+------------------------------+------------+
| InnoDB | rwlock: dict0dict.cc:2455 | waits=748 |
| InnoDB | rwlock: dict0dict.cc:2455 | waits=171 |
| InnoDB | rwlock: fil0fil.cc:3206 | waits=38 |
| InnoDB | rwlock: sync0sharded_rw.h:72 | waits=1 |
| InnoDB | rwlock: sync0sharded_rw.h:72 | waits=1 |
| InnoDB | rwlock: sync0sharded_rw.h:72 | waits=1 |
| InnoDB | sum rwlock: buf0buf.cc:778 | waits=2436 |
+--------+------------------------------+------------+
7 rows in set (0.0111 sec)
Значения имени файла и номера строки соотносятся с теми, в которых создано данное взаимное исключение. Такой монитор мьютексов не является наиболее дружественным пользователю инструментом из MySQL, ибо каждое взаимное исключение может быть представлено неоднократно и такие ожидания не могут суммироваться без синтаксического анализа получаемого вывода. Тем не менее, он разрешён по умолчанию, а потому вы вольны применять его всякий раз.
![]() | Замечание |
---|---|
|
Общая коллекция сведений о взаимных блокировках разрешается и отключается при помощи метрики
latch
InnoDB (которая скрыта, а потому вы не способны видеть её текущее значение).
Тем не менее не существует причин запрещать эту метрику latch
.
В данной главе было представлено введение в основные ресурсы, доступные для мониторинга и исследования блокировок. Прежде всего мы рассмотрели таблицы схемы Производительности. Существуют выделенные таблицы для запросов значений текущих блокировок метаданных и данных, будь они совместными или исключительно используемыми блокировками и были ли такие запросы на блокирование предоставлены. На самом нижнем уровне также имеются таблицы, которые позволяют вам исследовать ожидания синхронизации; тем не менее, они не разрешены по умолчанию и обладают значительными накладными расходами. С другой стороны для исследования шкалы грануляции того, какие операторы сталкивались с ошибками и значениями частот ошибок, могут применяться таблицы операторов и итоговые таблицы ошибок.
На втором месте также может оказаться полезной, в особенности, для изучения проблем с ожиданиями блокировок,
схема sys
со своим представлением
innodb_lock_waits
, которое снабжает сведениями относительно происходящих в
данный момент ожиданий блокировок данных и schema_table_lock_waits
для
происходящих сейчас ожиданий блокировок метаданных таблиц.
Третьими, на самом верхнем уровне, значения счётчиков состояния и метрик InnoDB, предоставляют некий обзор собственно активности рассматриваемого экземпляра, включая имеющиеся применения блокировок и отказы в получении блокировок. Если вам требуются дополнительные сведения относительно блокировок InnoDB, тогда есть монитор блокировок, предоставляющий аналогичные рассмотренным таблицам блокировок данных из схемы Производительности сведения, однако в менее пригодном формате, а этот монитор InnoDB содержит подробности относительно самых последних произошедших взаимных блокировок. Такой монитор InnoDB к тому же содержит сведения относительно ожиданий семафоров и, наконец, имеющийся монитор взаимных исключений innoDB предоставляет статистические сведения относительно ожиданий взаимных исключений.
Другим полезным способом получения сведений относительно использования имеющихся блокировок это просмотр сведений о транзакциях. Именно это мы и рассмотрим в своей следующей главе.