Lists the InnoDB Configuration configuration metrics and provides a brief description of each.
Table 5.9 InnoDB Configuration Configuration Metrics
| Name | Description |
|---|---|
| Adaptive Flushing Enabled (Total) | Whether an heuristic to determine when to flush dirty pages in the buffer pool should be used. |
| Adaptive Flushing Lwm (Total) | Low water mark representing percentage of redo log capacity at which adaptive flushing is enabled. |
| Adaptive Hash Index Enabled (Total) | Whether InnoDB adaptive hash indexes are enabled or disabled. |
| Adaptive Max Sleep Delay (Total) | Allows InnoDB to automatically adjust the value of innodb_thread_sleep_delay up or down according to the current workload. |
| Additional Mem Pool Size (Total) | The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures. |
| Autoextend Increment (Total) | The increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full. |
| Autoinc Lock Mode (Total) | The locking mode to use for generating auto-increment values. |
| Buffer Pool Dump At Shutdown Enabled (Total) | 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 (Total) | Immediately records the pages cached in the InnoDB buffer pool. |
| Buffer Pool Filename (Total) | 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 (Total) | 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 (Total) | 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 (Total) | 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 (Total) | 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 (Total) | The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables. |
| Change Buffering (Total) | 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 (Total) | 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 (Total) | Specifies how to generate and verify the checksum stored in each disk block of each InnoDB tablespace. Replaces the innodb_checksums option. |
| Checksums Enabled (Total) | 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 (Total) | The number of threads that can commit at the same time within InnoDB. |
| Compression Failure Threshold Pct (Total) | 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 (Total) | Specifies the level of zlib compression to use for InnoDB compressed tables and indexes. |
| Compression Pad Pct Max (Total) | 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 (Total) | Enables per-index compression-related statistics in the INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table. |
| Concurrency Tickets (Total) | The number of threads that can enter InnoDB concurrently. |
| Data File Path (Total) | The paths to individual InnoDB data files and their sizes. |
| Data Home Dir (Total) | The common part of the directory path for all InnoDB data files in the shared tablespace. |
| Disable Sort File Cache Enabled (Total) | Disables caching of sort files on Linux and Solaris when creating indexes. |
| Doublewrite Enabled (Total) | Whether to enabled doublewrite buffering (writing the data twice, to a buffer then the data file) within InnoDB. |
| Fast Shutdown (Total) | Speeds up the shutdown process of the InnoDB storage engine. |
| File Format (Total) | The file format to use for new InnoDB tables. |
| File Format Check Enabled (Total) | Whether InnoDB checks the file format tag in the shared tablespace on server start for version compatibility. |
| File Format Max (Total) | The file format tag InnoDB sets in in the shared tablespace (for example, Antelope or Barracuda). |
| File Per Table Enabled (Total) | Whether each InnoDB table should be stored in an .ibd file in the database dir instead of the central tablespace (ibdata*) files. |
| Flushing Avg Loops (Total) | 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 (Total) | 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 (Total) | Defines how InnoDB should flush it's transaction redo log bufferand files. |
| Flush Method (Total) | Defines how InnoDB flushes both data and redo log files to disk. |
| Flush Neighbors Enabled (Total) | Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent. |
| Force Load Corrupted Enabled (Total) | 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 (Total) | The crash recovery mode that InnoDB should be started with, should not be used in normal operations. |
| Foreign Key Checks Enabled (Total) | Whether foreign key constraint checking is enabled/disbaled globally. |
| Ignore Builtin InnoDB Enabled (Total) | Whether the server was started with the --ignore-builtin-innodb option. |
| Io Capacity (Total) | The maximum number of I/O operations per second that InnoDB will perform. |
| Io Capacity Max (Total) | 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 (Total) | 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 (Total) | Affects how InnoDB uses gap locking for searches and index scans. |
| Lock Wait Timeout (Total) | The timeout in seconds an InnoDB transaction may wait for a row lock before giving up. |
| Log Buffer Size (Total) | The size in bytes of the buffer that InnoDB uses to write to the log files on disk. |
| Log Compressed Pages Enabled (Total) | Whether to log the compressed version of pages to the redo logs or not. |
| Log Files In Group (Total) | The number of redo log files in the InnoDB redo log group. |
| Log File Size (Total) | The size in bytes of each InnoDB redo log file in a redo log group. |
| Log Group Home Dir (Total) | The directory path to the InnoDB redo log files. |
| Lru Scan Depth (Total) | 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 (Total) | 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 (Total) | Low water mark representing percentage of dirty pages where preflushing is enabled to control the dirty page ratio. |
| Max Purge Lag (Total) | How long InnoDB should delay INSERT, UPDATE, and DELETE operations when purge operations are lagging. |
| Max Purge Lag Delay (Total) | Specifies the maximum delay in milliseconds for the delay imposed by the innodb_max_purge_lag configuration option. |
| Mirrored Log Groups (Total) | The number of identical copies of log groups to keep for the database. |
| Monitor Disable (Total) | Turns off one or more counters in the information_schema.innodb_metrics table. |
| Monitor Enable (Total) | Turns on one or more counters in the information_schema.innodb_metrics table. |
| Monitor Reset (Total) | Resets to zero the count value for one or more counters in the information_schema.innodb_metrics table. |
| Monitor Reset All (Total) | Resets all values (minimum, maximum, and so on) for one or more counters in the information_schema.innodb_metrics table. |
| Old Blocks Pct (Total) | Specifies the approximate percentage of the InnoDB buffer pool used for the old block sublist. |
| Old Blocks Time (Total) | 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 (Total) | Specifies an upper limit on the size of the temporary log files used during online DDL operations for InnoDB tables. |
| Open Files (Total) | The maximum number of .ibd files that InnoDB can keep open at one time. |
| Page Size (Total) | 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 (Total) | When this option is enabled, information about all deadlocks in InnoDB user transactions is recorded in the mysqld error log. |
| Purge Batch Size (Total) | 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 Threads (Total) | The number of background threads devoted to the InnoDB purge operation. |
| Random Read Ahead Enabled (Total) | Enables the random read-ahead technique for optimizing InnoDB I/O. |
| Read Ahead Threshold (Total) | The sensitivity of linear read-ahead that InnoDB uses to prefetch pages into the buffer cache. |
| Read Io Threads (Total) | The number of I/O threads for read operations in InnoDB. |
| Read Only Enabled (Total) | 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. |
| Replication Delay (Total) | The replication thread delay (in ms) on a slave server if innodb_thread_concurrency is reached. |
| Rollback On Timeout Enabled (Total) | Whether InnoDB should rollback the entire transaction if a lock wait timeout is occurs, or just the last statement. |
| Sort Buffer Size (Total) | Specifies the sizes of several buffers used for sorting data during creation of an InnoDB index. |
| Spin Wait Delay (Total) | The maximum delay between polls for a spin lock. |
| Stats Auto Recalc Enabled (Total) | 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 (Total) | How the server treats NULL values when collecting statistics about the distribution of index values for InnoDB tables. |
| Stats On Metadata Enabled (Total) | Whether InnoDB should update statistics during metadata statements such as SHOW TABLE STATUS, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. |
| Stats Persistent (Total) | 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 (Total) | 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 (Total) | The number of InnoDB index pages to sample for index distribution statistics such as are calculated by ANALYZE TABLE. |
| Status File (Total) | Whether InnoDB should report the output of SHOW ENGINE INNODB STATUSto an innodb_status.<pid> file in the datadir. |
| Strict Mode Enabled (Total) | Whether InnoDB returns errors rather than warnings for exceptional conditions. |
| Support XA Enabled (Total) | Enables InnoDB support for two-phase commit in XA transactions. |
| Sync Array Size (Total) | Splits an internal data structure used to coordinate threads, for higher concurrency in workloads with large numbers of waiting threads. |
| Sync Spin Loops (Total) | The number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended. |
| Table Locks Enabled (Total) | Whether InnoDB should honor LOCK TABLES if autocommit = 0. |
| Thread Concurrency (Total) | The maximum number of operating system threads concurrently allowed inside InnoDB for transactions. |
| Thread Sleep Delay (Total) | How long InnoDB threads sleep before joining the InnoDB queue, in microseconds. |
| Timed Mutexes Enabled (Total) | Whether mutex waits within InnoDB are timed or not. |
| Undo Directory (Total) | The relative or absolute directory path where InnoDB creates separate tablespaces for the undo logs. |
| Undo Logs (Total) | Defines how many rollback segments in the system tablespace that InnoDB uses within a transaction. |
| Undo Tablespaces (Total) | 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 (Total) | Whether uniqueness checks for secondary indexes are performed within InnoDB. |
| Use Native Aio Enabled (Total) | Whether to use the (Linux only) OS asynchronous IO subsystem. |
| Use Sys Malloc Enabled (Total) | Whether InnoDB uses the operating system memory allocator or its own. |
| Write Io Threads (Total) | The number of I/O threads for write operations in InnoDB. |