14.3.15.3 InnoDB INFORMATION_SCHEMA tables

14.3.15.3.1 Information Schema Tables about Compression
14.3.15.3.2 Information Schema Tables about Transactions
14.3.15.3.3 Special Locking Considerations for InnoDB INFORMATION_SCHEMA Tables

The INFORMATION_SCHEMA is a MySQL feature that helps you monitor server activity to diagnose capacity and performance issues. Several InnoDB-related INFORMATION_SCHEMA tables (INNODB_CMP, INNODB_CMP_RESET, INNODB_CMPMEM, INNODB_CMPMEM_RESET, INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS) contain live information about compressed InnoDB tables, the compressed InnoDB buffer pool, all transactions currently executing inside InnoDB, the locks that transactions hold and those that are blocking transactions waiting for access to a resource (a table or row).

The Information Schema tables are themselves plugins to the MySQL server, and must be activated by INSTALL statements. If they are installed, but the InnoDB storage engine plugin is not installed, these tables appear to be empty.

This section describes the InnoDB-related Information Schema tables and shows some examples of their use.

14.3.15.3.1 Information Schema Tables about Compression

Two new pairs of Information Schema tables can give you some insight into how well compression is working overall. One pair of tables contains information about the number of compression operations and the amount of time spent performing compression. Another pair of tables contains information on the way memory is allocated for compression.

14.3.15.3.1.1 INNODB_CMP and INNODB_CMP_RESET

The INNODB_CMP and INNODB_CMP_RESET tables contain status information on the operations related to compressed tables, which are covered in Section 14.3.10, “InnoDB Compressed Tables”. The compressed page size is in the column PAGE_SIZE.

These two tables have identical contents, but reading from INNODB_CMP_RESET resets the statistics on compression and uncompression operations. For example, if you archive the output of INNODB_CMP_RESET every 60 minutes, you see the statistics for each hourly period. If you monitor the output of INNODB_CMP (making sure never to read INNODB_CMP_RESET), you see the cumulated statistics since InnoDB was started.

For the table definition, see Table 20.1, “Columns of INNODB_CMP and INNODB_CMP_RESET.

14.3.15.3.1.2 INNODB_CMPMEM and INNODB_CMPMEM_RESET

The INNODB_CMPMEM and INNODB_CMPMEM_RESET tables contain status information on the compressed pages that reside in the buffer pool. Please consult Section 14.3.10, “InnoDB Compressed Tables” for further information on compressed tables and the use of the buffer pool. The INNODB_CMP and INNODB_CMP_RESET tables should provide more useful statistics on compression.

Internal Details

InnoDB uses a buddy allocator system to manage memory allocated to pages of various sizes, from 1KB to 16KB. Each row of the two tables described here corresponds to a single page size.

These two tables have identical contents, but reading from INNODB_CMPMEM_RESET resets the statistics on relocation operations. For example, if every 60 minutes you archived the output of INNODB_CMPMEM_RESET, it would show the hourly statistics. If you never read INNODB_CMPMEM_RESET and monitored the output of INNODB_CMPMEM instead, it would show the cumulated statistics since InnoDB was started.

For the table definition, see Table 20.2, “Columns of INNODB_CMPMEM and INNODB_CMPMEM_RESET”.

14.3.15.3.1.3 Using the Compression Information Schema Tables

Example 14.1 Using the Compression Information Schema Tables

The following is sample output from a database that contains compressed tables (see Section 14.3.10, “InnoDB Compressed Tables”, INNODB_CMP, and INNODB_CMPMEM).

The following table shows the contents of INFORMATION_SCHEMA.INNODB_CMP under a light workload. The only compressed page size that the buffer pool contains is 8K. Compressing or uncompressing pages has consumed less than a second since the time the statistics were reset, because the columns COMPRESS_TIME and UNCOMPRESS_TIME are zero.

page sizecompress opscompress ops okcompress timeuncompress opsuncompress time
102400000
204800000
409600000
819210489210610
1638400000

According to INNODB_CMPMEM, there are 6169 compressed 8KB pages in the buffer pool.

The following table shows the contents of INFORMATION_SCHEMA.INNODB_CMPMEM under a light workload. Some memory is unusable due to fragmentation of the InnoDB memory allocator for compressed pages: SUM(PAGE_SIZE*PAGES_FREE)=6784. This is because small memory allocation requests are fulfilled by splitting bigger blocks, starting from the 16K blocks that are allocated from the main buffer pool, using the buddy allocation system. The fragmentation is this low because some allocated blocks have been relocated (copied) to form bigger adjacent free blocks. This copying of SUM(PAGE_SIZE*RELOCATION_OPS) bytes has consumed less than a second (SUM(RELOCATION_TIME)=0).

page sizepages usedpages freerelocation opsrelocation time
10240000
20480100
40960100
81926169050
163840000

14.3.15.3.2 Information Schema Tables about Transactions

Three InnoDB-related Information Schema tables make it easy to monitor transactions and diagnose possible locking problems. The three tables are INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS.

  • INNODB_TRX

    Contains information about every transaction currently executing inside InnoDB, including whether the transaction is waiting for a lock, when the transaction started, and the particular SQL statement the transaction is executing.

    For the table definition, see Table 20.3, “INNODB_TRX Columns”.

  • INNODB_LOCKS

    Each transaction in InnoDB that is waiting for another transaction to release a lock (INNODB_TRX.TRX_STATE='LOCK WAIT') is blocked by exactly one blocking lock request. That blocking lock request is for a row or table lock held by another transaction in an incompatible mode. The waiting or blocked transaction cannot proceed until the other transaction commits or rolls back, thereby releasing the requested lock. For every blocked transaction, INNODB_LOCKS contains one row that describes each lock the transaction has requested, and for which it is waiting. INNODB_LOCKS also contains one row for each lock that is blocking another transaction, whatever the state of the transaction that holds the lock ('RUNNING', 'LOCK WAIT', 'ROLLING BACK' or 'COMMITTING'). The lock that is blocking a transaction is always held in a mode (read vs. write, shared vs. exclusive) incompatible with the mode of requested lock.

    For the table definition, see Table 20.4, “INNODB_LOCKS Columns”.

  • INNODB_LOCK_WAITS

    Using this table, you can tell which transactions are waiting for a given lock, or for which lock a given transaction is waiting. This table contains one or more rows for each blocked transaction, indicating the lock it has requested and any locks that are blocking that request. The REQUESTED_LOCK_ID refers to the lock that a transaction is requesting, and the BLOCKING_LOCK_ID refers to the lock (held by another transaction) that is preventing the first transaction from proceeding. For any given blocked transaction, all rows in INNODB_LOCK_WAITS have the same value for REQUESTED_LOCK_ID and different values for BLOCKING_LOCK_ID.

    For the table definition, see Table 20.5, “INNODB_LOCK_WAITS Columns”.

14.3.15.3.2.1 Using the Transaction Information Schema Tables

Example 14.2 Identifying Blocking Transactions

It is sometimes helpful to be able to identify which transaction is blocking another. You can use the Information Schema tables to find out which transaction is waiting for another, and which resource is being requested.

Suppose you have the following scenario, with three users running concurrently. Each user (or session) corresponds to a MySQL thread, and executes one transaction after another. Consider the state of the system when these users have issued the following commands, but none has yet committed its transaction:

  • User A:

    BEGIN;
    SELECT a FROM t FOR UPDATE;
    SELECT SLEEP(100);
    
  • User B:

    SELECT b FROM t FOR UPDATE;
    
  • User C:

    SELECT c FROM t FOR UPDATE;
    

In this scenario, you can use this query to see who is waiting for whom:

SELECT r.trx_id waiting_trx_id,  
       r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id, 
       b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
   FROM       information_schema.innodb_lock_waits w
   INNER JOIN information_schema.innodb_trx b  ON  
    b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r  ON  
    r.trx_id = w.requesting_trx_id;
waiting trx idwaiting threadwaiting queryblocking trx idblocking threadblocking query
A46SELECT b FROM t FOR UPDATEA35SELECT SLEEP(100)
A57SELECT c FROM t FOR UPDATEA35SELECT SLEEP(100)
A57SELECT c FROM t FOR UPDATEA46SELECT b FROM t FOR UPDATE

In the above result, you can identify users by the waiting query or blocking query. As you can see:

  • User B (trx id 'A4', thread 6) and User C (trx id 'A5', thread 7) are both waiting for User A (trx id 'A3', thread 5).

  • User C is waiting for User B as well as User A.

You can see the underlying data in the tables INNODB_TRX, INNODB_LOCKS, and INNODB_LOCK_WAITS.

The following table shows some sample contents of INFORMATION_SCHEMA.INNODB_TRX.

trx idtrx statetrx startedtrx requested lock idtrx wait startedtrx weighttrx mysql thread idtrx query
A3RUN­NING2008-01-15 16:44:54NULLNULL25SELECT SLEEP(100)
A4LOCK WAIT2008-01-15 16:45:09A4:1:3:22008-01-15 16:45:0926SELECT b FROM t FOR UPDATE
A5LOCK WAIT2008-01-15 16:45:14A5:1:3:22008-01-15 16:45:1427SELECT c FROM t FOR UPDATE

The following table shows some sample contents of INFORMATION_SCHEMA.INNODB_LOCKS.

lock idlock trx idlock modelock typelock tablelock indexlock spacelock pagelock reclock data
A3:1:3:2A3XRECORD`test`.`t``PRIMARY`1320x0200
A4:1:3:2A4XRECORD`test`.`t``PRIMARY`1320x0200
A5:1:3:2A5XRECORD`test`.`t``PRIMARY`1320x0200

The following table shows some sample contents of INFORMATION_SCHEMA.INNODB_LOCK_WAITS.

requesting trx idrequested lock idblocking trx idblocking lock id
A4A4:1:3:2A3A3:1:3:2
A5A5:1:3:2A3A3:1:3:2
A5A5:1:3:2A4A4:1:3:2

Example 14.3 More Complex Example of Transaction Data in Information Schema Tables

Sometimes you would like to correlate the internal InnoDB locking information with session-level information maintained by MySQL. For example, you might like to know, for a given InnoDB transaction ID, the corresponding MySQL session ID and name of the user that may be holding a lock, and thus blocking another transaction.

The following output from the INFORMATION_SCHEMA tables is taken from a somewhat loaded system.

As can be seen in the following tables, there are several transactions running.

The following INNODB_LOCKS and INNODB_LOCK_WAITS tables shows that:

  • Transaction 77F (executing an INSERT) is waiting for transactions 77E, 77D and 77B to commit.

  • Transaction 77E (executing an INSERT) is waiting for transactions 77D and 77B to commit.

  • Transaction 77D (executing an INSERT) is waiting for transaction 77B to commit.

  • Transaction 77B (executing an INSERT) is waiting for transaction 77A to commit.

  • Transaction 77A is running, currently executing SELECT.

  • Transaction E56 (executing an INSERT) is waiting for transaction E55 to commit.

  • Transaction E55 (executing an INSERT) is waiting for transaction 19C to commit.

  • Transaction 19C is running, currently executing an INSERT.

Note that there may be an inconsistency between queries shown in the two tables INNODB_TRX.TRX_QUERY and PROCESSLIST.INFO. The current transaction ID for a thread, and the query being executed in that transaction, may be different in these two tables for any given thread. See Section 14.3.15.3.3.3, “Possible Inconsistency with PROCESSLIST for an explanation.

The following table shows the contents of INFORMATION_SCHEMA.PROCESSLIST in a system running a heavy workload.

IDUSERHOSTDBCOMMANDTIMESTATEINFO
384rootlocalhosttestQuery10updateinsert into t2 values …
257rootlocalhosttestQuery3updateinsert into t2 values …
130rootlocalhosttestQuery0updateinsert into t2 values …
61rootlocalhosttestQuery1updateinsert into t2 values …
8rootlocalhosttestQuery1updateinsert into t2 values …
4rootlocalhosttestQuery0preparingSELECT * FROM processlist
2rootlocalhosttestSleep566NULL

The following table shows the contents of INFORMATION_SCHEMA.INNODB_TRX in a system running a heavy workload.

trx idtrx statetrx startedtrx requested lock idtrx wait startedtrx weighttrx mysql thread idtrx query
77FLOCK WAIT2008-01-15 13:10:1677F:8062008-01-15 13:10:161876insert into t09 (D, B, C) values …
77ELOCK WAIT2008-01-15 13:10:1677E:8062008-01-15 13:10:161875insert into t09 (D, B, C) values …
77DLOCK WAIT2008-01-15 13:10:1677D:8062008-01-15 13:10:161874insert into t09 (D, B, C) values …
77BLOCK WAIT2008-01-15 13:10:1677B:733​:12:12008-01-15 13:10:164873insert into t09 (D, B, C) values …
77ARUN­NING2008-01-15 13:10:16NULLNULL4872select b, c from t09 where …
E56LOCK WAIT2008-01-15 13:10:06E56:743​:6:22008-01-15 13:10:065384insert into t2 values …
E55LOCK WAIT2008-01-15 13:10:06E55:743​:38:22008-01-15 13:10:13965257insert into t2 values …
19CRUN­NING2008-01-15 13:09:10NULLNULL2900130insert into t2 values …
E15RUN­NING2008-01-15 13:08:59NULLNULL539561insert into t2 values …
51DRUN­NING2008-01-15 13:08:47NULLNULL98078insert into t2 values …

The following table shows the contents of INFORMATION_SCHEMA.INNODB_LOCK_WAITS in a system running a heavy workload.

requesting trx idrequested lock idblocking trx idblocking lock id
77F77F:80677E77E:806
77F77F:80677D77D:806
77F77F:80677B77B:806
77E77E:80677D77D:806
77E77E:80677B77B:806
77D77D:80677B77B:806
77B77B:733:12:177A77A:733:12:1
E56E56:743:6:2E55E55:743:6:2
E55E55:743:38:219C19C:743:38:2

The following table shows the contents of INFORMATION_SCHEMA.INNODB_LOCKS in a system running a heavy workload.

lock idlock trx idlock modelock typelock tablelock indexlock spacelock pagelock reclock data
77F:80677FAUTO​_INCTABLE`test`​.`t09`NULLNULLNULLNULLNULL
77E:80677EAUTO​_INCTABLE`test`​.`t09`NULLNULLNULLNULLNULL
77D:80677DAUTO​_INCTABLE`test`​.`t09`NULLNULLNULLNULLNULL
77B:80677BAUTO​_INCTABLE`test`​.`t09`NULLNULLNULLNULLNULL
77B:733​:12:177BXRECORD`test`​.`t09``PRIMARY`733121supremum pseudo-record
77A:733​:12:177AXRECORD`test`​.`t09``PRIMARY`733121supremum pseudo-record
E56:743​:6:2E56SRECORD`test`​.`t2``PRIMARY`743620, 0
E55:743​:6:2E55XRECORD`test`​.`t2``PRIMARY`743620, 0
E55:743​:38:2E55SRECORD`test`​.`t2``PRIMARY`7433821922, 1922
19C:743​:38:219CXRECORD`test`​.`t2``PRIMARY`7433821922, 1922

14.3.15.3.3 Special Locking Considerations for InnoDB INFORMATION_SCHEMA Tables
14.3.15.3.3.1 Understanding InnoDB Locking

When a transaction updates a row in a table, or locks it with SELECT FOR UPDATE, InnoDB establishes a list or queue of locks on that row. Similarly, InnoDB maintains a list of locks on a table for table-level locks transactions hold. If a second transaction wants to update a row or lock a table already locked by a prior transaction in an incompatible mode, InnoDB adds a lock request for the row to the corresponding queue. For a lock to be acquired by a transaction, all incompatible lock requests previously entered into the lock queue for that row or table must be removed (the transactions holding or requesting those locks either commit or roll back).

A transaction may have any number of lock requests for different rows or tables. At any given time, a transaction may be requesting a lock that is held by another transaction, in which case it is blocked by that other transaction. The requesting transaction must wait for the transaction that holds the blocking lock to commit or rollback. If a transaction is not waiting for a a lock, it is in the 'RUNNING' state. If a transaction is waiting for a lock, it is in the 'LOCK WAIT' state.

The INNODB_LOCKS table holds one or more row for each 'LOCK WAIT' transaction, indicating any lock requests that are preventing its progress. This table also contains one row describing each lock in a queue of locks pending for a given row or table. The INNODB_LOCK_WAITS table shows which locks already held by a transaction are blocking locks requested by other transactions.

14.3.15.3.3.2 Granularity of INFORMATION_SCHEMA Data

The data exposed by the transaction and locking tables represent a glimpse into fast-changing data. This is not like other (user) tables, where the data changes only when application-initiated updates occur. The underlying data is internal system-managed data, and can change very quickly.

For performance reasons, and to minimize the chance of misleading JOINs between the INFORMATION_SCHEMA tables, InnoDB collects the required transaction and locking information into an intermediate buffer whenever a SELECT on any of the tables is issued. This buffer is refreshed only if more than 0.1 seconds has elapsed since the last time the buffer was read. The data needed to fill the three tables is fetched atomically and consistently and is saved in this global internal buffer, forming a point-in-time snapshot. If multiple table accesses occur within 0.1 seconds (as they almost certainly do when MySQL processes a join among these tables), then the same snapshot is used to satisfy the query.

A correct result is returned when you JOIN any of these tables together in a single query, because the data for the three tables comes from the same snapshot. Because the buffer is not refreshed with every query of any of these tables, if you issue separate queries against these tables within a tenth of a second, the results are the same from query to query. On the other hand, two separate queries of the same or different tables issued more than a tenth of a second apart may see different results, since the data come from different snapshots.

Because InnoDB must temporarily stall while the transaction and locking data is collected, too frequent queries of these tables can negatively impact performance as seen by other users.

As these tables contain sensitive information (at least INNODB_LOCKS.LOCK_DATA and INNODB_TRX.TRX_QUERY), for security reasons, only the users with the PROCESS privilege are allowed to SELECT from them.

14.3.15.3.3.3 Possible Inconsistency with PROCESSLIST

As just described, while the transaction and locking data is correct and consistent when these INFORMATION_SCHEMA tables are populated. For example, the query in INNODB_TRX is always consistent with the rest of INNODB_TRX, INNODB_LOCKS and INNODB_LOCK_WAITS when the data comes from the same snapshot. However, the underlying data changes so fast that similar glimpses at other, similarly fast-changing data, may not be in synchrony. Thus, you should be careful in comparing the data in the InnoDB transaction and locking tables with that in the PROCESSLIST table. The data from the PROCESSLIST table does not come from the same snapshot as the data about locking and transactions. Even if you issue a single SELECT (joining INNODB_TRX and PROCESSLIST, for example), the content of those tables is generally not consistent. INNODB_TRX may reference rows that are not present in PROCESSLIST or the currently executing SQL query of a transaction, shown in INNODB_TRX.TRX_QUERY may differ from the one in PROCESSLIST.INFO.