Documentation Home
Oracle Enterprise Manager for MySQL Database User's Guide
Download this Manual
PDF (US Ltr) - 0.7Mb
PDF (A4) - 0.7Mb
HTML Download (TGZ) - 223.8Kb
HTML Download (Zip) - 231.3Kb

Oracle Enterprise Manager for MySQL Database User's Guide  /  Configuration Metrics  /  InnoDB Configuration Configuration Metrics

7.13 InnoDB Configuration Configuration Metrics

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

Table 7.13 InnoDB Configuration Configuration Metrics

Adaptive Flushing EnabledWhether an heuristic to determine when to flush dirty pages in the buffer pool is used.
Adaptive Flushing LwmLow water mark representing percentage of redo log capacity at which adaptive flushing is enabled.
Adaptive Hash Index EnabledWhether InnoDB adaptive hash indexes are enabled or disabled.
Adaptive Max Sleep DelayAllows InnoDB to automatically adjust the value of innodb_thread_sleep_delay up or down according to the current workload.
Additional Mem Pool SizeThe size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
Autoextend IncrementThe increment size (in MB) for extending the size of an auto-extending shared tablespace file when it becomes full.
Autoinc Lock ModeThe locking mode to use for generating auto-increment values.
Buffer Pool Chunk SizeDefines the chunk size for online InnoDB buffer pool resizing operations.
Buffer Pool Dump At Shutdown EnabledWhether 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 EnabledImmediately records the pages cached in the InnoDB buffer pool.
Buffer Pool Dump PctSpecifies the percentage of the most recently used pages for each buffer pool to read out and dump.
Buffer Pool FilenameSpecifies 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 InstancesThe 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 EnabledSpecifies 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 EnabledImmediately warms up the InnoDB buffer pool by loading a set of data pages, without waiting for a server restart.
Buffer Pool Load On Abort EnabledInterrupts the process of restoring InnoDB buffer pool contents triggered by innodb_buffer_pool_load_at_startup or innodb_buffer_pool_load_now.
Buffer Pool SizeThe size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables.
Change BufferingWhether 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 SizeMaximum 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 AlgorithmSpecifies how to generate and verify the checksum stored in each disk block of each InnoDB tablespace. Replaces the innodb_checksums option.
Checksums EnabledWhether InnoDB uses checksum validation on all pages read from the disk to ensure extra fault tolerance against broken hardware or data files.
Commit ConcurrencyThe number of threads that can commit at the same time within InnoDB.
Compression Failure Threshold PctSets 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 LevelSpecifies the level of zlib compression to use for InnoDB compressed tables and indexes.
Compression Pad Pct MaxSpecifies 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 EnabledEnables per-index compression-related statistics in the INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table.
Concurrency TicketsThe number of threads that can enter InnoDB concurrently.
Data File PathThe paths to individual InnoDB data files and their sizes.
Data Home DirThe common part of the directory path for all InnoDB data files in the shared tablespace.
Dedicated Server EnabledEnables 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 EnabledDisables caching of sort files on Linux and Solaris when creating indexes.
Doublewrite EnabledWhether to enabled doublewrite buffering (writing the data twice, to a buffer then the data file) within InnoDB.
Fast ShutdownSpeeds up the shutdown process of the InnoDB storage engine.
File FormatThe file format to use for new InnoDB tables.
File Format Check EnabledWhether InnoDB checks the file format tag in the shared tablespace on server start for version compatibility.
File Format MaxThe file format tag InnoDB sets in the shared tablespace (for example, Antelope or Barracuda).
File Per Table EnabledWhether each InnoDB table is stored in an .ibd file in the database dir instead of the central tablespace (ibdata*) files.
Fill FactorDefines 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 LoopsNumber 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 TimeoutWrite 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 CommitDefines how InnoDB flushes it's transaction redo log buffer and files.
Flush MethodDefines how InnoDB flushes both data and redo log files to disk.
Flush Neighbors EnabledSpecifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent.
Force Load Corrupted EnabledLets 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 RecoveryThe crash recovery mode that InnoDB started with. Force Recovery is not intended to be used in normal operations.
Foreign Key Checks EnabledWhether foreign key constraint checking is enabled/disabled globally.
Ignore Built-in InnoDB EnabledWhether the server was started with the --ignore-builtin-innodb option.
Io CapacityThe maximum number of I/O operations per second that InnoDB will perform.
Io Capacity MaxThe 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 EnabledEnable 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 EnabledAffects how InnoDB uses gap locking for searches and index scans.
Lock Wait TimeoutThe timeout in seconds an InnoDB transaction may wait for a row lock before giving up.
Log Buffer SizeThe size in bytes of the buffer that InnoDB uses to write to the log files on disk.
Log Compressed Pages EnabledWhether to log the compressed version of pages to the redo logs or not.
Log Files In GroupThe number of redo log files in the InnoDB redo log group.
Log File SizeThe size in bytes of each InnoDB redo log file in a redo log group.
Log Group Home DirThe directory path to the InnoDB redo log files.
Log Write Ahead SizeThe write-ahead block size for the redo log, in bytes.
Lru Scan DepthSpecifies, 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 PctInnoDB 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 LwmLow water mark representing percentage of dirty pages where preflushing is enabled to control the dirty page ratio.
Max Purge LagHow long InnoDB delays INSERT, UPDATE, and DELETE operations when purge operations are lagging.
Max Purge Lag DelaySpecifies the maximum delay in milliseconds for the delay imposed by the innodb_max_purge_lag configuration option.
Max Undo Log SizeDefines a threshold size for undo tablespaces.
Mirrored Log GroupsThe number of identical copies of log groups to keep for the database.
Monitor DisableTurns off one or more counters in the information_schema.innodb_metrics table.
Monitor EnableTurns on one or more counters in the information_schema.innodb_metrics table.
Monitor ResetResets to zero the count value for one or more counters in the information_schema.innodb_metrics table.
Monitor Reset AllResets all values (minimum, maximum, and so on) for one or more counters in the information_schema.innodb_metrics table.
Old Blocks PctSpecifies the approximate percentage of the InnoDB buffer pool used for the old block sublist.
Old Blocks TimeSpecifies 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 SizeSpecifies an upper limit on the size of the temporary log files used during online DDL operations for InnoDB tables.
Open FilesThe maximum number of .ibd files that InnoDB can keep open at one time.
Page CleanersThe number of page cleaner threads that flush dirty pages from buffer pool instances.
Page SizeSpecifies 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 EnabledWhen this option is enabled, information about all deadlocks in InnoDB user transactions is recorded in the mysqld error log.
Print DDL Logs EnabledEnabling this option causes MySQL to write DDL logs to stderr.
Purge Batch SizeThe 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 FrequencyDefines the frequency with which the purge system frees rollback segments.
Purge ThreadsThe number of background threads devoted to the InnoDB purge operation.
Random Read Ahead EnabledEnables the random read-ahead technique for optimizing InnoDB I/O.
Read Ahead ThresholdThe sensitivity of linear read-ahead that InnoDB uses to prefetch pages into the buffer cache.
Read Io ThreadsThe number of I/O threads for read operations in InnoDB.
Read Only EnabledStarts 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 EnabledControls encryption of redo log data for tables encrypted using InnoDB tablespace encryption. This feature is disabled by default.
Replication DelayThe replication thread delay (in ms) on a slave server if innodb_thread_concurrency is reached.
Rollback On Timeout EnabledWhether InnoDB rolls back the entire transaction if a lock wait timeout is occurs, or just the last statement.
Scan DirectoriesDefines the directories to scan for tablespace files during InnoDB recovery.
Sort Buffer SizeSpecifies the sizes of several buffers used for sorting data during creation of an InnoDB index.
Spin Wait DelayThe maximum delay between polls for a spin lock.
Stats Auto Recalc EnabledCauses 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 MethodHow the server treats NULL values when collecting statistics about the distribution of index values for InnoDB tables.
Stats On Metadata EnabledWhether InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS.
Stats PersistentSpecifies 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 PagesThe 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 PagesThe number of InnoDB index pages to sample for index distribution statistics such as are calculated by ANALYZE TABLE.
Status FileWhether InnoDB reports the output of SHOW ENGINE INNODB STATUS to an innodb_status.<pid> file in the datadir.
Strict Mode EnabledWhether InnoDB returns errors rather than warnings for exceptional conditions.
Support XA EnabledEnables InnoDB support for two-phase commit in XA transactions.
Sync Array SizeSplits an internal data structure used to coordinate threads, for higher concurrency in workloads with large numbers of waiting threads.
Sync Spin LoopsThe number of times a thread waits for an InnoDB mutex to be freed before the thread is suspended.
Table Locks EnabledWhether InnoDB honors LOCK TABLES if autocommit = 0.
Temp Data File PathSpecifies the path, file name, and file size for InnoDB temporary table tablespace data files.
Temp DirThe InnoDB temporary file directory for online ALTER TABLE operations.
Thread ConcurrencyThe maximum number of operating system threads concurrently allowed inside InnoDB for transactions.
Thread Sleep DelayHow long InnoDB threads sleep before joining the InnoDB queue, in microseconds.
Timed Mutexes EnabledWhether mutex waits within InnoDB are timed or not.
Undo DirectoryThe relative or absolute directory path where InnoDB creates separate tablespaces for the undo logs.
Undo Log Encrypt EnabledControls 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 LogsDefines how many rollback segments in the system tablespace that InnoDB uses within a transaction.
Undo Log Truncate EnabledWhen 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 TablespacesThe number of tablespace files that the undo logs are divided between, when you use a non-zero innodb_undo_logs setting.
Unique Checks EnabledWhether uniqueness checks for secondary indexes are performed within InnoDB.
Use Native Aio EnabledWhether to use the (Linux only) OS asynchronous IO subsystem.
Use Sys Malloc EnabledWhether InnoDB uses the operating system memory allocator or its own.
Write Io ThreadsThe number of I/O threads for write operations in InnoDB.