InnoDB Server Status Variables

See Server Status Variables for a complete list of status variables that can be viewed with SHOW STATUS.

Much of the InnoDB/XtraDB information here can also be seen with a SHOW ENGINE INNODB STATUS statement.

See also the Full list of MariaDB options, system and status variables.

Innodb_adaptive_hash_cells


Innodb_adaptive_hash_hash_searches


Innodb_adaptive_hash_heap_buffers


Innodb_adaptive_hash_non_hash_searches


Innodb_available_undo_logs

  • Description: Total number available InnoDB undo logs. Differs from the innodb_undo_logs system variable, which specifies the number of active undo logs.
  • Scope: Global
  • Data Type: numeric

Innodb_background_log_sync


Innodb_buffer_pool_bytes_data

  • Description: Number of bytes contained in the InnoDB buffer pool, both dirty (modified) and clean (unmodified). See also Innodb_buffer_pool_pages_data, which can contain pages of different sizes in the case of compression.
  • Scope: Global
  • Data Type: numeric

Innodb_buffer_pool_bytes_dirty


Innodb_buffer_pool_dump_status

  • Description: A text description of the progress or final status of the last Innodb buffer pool dump.
  • Scope: Global
  • Data Type: string
  • Introduced: MariaDB 10.0.0

Innodb_buffer_pool_load_incomplete

  • Description: Whether or not the loaded buffer pool is incomplete, for example after a shutdown or abort during innodb buffer pool load from file caused an incomplete save.
  • Scope: Global
  • Data Type: boolean
  • Introduced: MariaDB 10.3.5

Innodb_buffer_pool_load_status

  • Description: A text description of the progress or final status of the last Innodb buffer pool load.
  • Scope: Global
  • Data Type: string
  • Introduced: MariaDB 10.0.0

Innodb_buffer_pool_pages_data


Innodb_buffer_pool_pages_dirty


Innodb_buffer_pool_pages_flushed

  • Description: Number of InnoDB buffer pool pages which have been flushed.
  • Scope: Global
  • Data Type: numeric

Innodb_buffer_pool_pages_LRU_flushed


Innodb_buffer_pool_pages_LRU_freed

  • Description: Monitor the number of pages that were freed by a buffer pool LRU eviction scan, without flushing.
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.6.0

Innodb_buffer_pool_pages_free


Innodb_buffer_pool_pages_made_not_young


Innodb_buffer_pool_pages_made_young


Innodb_buffer_pool_pages_misc

  • Description: Number of InnoDB buffer pool pages set aside for internal use.
  • Scope: Global
  • Data Type: numeric

Innodb_buffer_pool_pages_old


Innodb_buffer_pool_pages_total


Innodb_buffer_pool_read_ahead

  • Description: Number of pages read into the InnoDB buffer pool by the read-ahead background thread.
  • Scope: Global
  • Data Type: numeric

Innodb_buffer_pool_read_ahead_evicted

  • Description: Number of pages read into the InnoDB buffer pool by the read-ahead background thread that were evicted without having been accessed by queries.
  • Scope: Global
  • Data Type: numeric

Innodb_buffer_pool_read_ahead_rnd

  • Description: Number of random read-aheads.
  • Scope: Global
  • Data Type: numeric

Innodb_buffer_pool_read_requests

  • Description: Number of requests to read from the InnoDB buffer pool.
  • Scope: Global
  • Data Type: numeric

Innodb_buffer_pool_reads

  • Description: Number of reads that could not be satisfied by the InnoDB buffer pool and had to be read from disk.
  • Scope: Global
  • Data Type: numeric

Innodb_buffer_pool_resize_status


Innodb_buffer_pool_wait_free

  • Description: Number of times InnoDB waited for a free page before reading or creating a page. Normally, writes to the InnoDB buffer pool happen in the background. When no clean pages are available, dirty pages are flushed first in order to free some up. This counts the numbers of wait for this operation to finish. If this value is not small, look at increasing innodb_buffer_pool_size.
  • Scope: Global
  • Data Type: numeric

Innodb_buffer_pool_write_requests

  • Description: Number of requests to write to the InnoDB buffer pool.
  • Scope: Global
  • Data Type: numeric

Innodb_buffered_aio_submitted

  • Description:
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.5.0

Innodb_checkpoint_age


Innodb_checkpoint_max_age


Innodb_checkpoint_target_age


Innodb_current_row_locks


Innodb_data_fsyncs

  • Description: Number of InnoDB fsync (sync-to-disk) calls. fsync call frequency can be influenced by the innodb_flush_method configuration option.
  • Scope: Global
  • Data Type: numeric

Innodb_data_pending_fsyncs

  • Description: Number of pending InnoDB fsync (sync-to-disk) calls. fsync call frequency can be influenced by the innodb_flush_method configuration option.
  • Scope: Global
  • Data Type: numeric

Innodb_data_pending_reads

  • Description: Number of pending InnoDB reads.
  • Scope: Global
  • Data Type: numeric

Innodb_data_pending_writes

  • Description: Number of pending InnoDB writes.
  • Scope: Global
  • Data Type: numeric

Innodb_data_read

  • Description: Number of InnoDB bytes read since server startup (not to be confused with Innodb_data_reads).
  • Scope: Global
  • Data Type: numeric

Innodb_data_reads

  • Description: Number of InnoDB read operations (not to be confused with Innodb_data_read).
  • Scope: Global
  • Data Type: numeric

Innodb_data_writes

  • Description: Number of InnoDB write operations.
  • Scope: Global
  • Data Type: numeric

Innodb_data_written

  • Description: Number of InnoDB bytes written since server startup.
  • Scope: Global
  • Data Type: numeric

Innodb_dblwr_pages_written


Innodb_dblwr_writes


Innodb_deadlocks

  • Description: Total number of InnoDB deadlocks. Deadlocks occur when at least two transactions are waiting for the other to finish, creating a circular dependency. InnoDB usually detects these quickly, returning an error.
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 5.5 (XtraDB-only), MariaDB 10.5.0

Innodb_defragment_compression_failures


Innodb_defragment_count


Innodb_defragment_failures


Innodb_dict_tables

  • Description: Number of entries in the XtraDB data dictionary cache. This Percona XtraDB variable was removed in MariaDB 10/XtraDB 5.6 as it was replaced with MySQL 5.6's table_definition_cache implementation.
  • Scope: Global
  • Data Type: numeric
  • Introduced: XtraDB 5.0.77-b13
  • Removed: MariaDB 10.0

Innodb_encryption_n_merge_blocks_decrypted


Innodb_encryption_n_merge_blocks_encrypted


Innodb_encryption_n_rowlog_blocks_decrypted


Innodb_encryption_n_rowlog_blocks_encrypted


Innodb_encryption_n_temp_blocks_decrypted


Innodb_encryption_n_temp_blocks_encrypted


Innodb_encryption_num_key_requests


Innodb_encryption_rotation_estimated_iops


Innodb_encryption_rotation_pages_flushed


Innodb_encryption_rotation_pages_modified


Innodb_encryption_rotation_pages_read_from_cache


Innodb_encryption_rotation_pages_read_from_disk


Innodb_have_atomic_builtins

  • Description: Whether the server has been built with atomic instructions, provided by the CPU ensuring that critical low-level operations can't be interrupted. XtraDB only.
  • Scope: Global
  • Data Type: boolean

Innodb_have_bzip2


Innodb_have_lz4


Innodb_have_lzma


Innodb_have_lzo


Innodb_have_punch_hole

  • Description:
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.2.4

Innodb_have_snappy


Innodb_history_list_length


Innodb_ibuf_discarded_delete_marks


Innodb_ibuf_discarded_deletes


Innodb_ibuf_discarded_inserts


Innodb_ibuf_free_list


Innodb_ibuf_merged_delete_marks


Innodb_ibuf_merged_deletes


Innodb_ibuf_merged_inserts


Innodb_ibuf_merges


Innodb_ibuf_segment_size


Innodb_ibuf_size


Innodb_instant_alter_column


Innodb_log_waits

  • Description: Number of times InnoDB was forced to wait for log writes to be flushed due to the log buffer being too small.
  • Scope: Global
  • Data Type: numeric

Innodb_log_write_requests

  • Description: Number of requests to write to the InnoDB redo log.
  • Scope: Global
  • Data Type: numeric

Innodb_log_writes

  • Description: Number of writes to the InnoDB redo log.
  • Scope: Global
  • Data Type: numeric

Innodb_lsn_current


Innodb_lsn_flushed


Innodb_lsn_last_checkpoint


Innodb_master_thread_1_second_loops


Innodb_master_thread_10_second_loops


Innodb_master_thread_active_loops


Innodb_master_thread_background_loops


Innodb_master_thread_idle_loops


Innodb_master_thread_main_flush_loops


Innodb_master_thread_sleeps


Innodb_max_trx_id


Innodb_mem_adaptive_hash


Innodb_mem_dictionary


Innodb_mem_total


Innodb_mutex_os_waits


Innodb_mutex_spin_rounds


Innodb_mutex_spin_waits


Innodb_num_index_pages_written


Innodb_num_non_index_pages_written


Innodb_num_open_files

  • Description: Number of open files held by InnoDB. InnoDB only.
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.0.0

Innodb_num_page_compressed_trim_op

  • Description: Number of trim operations performed.
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.1.0

Innodb_num_page_compressed_trim_op_saved

  • Description: Number of trim operations not done because of an earlier trim.
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.1.0

Innodb_num_pages_decrypted


Innodb_num_pages_encrypted


Innodb_num_pages_page_compressed

  • Description: Number of pages that are page compressed.
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.1.0

Innodb_num_pages_page_compression_error

  • Description: Number of compression errors.
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.1

Innodb_num_pages_page_decompressed

  • Description: Number of pages compressed with page compression that are decompressed.
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.1.0

Innodb_num_pages_page_encryption_error


Innodb_oldest_view_low_limit_trx_id


Innodb_onlineddl_pct_progress


Innodb_onlineddl_rowlog_pct_used


Innodb_onlineddl_rowlog_rows


Innodb_os_log_fsyncs

  • Description: Number of InnoDB log fsync (sync-to-disk) requests.
  • Scope: Global
  • Data Type: numeric

Innodb_os_log_pending_fsyncs

  • Description: Number of pending InnoDB log fsync (sync-to-disk) requests.
  • Scope: Global
  • Data Type: numeric

Innodb_os_log_pending_writes

  • Description: Number of pending InnoDB log writes.
  • Scope: Global
  • Data Type: numeric

Innodb_os_log_written

  • Description: Number of bytes written to the InnoDB log.
  • Scope: Global
  • Data Type: numeric

Innodb_page_compression_saved


Innodb_page_compression_trim_sect512

  • Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 512 byte block-size.
  • Scope:
  • Data Type: numeric
  • Introduced: MariaDB 10.1.0, MariaDB 10.0.15 Fusion-io
  • Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect1024

  • Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 1K block-size.
  • Scope:
  • Data Type: numeric
  • Introduced: MariaDB 10.1.2, MariaDB 10.0.15 Fusion-io
  • Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect2048

  • Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 2K block-size.
  • Scope:
  • Data Type: numeric
  • Introduced: MariaDB 10.1.2, MariaDB 10.0.15 Fusion-io
  • Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect4096

  • Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 4K block-size.
  • Scope:
  • Data Type: numeric
  • Introduced: MariaDB 10.1.0, MariaDB 10.0.15 Fusion-io
  • Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect8192

  • Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 8K block-size.
  • Scope:
  • Data Type: numeric
  • Introduced: MariaDB 10.1.2, MariaDB 10.0.15 Fusion-io
  • Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect16384

  • Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 16K block-size.
  • Scope:
  • Data Type: numeric
  • Introduced: MariaDB 10.1.2, MariaDB 10.0.15 Fusion-io
  • Removed: MariaDB 10.2.4

Innodb_page_compression_trim_sect32768

  • Description: Number of TRIM operations performed for the page-compression/NVM Compression workload for the 32K block-size.
  • Scope:
  • Data Type: numeric
  • Introduced: MariaDB 10.1.2, MariaDB 10.0.15 Fusion-io
  • Removed: MariaDB 10.2.4

Innodb_page_size

  • Description: Page size used by InnoDB. Defaults to 16KB, can be compiled with a different value.
  • Scope: Global
  • Data Type: numeric

Innodb_pages_created

  • Description: Number of InnoDB pages created.
  • Scope: Global
  • Data Type: numeric

Innodb_pages_read

  • Description: Number of InnoDB pages read.
  • Scope: Global
  • Data Type: numeric

Innodb_pages0_read

  • Description: Counter for keeping track of reads of the first page of InnoDB data files, because the original implementation of data-at-rest-encryption for InnoDB introduced new code paths for reading the pages. Removed in MariaDB 10.4.0 as the extra reads of the first page were removed, and the encryption subsystem will be initialized whenever we first read the first page of each data file, in fil_node_open_file().
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.2.4, MariaDB 10.1.21
  • Removed: MariaDB 10.4.0

Innodb_pages_written

  • Description: Number of InnoDB pages written.
  • Scope: Global
  • Data Type: numeric

Innodb_purge_trx_id


Innodb_purge_undo_no


Innodb_read_views_memory

  • Description: As shown in the BUFFER POOL AND MEMORY section of the SHOW ENGINE INNODB STATUS output. Shows the total of memory in bytes allocated for the InnoDB read view.
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 5.5.32

Innodb_row_lock_current_waits

  • Description: Number of pending row lock waits on InnoDB tables.
  • Scope: Global
  • Data Type: numeric

Innodb_row_lock_numbers


Innodb_row_lock_time

  • Description: Total time in milliseconds spent getting InnoDB row locks.
  • Scope: Global
  • Data Type: numeric

Innodb_row_lock_time_avg

  • Description: Average time in milliseconds spent getting an InnoDB row lock.
  • Scope: Global
  • Data Type: numeric

Innodb_row_lock_time_max

  • Description: Maximum time in milliseconds spent getting an InnoDB row lock.
  • Scope: Global
  • Data Type: numeric

Innodb_row_lock_waits

  • Description: Number of times InnoDB had to wait before getting a row lock.
  • Scope: Global
  • Data Type: numeric

Innodb_rows_deleted

  • Description: Number of rows deleted from InnoDB tables.
  • Scope: Global
  • Data Type: numeric

Innodb_rows_inserted

  • Description: Number of rows inserted into InnoDB tables.
  • Scope: Global
  • Data Type: numeric

Innodb_rows_read

  • Description: Number of rows read from InnoDB tables.
  • Scope: Global
  • Data Type: numeric

Innodb_rows_updated

  • Description: Number of rows updated in InnoDB tables.
  • Scope: Global
  • Data Type: numeric

Innodb_s_lock_os_waits


Innodb_s_lock_spin_rounds


Innodb_s_lock_spin_waits


Innodb_scrub_background_page_reorganizations


Innodb_scrub_background_page_split_failures_missing_index


Innodb_scrub_background_page_split_failures_out_of_filespace


Innodb_scrub_background_page_split_failures_underflow


Innodb_scrub_background_page_split_failures_unknown


Innodb_scrub_background_page_splits


Innodb_scrub_log


Innodb_secondary_index_triggered_cluster_reads

  • Description: Used to track the effectiveness of the Prefix Index Queries Optimization (MDEV-6929)
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.1.2

Innodb_secondary_index_triggered_cluster_reads_avoided

  • Description: Used to track the effectiveness of the Prefix Index Queries Optimization (MDEV-6929)
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.1.2

Innodb_system_rows_deleted


Innodb_system_rows_inserted


Innodb_system_rows_read


Innodb_system_rows_updated


Innodb_truncated_status_writes


Innodb_undo_truncations

  • Description: Number of undo tablespace truncation operations.
  • Scope: Global
  • Data Type: numeric
  • Introduced: MariaDB 10.3.10

Innodb_x_lock_os_waits


Innodb_x_lock_spin_rounds


Innodb_x_lock_spin_waits


Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.

© 2021 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/innodb-status-variables/