Troubleshooting InnoDB History Length with Hung MySQL Transaction


In this post we have explained in detail how a hung transaction had lead InnoDB history length to grow uncontrolled and affected MySQL performance negatively. Sometimes we get  emergency support calls in MinervaDB that SELECT queries are running unacceptably slower and slower, Eventually customer should restart the MySQL server again and that’s not a solution for troubleshooting MySQL performance, The symptoms of this ticket were so misleading first (we even spend initially sometime on query performance analysis and index operation forensics to reach nowhere) and finally we got some clarity on the output of the query “show engine innodb status”, We have copied the same below:

Simulation

We decided to simulate the entire issue with sysbench (used Sysbench script from Percona for this )

The above output led us to check InnoDB_history_list_length:

When innodb transaction history grows continuously, The SELECTs need to scan more and more for the previous version of the rows, This leads to a major performance bottleneck. But, Why InnoDB_history_list_length is growing continuously ? There are some 1278 transactions in this state and active for 915248 sec. The output of MySQL processlist showed they were in “Sleep” state, Which clearly says those transaction were either “lost” or “hung”. We could also see each of these transactions were holding two lock structures and one undo record, They were not committed and not rolled-back. These transactions were doing absolutely nothing. This happens due to the default transaction isolation level implementation REPEATABLE-READ in the InnoDB. InnoDB is an MVCC storage engine, which means you can start a transaction and continue to see a consistent snapshot even as the data changes. This is implemented by keeping old versions of rows as they are modified. So InnoDB history list is the undo logs which are used to store these modifications. They are a fundamental part of InnoDB’s transaction processing architecture.

How do we measure the the InnoDB History List or  Do we have some units for that ?

What does it really means if InnoDB history list length is 7625 ?

This topic is super confusing because there are several claims made on units of InnoDB history list length:

  • unpurged old row versions
  • unpurged transactions in the undo space
  • modifications to the database
  • undo segments
  • unflushed segments in the undo log
  • undo pages, or number of pages in the undo log
  • undo logs

So what is it, really?

Let’s go to the source:

Actually this will encourage you to learn more about InnoDb internals.

The best name for the unit is undo logs, which are “update undo logs for committed transactions”, to quote the source. But an “undo log” is a special term with specific meaning in InnoDB. An undo log is a single set of atomic changes a transaction makes, which may actually modify multiple records.

Troubleshooting InnoDB transaction history issue

We have copied below InnoDB transaction isolation levels and how they impact on InnoDB History Length:

MySQL VersionTransaction isolationInnoDB History Length
MySQL 5.6repeatable readInnoDB history is not purged until “hung” transactions are completed
MySQL 5.6repeatable readInnoDB history is purged (this solution worked for most of our customers)
Aurorarepeatable readInnoDB history is not purged until “hung” transactions are completed
Aurorarepeatable readInnoDB history is not purged until “hung” transactions are completed

In the InnoDB transaction isolation level READ COMMITTED, InnoDB does not need to maintain history length when other transactions have committed changes, This setting works with troubleshooting InnoDB History Length in MySQL 5.6 and later. This recommendation (READ COMMITTED isolation level) doesn’t work with Amazon Aurora, the history length still continues to grow. 

Forensics on the hung transactions and list the queries

There are several methods to audit performance bottleneck on the transactions and queries related to the same in MySQL:

Finding queries from the hung transactions with Performance Schema

To identify the hung queries in MySQL with Performance Schema, Follow the steps below:

  • Enable Performance Schema
  • Enable events_statement_history 

  • To find all transactions started in 60 seconds ago, Please run the query below:

Note: Please change the number of seconds to match your workload. 

Conclusions 

The hung transactions in InnoDB will lead to uncontrolled growth of InnoDB History Length, This will directly impact SELECT query performance in MySQL negatively. We have addressed this problem for several customers by changing the TRANSACTION ISOLATION LEVEL to READ-COMMITTED. Technically, InnoDB History Length are the undo logs which can be used for recreating the history for MVCC purposes or can be rolled back. Let us know your comments, Thank you !

References:

☛ MinervaDB contacts – Sales & General Inquiries

Business FunctionContact
☎ CONTACT GLOBAL SALES (24*7)📞 (844) 588-7287 (USA)
📞 (415) 212-6625 (USA)
📞 (778) 770-5251 (Canada)
☎ TOLL FREE PHONE (24*7)📞 (844) 588-7287
🚩 MINERVADB FAX+1 (209) 314-2364
📨 MinervaDB Email - General / Sales / Consultingcontact@minervadb.com
📨 MinervaDB Email - Supportsupport@minervadb.com
📨 MinervaDB Email -Remote DBAremotedba@minervadb.com
📨 Shiv Iyer Email - Founder and Principalshiv@minervadb.com
🏠 CORPORATE ADDRESS: CALIFORNIAMinervaDB Inc.,
340 S LEMON AVE #9718
WALNUT 91789 CA, US
🏠 CORPORATE ADDRESS: DELAWAREMinervaDB Inc.,
PO Box 2093 PHILADELPHIA PIKE #3339
CLAYMONT, DE 19703
🏠 CORPORATE ADDRESS: HOUSTONMinervaDB Inc., 1321 Upland Dr. PMB 19322, Houston,
TX, 77043, US