Bug #53336 Improved InnoDB Transaction Reporting
Submitted: 30 Apr 2010 21:56 Modified: 14 Dec 2010 20:19
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S4 (Feature request)
Version:1.1 OS:Any
Assigned to: Jimmy Yang CPU Architecture:Any
Tags: innodb_trx, show innodb status

[30 Apr 2010 21:56] Mark Leith
Description:
Parsing the transaction list within SHOW ENGINE INNODB STATUS for details is not easy. It would be more usable if all of the information within the TRANSACTIONS section were also in the INNODB_TRX section. 

Further, truncating at ~64K should no longer be necessary. 

How to repeat:
Try to view a very large list of transactions in SHOW ENGINE INNODB STATUS.
[30 Apr 2010 21:59] Mark Leith
Patch to improve the I_S.INNODB_TRX table, increase truncation limit of SHOW ENGINE INNODB STATUS to 1Mb

Attachment: innodb_trx.patch (application/octet-stream, text), 17.72 KiB.

[30 Apr 2010 22:03] Mark Leith
The attached patch extends the INNODB_TRX table to:

mysql> desc information_schema.innodb_trx;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field                      | Type                | Null | Key | Default             | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
| trx_id                     | varchar(18)         | NO   |     |                     |       |
| trx_state                  | varchar(13)         | NO   |     |                     |       |
| trx_started                | datetime            | NO   |     | 0000-00-00 00:00:00 |       |
| trx_requested_lock_id      | varchar(81)         | YES  |     | NULL                |       |
| trx_wait_started           | datetime            | YES  |     | NULL                |       |
| trx_weight                 | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_mysql_thread_id        | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_query                  | varchar(1024)       | YES  |     | NULL                |       |
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |
| trx_apative_hash_latched   | int(1)              | NO   |     | 0                   |       |
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |
+----------------------------+---------------------+------+-----+---------------------+-------+
22 rows in set (0.05 sec)
[3 May 2010 16:21] Mark Callaghan
This is nice. When can we have it in a prod release?
[15 Jun 2010 8:14] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100615080459-smuswd9ooeywcxuc) (version source revid:marko.makela@oracle.com-20100514130815-ym7j7cfu88ro6km4) (merge vers: 5.1.48) (pib:16)
[15 Jun 2010 8:30] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100615080558-cw01bzdqr1bdmmec) (version source revid:marko.makela@oracle.com-20100514130815-ym7j7cfu88ro6km4) (pib:16)
[9 Aug 2010 10:01] Jimmy Yang
Pushed into 5.5.5
[17 Aug 2010 22:12] John Russell
Adding to the 5.5.5 change log (for now, just the part about the bigger truncation limit):

The output of the SHOW ENGINE INNODB STATUS command can now be as
large as 1MB without being truncated. You might need such a long
report when monitoring a system with many concurrent transactions.
[18 Aug 2010 13:29] Mark Leith
Hey John,

Actually, the truncation limit was not accepted in the final patch (and still has not been pushed yet to my knowledge). 

This patch adds the following columns:

mysql> desc innodb_trx;
+----------------------------+---------------------+------+-----+---------------------+-------+
| Field                      | Type                | Null | Key | Default             | Extra |
+----------------------------+---------------------+------+-----+---------------------+-------+
...
| trx_operation_state        | varchar(64)         | YES  |     | NULL                |       |
| trx_tables_in_use          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_tables_locked          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_lock_structs           | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_lock_memory_bytes      | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_rows_locked            | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_rows_modified          | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_concurrency_tickets    | bigint(21) unsigned | NO   |     | 0                   |       |
| trx_isolation_level        | varchar(16)         | NO   |     |                     |       |
| trx_unique_checks          | int(1)              | NO   |     | 0                   |       |
| trx_foreign_key_checks     | int(1)              | NO   |     | 0                   |       |
| trx_last_foreign_key_error | varchar(256)        | YES  |     | NULL                |       |
| trx_adaptive_hash_latched  | int(1)              | NO   |     | 0                   |       |
| trx_adaptive_hash_timeout  | bigint(21) unsigned | NO   |     | 0                   |       |
+----------------------------+---------------------+------+-----+---------------------+-------+
22 rows in set (0.05 sec)

Most of these are self explanatory, a lot of them duplicate the information within SHOW ENGINE INNODB STATUS (so you now no longer need to parse the output of that to get complete transaction information).

Below are some of my notes. For comparison, here's an example of a transaction from the SHOW ENGINE INNODB STATUS statement:

---TRANSACTION 517, ACTIVE 1 sec, OS thread id 2958520320 inserting
mysql tables in use 2, locked 2
189 lock struct(s), heap size 27968, 54389 row lock(s), undo log entries 2406
MySQL thread id 2, query id 36 localhost root Sending data
insert ignore into t1 (select (i * rand())*100, 1 from t1)

trx_operation_state  - corresponds to "inserting", InnoDB's internal transaction state
trx_tables_in_use - corresponds to "mysql tables in use 2", the number of table locks requested by MySQL via external_lock()
trx_tables_locked - corresponds to "locked 2", the number of actual table locks taken via external_lock()
trx_lock_structs - corresponds to "189 lock struct(s)", the size of the lock struct list
trx_lock_memory_bytes - corresponds to "heap size 27968", the number of bytes allocated to locks structs
trx_rows_locked - corresponds to "54389 row lock(s)", an estimation of the number of rows locked (delete marked rows may make it imprecise)
trx_rows_modified - corresponds to "undo log entries 2406", the number of rows modified in the transaction (inserted, updated, deleted)

Not seen in the transaction output above:

trx_concurrency_tickets - corresponds to "thread declared inside InnoDB 89" for a transaction in SHOW ENGINE INNODB STATUS, the number of concurrency tickets remaining for the transaction when innodb_thread_concurrency != 0
trx_isolation_level - the transactions isolation level
trx_unique_checks - whether the transaction has "SET UNIQUE_CHECKS = 0"
trx_foreign_key_checks - whether the transaction has "SET FOREIGN_KEY_CHECKS = 0"
trx_last_foreign_key_error - if the last statement in the transaction resulted in an FK error, the error text is printed here
trx_adaptive_hash_latched - corresponds to "holds adaptive hash latch" being printed for a transaction in SHOW ENGINE INNODB STATUS
trx_adaptive_hash_timeout - when innodb_adapative_hash_index is enabled (default), statements that try to get the adapative hash latch spin 10000 (BTR_SEA_TIMEOUT) times, re-trying getting the adaptive hash latch, before giving up. lower numbers here for a lot of transactions may indicate contention on the adaptive hash latch
[18 Aug 2010 17:21] John Russell
Changed the changelog entry to say:

The INFORMATION_SCHEMA.INNODB_TRX table now includes a number of new
fields that duplicate information from the SHOW ENGINE INNODB STATUS
output. You no longer need to parse that output to get complete
transaction information.

(Will leave in "Documenting" status until the info about the new fields is incorporated into the body of the manual.)
[18 Aug 2010 22:30] Mark Callaghan
MySQL has recently been making a lot of changes like this that make MySQL much better. Thank you.
[18 Aug 2010 23:40] Baron Schwartz
I second Mark's comment: thank you.  I have written way too many parsers.