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 upstream bug #29126):

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.

Extended Show Engine InnoDB Status¶

Version Specific Information¶

Other Information¶

System Variables¶

Status Variables¶

BACKGROUND THREAD¶

SEMAPHORES¶

INSERT BUFFER AND ADAPTIVE HASH INDEX¶

LOG¶

BUFFER POOL AND MEMORY¶

OLDEST VIEW¶

TRANSACTIONS¶

Other reading¶

Table Of Contents

This Page

Percona Server Series

General Inquiries

PERCONA

SERVICES

SOFTWARE

RESOURCES

CONTACT US

COMMUNITY

CONFERENCES

ABOUT

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