7.14 InnoDB Configuration Configuration Metrics

Lists the InnoDB Configuration configuration metrics and provides a brief description of each.

Table 7.14 InnoDB Configuration Configuration Metrics

Name	Description
Adaptive Flushing Enabled	Whether an heuristic to determine when to flush dirty pages in the buffer pool is used.
Adaptive Flushing Lwm	Low water mark representing percentage of redo log capacity at which adaptive flushing is enabled.
Adaptive Hash Index Enabled	Whether InnoDB adaptive hash indexes are enabled or disabled.
Adaptive Max Sleep Delay	Allows InnoDB to automatically adjust the value of innodb_thread_sleep_delay up or down according to the current workload.
Additional Mem Pool Size	The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
Autoextend Increment	The increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full.
Autoinc Lock Mode	The locking mode to use for generating auto-increment values.
Buffer Pool Chunk Size	Defines the chunk size for online InnoDB buffer pool resizing operations.
Buffer Pool Dump At Shutdown Enabled	Whether to record the pages cached in the InnoDB buffer pool when the MySQL server is shut down, to shorten the warmup process at the next restart.
Buffer Pool Dump Now Enabled	Immediately records the pages cached in the InnoDB buffer pool.
Buffer Pool Dump Pct	Specifies the percentage of the most recently used pages for each buffer pool to read out and dump.
Buffer Pool Filename	Specifies the file that holds the list of page numbers produced by innodb_buffer_pool_dump_at_shutdown or innodb_buffer_pool_dump_now.
Buffer Pool Instances	The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages.
Buffer Pool Load At Startup Enabled	Specifies that, on MySQL server startup, the InnoDB buffer pool is automatically warmed up by loading the same pages it held at an earlier time.
Buffer Pool Load Now Enabled	Immediately warms up the InnoDB buffer pool by loading a set of data pages, without waiting for a server restart.
Buffer Pool Load On Abort Enabled	Interrupts the process of restoring InnoDB buffer pool contents triggered by innodb_buffer_pool_load_at_startup or innodb_buffer_pool_load_now.
Buffer Pool Size	The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables.
Change Buffering	Whether InnoDB performs change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially.
Change Buffer Max Size	Maximum size for the InnoDB change buffer, as a percentage of the total size of the buffer pool. You might increase this value for a MySQL server with heavy insert, update, and delete activity, or decrease it for a MySQL server with unchanging data used for reporting.
Checksum Algorithm	Specifies how to generate and verify the checksum stored in each disk block of each InnoDB tablespace. Replaces the innodb_checksums option.
Checksums Enabled	Whether InnoDB uses checksum validation on all pages read from the disk to ensure extra fault tolerance against broken hardware or data files.
Commit Concurrency	The number of threads that can commit at the same time within InnoDB.
Compression Failure Threshold Pct	Sets the cutoff point at which MySQL begins adding padding within compressed pages to avoid expensive compression failures. A value of zero disables the mechanism that monitors compression efficiency and dynamically adjusts the padding amount.
Compression Level	Specifies the level of zlib compression to use for InnoDB compressed tables and indexes.
Compression Pad Pct Max	Specifies the maximum percentage that can be reserved as free space within each compressed page, allowing room to reorganize the data and modification log within the page when a compressed table or index is updated and the data might be recompressed.
Compression Per Index Enabled	Enables per-index compression-related statistics in the INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table.
Concurrency Tickets	The number of threads that can enter InnoDB concurrently.
Data File Path	The paths to individual InnoDB data files and their sizes.
Data Home Dir	The common part of the directory path for all InnoDB data files in the shared tablespace.
Dedicated Server Enabled	Enables automatic configuration of buffer pool size, log file size, and flush method. Only consider enabling this option if your MySQL instance runs on a dedicated server.
Disable Sort File Cache Enabled	Disables caching of sort files on Linux and Solaris when creating indexes.
Doublewrite Enabled	Whether to enabled doublewrite buffering (writing the data twice, to a buffer then the data file) within InnoDB.
Fast Shutdown	Speeds up the shutdown process of the InnoDB storage engine.
File Format	The file format to use for new InnoDB tables.
File Format Check Enabled	Whether InnoDB checks the file format tag in the shared tablespace on server start for version compatibility.
File Format Max	The file format tag InnoDB sets in the shared tablespace (for example, Antelope or Barracuda).
File Per Table Enabled	Whether each InnoDB table is stored in an .ibd file in the database dir instead of the central tablespace (ibdata*) files.
Fill Factor	Defines the percentage of space on each B-tree page that is filled during a sorted index build, with the remaining space reserved for future index growth.
Flushing Avg Loops	Number of iterations for which InnoDB keeps the previously calculated snapshot of the flushing state, controlling how quickly adaptive flushing responds to changing workloads.
Flush Log At Timeout	Write and flush the logs every N seconds. This setting has an effect only when innodb_flush_log_at_trx_commit has a value of 2.
Flush Log At Trx Commit	Defines how InnoDB flushes it's transaction redo log buffer and files.
Flush Method	Defines how InnoDB flushes both data and redo log files to disk.
Flush Neighbors Enabled	Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent.
Force Load Corrupted Enabled	Lets InnoDB load tables at startup that are marked as corrupted. Use only during troubleshooting, to recover data that is otherwise inaccessible. When troubleshooting is complete, turn this setting back off and restart the server.
Force Recovery	The crash recovery mode that InnoDB started with. Force Recovery is not intended to be used in normal operations.
Foreign Key Checks Enabled	Whether foreign key constraint checking is enabled/disabled globally.
Ignore Built-in InnoDB Enabled	Whether the server was started with the --ignore-builtin-innodb option.
Io Capacity	The maximum number of I/O operations per second that InnoDB will perform.
Io Capacity Max	The limit up to which InnoDB is allowed to extend the innodb_io_capacity setting, per buffer pool instance, in case of emergency. Its default value is twice the default value of innodb_io_capacity, with a lower limit of 2000.
Large Prefix Enabled	Enable this option to allow index key prefixes longer than 767 bytes (up to 3072 bytes), for InnoDB tables that use the DYNAMIC and COMPRESSED row formats.
Locks Unsafe For Binlog Enabled	Affects how InnoDB uses gap locking for searches and index scans.
Lock Wait Timeout	The timeout in seconds an InnoDB transaction may wait for a row lock before giving up.
Log Buffer Size	The size in bytes of the buffer that InnoDB uses to write to the log files on disk.
Log Compressed Pages Enabled	Whether to log the compressed version of pages to the redo logs or not.
Log Files In Group	The number of redo log files in the InnoDB redo log group.
Log File Size	The size in bytes of each InnoDB redo log file in a redo log group.
Log Group Home Dir	The directory path to the InnoDB redo log files.
Log Write Ahead Size	The write-ahead block size for the redo log, in bytes.
Lru Scan Depth	Specifies, per buffer pool instance, how far down the buffer pool LRU list the page_cleaner thread scans looking for dirty pages to flush.
Max Dirty Pages Pct	InnoDB tries to write pages from the buffer pool so that the percentage of dirty (not yet written) pages will not exceed this value.
Max Dirty Pages Pct Lwm	Low water mark representing percentage of dirty pages where preflushing is enabled to control the dirty page ratio.
Max Purge Lag	How long InnoDB delays INSERT, UPDATE, and DELETE operations when purge operations are lagging.
Max Purge Lag Delay	Specifies the maximum delay in milliseconds for the delay imposed by the innodb_max_purge_lag configuration option.
Max Undo Log Size	Defines a threshold size for undo tablespaces.
Mirrored Log Groups	The number of identical copies of log groups to keep for the database.
Monitor Disable	Turns off one or more counters in the information_schema.innodb_metrics table.
Monitor Enable	Turns on one or more counters in the information_schema.innodb_metrics table.
Monitor Reset	Resets to zero the count value for one or more counters in the information_schema.innodb_metrics table.
Monitor Reset All	Resets all values (minimum, maximum, and so on) for one or more counters in the information_schema.innodb_metrics table.
Old Blocks Pct	Specifies the approximate percentage of the InnoDB buffer pool used for the old block sublist.
Old Blocks Time	Specifies how long in milliseconds a block inserted into the old sublist of the InnoDB buffer pool must stay there after its first access before it can be moved to the new sublist.
Online Alter Log Max Size	Specifies an upper limit on the size of the temporary log files used during online DDL operations for InnoDB tables.
Open Files	The maximum number of .ibd files that InnoDB can keep open at one time.
Page Cleaners	The number of page cleaner threads that flush dirty pages from buffer pool instances.
Page Size	Specifies the page size for all InnoDB tablespaces in a MySQL instance. This value is set when the instance is created and remains constant afterwards. You can specify page size using the values 16k (the default), 8k, or 4k.
Print All Deadlocks Enabled	When this option is enabled, information about all deadlocks in InnoDB user transactions is recorded in the mysqld error log.
Print DDL Logs Enabled	Enabling this option causes MySQL to write DDL logs to stderr.
Purge Batch Size	The granularity of changes, expressed in units of InnoDB redo log records, that trigger a purge operation, flushing the changed buffer pool blocks to disk.
Purge Rseg Truncate Frequency	Defines the frequency with which the purge system frees rollback segments.
Purge Threads	The number of background threads devoted to the InnoDB purge operation.
Random Read Ahead Enabled	Enables the random read-ahead technique for optimizing InnoDB I/O.
Read Ahead Threshold	The sensitivity of linear read-ahead that InnoDB uses to prefetch pages into the buffer cache.
Read Io Threads	The number of I/O threads for read operations in InnoDB.
Read Only Enabled	Starts the server in read-only mode. For distributing database applications or data sets on read-only media. Can also be used in data warehouses to share the same data directory between multiple instances.
Redo Log Encrypt Enabled	Controls encryption of redo log data for tables encrypted using InnoDB tablespace encryption. This feature is disabled by default.
Replication Delay	The replication thread delay (in ms) on a replica server if innodb_thread_concurrency is reached.
Rollback On Timeout Enabled	Whether InnoDB rolls back the entire transaction if a lock wait timeout is occurs, or just the last statement.
Scan Directories	Defines the directories to scan for tablespace files during InnoDB recovery.
Sort Buffer Size	Specifies the sizes of several buffers used for sorting data during creation of an InnoDB index.
Spin Wait Delay	The maximum delay between polls for a spin lock.
Stats Auto Recalc Enabled	Causes InnoDB to automatically recalculate persistent statistics after the data in a table is changed substantially. The threshold value is currently 10% of the rows in the table.
Stats Method	How the server treats NULL values when collecting statistics about the distribution of index values for InnoDB tables.
Stats On Metadata Enabled	Whether InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS.
Stats Persistent	Specifies whether the InnoDB index statistics produced by the ANALYZE TABLE command are stored on disk, remaining consistent until a subsequent ANALYZE TABLE. Otherwise, the statistics are recalculated more frequently, such as at each server restart, which can lead to variations in query execution plans.
Stats Persistent Sample Pages	The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by ANALYZE TABLE, for tables with persistent statistics.
Stats Sample Transient Pages	The number of InnoDB index pages to sample for index distribution statistics such as are calculated by ANALYZE TABLE.
Status File	Whether InnoDB reports the output of SHOW ENGINE INNODB STATUS to an innodb_status.<pid> file in the datadir.
Strict Mode Enabled	Whether InnoDB returns errors rather than warnings for exceptional conditions.
Support XA Enabled	Enables InnoDB support for two-phase commit in XA transactions.
Sync Array Size	Splits an internal data structure used to coordinate threads, for higher concurrency in workloads with large numbers of waiting threads.
Sync Spin Loops	The number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended.
Table Locks Enabled	Whether InnoDB honors LOCK TABLES if autocommit = 0.
Temp Data File Path	Specifies the path, file name, and file size for InnoDB temporary table tablespace data files.
Temp Dir	The InnoDB temporary file directory for online ALTER TABLE operations.
Thread Concurrency	The maximum number of operating system threads concurrently allowed inside InnoDB for transactions.
Thread Sleep Delay	How long InnoDB threads sleep before joining the InnoDB queue, in microseconds.
Timed Mutexes Enabled	Whether mutex waits within InnoDB are timed or not.
Undo Directory	The relative or absolute directory path where InnoDB creates separate tablespaces for the undo logs.
Undo Log Encrypt Enabled	Controls encryption of undo log data for tables encrypted using InnoDB tablespace encryption. This only applies to undo logs that reside in separate undo tablespaces, and is disabled by default.
Undo Logs	Defines how many rollback segments in the system tablespace that InnoDB uses within a transaction.
Undo Log Truncate Enabled	When you enable innodb_undo_log_truncate, undo tablespaces that exceed the threshold value defined by innodb_max_undo_log_size are marked for truncation.
Undo Tablespaces	The number of tablespace files that the undo logs are divided between, when you use a non-zero innodb_undo_logs setting.
Unique Checks Enabled	Whether uniqueness checks for secondary indexes are performed within InnoDB.
Use Native Aio Enabled	Whether to use the (Linux only) OS asynchronous IO subsystem.
Use Sys Malloc Enabled	Whether InnoDB uses the operating system memory allocator or its own.
Write Io Threads	The number of I/O threads for write operations in InnoDB.