Extended Show Engine InnoDB Status

This feature reorganizes the output of SHOW ENGINE INNODB STATUS for a better readability and prints the amount of memory used by the internal hash tables. In addition, new variables are available to control the output.

This feature modified the SHOW ENGINE INNODB STATUS command as follows:

• TRANSACTION section was moved to the end of the output, so that important information is not overlooked when the there is a large amount of it.
• Added two variables to control SHOW ENGINE INNODB STATUS information presented (bugfix for #29123):
  • innodb_show_verbose_locks - Whether to show records locked
  • innodb_show_locks_held - Number of locks held to print for each InnoDB transaction
• Added extended information about InnoDB internal hash table sizes (in bytes) in the BUFFER POOL AND MEMORY section; also added buffer pool size in bytes.
• Added additional LOG section information (beginning in release 5.5.8-20.0).

Version Specific Information

• 5.5.8-20.0 Added status variables showing information from SHOW ENGINE INNODB STATUS.
• 5.5.8-20.0 Added additional information in the LOG section.
• 5.5.10-20.1: Renamed status variable innodb_row_lock_numbers to innodb_current_row_locks.
• 5.5.31-30.3: Added innodb_read_views_memory and innodb_descriptors_memory to improve InnoDB memory diagnostics.

Other Information

• Author / Origin: Baron Schwartz, http://lists.mysql.com/internals/35174

System Variables

variable innodb_show_verbose_locks

Command Line:Yes Config File:Yes Scope:Global Dynamic:Yes Variable Type:ULONG Default Value:0 Range:0 - 1

Specifies to show records locked in SHOW ENGINE INNODB STATUS. The default is 0, which means only the higher-level information about the lock (which table and index is locked, etc.) is printed. If set to 1, then traditional InnoDB behavior is enabled: the records that are locked are dumped to the output.

variable innodb_show_locks_held

Command Line:Yes Config File:Yes Scope:Global Dynamic:Yes Variable Type:ULONG Default Value:10 Range:0 - 1000

Specifies the number of locks held to print for each InnoDB transaction in SHOW ENGINE INNODB STATUS.

Status Variables

The status variables here contain information available in the output of SHOW ENGINE INNODB STATUS, organized by the sections SHOW ENGINE INNODB STATUS displays. If you are familiar with the output of SHOW ENGINE INNODB STATUS, you will probably already recognize the information these variables contain.

BACKGROUND THREAD

The following variables contain information in the BACKGROUND THREAD section of the output from SHOW ENGINE INNODB STATUS. An example of that output is:

Insert an example of BACKGROUND THREAD section output here.

variable innodb_master_thread_1_second_loops

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_master_thread_10_second_loops

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_master_thread_background_loops

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_master_thread_main_flush_loops

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_master_thread_sleeps

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_background_log_sync

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

SEMAPHORES

The following variables contain information in the SEMAPHORES section of the output from SHOW ENGINE INNODB STATUS. An example of that output is:

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 9664, signal count 11182
Mutex spin waits 20599, rounds 223821, OS waits 4479
RW-shared spins 5155, OS waits 1678; RW-excl spins 5632, OS waits 2592
Spin rounds per wait: 10.87 mutex, 15.01 RW-shared, 27.19 RW-excl

variable innodb_mutex_os_waits

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_mutex_spin_rounds

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_mutex_spin_waits

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_s_lock_os_waits

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_s_lock_spin_rounds

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_s_lock_spin_waits

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_x_lock_os_waits

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_x_lock_spin_rounds

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_x_lock_spin_waits

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

INSERT BUFFER AND ADAPTIVE HASH INDEX

The following variables contain information in the INSERT BUFFER AND ADAPTIVE HASH INDEX section of the output from SHOW ENGINE INNODB STATUS. An example of that output is:

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 6089, seg size 6091,
44497 inserts, 44497 merged recs, 8734 merges
Hash table size 276707, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

variable innodb_ibuf_discarded_delete_marks

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_ibuf_discarded_deletes

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_ibuf_discarded_inserts

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_ibuf_free_list

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_ibuf_merged_delete_marks

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_ibuf_merged_deletes

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_ibuf_merged_inserts

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_ibuf_merges

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_ibuf_segment_size

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_ibuf_size

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_adaptive_hash_cells

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_adaptive_hash_heap_buffers

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_adaptive_hash_hash_searches

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_adaptive_hash_non_hash_searches

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

LOG

The following variables contain information in the LOG section of the output from SHOW ENGINE INNODB STATUS. An example of that output is:

---
LOG
---
Log sequence number 28219393219
Log flushed up to 28219393219
Last checkpoint at 28212583337
Max checkpoint age 7782360
Checkpoint age target 7539162
Modified age 6809882
Checkpoint age 6809882
0 pending log writes, 0 pending chkp writes
8570 log i/o's done, 2000.00 log i/o's/second

variable innodb_lsn_current

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_lsn_flushed

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_lsn_last_checkpoint

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_checkpoint_age

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_checkpoint_max_age

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_checkpoint_target_age

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

BUFFER POOL AND MEMORY

The following variables contain information in the BUFFER POOL AND MEMORY section of the output from SHOW ENGINE INNODB STATUS. An example of that output is:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137625600; in additional pool allocated 0
Total memory allocated by read views 88
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 3774352 (2213656 + 1560696)
    Page hash 139144
    Dictionary cache 629811 (554864 + 74947)
    File system 83536 (82672 + 864)
    Lock system 380792 (332872 + 47920)
    Recovery system 0 (0 + 0)
    Threads 84040 (82696 + 1344)
Dictionary memory allocated 74947
Buffer pool size 8192
Buffer pool size, bytes 134217728
Free buffers 0
Database pages 8095
Old database pages 2968
Modified db pages 5914
Pending reads 0
Pending writes: LRU 0, flush list 129, single page 0
Pages made young 372084, not young 0
2546000.00 youngs/s, 0.00 non-youngs/s
Pages read 103356, created 154787, written 979572
469000.00 reads/s, 78000.00 creates/s, 138000.00 writes/s
Buffer pool hit rate 994 / 1000, young-making rate 34 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 15000.00/s

variable innodb_mem_adaptive_hash

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_mem_dictionary

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_mem_total

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_buffer_pool_pages_LRU_flushed

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_buffer_pool_pages_made_not_young

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_buffer_pool_pages_made_young

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_buffer_pool_pages_old

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_descriptors_memory

Version Info:

• 5.5.31-30.3 – Introduced.

Variable Type:

Numeric

Scope:

Global

This status variable shows the current size of the descriptors array (in bytes). The descriptor array is an XtraDB data structure that contains the information on currently running transactions.

variable innodb_read_views_memory

Version Info:

• 5.5.31-30.3 – Introduced.

Variable Type:

Numeric

Scope:

Global

This status variable shows the total amount of memory allocated for the InnoDB read view (in bytes).

OLDEST VIEW

This part contains the information about the oldest active transaction in the system. An example of that output is:

---OLDEST VIEW---
Normal read view
Read view low limit trx n:o 3300
Read view up limit trx id 3300
Read view low limit trx id 3300
Read view individually stored trx ids:

• Read view low limit trx n:o and Read view up limit trx id are the highest transactions IDs at the time the view was created. This means that it should not see newer transactions with IDs bigger than or equal to that value.
• Read view low limit trx id is the latest committed transaction ID at the time the oldest view was created. This means that it should see all transactions with IDs smaller than or equal to that value.
• Read view individually stored trx ids contains the list of active transactions at the time the view was created.

TRANSACTIONS

The following variables contain information in the TRANSACTIONS section of the output from SHOW ENGINE INNODB STATUS. An example of that output is:

------------
TRANSACTIONS
------------
Trx id counter F561FD
Purge done for trx's n:o < F561EB undo n:o < 0
History list length 19
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started, process no 993, OS thread id 140213152634640
mysql thread id 15933, query id 32109 localhost root
show engine innodb status
---TRANSACTION F561FC, ACTIVE 29 sec, process no 993, OS thread id 140213152769808 updating or deleting
mysql tables in use 1, locked 1

variable innodb_history_list_length

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_max_trx_id

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_oldest_view_low_limit_trx_id

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_purge_trx_id

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_purge_undo_no

Version Info:

• 5.5.8-20.0 – Introduced.

Variable Type:

Numeric

Scope:

Global

variable innodb_current_row_locks

version 5.5.8-20.0:  Introduced. version 5.5.10-20.1:  Renamed. vartype:Numeric scope:Global

This variable was named innodb_row_lock_numbers in release 5.5.8-20.0.

Other reading

• SHOW INNODB STATUS walk through
• Table locks in SHOW INNODB STATUS