Глава 3. Мониторинг транзакций InnoDB

Содержание

Глава 3. Мониторинг транзакций InnoDB
Информационная схема INNODB_TRX
Монитор InnoDB
INNODB_METRICS и sys.metrics
Выводы

В нашей предыдущей главе вы изучили как разыскивать сведения относительно блокировок на относительно нижнем уровне. Также важно включать информацию на верхнем уровне, ибо блокировки обладают продолжительностью вплоть до завершения соответствующей транзакции. (Исключением выступают пользовательские блокировки и блокировки таблиц в явном виде, которые могут длиться дольше.) В сервере MySQL подразумеваются транзакции InnoDB и данная глава сосредоточена на мониторинге транзакций InnoDB.

Первым будет рассмотрено представление INNODB_TRX из схемы Информации. Именно оно зачастую выступает наиболее важным когда речь заходит о исследовании проистекающих транзакций. Другим источником сведений о транзакциях выступает монитор InnoDB с которым вы уже сталкивались в нашей предыдущей главе. Наконец, обсуждаются метрики из представлений INNODB_METRICS и sys.metrics.

Информационная схема INNODB_TRX

Представление INNODB_TRX из схемы Информации выступает наиболее специализированным источником сведений относительно транзакций InnoDB. Оно включает такую информацию, как то, когда стартовала транзакция, сколько строк было изменено и количество удерживаемых блокировок. Представление INNODB_TRX также применяется представлением sys.innodb_lock_waits для снабжения сведениями относительно вовлечённых в проблемы с ожиданиями блокировок транзакций. Таблица 3-1 суммирует имеющиеся в этой таблице столбцы.

Таблица 3-1. Столбцы из представления information_schema.INNODB_TRX
Столбец/ тип данных Описание

trx_id
varchar(18)

Значение идентификатора транзакции. Оно может быть полезным для ссылки на данную транзакцию или для сопоставления с выводом самого монитора InnoDB. В противном случае значение идентификатора следует рассматривать как целиком внутреннее и не обладать никаким смыслом. Значение идентификатора присваивается лишь транзакциям, которые изменили данные или заблокировали строки; транзакция, выполнившая только операторы SELECT с исключительным доступом на чтение, будут обладать фиктивным идентификатором, например 421124985258256, который изменится если данная транзакция приступит к изменению или блокировке записей.

trx_state
varchar(13)

Значение состояния данной транзакции. Оно может быть одним из RUNNING, LOCK WAIT ROLLING BACK и COMMITTING .

trx_started
datetime

Когда была запущена эта транзакция с применением значения зоны системного времени.

trx_requested_lock_id
varchar(105)

Когда trx_state это LOCK WAIT, данный столбец отображает значение идентификатора той блокировки, которую ожидает данная транзакция.

trx_wait_started
datetime

Когда trx_state это LOCK WAIT, данный столбец показывает когда было запущено ожидание этой блокировки с применением значения зоны системного впемени.

trx_weight
bigint unsigned

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

trx_mysql_thread_id
bigint unsigned

Значение идентификатора соединения (то же самое что и значение столбца PROCESSLIST_ID в таблице threads схемы Производительности) того подключения, которое выполняет данную транзакцию.

trx_query
varchar(1024)

Значение выполняемого в данной транзакции запроса. Когда транзакция пустая, значением запроса является NULL.

trx_operation_state
varchar(64)

Значение операции, выполняемой данной транзакцией. Им может быть NULL даже при исполнении некого запроса.

trx_tables_in_use
bigint unsigned

Значение числа таблиц, используемых данной транзакцией.

trx_tables_locked
bigint unsigned

Значение числа таблиц, в которых эта транзакция удерживает блокировки строк.

trx_lock_structs
bigint unsigned

Значение числа блокируемых структур, созданных этой таблицей.

trx_lock_memory_bytes
bigint unsigned

Объём памяти в байт, задействованных той блокировкой, которая удерживается этой транзакцией.

trx_rows_locked
bigint unsigned

Значение числа удерживаемых данной транзакцией блоировок записей. При вызове блокировок строк, они также содержат блокировки индексов.

trx_rows_modified
bigint unsigned

Значение числа изменённых этой транзакцией строк.

trx_concurrency_tickets
bigint unsigned

Когда innodb_thread_concurrency не равно 0, транзакции назначаются квитанции innodb_concurrency_tickets, которые она может применять прежде чем она обязана позволить иной транзакции выполнять работу. Одна квитанция соответствует доступу к одной строке. Этот столбец показывает сколько квитанций задействовано.

trx_isolation_level
varchar(16)

Значение уровня изоляции, применяемого к данной транзакции.

trx_unique_checks
int

Разрешена ли для данного соединения переменная unique_checks.

trx_foreign_key_checks
int

Разрешена ли для данного соединения переменная foreign_key_checks.

trx_last_foreign_key_error
varchar(256)

Сообщение об ошибке самой последней (если таковая имелась) ошибки внешнего ключа, с которой столкнулась данная транзакция.

trx_adaptive_hash_latched
int

Бала ли данная транзакция заблокирована как часть соответствующего адаптивного идекса хэширования. В общей сумме имеется innodb_adaptive_hash_index_parts частей. Данны столбец в действительности выступает Булевым значением.

trx_adaptive_hash_timeout
bigint unsigned

Сохранять ли блокирование адаптивного индекса хэширования для множества запросов. Когда имеется лишь одна часть для такого адаптивного индекса хэшировани в отсутствии состязательности, тогда начинается отсчёт вниз значения соответствующего таймаута, а сама блокировка снимается по достижению этим таймаутом значения 0.

trx_is_read_only
int

Выступает ли данная транзакция доступной исключительно для чтения. Транзакция может быть доступной только на чтение либо по явному объявлению, либо для транзакций из одного оператора совключённым autocommit, когда InnoDB способна определять что данный запрос будет лишь считывать данные.

trx_autocommit_non_locking
int

Когда данная транзакция это SELECT из единственного оператора без блокирования и включён параметр autocommit, этот столбец устанавливается равным 1. Когда оба столбца, и этот, и trx_is_read_only равны 1, InnoDB способен оптимизировать данную транзакцию для снижения накладных расходов.

trx_schedule_weight
bigint unsigned

Значение веса транзакции, которое назначается этой транзакции алгоритмом CATS (Contention-Aware Transaction Scheduling, планирования транзакцию, осведомлённых о содержимом, подробнее в Главе 8). Данное значение имеет смысл только для транзакций в состоянии LOCK WAIT. Этот столбец был добавлен в 8.0.20.

Доступные в представлении INNODB_TRX сведения делают возможным определения того, какие из транзакций оказывают наибольшее воздействие. Листинг 3-1 показывает образец запуска двух транзакций, которые можно изучить.

 

Листинг 3-1. Пример транзакции


-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1               53         163          6
--          2               54         164          6

-- Connection 1
Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)
Connection 1> UPDATE world.city SET Population = Population + MOD(ID, 2) + SLEEP(0.01);

-- Connection 2
Connection 2> SET SESSION autocommit = ON;
Query OK, 0 rows affected (0.0004 sec)

Connection 2> SELECT COUNT(*) FROM world.city WHERE ID > SLEEP(0.01);

Эти транзакции будет выполняться 40-50 секунд. При их исполнении вы можете запросить представление INNODB_TRX подобно тому как это отображено в Листинге 3-2 (точные данные зависят от значения идентификаторов вашей проверки при запросе вами своего представления INNODB_TRX).

 

Листинг 3-2. Пример вывода представления INNODB_TRX


-- Investigation #1
-- Connection 3
Connection 3> SELECT *
                FROM information_schema.INNODB_TRX
               WHERE trx_mysql_thread_id IN (53, 54)\G
*************************** 1. row ***************************
                    trx_id: 296813
                 trx_state: RUNNING
               trx_started: 2020-06-27 17:46:10
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 1023
       trx_mysql_thread_id: 53
                 trx_query: UPDATE world.city SET Population = Population + MOD(ID, 2) + SLEEP(0.01)
       trx_operation_state: NULL
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 14
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 2031
         trx_rows_modified: 1009
   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
*************************** 2. row ***************************
                    trx_id: 283598406543136
                 trx_state: RUNNING
               trx_started: 2020-06-27 17:46:10
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 0
       trx_mysql_thread_id: 54
                 trx_query: SELECT COUNT(*) FROM world.city WHERE ID > SLEEP(0.01)
       trx_operation_state: NULL
         trx_tables_in_use: 1
         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: 1
trx_autocommit_non_locking: 1
       trx_schedule_weight: NULL
2 rows in set (0.0008 sec)

Самая первая строка отображает некий пример изменяющей данные транзакции. В момент выборки своих сведений было изменено 1009 строк и имелось примерно в два раза больше блокировок записей. Вы также можете наблюдать что все транзакции всё ещё активно выполняются неким запросом (оператором UPDATE).

Вторая строка это некий образец оператора SELECT, выполняемого при разрешённом autocommit. Поскольку включена автоматическая фиксация, он может быть лишь единственным оператором в данной транзакции (START TRANSACTION в явном виде отключает автоматическую фиксацию) Значение столбца trx_query показывает что это запрос SELECT COUNT(*) без каких бы то ни было условий блокирования, а потому это некий оператор на исключительное считывание. Это означает, что InnoDB может опускать некие моменты, такие как подготовку к удерживанию блокировки и сведений по восстановлению для данной транзакции со снижением объёма накладных расходов такой транзакции. Для отображения этого значение столбца trx_autocommit_non_locking устанавливается равным 1.

Относительно того, о каких транзакциях вам стоит беспокоиться, зависит от ожидаемых рабочих нагрузок в вашей системе. Когда вы имеете нагрузки OLAP, ожидается, что будут присутствовать запросы SELECT с относительно продолжительным выполнением. Для рабочих нагрузок OLTP в чистом виде все исполняющиеся свыше одной секунды и изменяющие более нескольких строк могут указывать на проблемы. Например, для поиска транзакций, которые старше 10 секунд,вы можете воспользоваться таким запросом:


SELECT *
  FROM information_schema.INNODB_TRX
 WHERE trx_started < NOW() - INTERVAL 10 SECOND;

По желанию вы можете выполнять соединения с прочими таблицами, такими как threads и events_statements_current из схемы Производительности. Некий пример этого отображается в Листинге 3-3.

 

Листинг 3-3. Запрос подробностей старых транзакций


-- Investigation #3
Connection 3> SELECT thd.thread_id, thd.processlist_id,
                     trx.trx_id, stmt.event_id, trx.trx_started,
                     TO_SECONDS(NOW()) -
                                TO_SECONDS(trx.trx_started
                               ) AS age_seconds,
                     trx.trx_rows_locked, trx.trx_rows_modified,
                     FORMAT_PICO_TIME(stmt.timer_wait) AS latency,
                     stmt.rows_examined, stmt.rows_affected,
                     sys.format_statement(SQL_TEXT) as statement
                FROM information_schema.INNODB_TRX trx
                     INNER JOIN performance_schema.threads thd
                             ON thd.processlist_id = trx.trx_mysql_thread_id
                     INNER JOIN performance_schema.events_statements_current stmt
                          USING (thread_id)
               WHERE trx_started < NOW() - INTERVAL 10 SECOND\G
*************************** 1. row ***************************
        thread_id: 163
   processlist_id: 53
           trx_id: 296813
         event_id: 9
      trx_started: 2020-06-27 17:46:10
      age_seconds: 25
  trx_rows_locked: 2214
trx_rows_modified: 1100
          latency: 25.24 s
    rows_examined: 2201
    rows_affected: 0
        statement: UPDATE world.city SET Populati ... ion + MOD(ID, 2) + SLEEP(0.01)
*************************** 2. row ***************************
        thread_id: 164
   processlist_id: 54
           trx_id: 283598406543136
         event_id: 8
      trx_started: 2020-06-27 17:46:10
      age_seconds: 25
  trx_rows_locked: 0
trx_rows_modified: 0
          latency: 25.14 s
    rows_examined: 0
    rows_affected: 0
        statement: SELECT COUNT(*) FROM world.city WHERE ID > SLEEP(0.01)
        2 rows in set (0.0021 sec)

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

С представлением INNODB_TRX связан список имеющихся транзакций из монитора InnoDB.

Монитор InnoDB

Монитор InnoDB это некий представитель ножа Швейцарской армии сведений InnoDB, который к тому же содержит сведения относительно транзакций. Раздел TRANSACTIONS в получаемом от монитора InnDB выводе посвящён сведениям о транзакциях. Эта информация содержит не только список транзакций, но также и собственно длину исторического списка. Листинг 3-4 отображает некую выдержку монитора InnoDB с соответствующим образцом раздела транзакции, взятого сразу после предыдущего вывода от представления INNODB_TRX.

 

Листинг 3-4. Сведения о транзакциях из монитора InnoDB


-- Investigation #4
Connection 3> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2020-06-27 17:46:36 0x5784 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 20 seconds
...
------------
TRANSACTIONS
------------
Trx id counter 296814
Purge done for trx's n:o < 296813 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 296813, ACTIVE 26 sec fetching rows
mysql tables in use 1, locked 1
15 lock struct(s), heap size 1136, 2333 row lock(s), undo log entries 1160
MySQL thread id 53, OS thread handle 23748, query id 56574 localhost ::1 root User sleep
UPDATE world.city SET Population = Population + MOD(ID, 2) + SLEEP(0.01)
...

Самый верх раздела TRANSACTIONS показывает текущее значение счётчика имеющихся идентификаторов транзакций, за которым следует информация относительно того что было удалено из зарегистрированных операций отмены. Он отображает, что было очищено менее 296813 операций отмены в журналах для идентификаторов транзакций. Чем дальше отстаёт эта очистка, тем больше длина исторического списка (в третьей строке этого раздела). Считывание длины исторического списка из вывода монитора InnoDB является традиционным способом получения значения длины такого исторического списка. В нашем следующем разделе будет показано как лучше получать это значение для целей проведения мониторинга.

Остаток этого раздела составляет перечень транзакций. Обратите внимание на то, что эти выходные данные вырабатываются с теми же самыми двумя активными транзакциями, которые мы обнаружили в INNODB_TRX, сам перечень транзакций включат лишь одну активную транзакцию (а именно ту,которая служит для оператора UPDATE). В MySQL 5.7 и последующих версиях транзакции только для считывания без блокировки не включаются в перечень транзакция монитора InnoDB. По этой причине, когда вам требуется включать в список все активные транзакции, лучше применять представление INNODB_TRX .

Как уже упоминалось, имеется некий альтернативный способ получения длины исторического списка. Для этого вам потребуется воспользоваться метриками InnoDB.

INNODB_METRICS и sys.metrics

Отчёты монитора InnoDB полезны для администратора базы данных для получения некого обзора того что происходит в InnoDB, однако для выполнения мониторинга они не очень полезны, поскольку требуют синтаксического разбора для получения необходимых данных тем способом, которым мониторинг может их применять. Ранее в этой главе вы видели как можно получать необходимые сведения о транзакциях из представления information_schema.INNODB_TRX, однако что относительно таких метрик как длина исторического списка?

Система метрик InnoDB включает некоторые метрики, которые показывают сведения относительно необходимых транзакций в соответствующем представлении nformation_schema.INNODB_METRICS. Все эти метрики располагаются в подсистеме transaction. Листинг 3-5 показывает перечень необходимых метрик транзакций когда они разрешены по умолчанию, а также краткие комментарии, поясняющие что эти метрики измеряют.

 

Листинг 3-5. Относящиеся к транзакциям метрики InnoDB


-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1               56         166          6
-- Connection 1
Connection 1> SELECT NAME, COUNT, STATUS, COMMENT
                FROM information_schema.INNODB_METRICS
               WHERE SUBSYSTEM = 'transaction'\G
*************************** 1. row ***************************
   NAME: trx_rw_commits
  COUNT: 0
 STATUS: disabled
COMMENT: Number of read-write transactions  committed
*************************** 2. row ***************************
   NAME: trx_ro_commits
  COUNT: 0
 STATUS: disabled
COMMENT: Number of read-only transactions committed
*************************** 3. row ***************************
   NAME: trx_nl_ro_commits
  COUNT: 0
 STATUS: disabled
COMMENT: Number of non-locking auto-commit read-only transactions committed
*************************** 4. row ***************************
   NAME: trx_commits_insert_update
  COUNT: 0
 STATUS: disabled
COMMENT: Number of transactions committed with inserts and updates
*************************** 5. row ***************************
   NAME: trx_rollbacks
  COUNT: 0
 STATUS: disabled
COMMENT: Number of transactions rolled back
*************************** 6. row ***************************
   NAME: trx_rollbacks_savepoint
  COUNT: 0
 STATUS: disabled
COMMENT: Number of transactions rolled back to savepoint
*************************** 7. row ***************************
   NAME: trx_rollback_active
  COUNT: 0
 STATUS: disabled
COMMENT: Number of resurrected active transactions rolled back
*************************** 8. row ***************************
   NAME: trx_active_transactions
  COUNT: 0
 STATUS: disabled
COMMENT: Number of active transactions
*************************** 9. row ***************************
   NAME: trx_on_log_no_waits
  COUNT: 0
 STATUS: disabled
COMMENT: Waits for redo during transaction commits
*************************** 10. row ***************************
   NAME: trx_on_log_waits
  COUNT: 0
 STATUS: disabled
COMMENT: Waits for redo during transaction commits
*************************** 11. row ***************************
   NAME: trx_on_log_wait_loops
  COUNT: 0
 STATUS: disabled
COMMENT: Waits for redo during transaction commits
*************************** 12. row ***************************
   NAME: trx_rseg_history_len
  COUNT: 9
 STATUS: enabled
COMMENT: Length of the TRX_RSEG_HISTORY list
*************************** 13. row ***************************
   NAME: trx_undo_slots_used
  COUNT: 0
 STATUS: disabled
COMMENT: Number of undo slots used
*************************** 14. row ***************************
   NAME: trx_undo_slots_cached
  COUNT: 0
 STATUS: disabled
COMMENT: Number of undo slots cached
*************************** 15. row ***************************
   NAME: trx_rseg_current_size
  COUNT: 0
 STATUS: disabled
COMMENT: Current rollback segment size in pages
15 rows in set (0.0012 sec)

Наиболее важной из этих метрик является trx_rseg_history_len, которая является длиной исторического списка. Именно это та единственная метрика, которая разрешена по умолчанию. Те метрики, которые относятся к фиксациям и откатам могут применяться для определения того сколько у вас имеется транзакций чтения- записи, исключительного чтения и неблокированного исключительного считывания и насколько часто они фиксируются и откатываются обратно. Большое число обратных откатов предполагают наличие некой проблемы. Если вы подозреваете что журнал повторного выполнения выступает узким местом, можно воспользоваться метриками trx_on_log_% для измерения того сколько транзакций ожидается для журнала повторного исполнения во время фиксации транзакций.

[Совет]Совет

Вы разрешаете метрики InnoDB при помощи параметра innodb_monitor_enable и отключаете их через innodb_monitor_disable. Их можно выполнять динамическим способом.

Альтернативным и удобным способом для запросов метрик InnoDB высупает использование представление sys.metrics, которое также включает переменные глобального состояния. Листинг 3-6 отображает некий пример использования представления sys.metrics для получения текущих значений и того разрешены ли эти метрики.

 

Листинг 3-6. Применение представления sys.metrics для получения метрик транзакции


-- Connection   Processlist ID   Thread ID   Event ID
-- --------------------------------------------------
--          1               52         125          6
-- Connection 1
Connection 1> SELECT Variable_name AS Name,
                     Variable_value AS Value,
                     Enabled
                FROM sys.metrics
               WHERE Type = 'InnoDB Metrics - transaction';
+---------------------------+-------+---------+
| Name                      | Value | Enabled |
+---------------------------+-------+---------+
| trx_active_transactions   | 0     | NO      |
| trx_commits_insert_update | 0     | NO      |
| trx_nl_ro_commits         | 0     | NO      |
| trx_on_log_no_waits       | 0     | NO      |
| trx_on_log_wait_loops     | 0     | NO      |
| trx_on_log_waits          | 0     | NO      |
| trx_ro_commits            | 0     | NO      |
| trx_rollback_active       | 0     | NO      |
| trx_rollbacks             | 0     | NO      |
| trx_rollbacks_savepoint   | 0     | NO      |
| trx_rseg_current_size     | 0     | NO      |
| trx_rseg_history_len      | 16    | YES     |
| trx_rw_commits            | 0     | NO      |
| trx_undo_slots_cached     | 0     | NO      |
| trx_undo_slots_used       | 0     | NO      |
+---------------------------+-------+---------+
15 rows in set (0.0089 sec)

Здесь показано что длина нашего исторического списка равна 16, что достаточно низкое значение, а потому почти нет накладных расходов от таких журналов отмены. Все остальные метрики отключены.

Выводы

Эта глава обсудила как вы можете получать сведения относительно транзакций InnoDB. Первичным источником подробных сведений выступает представление INNODB_TRX из схемы Информации, которое включает такие подробности как то, когда соответствующая транзакция была запущена, общее число заблокированных и изменённых строк и т.п.. По своему желанию вы можете выполнять соединение с таблицами схемы Производительности для получения дополнительных сведений относительно соответствующей транзакции.

Для получения сведений относительно заблокированных транзакций вы также можете применять монитор InnoDB; тем не менее, в целом предпочитается использование представления INNODB_TRX. Если вы ищите агрегированные статистические данные верхнего уровня, вы можете воспользоваться представлением information_schema.INNODB_METRICS или представлением sys.metrics. Наиболее часто применяется метрика trx_rseg_history_len, которая показывает длину исторического списка.

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