MySQL 8.4 Release Notes
System variables that are true or false can be enabled at server startup by naming them, or disabled by using a
--skip-prefix. For example, to enable or disable theInnoDBadaptive hash index, you can use--innodb-adaptive-hash-indexor--skip-innodb-adaptive-hash-indexon the command line, orinnodb_adaptive_hash_indexorskip_innodb_adaptive_hash_indexin an option file.Some variable descriptions refer to “enabling” or “disabling” a variable. These variables can be enabled with the
SETstatement by setting them toONor1, or disabled by setting them toOFFor0. Boolean variables can be set at startup to the valuesON,TRUE,OFF, andFALSE(not case-sensitive), as well as1and0. See Section 6.2.2.4, “Program Option Modifiers”.System variables that take a numeric value can be specified as
--on the command line or asvar_name=valuevar_name=valueMany system variables can be changed at runtime (see Section 7.1.9.2, “Dynamic System Variables”).
For information about
GLOBALandSESSIONvariable scope modifiers, refer to theSETstatement documentation.Certain options control the locations and layout of the
InnoDBdata files. Section 17.8.1, “InnoDB Startup Configuration” explains how to use these options.Some options, which you might not use initially, help tune
InnoDBperformance characteristics based on machine capacity and database workload.For more information on specifying options and system variables, see Section 6.2.2, “Specifying Program Options”.
Table 17.23 InnoDB Option and Variable Reference
InnoDB Startup Options
-
Command-Line Format --innodb-dedicated-server[={OFF|ON}]System Variable innodb_dedicated_serverScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value OFFWhen this option is set by starting the server with
--innodb-dedicated-serveror--innodb-dedicated-server=ON, either on the command line or in amy.cnffile,InnoDBautomatically calculates and sets the values of the following variables:NoteIn older versions of MySQL 8.0,
innodb_log_file_sizeandinnodb_log_files_in_groupwere also set by--innodb-dedicated-server.innodb_log_file_sizeandinnodb_log_files_in_grouphave since been deprecated, and superseded byinnodb_redo_log_capacity. See Section 17.6.5, “Redo Log”.In MySQL 8.0,
innodb_flush_methodwas also set automatically by this option, but in MySQL 8.4, this is no longer the case.You should consider using
--innodb-dedicated-serveronly if the MySQL instance resides on a dedicated server where it can use all available system resources. Using this option is not recommended if the MySQL instance shares system resources with other applications.It is strongly recommended that you read Section 17.8.12, “Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server”, before using this option in production.
-
Command-Line Format --innodb-status-file[={OFF|ON}]Type Boolean Default Value OFFThe
--innodb-status-filestartup option controls whetherInnoDBcreates a file namedinnodb_status.in the data directory and writespidSHOW ENGINE INNODB STATUSoutput to it every 15 seconds, approximately.The
innodb_status.file is not created by default. To create it, start mysqld with thepid--innodb-status-fileoption.InnoDBremoves the file when the server is shut down normally. If an abnormal shutdown occurs, the status file may have to be removed manually.The
--innodb-status-fileoption is intended for temporary use, asSHOW ENGINE INNODB STATUSoutput generation can affect performance, and theinnodb_status.file can become quite large over time.pidFor related information, see Section 17.17.2, “Enabling InnoDB Monitors”.
InnoDB System Variables
-
Command-Line Format --innodb-adaptive-flushing[={OFF|ON}]System Variable innodb_adaptive_flushingScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONSpecifies whether to dynamically adjust the rate of flushing dirty pages in the
InnoDBbuffer pool based on the workload. Adjusting the flush rate dynamically is intended to avoid bursts of I/O activity. This setting is enabled by default. See Section 17.8.3.5, “Configuring Buffer Pool Flushing” for more information. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”. -
Command-Line Format --innodb-adaptive-flushing-lwm=#System Variable innodb_adaptive_flushing_lwmScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 10Minimum Value 0Maximum Value 70Defines the low water mark representing percentage of redo log capacity at which adaptive flushing is enabled. For more information, see Section 17.8.3.5, “Configuring Buffer Pool Flushing”.
-
Command-Line Format --innodb-adaptive-hash-index[={OFF|ON}]System Variable innodb_adaptive_hash_indexScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFWhether the
InnoDBadaptive hash index is enabled or disabled. It may be desirable, depending on your workload, to dynamically enable or disable adaptive hash indexing to improve query performance. Because the adaptive hash index may not be useful for all workloads, conduct benchmarks with it both enabled and disabled, using realistic workloads. See Section 17.5.3, “Adaptive Hash Index” for details.This variable is disabled by default. You can modify this parameter using the
SET GLOBALstatement, without restarting the server. Changing the setting at runtime requires privileges sufficient to set global system variables. See Section 7.1.9.1, “System Variable Privileges”. You can also use--innodb-adaptive-hash-indexat server startup to enable it.Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly instead. When the adaptive hash index is re-enabled, the hash table is populated again during normal operation.
Before MySQL 8.4, this option was enabled by default.
innodb_adaptive_hash_index_partsCommand-Line Format --innodb-adaptive-hash-index-parts=#System Variable innodb_adaptive_hash_index_partsScope Global Dynamic No SET_VARHint AppliesNo Type Numeric Default Value 8Minimum Value 1Maximum Value 512Partitions the adaptive hash index search system. Each index is bound to a specific partition, with each partition protected by a separate latch.
The adaptive hash index search system is partitioned into 8 parts by default. The maximum setting is 512.
For related information, see Section 17.5.3, “Adaptive Hash Index”.
innodb_adaptive_max_sleep_delayCommand-Line Format --innodb-adaptive-max-sleep-delay=#System Variable innodb_adaptive_max_sleep_delayScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 150000Minimum Value 0Maximum Value 1000000Unit microseconds Permits
InnoDBto automatically adjust the value ofinnodb_thread_sleep_delayup or down according to the current workload. Any nonzero value enables automated, dynamic adjustment of theinnodb_thread_sleep_delayvalue, up to the maximum value specified in theinnodb_adaptive_max_sleep_delayoption. The value represents the number of microseconds. This option can be useful in busy systems, with greater than 16InnoDBthreads. (In practice, it is most valuable for MySQL systems with hundreds or thousands of simultaneous connections.)For more information, see Section 17.8.4, “Configuring Thread Concurrency for InnoDB”.
-
Command-Line Format --innodb-autoextend-increment=#System Variable innodb_autoextend_incrementScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 64Minimum Value 1Maximum Value 1000Unit megabytes The increment size (in megabytes) for extending the size of an auto-extending
InnoDBsystem tablespace file when it becomes full. The default value is 64. For related information, see System Tablespace Data File Configuration, and Resizing the System Tablespace.The
innodb_autoextend_incrementsetting does not affect file-per-table tablespace files or general tablespace files. These files are auto-extending regardless of theinnodb_autoextend_incrementsetting. The initial extensions are by small amounts, after which extensions occur in increments of 4MB. -
Command-Line Format --innodb-autoinc-lock-mode=#System Variable innodb_autoinc_lock_modeScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 2Valid Values 012The lock mode to use for generating auto-increment values. Permissible values are 0, 1, or 2, for traditional, consecutive, or interleaved, respectively.
The default setting is 2 (interleaved), for compatibility with row-based replication.
For the characteristics of each lock mode, see InnoDB AUTO_INCREMENT Lock Modes.
innodb_background_drop_list_emptyCommand-Line Format --innodb-background-drop-list-empty[={OFF|ON}]System Variable innodb_background_drop_list_emptyScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFEnabling the
innodb_background_drop_list_emptydebug option helps avoid test case failures by delaying table creation until the background drop list is empty. For example, if test case A places tablet1on the background drop list, test case B waits until the background drop list is empty before creating tablet1.-
Command-Line Format --innodb-buffer-pool-chunk-size=#System Variable innodb_buffer_pool_chunk_sizeScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 134217728Minimum Value 1048576Maximum Value innodb_buffer_pool_size / innodb_buffer_pool_instancesUnit bytes innodb_buffer_pool_chunk_sizedefines the chunk size forInnoDBbuffer pool resizing operations.To avoid copying all buffer pool pages during resizing operations, the operation is performed in “chunks”. By default,
innodb_buffer_pool_chunk_sizeis 128MB (134217728 bytes). The number of pages contained in a chunk depends on the value ofinnodb_page_size.innodb_buffer_pool_chunk_sizecan be increased or decreased in units of 1MB (1048576 bytes).The following conditions apply when altering the
innodb_buffer_pool_chunk_sizevalue:If
innodb_buffer_pool_chunk_size*innodb_buffer_pool_instancesis larger than the current buffer pool size when the buffer pool is initialized,innodb_buffer_pool_chunk_sizeis truncated toinnodb_buffer_pool_size/innodb_buffer_pool_instances.Buffer pool size must always be equal to or a multiple of
innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances. If you alterinnodb_buffer_pool_chunk_size,innodb_buffer_pool_sizeis automatically rounded to a value that is equal to or a multiple ofinnodb_buffer_pool_chunk_size*innodb_buffer_pool_instances. The adjustment occurs when the buffer pool is initialized.
ImportantCare should be taken when changing
innodb_buffer_pool_chunk_size, as changing this value can automatically increase the size of the buffer pool. Before changinginnodb_buffer_pool_chunk_size, calculate its effect oninnodb_buffer_pool_sizeto ensure that the resulting buffer pool size is acceptable.To avoid potential performance issues, the number of chunks (
innodb_buffer_pool_size/innodb_buffer_pool_chunk_size) should not exceed 1000.The
innodb_buffer_pool_sizevariable is dynamic, which permits resizing the buffer pool while the server is online. However, the buffer pool size must be equal to or a multiple ofinnodb_buffer_pool_chunk_size*innodb_buffer_pool_instances, and changing either of those variable settings requires restarting the server.See Section 17.8.3.1, “Configuring InnoDB Buffer Pool Size” for more information.
-
Command-Line Format --innodb-buffer-pool-debug[={OFF|ON}]System Variable innodb_buffer_pool_debugScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value OFFEnabling this option permits multiple buffer pool instances when the buffer pool is less than 1GB in size, ignoring the 1GB minimum buffer pool size constraint imposed on
innodb_buffer_pool_instances. Theinnodb_buffer_pool_debugoption is only available if debugging support is compiled in using theWITH_DEBUGCMake option. innodb_buffer_pool_dump_at_shutdownCommand-Line Format --innodb-buffer-pool-dump-at-shutdown[={OFF|ON}]System Variable innodb_buffer_pool_dump_at_shutdownScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONSpecifies whether to record the pages cached in the
InnoDBbuffer pool when the MySQL server is shut down, to shorten the warmup process at the next restart. Typically used in combination withinnodb_buffer_pool_load_at_startup. Theinnodb_buffer_pool_dump_pctoption defines the percentage of most recently used buffer pool pages to dump.Both
innodb_buffer_pool_dump_at_shutdownandinnodb_buffer_pool_load_at_startupare enabled by default.For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb-buffer-pool-dump-now[={OFF|ON}]System Variable innodb_buffer_pool_dump_nowScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFImmediately makes a record of pages cached in the
InnoDBbuffer pool. Typically used in combination withinnodb_buffer_pool_load_now.Enabling
innodb_buffer_pool_dump_nowtriggers the recording action but does not alter the variable setting, which always remainsOFFor0. To view buffer pool dump status after triggering a dump, query theInnodb_buffer_pool_dump_statusvariable.Enabling
innodb_buffer_pool_dump_nowtriggers the dump action but does not alter the variable setting, which always remainsOFFor0. To view buffer pool dump status after triggering a dump, query theInnodb_buffer_pool_dump_statusvariable.For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb-buffer-pool-dump-pct=#System Variable innodb_buffer_pool_dump_pctScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 25Minimum Value 1Maximum Value 100Specifies the percentage of the most recently used pages for each buffer pool to read out and dump. The range is 1 to 100. The default value is 25. For example, if there are 4 buffer pools with 100 pages each, and
innodb_buffer_pool_dump_pctis set to 25, the 25 most recently used pages from each buffer pool are dumped. -
Command-Line Format --innodb-buffer-pool-filename=file_nameSystem Variable innodb_buffer_pool_filenameScope Global Dynamic Yes SET_VARHint AppliesNo Type File name Default Value ib_buffer_poolSpecifies the name of the file that holds the list of tablespace IDs and page IDs produced by
innodb_buffer_pool_dump_at_shutdownorinnodb_buffer_pool_dump_now. Tablespace IDs and page IDs are saved in the following format:space, page_id. By default, the file is namedib_buffer_pooland is located in theInnoDBdata directory. A non-default location must be specified relative to the data directory.A file name can be specified at runtime, using a
SETstatement:SET GLOBAL innodb_buffer_pool_filename='file_name';You can also specify a file name at startup, in a startup string or MySQL configuration file. When specifying a file name at startup, the file must exist or
InnoDBreturns a startup error indicating that there is no such file or directory.For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.
innodb_buffer_pool_in_core_fileCommand-Line Format --innodb-buffer-pool-in-core-file[={OFF|ON}]System Variable innodb_buffer_pool_in_core_fileScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFDisabling (default) the
innodb_buffer_pool_in_core_filevariable reduces the size of core files by excludingInnoDBbuffer pool pages.To use this variable, the
core_filevariable must be enabled, and to disable this option the operating system must support theMADV_DONTDUMPnon-POSIX extension tomadvise(), which is supported in Linux 3.4 and later. For more information, see Section 17.8.3.7, “Excluding or Including Buffer Pool Pages from Core Files”.This is disabled by default on systems that support
MADV_DONTDUMP, which is typically only Linux and not macOS or Windows.Before MySQL 8.4, this option was enabled by default.
-
Command-Line Format --innodb-buffer-pool-instances=#System Variable innodb_buffer_pool_instancesScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value see descriptionMinimum Value 1Maximum Value 64The number of regions that the
InnoDBbuffer 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. Each page that is stored in or read from the buffer pool is assigned to one of the buffer pool instances randomly, using a hashing function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex.The total buffer pool size is divided among all the buffer pools. For best efficiency, specify a combination of
innodb_buffer_pool_instancesandinnodb_buffer_pool_sizeso that each buffer pool instance is at least 1GB.If
innodb_buffer_pool_size<= 1 GiB, then the defaultinnodb_buffer_pool_instancesvalue is 1.If
innodb_buffer_pool_size> 1 GiB, then the defaultinnodb_buffer_pool_instancesvalue is the minimum value from the following two calculated hints, within a range of 1-64:Buffer pool hint: calculated as 1/2 of (
innodb_buffer_pool_size/innodb_buffer_pool_chunk_size)CPU hint: calculated as 1/4 of available logical processors
For related information, see Section 17.8.3.1, “Configuring InnoDB Buffer Pool Size”.
-
Command-Line Format --innodb-buffer-pool-load-abort[={OFF|ON}]System Variable innodb_buffer_pool_load_abortScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFInterrupts the process of restoring
InnoDBbuffer pool contents triggered byinnodb_buffer_pool_load_at_startuporinnodb_buffer_pool_load_now.Enabling
innodb_buffer_pool_load_aborttriggers the abort action but does not alter the variable setting, which always remainsOFFor0. To view buffer pool load status after triggering an abort action, query theInnodb_buffer_pool_load_statusvariable.For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.
innodb_buffer_pool_load_at_startupCommand-Line Format --innodb-buffer-pool-load-at-startup[={OFF|ON}]System Variable innodb_buffer_pool_load_at_startupScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value ONSpecifies that, on MySQL server startup, the
InnoDBbuffer pool is automatically warmed up by loading the same pages it held at an earlier time. Typically used in combination withinnodb_buffer_pool_dump_at_shutdown.Both
innodb_buffer_pool_dump_at_shutdownandinnodb_buffer_pool_load_at_startupare enabled by default.For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb-buffer-pool-load-now[={OFF|ON}]System Variable innodb_buffer_pool_load_nowScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFImmediately warms up the
InnoDBbuffer pool by loading data pages without waiting for a server restart. Can be useful to bring cache memory back to a known state during benchmarking or to ready the MySQL server to resume its normal workload after running queries for reports or maintenance.Enabling
innodb_buffer_pool_load_nowtriggers the load action but does not alter the variable setting, which always remainsOFFor0. To view buffer pool load progress after triggering a load, query theInnodb_buffer_pool_load_statusvariable.For more information, see Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.
-
Command-Line Format --innodb-buffer-pool-size=#System Variable innodb_buffer_pool_sizeScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 134217728Minimum Value 5242880Maximum Value (64-bit platforms) 2**64-1Maximum Value (32-bit platforms) 2**32-1Unit bytes The size in bytes of the buffer pool, the memory area where
InnoDBcaches table and index data. The default value is 134217728 bytes (128MB). The maximum value depends on the CPU architecture; the maximum is 4294967295 (232-1) on 32-bit systems and 18446744073709551615 (264-1) on 64-bit systems. On 32-bit systems, the CPU architecture and operating system may impose a lower practical maximum size than the stated maximum. When the size of the buffer pool is greater than 1GB, settinginnodb_buffer_pool_instancesto a value greater than 1 can improve the scalability on a busy server.A larger buffer pool requires less disk I/O to access the same table data more than once. On a dedicated database server, you might set the buffer pool size to 80% of the machine's physical memory size. Be aware of the following potential issues when configuring buffer pool size, and be prepared to scale back the size of the buffer pool if necessary.
Competition for physical memory can cause paging in the operating system.
InnoDBreserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified buffer pool size.Address space for the buffer pool must be contiguous, which can be an issue on Windows systems with DLLs that load at specific addresses.
The time to initialize the buffer pool is roughly proportional to its size. On instances with large buffer pools, initialization time might be significant. To reduce the initialization period, you can save the buffer pool state at server shutdown and restore it at server startup. See Section 17.8.3.6, “Saving and Restoring the Buffer Pool State”.
When you increase or decrease buffer pool size, the operation is performed in chunks. Chunk size is defined by the
innodb_buffer_pool_chunk_sizevariable, which has a default of 128 MB.Buffer pool size must always be equal to or a multiple of
innodb_buffer_pool_chunk_size*innodb_buffer_pool_instances. If you alter the buffer pool size to a value that is not equal to or a multiple ofinnodb_buffer_pool_chunk_size*innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple ofinnodb_buffer_pool_chunk_size*innodb_buffer_pool_instances.innodb_buffer_pool_sizecan be set dynamically, which allows you to resize the buffer pool without restarting the server. TheInnodb_buffer_pool_resize_statusstatus variable reports the status of online buffer pool resizing operations. See Section 17.8.3.1, “Configuring InnoDB Buffer Pool Size” for more information.If the server is started with
--innodb-dedicated-server, the value ofinnodb_buffer_pool_sizeis set automatically if it is not explicitly defined. For more information, see Section 17.8.12, “Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server”. -
Command-Line Format --innodb-change-buffer-max-size=#System Variable innodb_change_buffer_max_sizeScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 25Minimum Value 0Maximum Value 50Maximum size for the
InnoDBchange 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. For more information, see Section 17.5.2, “Change Buffer”. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”. -
Command-Line Format --innodb-change-buffering=valueSystem Variable innodb_change_bufferingScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value noneValid Values noneinsertsdeleteschangespurgesallWhether
InnoDBperforms change buffering, an optimization that delays write operations to secondary indexes so that the I/O operations can be performed sequentially. Permitted values are described in the following table. Values may also be specified numerically.Table 17.24 Permitted Values for innodb_change_buffering
Value Numeric Value Description none0Default. Do not buffer any operations. inserts1Buffer insert operations. deletes2Buffer delete marking operations; strictly speaking, the writes that mark index records for later deletion during a purge operation. changes3Buffer inserts and delete-marking operations. purges4Buffer the physical deletion operations that happen in the background. all5Buffer inserts, delete-marking operations, and purges. Before MySQL 8.4, the default value was
all.For more information, see Section 17.5.2, “Change Buffer”. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.
-
Command-Line Format --innodb-change-buffering-debug=#System Variable innodb_change_buffering_debugScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 2Sets a debug flag for
InnoDBchange buffering. A value of 1 forces all changes to the change buffer. A value of 2 causes an unexpected exit at merge. A default value of 0 indicates that the change buffering debug flag is not set. This option is only available when debugging support is compiled in using theWITH_DEBUGCMake option. -
Command-Line Format --innodb-checkpoint-disabled[={OFF|ON}]System Variable innodb_checkpoint_disabledScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFThis is a debug option that is only intended for expert debugging use. It disables checkpoints so that a deliberate server exit always initiates
InnoDBrecovery. It should only be enabled for a short interval, typically before running DML operations that write redo log entries that would require recovery following a server exit. This option is only available if debugging support is compiled in using theWITH_DEBUGCMake option. -
Command-Line Format --innodb-checksum-algorithm=valueSystem Variable innodb_checksum_algorithmScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value crc32Valid Values crc32strict_crc32innodbstrict_innodbnonestrict_noneSpecifies how to generate and verify the checksum stored in the disk blocks of
InnoDBtablespaces. The default value forinnodb_checksum_algorithmiscrc32.The value
innodbis backward-compatible with earlier versions of MySQL. The valuecrc32uses an algorithm that is faster to compute the checksum for every modified block, and to check the checksums for each disk read. It scans blocks 64 bits at a time, which is faster than theinnodbchecksum algorithm, which scans blocks 8 bits at a time. The valuenonewrites a constant value in the checksum field rather than computing a value based on the block data. The blocks in a tablespace can use a mix of old, new, and no checksum values, being updated gradually as the data is modified; once blocks in a tablespace are modified to use thecrc32algorithm, the associated tables cannot be read by earlier versions of MySQL.The strict form of a checksum algorithm reports an error if it encounters a valid but non-matching checksum value in a tablespace. It is recommended that you only use strict settings in a new instance, to set up tablespaces for the first time. Strict settings are somewhat faster, because they do not need to compute all checksum values during disk reads.
The following table shows the difference between the
none,innodb, andcrc32option values, and their strict counterparts.none,innodb, andcrc32write the specified type of checksum value into each data block, but for compatibility accept other checksum values when verifying a block during a read operation. Strict settings also accept valid checksum values but print an error message when a valid non-matching checksum value is encountered. Using the strict form can make verification faster if allInnoDBdata files in an instance are created under an identicalinnodb_checksum_algorithmvalue.Table 17.25 Permitted innodb_checksum_algorithm Values
Value Generated checksum (when writing) Permitted checksums (when reading) none A constant number. Any of the checksums generated by none,innodb, orcrc32.innodb A checksum calculated in software, using the original algorithm from InnoDB.Any of the checksums generated by none,innodb, orcrc32.crc32 A checksum calculated using the crc32algorithm, possibly done with a hardware assist.Any of the checksums generated by none,innodb, orcrc32.strict_none A constant number Any of the checksums generated by none,innodb, orcrc32.InnoDBprints an error message if a valid but non-matching checksum is encountered.strict_innodb A checksum calculated in software, using the original algorithm from InnoDB.Any of the checksums generated by none,innodb, orcrc32.InnoDBprints an error message if a valid but non-matching checksum is encountered.strict_crc32 A checksum calculated using the crc32algorithm, possibly done with a hardware assist.Any of the checksums generated by none,innodb, orcrc32.InnoDBprints an error message if a valid but non-matching checksum is encountered. -
Command-Line Format --innodb-cmp-per-index-enabled[={OFF|ON}]System Variable innodb_cmp_per_index_enabledScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFEnables per-index compression-related statistics in the Information Schema
INNODB_CMP_PER_INDEXtable. Because these statistics can be expensive to gather, only enable this option on development, test, or replica instances during performance tuning related toInnoDBcompressed tables.For more information, see Section 28.4.8, “The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables”, and Section 17.9.1.4, “Monitoring InnoDB Table Compression at Runtime”.
-
Command-Line Format --innodb-commit-concurrency=#System Variable innodb_commit_concurrencyScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 1000The number of threads that can commit at the same time. A value of 0 (the default) permits any number of transactions to commit simultaneously.
The value of
innodb_commit_concurrencycannot be changed at runtime from zero to nonzero or vice versa. The value can be changed from one nonzero value to another. -
Command-Line Format --innodb-compress-debug=valueSystem Variable innodb_compress_debugScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value noneValid Values nonezliblz4lz4hcCompresses all tables using a specified compression algorithm without having to define a
COMPRESSIONattribute for each table. This option is only available if debugging support is compiled in using theWITH_DEBUGCMake option.For related information, see Section 17.9.2, “InnoDB Page Compression”.
innodb_compression_failure_threshold_pctCommand-Line Format --innodb-compression-failure-threshold-pct=#System Variable innodb_compression_failure_threshold_pctScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 5Minimum Value 0Maximum Value 100Defines the compression failure rate threshold for a table, as a percentage, at which point MySQL begins adding padding within compressed pages to avoid expensive compression failures. When this threshold is passed, MySQL begins to leave additional free space within each new compressed page, dynamically adjusting the amount of free space up to the percentage of page size specified by
innodb_compression_pad_pct_max. A value of zero disables the mechanism that monitors compression efficiency and dynamically adjusts the padding amount.For more information, see Section 17.9.1.6, “Compression for OLTP Workloads”.
-
Command-Line Format --innodb-compression-level=#System Variable innodb_compression_levelScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 6Minimum Value 0Maximum Value 9Specifies the level of zlib compression to use for
InnoDBcompressed tables and indexes. A higher value lets you fit more data onto a storage device, at the expense of more CPU overhead during compression. A lower value lets you reduce CPU overhead when storage space is not critical, or you expect the data is not especially compressible.For more information, see Section 17.9.1.6, “Compression for OLTP Workloads”.
innodb_compression_pad_pct_maxCommand-Line Format --innodb-compression-pad-pct-max=#System Variable innodb_compression_pad_pct_maxScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 50Minimum Value 0Maximum Value 75Specifies 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. Only applies when
innodb_compression_failure_threshold_pctis set to a nonzero value, and the rate of compression failures passes the cutoff point.For more information, see Section 17.9.1.6, “Compression for OLTP Workloads”.
-
Command-Line Format --innodb-concurrency-tickets=#System Variable innodb_concurrency_ticketsScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 5000Minimum Value 1Maximum Value 4294967295Determines the number of threads that can enter
InnoDBconcurrently. A thread is placed in a queue when it tries to enterInnoDBif the number of threads has already reached the concurrency limit. When a thread is permitted to enterInnoDB, it is given a number of “ tickets” equal to the value ofinnodb_concurrency_tickets, and the thread can enter and leaveInnoDBfreely until it has used up its tickets. After that point, the thread again becomes subject to the concurrency check (and possible queuing) the next time it tries to enterInnoDB. The default value is 5000.With a small
innodb_concurrency_ticketsvalue, small transactions that only need to process a few rows compete fairly with larger transactions that process many rows. The disadvantage of a smallinnodb_concurrency_ticketsvalue is that large transactions must loop through the queue many times before they can complete, which extends the amount of time required to complete their task.With a large
innodb_concurrency_ticketsvalue, large transactions spend less time waiting for a position at the end of the queue (controlled byinnodb_thread_concurrency) and more time retrieving rows. Large transactions also require fewer trips through the queue to complete their task. The disadvantage of a largeinnodb_concurrency_ticketsvalue is that too many large transactions running at the same time can starve smaller transactions by making them wait a longer time before executing.With a nonzero
innodb_thread_concurrencyvalue, you may need to adjust theinnodb_concurrency_ticketsvalue up or down to find the optimal balance between larger and smaller transactions. TheSHOW ENGINE INNODB STATUSreport shows the number of tickets remaining for an executing transaction in its current pass through the queue. This data may also be obtained from theTRX_CONCURRENCY_TICKETScolumn of the Information SchemaINNODB_TRXtable.For more information, see Section 17.8.4, “Configuring Thread Concurrency for InnoDB”.
-
Command-Line Format --innodb-data-file-path=file_nameSystem Variable innodb_data_file_pathScope Global Dynamic No SET_VARHint AppliesNo Type String Default Value ibdata1:12M:autoextendDefines the name, size, and attributes of
InnoDBsystem tablespace data files. If you do not specify a value forinnodb_data_file_path, the default behavior is to create a single auto-extending data file, slightly larger than 12MB, namedibdata1.The full syntax for a data file specification includes the file name, file size,
autoextendattribute, andmaxattribute:file_name:file_size[:autoextend[:max:max_file_size]]File sizes are specified in kilobytes, megabytes, or gigabytes by appending
K,MorGto the size value. If specifying the data file size in kilobytes, do so in multiples of 1024. Otherwise, KB values are rounded to nearest megabyte (MB) boundary. The sum of file sizes must be, at a minimum, slightly larger than 12MB.For additional configuration information, see System Tablespace Data File Configuration. For resizing instructions, see Resizing the System Tablespace.
-
Command-Line Format --innodb-data-home-dir=dir_nameSystem Variable innodb_data_home_dirScope Global Dynamic No SET_VARHint AppliesNo Type Directory name The common part of the directory path for
InnoDBsystem tablespace data files. The default value is the MySQLdatadirectory. The setting is concatenated with theinnodb_data_file_pathsetting, unless that setting is defined with an absolute path.A trailing slash is required when specifying a value for
innodb_data_home_dir. For example:[mysqld] innodb_data_home_dir = /path/to/myibdata/This setting does not affect the location of file-per-table tablespaces.
For related information, see Section 17.8.1, “InnoDB Startup Configuration”.
-
Command-Line Format --innodb-ddl-buffer-size=#System Variable innodb_ddl_buffer_sizeScope Session Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 1048576Minimum Value 65536Maximum Value 4294967295Unit bytes Defines the maximum buffer size for DDL operations. The default setting is 1048576 bytes (approximately 1 MB). Applies to online DDL operations that create or rebuild secondary indexes. See Section 17.12.4, “Online DDL Memory Management”. The maximum buffer size per DDL thread is the maximum buffer size divided by the number of DDL threads (
innodb_ddl_buffer_size/innodb_ddl_threads). innodb_ddl_log_crash_reset_debugCommand-Line Format --innodb-ddl-log-crash-reset-debug[={OFF|ON}]System Variable innodb_ddl_log_crash_reset_debugScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFEnable this debug option to reset DDL log crash injection counters to 1. This option is only available when debugging support is compiled in using the
WITH_DEBUGCMake option.-
Command-Line Format --innodb-ddl-threads=#System Variable innodb_ddl_threadsScope Session Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 4Minimum Value 1Maximum Value 64Defines the maximum number of parallel threads for the sort and build phases of index creation. Applies to online DDL operations that create or rebuild secondary indexes. For related information, see Section 17.12.5, “Configuring Parallel Threads for Online DDL Operations”, and Section 17.12.4, “Online DDL Memory Management”.
-
Command-Line Format --innodb-deadlock-detect[={OFF|ON}]System Variable innodb_deadlock_detectScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONThis option is used to disable deadlock detection. On high concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the
innodb_lock_wait_timeoutsetting for transaction rollback when a deadlock occurs.For related information, see Section 17.7.5.2, “Deadlock Detection”.
-
Command-Line Format --innodb-default-row-format=valueSystem Variable innodb_default_row_formatScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value DYNAMICValid Values REDUNDANTCOMPACTDYNAMICThe
innodb_default_row_formatoption defines the default row format forInnoDBtables and user-created temporary tables. The default setting isDYNAMIC. Other permitted values areCOMPACTandREDUNDANT. TheCOMPRESSEDrow format, which is not supported for use in the system tablespace, cannot be defined as the default.Newly created tables use the row format defined by
innodb_default_row_formatwhen aROW_FORMAToption is not specified explicitly or whenROW_FORMAT=DEFAULTis used.When a
ROW_FORMAToption is not specified explicitly or whenROW_FORMAT=DEFAULTis used, any operation that rebuilds a table also silently changes the row format of the table to the format defined byinnodb_default_row_format. For more information, see Defining the Row Format of a Table.Internal
InnoDBtemporary tables created by the server to process queries use theDYNAMICrow format, regardless of theinnodb_default_row_formatsetting. -
Command-Line Format --innodb-directories=dir_nameSystem Variable innodb_directoriesScope Global Dynamic No SET_VARHint AppliesNo Type Directory name Default Value NULLDefines directories to scan at startup for tablespace files. This option is used when moving or restoring tablespace files to a new location while the server is offline. It is also used to specify directories of tablespace files created using an absolute path or that reside outside of the data directory.
Tablespace discovery during crash recovery relies on the
innodb_directoriessetting to identify tablespaces referenced in the redo logs. For more information, see Tablespace Discovery During Crash Recovery.The default value is NULL, but directories defined by
innodb_data_home_dir,innodb_undo_directory, anddatadirare always appended to theinnodb_directoriesargument value whenInnoDBbuilds a list of directories to scan at startup. These directories are appended regardless of whether aninnodb_directoriessetting is specified explicitly.innodb_directoriesmay be specified as an option in a startup command or in a MySQL option file. Quotes surround the argument value because otherwise some command interpreters interpret semicolon (;) as a special character. (For example, Unix shells treat it as a command terminator.)Startup command:
mysqld --innodb-directories="directory_path_1;directory_path_2"MySQL option file:
[mysqld] innodb_directories="directory_path_1;directory_path_2"Wildcard expressions cannot be used to specify directories.
The
innodb_directoriesscan also traverses the subdirectories of specified directories. Duplicate directories and subdirectories are discarded from the list of directories to be scanned.For more information, see Section 17.6.3.6, “Moving Tablespace Files While the Server is Offline”.
innodb_disable_sort_file_cacheCommand-Line Format --innodb-disable-sort-file-cache[={OFF|ON}]System Variable innodb_disable_sort_file_cacheScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFDisables the operating system file system cache for merge-sort temporary files. The effect is to open such files with the equivalent of
O_DIRECT.-
Command-Line Format --innodb-doublewrite=valueSystem Variable innodb_doublewriteScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value ONValid Values ONOFFDETECT_AND_RECOVERDETECT_ONLYThe
innodb_doublewritevariable controls doublewrite buffering. Doublewrite buffering is enabled by default in most cases.You can set
innodb_doublewritetoONorOFFwhen starting the server to enable or disable doublewrite buffering, respectively.DETECT_AND_RECOVERis the same asON. With this setting, except that the doublewrite buffer is fully enabled, with database page content written to the doublewrite buffer where it is accessed during recovery to fix incomplete page writes. WithDETECT_ONLY, only metadata is written to the doublewrite buffer. Database page content is not written to the doublewrite buffer, and recovery does not use the doublewrite buffer to fix incomplete page writes. This lightweight setting is intended for detecting incomplete page writes only.MySQL supports dynamic changes to the
innodb_doublewritesetting that enables the doublewrite buffer, betweenON,DETECT_AND_RECOVER, andDETECT_ONLY. MySQL does not support dynamic changes between a setting that enables the doublewrite buffer andOFFor vice versa.If the doublewrite buffer is located on a Fusion-io device that supports atomic writes, the doublewrite buffer is automatically disabled and data file writes are performed using Fusion-io atomic writes instead. However, be aware that the
innodb_doublewritesetting is global. When the doublewrite buffer is disabled, it is disabled for all data files including those that do not reside on Fusion-io hardware. This feature is only supported on Fusion-io hardware and is only enabled for Fusion-io NVMFS on Linux. To take full advantage of this feature, aninnodb_flush_methodsetting ofO_DIRECTis recommended.For related information, see Section 17.6.4, “Doublewrite Buffer”.
-
Command-Line Format --innodb-doublewrite-batch-size=#System Variable innodb_doublewrite_batch_sizeScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 256This variable was intended to represent the number of doublewrite pages to write in a batch. This functionality was replaced by
innodb_doublewrite_pages.For more information, see Section 17.6.4, “Doublewrite Buffer”.
-
Command-Line Format --innodb-doublewrite-dir=dir_nameSystem Variable innodb_doublewrite_dirScope Global Dynamic No SET_VARHint AppliesNo Type Directory name Defines the directory for doublewrite files. If no directory is specified, doublewrite files are created in the
innodb_data_home_dirdirectory, which defaults to the data directory if unspecified.For more information, see Section 17.6.4, “Doublewrite Buffer”.
-
Command-Line Format --innodb-doublewrite-files=#System Variable innodb_doublewrite_filesScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 2Minimum Value 1Maximum Value 256Defines the number of doublewrite files. By default, two doublewrite files are created for each buffer pool instance.
At a minimum, there are two doublewrite files. The maximum number of doublewrite files is two times the number of buffer pool instances. (The number of buffer pool instances is controlled by the
innodb_buffer_pool_instancesvariable.)For more information, see Section 17.6.4, “Doublewrite Buffer”.
-
Command-Line Format --innodb-doublewrite-pages=#System Variable innodb_doublewrite_pagesScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 128Minimum Value 1Maximum Value 512Defines the maximum number of doublewrite pages per thread for a batch write. If no value is specified,
innodb_doublewrite_pagesdefaults to 128.Before MySQL 8.4, the default value was the
innodb_write_io_threadsvalue, which is 4 by default.For more information, see Section 17.6.4, “Doublewrite Buffer”.
-
Command-Line Format --innodb=extend-and-initialize[={OFF|ON}]System Variable innodb_extend_and_initializeScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONControls how space is allocated to file-per-table and general tablespaces on Linux systems.
When enabled,
InnoDBwrites NULLs to newly allocated pages. When disabled, space is allocated usingposix_fallocate()calls, which reserve space without physically writing NULLs.For more information, see Section 17.6.3.8, “Optimizing Tablespace Space Allocation on Linux”.
-
Command-Line Format --innodb-fast-shutdown=#System Variable innodb_fast_shutdownScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 1Valid Values 012The
InnoDBshutdown mode. If the value is 0,InnoDBdoes a slow shutdown, a full purge and a change buffer merge before shutting down. If the value is 1 (the default),InnoDBskips these operations at shutdown, a process known as a fast shutdown. If the value is 2,InnoDBflushes its logs and shuts down cold, as if MySQL had crashed; no committed transactions are lost, but the crash recovery operation makes the next startup take longer.The slow shutdown can take minutes, or even hours in extreme cases where substantial amounts of data are still buffered. Use the slow shutdown technique before upgrading or downgrading between MySQL major releases, so that all data files are fully prepared in case the upgrade process updates the file format.
Use
innodb_fast_shutdown=2in emergency or troubleshooting situations, to get the absolute fastest shutdown if data is at risk of corruption. innodb_fil_make_page_dirty_debugCommand-Line Format --innodb-fil-make-page-dirty-debug=#System Variable innodb_fil_make_page_dirty_debugScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 2**32-1By default, setting
innodb_fil_make_page_dirty_debugto the ID of a tablespace immediately dirties the first page of the tablespace. Ifinnodb_saved_page_number_debugis set to a non-default value, settinginnodb_fil_make_page_dirty_debugdirties the specified page. Theinnodb_fil_make_page_dirty_debugoption is only available if debugging support is compiled in using theWITH_DEBUGCMake option.-
Command-Line Format --innodb-file-per-table[={OFF|ON}]System Variable innodb_file_per_tableScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONWhen
innodb_file_per_tableis enabled, tables are created in file-per-table tablespaces by default. When disabled, tables are created in the system tablespace by default. For information about file-per-table tablespaces, see Section 17.6.3.2, “File-Per-Table Tablespaces”. For information about theInnoDBsystem tablespace, see Section 17.6.3.1, “The System Tablespace”.The
innodb_file_per_tablevariable can be configured at runtime using aSET GLOBALstatement, specified on the command line at startup, or specified in an option file. Configuration at runtime requires privileges sufficient to set global system variables (see Section 7.1.9.1, “System Variable Privileges”) and immediately affects the operation of all connections.When a table that resides in a file-per-table tablespace is truncated or dropped, the freed space is returned to the operating system. Truncating or dropping a table that resides in the system tablespace only frees space in the system tablespace. Freed space in the system tablespace can be used again for
InnoDBdata but is not returned to the operating system, as system tablespace data files never shrink.The
innodb_file_per-tablesetting does not affect the creation of temporary tables; temporary tables are created in session temporary tablespaces. See Section 17.6.3.5, “Temporary Tablespaces”. -
Command-Line Format --innodb-fill-factor=#System Variable innodb_fill_factorScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 100Minimum Value 10Maximum Value 100InnoDBperforms a bulk load when creating or rebuilding indexes. This method of index creation is known as a “sorted index build”.innodb_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. For example, settinginnodb_fill_factorto 80 reserves 20 percent of the space on each B-tree page for future index growth. Actual percentages may vary. Theinnodb_fill_factorsetting is interpreted as a hint rather than a hard limit.An
innodb_fill_factorsetting of 100 leaves 1/16 of the space in clustered index pages free for future index growth.innodb_fill_factorapplies to both B-tree leaf and non-leaf pages. It does not apply to external pages used forTEXTorBLOBentries.For more information, see Section 17.6.2.3, “Sorted Index Builds”.
-
Command-Line Format --innodb-flush-log-at-timeout=#System Variable innodb_flush_log_at_timeoutScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 1Minimum Value 1Maximum Value 2700Unit seconds Write and flush the logs every
Nseconds.innodb_flush_log_at_timeoutallows the timeout period between flushes to be increased in order to reduce flushing and avoid impacting performance of binary log group commit. The default setting forinnodb_flush_log_at_timeoutis once per second. innodb_flush_log_at_trx_commitCommand-Line Format --innodb-flush-log-at-trx-commit=#System Variable innodb_flush_log_at_trx_commitScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value 1Valid Values 012Controls the balance between strict ACID compliance for commit operations and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value but then you can lose transactions in a crash.
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
For settings 0 and 2, once-per-second flushing is not 100% guaranteed. Flushing may occur more frequently due to DDL changes and other internal
InnoDBactivities that cause logs to be flushed independently of theinnodb_flush_log_at_trx_commitsetting, and sometimes less frequently due to scheduling issues. If logs are flushed once per second, up to one second of transactions can be lost in a crash. If logs are flushed more or less frequently than once per second, the amount of transactions that can be lost varies accordingly.Log flushing frequency is controlled by
innodb_flush_log_at_timeout, which allows you to set log flushing frequency toNseconds (whereNis1 ... 2700, with a default value of 1). However, any unexpected mysqld process exit can erase up toNseconds of transactions.DDL changes and other internal
InnoDBactivities flush the log independently of theinnodb_flush_log_at_trx_commitsetting.InnoDBcrash recovery works regardless of theinnodb_flush_log_at_trx_commitsetting. Transactions are either applied entirely or erased entirely.
For durability and consistency in a replication setup that uses
InnoDBwith transactions:If binary logging is enabled, set
sync_binlog=1.Always set
innodb_flush_log_at_trx_commit=1.
For information on the combination of settings on a replica that is most resilient to unexpected halts, see Section 19.4.2, “Handling an Unexpected Halt of a Replica”.
CautionMany operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. In this case, the durability of transactions is not guaranteed even with the recommended settings, and in the worst case, a power outage can corrupt
InnoDBdata. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try to disable the caching of disk writes in hardware caches.-
Command-Line Format --innodb-flush-method=valueSystem Variable innodb_flush_methodScope Global Dynamic No SET_VARHint AppliesNo Type String Default Value (Unix) O_DIRECT if supported, otherwise fsyncDefault Value (Windows) unbufferedValid Values (Unix) fsyncO_DSYNClittlesyncnosyncO_DIRECTO_DIRECT_NO_FSYNCValid Values (Windows) unbufferednormalDefines the method used to flush data to
InnoDBdata files and log files, which can affect I/O throughput.On Unix-like systems, the default value is
O_DIRECTif supported otherwise defaults tofsync. On Windows, the default value isunbuffered.The
innodb_flush_methodoptions for Unix-like systems include:fsyncor0:InnoDBuses thefsync()system call to flush both the data and log files.O_DSYNCor1:InnoDBusesO_SYNCto open and flush the log files, andfsync()to flush the data files.InnoDBdoes not useO_DSYNCdirectly because there have been problems with it on many varieties of Unix.littlesyncor2: This option is used for internal performance testing and is currently unsupported. Use at your own risk.nosyncor3: This option is used for internal performance testing and is currently unsupported. Use at your own risk.O_DIRECTor4:InnoDBusesO_DIRECT(ordirectio()on Solaris) to open the data files, and usesfsync()to flush both the data and log files. This option is available on some GNU/Linux versions, FreeBSD, and Solaris.O_DIRECT_NO_FSYNC:InnoDBusesO_DIRECTduring flushing I/O, but skips thefsync()system call after each write operation.MySQL calls
fsync()after creating a new file, after increasing file size, and after closing a file, to ensure that file system metadata changes are synchronized. Thefsync()system call is still skipped after each write operation.Data loss is possible if redo log files and data files reside on different storage devices, and an unexpected exit occurs before data file writes are flushed from a device cache that is not battery-backed. If you use or intend to use different storage devices for redo log files and data files, and your data files reside on a device with a cache that is not battery-backed, use
O_DIRECTinstead.
On platforms that support
fdatasync()system calls, theinnodb_use_fdatasyncvariable permitsinnodb_flush_methodoptions that usefsync()to usefdatasync()instead. Anfdatasync()system call does not flush changes to file metadata unless required for subsequent data retrieval, providing a potential performance benefit.The
innodb_flush_methodoptions for Windows systems include:unbufferedor0:InnoDBuses non-buffered I/O.NoteRunning MySQL server on a 4K sector hard drive on Windows is not supported with
unbuffered. The workaround is to useinnodb_flush_method=normal.normalor1:InnoDBuses buffered I/O.
How each setting affects performance depends on hardware configuration and workload. Benchmark your particular configuration to decide which setting to use, or whether to keep the default setting. Examine the
Innodb_data_fsyncsstatus variable to see the overall number offsync()calls (orfdatasync()calls ifinnodb_use_fdatasyncis enabled) for each setting. The mix of read and write operations in your workload can affect how a setting performs. For example, on a system with a hardware RAID controller and battery-backed write cache,O_DIRECTcan help to avoid double buffering between theInnoDBbuffer pool and the operating system file system cache. On some systems whereInnoDBdata and log files are located on a SAN, the default value orO_DSYNCmight be faster for a read-heavy workload with mostlySELECTstatements. Always test this parameter with hardware and workload that reflect your production environment. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”. -
Command-Line Format --innodb-flush-neighbors=#System Variable innodb_flush_neighborsScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value 0Valid Values 012Specifies whether flushing a page from the
InnoDBbuffer pool also flushes other dirty pages in the same extent.A setting of 0 disables
innodb_flush_neighbors. Dirty pages in the same extent are not flushed.A setting of 1 flushes contiguous dirty pages in the same extent.
A setting of 2 flushes dirty pages in the same extent.
When the table data is stored on a traditional HDD storage device, flushing such neighbor pages in one operation reduces I/O overhead (primarily for disk seek operations) compared to flushing individual pages at different times. For table data stored on SSD, seek time is not a significant factor and you can set this option to 0 to spread out write operations. For related information, see Section 17.8.3.5, “Configuring Buffer Pool Flushing”.
-
Command-Line Format --innodb-flush-sync[={OFF|ON}]System Variable innodb_flush_syncScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONThe
innodb_flush_syncvariable, which is enabled by default, causes theinnodb_io_capacityandinnodb_io_capacity_maxsettings to be ignored during bursts of I/O activity that occur at checkpoints. To adhere to the I/O rate defined byinnodb_io_capacityandinnodb_io_capacity_max, disableinnodb_flush_sync.For information about configuring the
innodb_flush_syncvariable, see Section 17.8.7, “Configuring InnoDB I/O Capacity”. -
Command-Line Format --innodb-flushing-avg-loops=#System Variable innodb_flushing_avg_loopsScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 30Minimum Value 1Maximum Value 1000Number of iterations for which
InnoDBkeeps the previously calculated snapshot of the flushing state, controlling how quickly adaptive flushing responds to changing workloads. Increasing the value makes the rate of flush operations change smoothly and gradually as the workload changes. Decreasing the value makes adaptive flushing adjust quickly to workload changes, which can cause spikes in flushing activity if the workload increases and decreases suddenly.For related information, see Section 17.8.3.5, “Configuring Buffer Pool Flushing”.
-
Command-Line Format --innodb-force-load-corrupted[={OFF|ON}]System Variable innodb_force_load_corruptedScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value OFFPermits
InnoDBto load tables at startup that are marked as corrupted. Use only during troubleshooting, to recover data that is otherwise inaccessible. When troubleshooting is complete, disable this setting and restart the server. -
Command-Line Format --innodb-force-recovery=#System Variable innodb_force_recoveryScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 6The crash recovery mode, typically only changed in serious troubleshooting situations. Possible values are from 0 to 6. For the meanings of these values and important information about
innodb_force_recovery, see Section 17.20.3, “Forcing InnoDB Recovery”.WarningOnly set this variable to a value greater than 0 in an emergency situation so that you can start
InnoDBand dump your tables. As a safety measure,InnoDBpreventsINSERT,UPDATE, orDELETEoperations wheninnodb_force_recoveryis greater than 0. Aninnodb_force_recoverysetting of 4 or greater placesInnoDBinto read-only mode.These restrictions may cause replication administration commands to fail with an error, as replication stores the replica status logs in
InnoDBtables. -
Command-Line Format --innodb-fsync-threshold=#System Variable innodb_fsync_thresholdScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 2**64-1By default, when
InnoDBcreates a new data file, such as a new log file or tablespace file, the file is fully written to the operating system cache before it is flushed to disk, which can cause a large amount of disk write activity to occur at once. To force smaller, periodic flushes of data from the operating system cache, you can use theinnodb_fsync_thresholdvariable to define a threshold value, in bytes. When the byte threshold is reached, the contents of the operating system cache are flushed to disk. The default value of 0 forces the default behavior, which is to flush data to disk only after a file is fully written to the cache.Specifying a threshold to force smaller, periodic flushes may be beneficial in cases where multiple MySQL instances use the same storage devices. For example, creating a new MySQL instance and its associated data files could cause large surges of disk write activity, impeding the performance of other MySQL instances that use the same storage devices. Configuring a threshold helps avoid such surges in write activity.
-
System Variable innodb_ft_aux_tableScope Global Dynamic Yes SET_VARHint AppliesNo Type String Specifies the qualified name of an
InnoDBtable containing aFULLTEXTindex. This variable is intended for diagnostic purposes and can only be set at runtime. For example:SET GLOBAL innodb_ft_aux_table = 'test/t1';After you set this variable to a name in the format
db_name/table_nameINFORMATION_SCHEMAtablesINNODB_FT_INDEX_TABLE,INNODB_FT_INDEX_CACHE,INNODB_FT_CONFIG,INNODB_FT_DELETED, andINNODB_FT_BEING_DELETEDshow information about the search index for the specified table.For more information, see Section 17.15.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.
-
Command-Line Format --innodb-ft-cache-size=#System Variable innodb_ft_cache_sizeScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 8000000Minimum Value 1600000Maximum Value 80000000Unit bytes The memory allocated, in bytes, for the
InnoDBFULLTEXTsearch index cache, which holds a parsed document in memory while creating anInnoDBFULLTEXTindex. Index inserts and updates are only committed to disk when theinnodb_ft_cache_sizesize limit is reached.innodb_ft_cache_sizedefines the cache size on a per table basis. To set a global limit for all tables, seeinnodb_ft_total_cache_size.For more information, see InnoDB Full-Text Index Cache.
-
Command-Line Format --innodb-ft-enable-diag-print[={OFF|ON}]System Variable innodb_ft_enable_diag_printScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFWhether to enable additional full-text search (FTS) diagnostic output. This option is primarily intended for advanced FTS debugging and is not of interest to most users. Output is printed to the error log and includes information such as:
FTS index sync progress (when the FTS cache limit is reached). For example:
FTS SYNC for table test, deleted count: 100 size: 10000 bytes SYNC words: 100FTS optimize progress. For example:
FTS start optimize test FTS_OPTIMIZE: optimize "mysql" FTS_OPTIMIZE: processed "mysql"FTS index build progress. For example:
Number of doc processed: 1000For FTS queries, the query parsing tree, word weight, query processing time, and memory usage are printed. For example:
FTS Search Processing time: 1 secs: 100 millisec: row(s) 10000 Full Search Memory: 245666 (bytes), Row: 10000
-
Command-Line Format --innodb-ft-enable-stopword[={OFF|ON}]System Variable innodb_ft_enable_stopwordScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONSpecifies that a set of stopwords is associated with an
InnoDBFULLTEXTindex at the time the index is created. If theinnodb_ft_user_stopword_tableoption is set, the stopwords are taken from that table. Else, if theinnodb_ft_server_stopword_tableoption is set, the stopwords are taken from that table. Otherwise, a built-in set of default stopwords is used.For more information, see Section 14.9.4, “Full-Text Stopwords”.
-
Command-Line Format --innodb-ft-max-token-size=#System Variable innodb_ft_max_token_sizeScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 84Minimum Value 10Maximum Value 84Maximum character length of words that are stored in an
InnoDBFULLTEXTindex. Setting a limit on this value reduces the size of the index, thus speeding up queries, by omitting long keywords or arbitrary collections of letters that are not real words and are not likely to be search terms.For more information, see Section 14.9.6, “Fine-Tuning MySQL Full-Text Search”.
-
Command-Line Format --innodb-ft-min-token-size=#System Variable innodb_ft_min_token_sizeScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 3Minimum Value 0Maximum Value 16Minimum length of words that are stored in an
InnoDBFULLTEXTindex. Increasing this value reduces the size of the index, thus speeding up queries, by omitting common words that are unlikely to be significant in a search context, such as the English words “a” and “to”. For content using a CJK (Chinese, Japanese, Korean) character set, specify a value of 1.For more information, see Section 14.9.6, “Fine-Tuning MySQL Full-Text Search”.
-
Command-Line Format --innodb-ft-num-word-optimize=#System Variable innodb_ft_num_word_optimizeScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 2000Minimum Value 1000Maximum Value 10000Number of words to process during each
OPTIMIZE TABLEoperation on anInnoDBFULLTEXTindex. Because a bulk insert or update operation to a table containing a full-text search index could require substantial index maintenance to incorporate all changes, you might do a series ofOPTIMIZE TABLEstatements, each picking up where the last left off.For more information, see Section 14.9.6, “Fine-Tuning MySQL Full-Text Search”.
-
Command-Line Format --innodb-ft-result-cache-limit=#System Variable innodb_ft_result_cache_limitScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 2000000000Minimum Value 1000000Maximum Value 2**32-1Unit bytes The
InnoDBfull-text search query result cache limit (defined in bytes) per full-text search query or per thread. Intermediate and finalInnoDBfull-text search query results are handled in memory. Useinnodb_ft_result_cache_limitto place a size limit on the full-text search query result cache to avoid excessive memory consumption in case of very largeInnoDBfull-text search query results (millions or hundreds of millions of rows, for example). Memory is allocated as required when a full-text search query is processed. If the result cache size limit is reached, an error is returned indicating that the query exceeds the maximum allowed memory.The maximum value of
innodb_ft_result_cache_limitfor all platform types and bit sizes is 2**32-1. innodb_ft_server_stopword_tableCommand-Line Format --innodb-ft-server-stopword-table=db_name/table_nameSystem Variable innodb_ft_server_stopword_tableScope Global Dynamic Yes SET_VARHint AppliesNo Type String Default Value NULLThis option is used to specify your own
InnoDBFULLTEXTindex stopword list for allInnoDBtables. To configure your own stopword list for a specificInnoDBtable, useinnodb_ft_user_stopword_table.Set
innodb_ft_server_stopword_tableto the name of the table containing a list of stopwords, in the formatdb_name/table_nameThe stopword table must exist before you configure
innodb_ft_server_stopword_table.innodb_ft_enable_stopwordmust be enabled andinnodb_ft_server_stopword_tableoption must be configured before you create theFULLTEXTindex.The stopword table must be an
InnoDBtable, containing a singleVARCHARcolumn namedvalue.For more information, see Section 14.9.4, “Full-Text Stopwords”.
-
Command-Line Format --innodb-ft-sort-pll-degree=#System Variable innodb_ft_sort_pll_degreeScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 2Minimum Value 1Maximum Value 16Number of threads used in parallel to index and tokenize text in an
InnoDBFULLTEXTindex when building a search index.For related information, see Section 17.6.2.4, “InnoDB Full-Text Indexes”, and
innodb_sort_buffer_size. -
Command-Line Format --innodb-ft-total-cache-size=#System Variable innodb_ft_total_cache_sizeScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 640000000Minimum Value 32000000Maximum Value 1600000000Unit bytes The total memory allocated, in bytes, for the
InnoDBfull-text search index cache for all tables. Creating numerous tables, each with aFULLTEXTsearch index, could consume a significant portion of available memory.innodb_ft_total_cache_sizedefines a global memory limit for all full-text search indexes to help avoid excessive memory consumption. If the global limit is reached by an index operation, a forced sync is triggered.For more information, see InnoDB Full-Text Index Cache.
-
Command-Line Format --innodb-ft-user-stopword-table=db_name/table_nameSystem Variable innodb_ft_user_stopword_tableScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type String Default Value NULLThis option is used to specify your own
InnoDBFULLTEXTindex stopword list on a specific table. To configure your own stopword list for allInnoDBtables, useinnodb_ft_server_stopword_table.Set
innodb_ft_user_stopword_tableto the name of the table containing a list of stopwords, in the formatdb_name/table_nameThe stopword table must exist before you configure
innodb_ft_user_stopword_table.innodb_ft_enable_stopwordmust be enabled andinnodb_ft_user_stopword_tablemust be configured before you create theFULLTEXTindex.The stopword table must be an
InnoDBtable, containing a singleVARCHARcolumn namedvalue.For more information, see Section 14.9.4, “Full-Text Stopwords”.
-
Command-Line Format --innodb-idle-flush-pct=#System Variable innodb_idle_flush_pctScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 100Minimum Value 0Maximum Value 100Limits page flushing when
InnoDBis idle. Theinnodb_idle_flush_pctvalue is a percentage of theinnodb_io_capacitysetting, which defines the number of I/O operations per second available toInnoDB. For more information, see Limiting Buffer Flushing During Idle Periods. -
Command-Line Format --innodb-io-capacity=#System Variable innodb_io_capacityScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 10000Minimum Value 100Maximum Value (64-bit platforms, 8.4.0) 2**64-1Maximum Value 2**32-1The
innodb_io_capacityvariable defines the number of I/O operations per second (IOPS) available toInnoDBbackground tasks, such as flushing pages from the buffer pool and merging data from the change buffer.For information about configuring the
innodb_io_capacityvariable, see Section 17.8.7, “Configuring InnoDB I/O Capacity”. -
Command-Line Format --innodb-io-capacity-max=#System Variable innodb_io_capacity_maxScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 2 * innodb_io_capacityMinimum Value 100Maximum Value 2**32-1If flushing activity falls behind,
InnoDBcan flush more aggressively, at a higher rate of I/O operations per second (IOPS) than defined by theinnodb_io_capacityvariable. Theinnodb_io_capacity_maxvariable defines a maximum number of IOPS performed byInnoDBbackground tasks in such situations. This option does not controlinnodb_flush_syncbehavior.The default value is twice the value of
innodb_io_capacity.For information about configuring the
innodb_io_capacity_maxvariable, see Section 17.8.7, “Configuring InnoDB I/O Capacity”. innodb_limit_optimistic_insert_debugCommand-Line Format --innodb-limit-optimistic-insert-debug=#System Variable innodb_limit_optimistic_insert_debugScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 2**32-1Limits the number of records per B-tree page. A default value of 0 means that no limit is imposed. This option is only available if debugging support is compiled in using the
WITH_DEBUGCMake option.-
Command-Line Format --innodb-lock-wait-timeout=#System Variable innodb_lock_wait_timeoutScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 50Minimum Value 1Maximum Value 1073741824Unit seconds The length of time in seconds an
InnoDBtransaction waits for a row lock before giving up. The default value is 50 seconds. A transaction that tries to access a row that is locked by anotherInnoDBtransaction waits at most this many seconds for write access to the row before issuing the following error:ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionWhen a lock wait timeout occurs, the current statement is rolled back (not the entire transaction). To have the entire transaction roll back, start the server with the
--innodb-rollback-on-timeoutoption. See also Section 17.20.5, “InnoDB Error Handling”.You might decrease this value for highly interactive applications or OLTP systems, to display user feedback quickly or put the update into a queue for processing later. You might increase this value for long-running back-end operations, such as a transform step in a data warehouse that waits for other large insert or update operations to finish.
innodb_lock_wait_timeoutapplies toInnoDBrow locks. A MySQL table lock does not happen insideInnoDBand this timeout does not apply to waits for table locks.The lock wait timeout value does not apply to deadlocks when
innodb_deadlock_detectis enabled (the default) becauseInnoDBdetects deadlocks immediately and rolls back one of the deadlocked transactions. Wheninnodb_deadlock_detectis disabled,InnoDBrelies oninnodb_lock_wait_timeoutfor transaction rollback when a deadlock occurs. See Section 17.7.5.2, “Deadlock Detection”.innodb_lock_wait_timeoutcan be set at runtime with theSET GLOBALorSET SESSIONstatement. Changing theGLOBALsetting requires privileges sufficient to set global system variables (see Section 7.1.9.1, “System Variable Privileges”) and affects the operation of all clients that subsequently connect. Any client can change theSESSIONsetting forinnodb_lock_wait_timeout, which affects only that client. -
Command-Line Format --innodb-log-buffer-size=#System Variable innodb_log_buffer_sizeScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 67108864Minimum Value 1048576Maximum Value 4294967295The size in bytes of the buffer that
InnoDBuses to write to the log files on disk. The default is 64MB. A large log buffer enables large transactions to run without the need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, making the log buffer larger saves disk I/O. For related information, see Memory Configuration, and Section 10.5.4, “Optimizing InnoDB Redo Logging”. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”. innodb_log_checkpoint_fuzzy_nowCommand-Line Format --innodb-log-checkpoint-fuzzy-now[={OFF|ON}]System Variable innodb_log_checkpoint_fuzzy_nowScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFEnable this debug option to force
InnoDBto write a fuzzy checkpoint. This option is only available if debugging support is compiled in using theWITH_DEBUGCMake option.-
Command-Line Format --innodb-log-checkpoint-now[={OFF|ON}]System Variable innodb_log_checkpoint_nowScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFEnable this debug option to force
InnoDBto write a checkpoint. This option is only available if debugging support is compiled in using theWITH_DEBUGCMake option. -
Command-Line Format --innodb-log-checksums[={OFF|ON}]System Variable innodb_log_checksumsScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONEnables or disables checksums for redo log pages.
innodb_log_checksums=ONenables theCRC-32Cchecksum algorithm for redo log pages. Wheninnodb_log_checksumsis disabled, the contents of the redo log page checksum field are ignored.Checksums on the redo log header page and redo log checkpoint pages are never disabled.
-
Command-Line Format --innodb-log-compressed-pages[={OFF|ON}]System Variable innodb_log_compressed_pagesScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONSpecifies whether images of re-compressed pages are written to the redo log. Re-compression may occur when changes are made to compressed data.
innodb_log_compressed_pagesis enabled by default to prevent corruption that could occur if a different version of thezlibcompression algorithm is used during recovery. If you are certain that thezlibversion is not subject to change, you can disableinnodb_log_compressed_pagesto reduce redo log generation for workloads that modify compressed data.To measure the effect of enabling or disabling
innodb_log_compressed_pages, compare redo log generation for both settings under the same workload. Options for measuring redo log generation include observing theLog sequence number(LSN) in theLOGsection ofSHOW ENGINE INNODB STATUSoutput, or monitoringInnodb_os_log_writtenstatus for the number of bytes written to the redo log files.For related information, see Section 17.9.1.6, “Compression for OLTP Workloads”.
-
Command-Line Format --innodb-log-file-size=#Deprecated Yes System Variable innodb_log_file_sizeScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 50331648Minimum Value 4194304Maximum Value 512GB / innodb_log_files_in_groupUnit bytes Noteinnodb_log_file_sizeandinnodb_log_files_in_grouphave been superseded byinnodb_redo_log_capacity; see Section 17.6.5, “Redo Log”.The size in bytes of each log file in a log group. The combined size of log files (
innodb_log_file_size*innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 512GB. A pair of 255 GB log files, for example, approaches the limit but does not exceed it. The default value is 48MB.Generally, the combined size of the log files should be large enough that the server can smooth out peaks and troughs in workload activity, which often means that there is enough redo log space to handle more than an hour of write activity. The larger the value, the less checkpoint flush activity is required in the buffer pool, saving disk I/O. Larger log files also make crash recovery slower.
The minimum
innodb_log_file_sizeis 4MB.For related information, see Redo Log Configuration. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.
If the server is started with
--innodb-dedicated-server, the value ofinnodb_log_file_sizeis set automatically if it is not explicitly defined. For more information, see Section 17.8.12, “Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server”. -
Command-Line Format --innodb-log-files-in-group=#Deprecated Yes System Variable innodb_log_files_in_groupScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 2Minimum Value 2Maximum Value 100Noteinnodb_log_file_sizeandinnodb_log_files_in_grouphave been superseded byinnodb_redo_log_capacity; see Section 17.6.5, “Redo Log”.The number of log files in the log group.
InnoDBwrites to the files in a circular fashion. The default (and recommended) value is 2. The location of the files is specified byinnodb_log_group_home_dir. The combined size of log files (innodb_log_file_size*innodb_log_files_in_group) can be up to 512GB.For related information, see Redo Log Configuration.
If the server is started with
--innodb-dedicated-server, the value ofinnodb_log_files_in_groupis set automatically if it is not explicitly defined. For more information, see Section 17.8.12, “Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server”. -
Command-Line Format --innodb-log-group-home-dir=dir_nameSystem Variable innodb_log_group_home_dirScope Global Dynamic No SET_VARHint AppliesNo Type Directory name The directory path to the
InnoDBredo log files.For related information, see Redo Log Configuration.
-
Command-Line Format --innodb-log-spin-cpu-abs-lwm=#System Variable innodb_log_spin_cpu_abs_lwmScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 80Minimum Value 0Maximum Value 4294967295Defines the minimum amount of CPU usage below which user threads no longer spin while waiting for flushed redo. The value is expressed as a sum of CPU core usage. For example, The default value of 80 is 80% of a single CPU core. On a system with a multi-core processor, a value of 150 represents 100% usage of one CPU core plus 50% usage of a second CPU core.
For related information, see Section 10.5.4, “Optimizing InnoDB Redo Logging”.
-
Command-Line Format --innodb-log-spin-cpu-pct-hwm=#System Variable innodb_log_spin_cpu_pct_hwmScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 50Minimum Value 0Maximum Value 100Defines the maximum amount of CPU usage above which user threads no longer spin while waiting for flushed redo. The value is expressed as a percentage of the combined total processing power of all CPU cores. The default value is 50%. For example, 100% usage of two CPU cores is 50% of the combined CPU processing power on a server with four CPU cores.
The
innodb_log_spin_cpu_pct_hwmvariable respects processor affinity. For example, if a server has 48 cores but the mysqld process is pinned to only four CPU cores, the other 44 CPU cores are ignored.For related information, see Section 10.5.4, “Optimizing InnoDB Redo Logging”.
innodb_log_wait_for_flush_spin_hwmCommand-Line Format --innodb-log-wait-for-flush-spin-hwm=#System Variable innodb_log_wait_for_flush_spin_hwmScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 400Minimum Value 0Maximum Value (64-bit platforms, 8.4.0) 2**64-1Maximum Value 2**32-1Unit microseconds Defines the maximum average log flush time beyond which user threads no longer spin while waiting for flushed redo. The default value is 400 microseconds.
For related information, see Section 10.5.4, “Optimizing InnoDB Redo Logging”.
-
Command-Line Format --innodb-log-write-ahead-size=#System Variable innodb_log_write_ahead_sizeScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 8192Minimum Value 512 (log file block size)Maximum Value Equal to innodb_page_sizeUnit bytes Defines the write-ahead block size for the redo log, in bytes. To avoid “read-on-write”, set
innodb_log_write_ahead_sizeto match the operating system or file system cache block size. The default setting is 8192 bytes. Read-on-write occurs when redo log blocks are not entirely cached to the operating system or file system due to a mismatch between write-ahead block size for the redo log and operating system or file system cache block size.Valid values for
innodb_log_write_ahead_sizeare multiples of theInnoDBlog file block size (2n). The minimum value is theInnoDBlog file block size (512). Write-ahead does not occur when the minimum value is specified. The maximum value is equal to theinnodb_page_sizevalue. If you specify a value forinnodb_log_write_ahead_sizethat is larger than theinnodb_page_sizevalue, theinnodb_log_write_ahead_sizesetting is truncated to theinnodb_page_sizevalue.Setting the
innodb_log_write_ahead_sizevalue too low in relation to the operating system or file system cache block size results in “read-on-write”. Setting the value too high may have a slight impact onfsyncperformance for log file writes due to several blocks being written at once.For related information, see Section 10.5.4, “Optimizing InnoDB Redo Logging”.
-
Command-Line Format --innodb-log-writer-threads[={OFF|ON}]System Variable innodb_log_writer_threadsScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONEnables dedicated log writer threads for writing redo log records from the log buffer to the system buffers and flushing the system buffers to the redo log files. Dedicated log writer threads can improve performance on high-concurrency systems, but for low-concurrency systems, disabling dedicated log writer threads provides better performance.
For more information, see Section 10.5.4, “Optimizing InnoDB Redo Logging”.
-
Command-Line Format --innodb-lru-scan-depth=#System Variable innodb_lru_scan_depthScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 1024Minimum Value 100Maximum Value (64-bit platforms, 8.4.0) 2**64-1Maximum Value 2**32-1A parameter that influences the algorithms and heuristics for the flush operation for the
InnoDBbuffer pool. Primarily of interest to performance experts tuning I/O-intensive workloads. It specifies, per buffer pool instance, how far down the buffer pool LRU page list the page cleaner thread scans looking for dirty pages to flush. This is a background operation performed once per second.A setting smaller than the default is generally suitable for most workloads. A value that is much higher than necessary may impact performance. Only consider increasing the value if you have spare I/O capacity under a typical workload. Conversely, if a write-intensive workload saturates your I/O capacity, decrease the value, especially in the case of a large buffer pool.
When tuning
innodb_lru_scan_depth, start with a low value and configure the setting upward with the goal of rarely seeing zero free pages. Also, consider adjustinginnodb_lru_scan_depthwhen changing the number of buffer pool instances, sinceinnodb_lru_scan_depth*innodb_buffer_pool_instancesdefines the amount of work performed by the page cleaner thread each second.For related information, see Section 17.8.3.5, “Configuring Buffer Pool Flushing”. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.
-
Command-Line Format --innodb-max-dirty-pages-pct=#System Variable innodb_max_dirty_pages_pctScope Global Dynamic Yes SET_VARHint AppliesNo Type Numeric Default Value 90Minimum Value 0Maximum Value 99.999InnoDBtries to flush data from the buffer pool so that the percentage of dirty pages does not exceed this value.The
innodb_max_dirty_pages_pctsetting establishes a target for flushing activity. It does not affect the rate of flushing. For information about managing the rate of flushing, see Section 17.8.3.5, “Configuring Buffer Pool Flushing”.For related information, see Section 17.8.3.5, “Configuring Buffer Pool Flushing”. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.
innodb_max_dirty_pages_pct_lwmCommand-Line Format --innodb-max-dirty-pages-pct-lwm=#System Variable innodb_max_dirty_pages_pct_lwmScope Global Dynamic Yes SET_VARHint AppliesNo Type Numeric Default Value 10Minimum Value 0Maximum Value 99.999Defines a low water mark representing the percentage of dirty pages at which preflushing is enabled to control the dirty page ratio. A value of 0 disables the pre-flushing behavior entirely. The configured value should always be lower than the
innodb_max_dirty_pages_pctvalue. For more information, see Section 17.8.3.5, “Configuring Buffer Pool Flushing”.-
Command-Line Format --innodb-max-purge-lag=#System Variable innodb_max_purge_lagScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 4294967295Defines the desired maximum purge lag. If this value is exceeded, a delay is imposed on
INSERT,UPDATE, andDELETEoperations to allow time for purge to catch up. The default value is 0, which means there is no maximum purge lag and no delay.For more information, see Section 17.8.9, “Purge Configuration”.
-
Command-Line Format --innodb-max-purge-lag-delay=#System Variable innodb_max_purge_lag_delayScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 10000000Unit microseconds Specifies the maximum delay in microseconds for the delay imposed when the
innodb_max_purge_lagthreshold is exceeded. The specifiedinnodb_max_purge_lag_delayvalue is an upper limit on the delay period calculated by theinnodb_max_purge_lagformula.For more information, see Section 17.8.9, “Purge Configuration”.
-
Command-Line Format --innodb-max-undo-log-size=#System Variable innodb_max_undo_log_sizeScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 1073741824Minimum Value 10485760Maximum Value 2**64-1Unit bytes Defines a threshold size for undo tablespaces. If an undo tablespace exceeds the threshold, it can be marked for truncation when
innodb_undo_log_truncateis enabled. The default value is 1073741824 bytes (1024 MiB).For more information, see Truncating Undo Tablespaces.
innodb_merge_threshold_set_all_debugCommand-Line Format --innodb-merge-threshold-set-all-debug=#System Variable innodb_merge_threshold_set_all_debugScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 50Minimum Value 1Maximum Value 50Defines a page-full percentage value for index pages that overrides the current
MERGE_THRESHOLDsetting for all indexes that are currently in the dictionary cache. This option is only available if debugging support is compiled in using theWITH_DEBUGCMake option. For related information, see Section 17.8.11, “Configuring the Merge Threshold for Index Pages”.-
Command-Line Format --innodb-monitor-disable={counter|module|pattern|all}System Variable innodb_monitor_disableScope Global Dynamic Yes SET_VARHint AppliesNo Type String This variable acts as a switch, disabling
InnoDBmetrics counters. Counter data may be queried using the Information SchemaINNODB_METRICStable. For usage information, see Section 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.innodb_monitor_disable='latch'disables statistics collection forSHOW ENGINE INNODB MUTEX. For more information, see Section 15.7.7.16, “SHOW ENGINE Statement”. -
Command-Line Format --innodb-monitor-enable={counter|module|pattern|all}System Variable innodb_monitor_enableScope Global Dynamic Yes SET_VARHint AppliesNo Type String This variable acts as a switch, enabling
InnoDBmetrics counters. Counter data may be queried using the Information SchemaINNODB_METRICStable. For usage information, see Section 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.innodb_monitor_enable='latch'enables statistics collection forSHOW ENGINE INNODB MUTEX. For more information, see Section 15.7.7.16, “SHOW ENGINE Statement”. -
Command-Line Format --innodb-monitor-reset={counter|module|pattern|all}System Variable innodb_monitor_resetScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value NULLValid Values countermodulepatternallThis variable acts as a switch, resetting the count value for
InnoDBmetrics counters to zero. Counter data may be queried using the Information SchemaINNODB_METRICStable. For usage information, see Section 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”.innodb_monitor_reset='latch'resets statistics reported bySHOW ENGINE INNODB MUTEX. For more information, see Section 15.7.7.16, “SHOW ENGINE Statement”. -
Command-Line Format --innodb-monitor-reset-all={counter|module|pattern|all}System Variable innodb_monitor_reset_allScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value NULLValid Values countermodulepatternallThis variable acts as a switch, resetting all values (minimum, maximum, and so on) for
InnoDBmetrics counters. Counter data may be queried using the Information SchemaINNODB_METRICStable. For usage information, see Section 17.15.6, “InnoDB INFORMATION_SCHEMA Metrics Table”. -
Command-Line Format --innodb-numa-interleave[={OFF|ON}]System Variable innodb_numa_interleaveScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value ONEnables the NUMA interleave memory policy for allocation of the
InnoDBbuffer pool. Wheninnodb_numa_interleaveis enabled, the NUMA memory policy is set toMPOL_INTERLEAVEfor the mysqld process. After theInnoDBbuffer pool is allocated, the NUMA memory policy is set back toMPOL_DEFAULT. For theinnodb_numa_interleaveoption to be available, MySQL must be compiled on a NUMA-enabled Linux system. The default value isONif the system supports it, otherwise it defaults toOFF.CMake sets the default
WITH_NUMAvalue based on whether the current platform hasNUMAsupport. For more information, see Section 2.8.7, “MySQL Source-Configuration Options”. -
Command-Line Format --innodb-old-blocks-pct=#System Variable innodb_old_blocks_pctScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 37Minimum Value 5Maximum Value 95Specifies the approximate percentage of the
InnoDBbuffer pool used for the old block sublist. The range of values is 5 to 95. The default value is 37 (that is, 3/8 of the pool). Often used in combination withinnodb_old_blocks_time.For more information, see Section 17.8.3.3, “Making the Buffer Pool Scan Resistant”. For information about buffer pool management, the LRU algorithm, and eviction policies, see Section 17.5.1, “Buffer Pool”.
-
Command-Line Format --innodb-old-blocks-time=#System Variable innodb_old_blocks_timeScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 1000Minimum Value 0Maximum Value 2**32-1Unit milliseconds Non-zero values protect against the buffer pool being filled by data that is referenced only for a brief period, such as during a full table scan. Increasing this value offers more protection against full table scans interfering with data cached in the buffer pool.
Specifies how long in milliseconds a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. If the value is 0, a block inserted into the old sublist moves immediately to the new sublist the first time it is accessed, no matter how soon after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many milliseconds after the first access. For example, a value of 1000 causes blocks to stay in the old sublist for 1 second after the first access before they become eligible to move to the new sublist.
The default value is 1000.
This variable is often used in combination with
innodb_old_blocks_pct. For more information, see Section 17.8.3.3, “Making the Buffer Pool Scan Resistant”. For information about buffer pool management, the LRU algorithm, and eviction policies, see Section 17.5.1, “Buffer Pool”. innodb_online_alter_log_max_sizeCommand-Line Format --innodb-online-alter-log-max-size=#System Variable innodb_online_alter_log_max_sizeScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 134217728Minimum Value 65536Maximum Value 2**64-1Unit bytes Specifies an upper limit in bytes on the size of the temporary log files used during online DDL operations for
InnoDBtables. There is one such log file for each index being created or table being altered. This log file stores data inserted, updated, or deleted in the table during the DDL operation. The temporary log file is extended when needed by the value ofinnodb_sort_buffer_size, up to the maximum specified byinnodb_online_alter_log_max_size. If a temporary log file exceeds the upper size limit, theALTER TABLEoperation fails and all uncommitted concurrent DML operations are rolled back. Thus, a large value for this option allows more DML to happen during an online DDL operation, but also extends the period of time at the end of the DDL operation when the table is locked to apply the data from the log.-
Command-Line Format --innodb-open-files=#System Variable innodb_open_filesScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value -1(signifies autosizing; do not assign this literal value)Minimum Value 10Maximum Value 2147483647Specifies the maximum number of files that
InnoDBcan have open at one time. The minimum value is 10. Ifinnodb_file_per_tableis disabled, the default value is 300; otherwise, the default value is 300 or thetable_open_cachesetting, whichever is higher.The
innodb_open_fileslimit can be set at runtime using aSELECT innodb_set_open_files_limit(statement, whereN)Nis the desiredinnodb_open_fileslimit; for example:mysql> SELECT innodb_set_open_files_limit(1000);The statement executes a stored procedure that sets the new limit. If the procedure is successful, it returns the value of the newly set limit; otherwise, a failure message is returned.
It is not permitted to set
innodb_open_filesusing aSETstatement. To setinnodb_open_filesat runtime, use theSELECT innodb_set_open_files_limit(statement described above.N)Setting
innodb_open_files=defaultis not supported. Only integer values are permitted.To prevent non-LRU managed files from consuming the entire
innodb_open_fileslimit, non-LRU managed files are limited to 90 percent of this limit, which reserves 10 percent of it for LRU managed files. -
Command-Line Format --innodb-optimize-fulltext-only[={OFF|ON}]System Variable innodb_optimize_fulltext_onlyScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFChanges the way
OPTIMIZE TABLEoperates onInnoDBtables. Intended to be enabled temporarily, during maintenance operations forInnoDBtables withFULLTEXTindexes.By default,
OPTIMIZE TABLEreorganizes data in the clustered index of the table. When this option is enabled,OPTIMIZE TABLEskips the reorganization of table data, and instead processes newly added, deleted, and updated token data forInnoDBFULLTEXTindexes. For more information, see Optimizing InnoDB Full-Text Indexes. -
Command-Line Format --innodb-page-cleaners=#System Variable innodb_page_cleanersScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value innodb_buffer_pool_instancesMinimum Value 1Maximum Value 64The number of page cleaner threads that flush dirty pages from buffer pool instances. Page cleaner threads perform flush list and LRU flushing. When there are multiple page cleaner threads, buffer pool flushing tasks for each buffer pool instance are dispatched to idle page cleaner threads. The
innodb_page_cleanersdefault value is set to the same value asinnodb_buffer_pool_instances. If the specified number of page cleaner threads exceeds the number of buffer pool instances, theninnodb_page_cleanersis automatically set to the same value asinnodb_buffer_pool_instances.If your workload is write-IO bound when flushing dirty pages from buffer pool instances to data files, and if your system hardware has available capacity, increasing the number of page cleaner threads may help improve write-IO throughput.
Multithreaded page cleaner support extends to shutdown and recovery phases.
The
setpriority()system call is used on Linux platforms where it is supported, and where the mysqld execution user is authorized to givepage_cleanerthreads priority over other MySQL andInnoDBthreads to help page flushing keep pace with the current workload.setpriority()support is indicated by thisInnoDBstartup message:[Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().For systems where server startup and shutdown is not managed by systemd, mysqld execution user authorization can be configured in
/etc/security/limits.conf. For example, if mysqld is run under themysqluser, you can authorize themysqluser by adding these lines to/etc/security/limits.conf:mysql hard nice -20 mysql soft nice -20For systemd managed systems, the same can be achieved by specifying
LimitNICE=-20in a localized systemd configuration file. For example, create a file namedoverride.confin/etc/systemd/system/mysqld.service.d/override.confand add this entry:[Service] LimitNICE=-20After creating or changing
override.conf, reload the systemd configuration, then tell systemd to restart the MySQL service:systemctl daemon-reload systemctl restart mysqld # RPM platforms systemctl restart mysql # Debian platformsFor more information about using a localized systemd configuration file, see Configuring systemd for MySQL.
After authorizing the mysqld execution user, use the cat command to verify the configured
Nicelimits for the mysqld process:$> cat /proc/mysqld_pid/limits | grep nice Max nice priority 18446744073709551596 18446744073709551596 -
Command-Line Format --innodb-page-size=#System Variable innodb_page_sizeScope Global Dynamic No SET_VARHint AppliesNo Type Enumeration Default Value 16384Valid Values 40968192163843276865536Specifies the page size for
InnoDBtablespaces. Values can be specified in bytes or kilobytes. For example, a 16 kilobyte page size value can be specified as 16384, 16KB, or 16k.innodb_page_sizecan only be configured prior to initializing the MySQL instance and cannot be changed afterward. If no value is specified, the instance is initialized using the default page size. See Section 17.8.1, “InnoDB Startup Configuration”.For both 32KB and 64KB page sizes, the maximum row length is approximately 16000 bytes.
ROW_FORMAT=COMPRESSEDis not supported wheninnodb_page_sizeis set to 32KB or 64KB. Forinnodb_page_size=32KB, extent size is 2MB. Forinnodb_page_size=64KB, extent size is 4MB.innodb_log_buffer_sizeshould be set to at least 16MB (the default is 64MB) when using 32KB or 64KB page sizes.The default 16KB page size or larger is appropriate for a wide range of workloads, particularly for queries involving table scans and DML operations involving bulk updates. Smaller page sizes might be more efficient for OLTP workloads involving many small writes, where contention can be an issue when single pages contain many rows. Smaller pages might also be efficient with SSD storage devices, which typically use small block sizes. Keeping the
InnoDBpage size close to the storage device block size minimizes the amount of unchanged data that is rewritten to disk.The minimum file size for the first system tablespace data file (
ibdata1) differs depending on theinnodb_page_sizevalue. See theinnodb_data_file_pathoption description for more information.A MySQL instance using a particular
InnoDBpage size cannot use data files or log files from an instance that uses a different page size.For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.
-
Command-Line Format --innodb-parallel-read-threads=#System Variable innodb_parallel_read_threadsScope Session Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value (available logical processors / 8), min of 4Minimum Value 1Maximum Value 256Defines the number of threads that can be used for parallel clustered index reads. Parallel scanning of partitions is also supported. Parallel read threads can improve
CHECK TABLEperformance.InnoDBreads the clustered index twice during aCHECK TABLEoperation. The second read can be performed in parallel. This feature does not apply to secondary index scans. Theinnodb_parallel_read_threadssession variable must be set to a value greater than 1 for parallel clustered index reads to occur. The actual number of threads used to perform a parallel clustered index read is determined by theinnodb_parallel_read_threadssetting or the number of index subtrees to scan, whichever is smaller. The pages read into the buffer pool during the scan are kept at the tail of the buffer pool LRU list so that they can be discarded quickly when free buffer pool pages are required.The maximum number of parallel read threads (256) is the total number of threads for all client connections. If the thread limit is reached, connections fall back to using a single thread. The default value is calculated by the number of available logical processors on the system divided by 8, with a minimum default value of 4.
Before MySQL 8.4, the default value was always 4.
-
Command-Line Format --innodb-print-all-deadlocks[={OFF|ON}]System Variable innodb_print_all_deadlocksScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFWhen this option is enabled, information about all deadlocks in
InnoDBuser transactions is recorded in themysqlderror log. Otherwise, you see information about only the last deadlock, using theSHOW ENGINE INNODB STATUSstatement. An occasionalInnoDBdeadlock is not necessarily an issue, becauseInnoDBdetects the condition immediately and rolls back one of the transactions automatically. You might use this option to troubleshoot why deadlocks are occurring if an application does not have appropriate error-handling logic to detect the rollback and retry its operation. A large number of deadlocks might indicate the need to restructure transactions that issue DML orSELECT ... FOR UPDATEstatements for multiple tables, so that each transaction accesses the tables in the same order, thus avoiding the deadlock condition.For related information, see Section 17.7.5, “Deadlocks in InnoDB”.
-
Command-Line Format --innodb-print-ddl-logs[={OFF|ON}]System Variable innodb_print_ddl_logsScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFEnabling this option causes MySQL to write DDL logs to
stderr. For more information, see Viewing DDL Logs. -
Command-Line Format --innodb-purge-batch-size=#System Variable innodb_purge_batch_sizeScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 300Minimum Value 1Maximum Value 5000Defines the number of undo log pages that purge parses and processes in one batch from the history list. In a multithreaded purge configuration, the coordinator purge thread divides
innodb_purge_batch_sizebyinnodb_purge_threadsand assigns that number of pages to each purge thread. Theinnodb_purge_batch_sizevariable also defines the number of undo log pages that purge frees after every 128 iterations through the undo logs.The
innodb_purge_batch_sizeoption is intended for advanced performance tuning in combination with theinnodb_purge_threadssetting. Most users need not changeinnodb_purge_batch_sizefrom its default value.For related information, see Section 17.8.9, “Purge Configuration”.
-
Command-Line Format --innodb-purge-threads=#System Variable innodb_purge_threadsScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 1 if # of available logical processors is <= 16; otherwise 4Minimum Value 1Maximum Value 32The number of background threads devoted to the
InnoDBpurge operation. Increasing the value creates additional purge threads, which can improve efficiency on systems where DML operations are performed on multiple tables.For related information, see Section 17.8.9, “Purge Configuration”.
innodb_purge_rseg_truncate_frequencyCommand-Line Format --innodb-purge-rseg-truncate-frequency=#System Variable innodb_purge_rseg_truncate_frequencyScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 128Minimum Value 1Maximum Value 128Defines the frequency with which the purge system frees rollback segments in terms of the number of times that purge is invoked. An undo tablespace cannot be truncated until its rollback segments are freed. Normally, the purge system frees rollback segments once every 128 times that purge is invoked. The default value is 128. Reducing this value increases the frequency with which the purge thread frees rollback segments.
innodb_purge_rseg_truncate_frequencyis intended for use withinnodb_undo_log_truncate. For more information, see Truncating Undo Tablespaces.-
Command-Line Format --innodb-random-read-ahead[={OFF|ON}]System Variable innodb_random_read_aheadScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFEnables the random read-ahead technique for optimizing
InnoDBI/O.For details about performance considerations for different types of read-ahead requests, see Section 17.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.
-
Command-Line Format --innodb-read-ahead-threshold=#System Variable innodb_read_ahead_thresholdScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 56Minimum Value 0Maximum Value 64Controls the sensitivity of linear read-ahead that
InnoDBuses to prefetch pages into the buffer pool. IfInnoDBreads at leastinnodb_read_ahead_thresholdpages sequentially from an extent (64 pages), it initiates an asynchronous read for the entire following extent. The permissible range of values is 0 to 64. A value of 0 disables read-ahead. For the default of 56,InnoDBmust read at least 56 pages sequentially from an extent to initiate an asynchronous read for the following extent.Knowing how many pages are read through the read-ahead mechanism, and how many of these pages are evicted from the buffer pool without ever being accessed, can be useful when fine-tuning the
innodb_read_ahead_thresholdsetting.SHOW ENGINE INNODB STATUSoutput displays counter information from theInnodb_buffer_pool_read_aheadandInnodb_buffer_pool_read_ahead_evictedglobal status variables, which report the number of pages brought into the buffer pool by read-ahead requests, and the number of such pages evicted from the buffer pool without ever being accessed, respectively. The status variables report global values since the last server restart.SHOW ENGINE INNODB STATUSalso shows the rate at which the read-ahead pages are read and the rate at which such pages are evicted without being accessed. The per-second averages are based on the statistics collected since the last invocation ofSHOW ENGINE INNODB STATUSand are displayed in theBUFFER POOL AND MEMORYsection of theSHOW ENGINE INNODB STATUSoutput.For more information, see Section 17.8.3.4, “Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)”. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.
-
Command-Line Format --innodb-read-io-threads=#System Variable innodb_read_io_threadsScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value (available logical processors / 2), min of 4Minimum Value 1Maximum Value 64The number of I/O threads for read operations in
InnoDB. Its counterpart for write threads isinnodb_write_io_threads. For more information, see Section 17.8.5, “Configuring the Number of Background InnoDB I/O Threads”. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”. The default value is the number of available logical processors on the system divided by 2, with a minimum default value of 4.Before MySQL 8.4, the default value was always 4.
NoteOn Linux systems, running multiple MySQL servers (typically more than 12) with default settings for
innodb_read_io_threads,innodb_write_io_threads, and the Linuxaio-max-nrsetting can exceed system limits. Ideally, increase theaio-max-nrsetting; as a workaround, you might reduce the settings for one or both of the MySQL variables. -
Command-Line Format --innodb-read-only[={OFF|ON}]System Variable innodb_read_onlyScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value OFFStarts
InnoDBin 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. For more information, see Section 17.8.2, “Configuring InnoDB for Read-Only Operation”.Enabling
innodb_read_onlyprevents creating and dropping tables for all storage engines, and not onlyInnoDB. Table creation and drop operations for any storage engine modify data dictionary tables in themysqlsystem database, but those tables use theInnoDBstorage engine and cannot be modified wheninnodb_read_onlyis enabled. The same principle applies to other table operations that require modifying data dictionary tables. Examples:If the
innodb_read_onlysystem variable is enabled,ANALYZE TABLEmay fail because it cannot update statistics tables in the data dictionary, which useInnoDB. ForANALYZE TABLEoperations that update the key distribution, failure may occur even if the operation updates the table itself (for example, if it is aMyISAMtable). To obtain the updated distribution statistics, setinformation_schema_stats_expiry=0.ALTER TABLEfails because it updates the storage engine designation, which is stored in the data dictionary.tbl_nameENGINE=engine_name
In addition, other tables in the
mysqlsystem database use theInnoDBstorage engine. Making those tables read-only results in restrictions on operations that modify them. Examples:Account-management statements such as
CREATE USERandGRANTfail because the grant tables useInnoDB.The
INSTALL PLUGINandUNINSTALL PLUGINplugin-management statements fail because themysql.pluginsystem table usesInnoDB.The
CREATE FUNCTIONandDROP FUNCTIONloadable function-management statements fail because themysql.funcsystem table usesInnoDB.
-
Command-Line Format --innodb-redo-log-archive-dirsSystem Variable innodb_redo_log_archive_dirsScope Global Dynamic Yes SET_VARHint AppliesNo Type String Default Value NULLDefines labeled directories where redo log archive files can be created. You can define multiple labeled directories in a semicolon-separated list. For example:
innodb_redo_log_archive_dirs='label1:/backups1;label2:/backups2'A label can be any string of characters, with the exception of colons (:), which are not permitted. An empty label is also permitted, but the colon (:) is still required in this case.
A path must be specified, and the directory must exist. The path can contain colons (':'), but semicolons (;) are not permitted.
-
Command-Line Format --innodb-redo-log-capacity=#System Variable innodb_redo_log_capacityScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 104857600Minimum Value 8388608Maximum Value 549755813888Unit bytes Defines the amount of disk space occupied by redo log files.
innodb_redo_log_capacitysupercedes theinnodb_log_files_in_groupandinnodb_log_file_sizevariables, which are both ignored ifinnodb_redo_log_capacityis defined.If
innodb_redo_log_capacityis not defined, and if neitherinnodb_log_file_sizeorinnodb_log_files_in_groupare defined, then the defaultinnodb_redo_log_capacityvalue is used.If
innodb_redo_log_capacityis not defined, and ifinnodb_log_file_sizeand/orinnodb_log_files_in_groupis defined, then the InnoDB redo log capacity is calculated as (innodb_log_files_in_group * innodb_log_file_size). This calculation does not modify the unusedinnodb_redo_log_capacitysetting's value.The
Innodb_redo_log_capacity_resizedserver status variable indicates the total redo log capacity for all redo log files.If the server is started with
--innodb-dedicated-server, the value ofinnodb_redo_log_capacityis set automatically if it is not explicitly defined. For more information, see Section 17.8.12, “Enabling Automatic InnoDB Configuration for a Dedicated MySQL Server”.For more information, see Section 17.6.5, “Redo Log”.
-
Command-Line Format --innodb-redo-log-encrypt[={OFF|ON}]System Variable innodb_redo_log_encryptScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFControls encryption of redo log data for tables encrypted using the
InnoDBdata-at-rest encryption feature. Encryption of redo log data is disabled by default. For more information, see Redo Log Encryption. -
Command-Line Format --innodb-replication-delay=#System Variable innodb_replication_delayScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 4294967295Unit milliseconds The replication thread delay in milliseconds on a replica server if
innodb_thread_concurrencyis reached. -
Command-Line Format --innodb-rollback-on-timeout[={OFF|ON}]System Variable innodb_rollback_on_timeoutScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value OFFInnoDBrolls back only the last statement on a transaction timeout by default. If--innodb-rollback-on-timeoutis specified, a transaction timeout causesInnoDBto abort and roll back the entire transaction.For more information, see Section 17.20.5, “InnoDB Error Handling”.
-
Command-Line Format --innodb-rollback-segments=#System Variable innodb_rollback_segmentsScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 128Minimum Value 1Maximum Value 128innodb_rollback_segmentsdefines the number of rollback segments allocated to each undo tablespace and the global temporary tablespace for transactions that generate undo records. The number of transactions that each rollback segment supports depends on theInnoDBpage size and the number of undo logs assigned to each transaction. For more information, see Section 17.6.6, “Undo Logs”.For related information, see Section 17.3, “InnoDB Multi-Versioning”. For information about undo tablespaces, see Section 17.6.3.4, “Undo Tablespaces”.
innodb_saved_page_number_debugCommand-Line Format --innodb-saved-page-number-debug=#System Variable innodb_saved_page_number_debugScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 2**32-1Saves a page number. Setting the
innodb_fil_make_page_dirty_debugoption dirties the page defined byinnodb_saved_page_number_debug. Theinnodb_saved_page_number_debugoption is only available if debugging support is compiled in using theWITH_DEBUGCMake option.-
Command-Line Format --innodb-segment-reserve-factor=#System Variable innodb_segment_reserve_factorScope Global Dynamic Yes SET_VARHint AppliesNo Type Numeric Default Value 12.5Minimum Value 0.03Maximum Value 40Defines the percentage of tablespace file segment pages reserved as empty pages. The setting is applicable to file-per-table and general tablespaces. The
innodb_segment_reserve_factordefault setting is 12.5 percent, which is the same percentage of pages reserved in previous MySQL releases.For more information, see Configuring the Percentage of Reserved File Segment Pages.
-
Command-Line Format --innodb-sort-buffer-size=#System Variable innodb_sort_buffer_sizeScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 1048576Minimum Value 65536Maximum Value 67108864Unit bytes This variable defines the amount by which the temporary log file is extended when recording concurrent DML during an online DDL operation, and the size of the temporary log file read buffer and write buffer.
For more information, see Section 17.12.3, “Online DDL Space Requirements”.
-
Command-Line Format --innodb-spin-wait-delay=#System Variable innodb_spin_wait_delayScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 6Minimum Value 0Maximum Value 1000The maximum delay between polls for a spin lock. The low-level implementation of this mechanism varies depending on the combination of hardware and operating system, so the delay does not correspond to a fixed time interval.
Can be used in combination with the
innodb_spin_wait_pause_multipliervariable for greater control over the duration of spin-lock polling delays.For more information, see Section 17.8.8, “Configuring Spin Lock Polling”.
innodb_spin_wait_pause_multiplierCommand-Line Format --innodb-spin-wait-pause-multiplier=#System Variable innodb_spin_wait_pause_multiplierScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 50Minimum Value 0Maximum Value 100Defines a multiplier value used to determine the number of PAUSE instructions in spin-wait loops that occur when a thread waits to acquire a mutex or rw-lock.
For more information, see Section 17.8.8, “Configuring Spin Lock Polling”.
-
Command-Line Format --innodb-stats-auto-recalc[={OFF|ON}]System Variable innodb_stats_auto_recalcScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONCauses
InnoDBto automatically recalculate persistent statistics after the data in a table is changed substantially. The threshold value is 10% of the rows in the table. This setting applies to tables created when theinnodb_stats_persistentoption is enabled. Automatic statistics recalculation may also be configured by specifyingSTATS_AUTO_RECALC=1in aCREATE TABLEorALTER TABLEstatement. The amount of data sampled to produce the statistics is controlled by theinnodb_stats_persistent_sample_pagesvariable.For more information, see Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.
innodb_stats_include_delete_markedCommand-Line Format --innodb-stats-include-delete-marked[={OFF|ON}]System Variable innodb_stats_include_delete_markedScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFBy default,
InnoDBreads uncommitted data when calculating statistics. In the case of an uncommitted transaction that deletes rows from a table,InnoDBexcludes records that are delete-marked when calculating row estimates and index statistics, which can lead to non-optimal execution plans for other transactions that are operating on the table concurrently using a transaction isolation level other thanREAD UNCOMMITTED. To avoid this scenario,innodb_stats_include_delete_markedcan be enabled to ensure thatInnoDBincludes delete-marked records when calculating persistent optimizer statistics.When
innodb_stats_include_delete_markedis enabled,ANALYZE TABLEconsiders delete-marked records when recalculating statistics.innodb_stats_include_delete_markedis a global setting that affects allInnoDBtables. It is only applicable to persistent optimizer statistics.For related information, see Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.
-
Command-Line Format --innodb-stats-method=valueSystem Variable innodb_stats_methodScope Global Dynamic Yes SET_VARHint AppliesNo Type Enumeration Default Value nulls_equalValid Values nulls_equalnulls_unequalnulls_ignoredHow the server treats
NULLvalues when collecting statistics about the distribution of index values forInnoDBtables. Permitted values arenulls_equal,nulls_unequal, andnulls_ignored. Fornulls_equal, allNULLindex values are considered equal and form a single value group with a size equal to the number ofNULLvalues. Fornulls_unequal,NULLvalues are considered unequal, and eachNULLforms a distinct value group of size 1. Fornulls_ignored,NULLvalues are ignored.The method used to generate table statistics influences how the optimizer chooses indexes for query execution, as described in Section 10.3.8, “InnoDB and MyISAM Index Statistics Collection”.
-
Command-Line Format --innodb-stats-on-metadata[={OFF|ON}]System Variable innodb_stats_on_metadataScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFThis option only applies when optimizer statistics are configured to be non-persistent. Optimizer statistics are not persisted to disk when
innodb_stats_persistentis disabled or when individual tables are created or altered withSTATS_PERSISTENT=0. For more information, see Section 17.8.10.2, “Configuring Non-Persistent Optimizer Statistics Parameters”.When
innodb_stats_on_metadatais enabled,InnoDBupdates non-persistent statistics when metadata statements such asSHOW TABLE STATUSor when accessing the Information SchemaTABLESorSTATISTICStables. (These updates are similar to what happens forANALYZE TABLE.) When disabled,InnoDBdoes not update statistics during these operations. Leaving the setting disabled can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involveInnoDBtables.To change the setting, issue the statement
SET GLOBAL innodb_stats_on_metadata=, wheremodemodeONorOFF(or1or0). Changing the setting requires privileges sufficient to set global system variables (see Section 7.1.9.1, “System Variable Privileges”) and immediately affects the operation of all connections. -
Command-Line Format --innodb-stats-persistent[={OFF|ON}]System Variable innodb_stats_persistentScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONSpecifies whether
InnoDBindex statistics are persisted to disk. Otherwise, statistics may be recalculated frequently which can lead to variations in query execution plans. This setting is stored with each table when the table is created. You can setinnodb_stats_persistentat the global level before creating a table, or use theSTATS_PERSISTENTclause of theCREATE TABLEandALTER TABLEstatements to override the system-wide setting and configure persistent statistics for individual tables.For more information, see Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.
innodb_stats_persistent_sample_pagesCommand-Line Format --innodb-stats-persistent-sample-pages=#System Variable innodb_stats_persistent_sample_pagesScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 20Minimum Value 1Maximum Value 18446744073709551615The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by
ANALYZE TABLE. Increasing the value improves the accuracy of index statistics, which can improve the query execution plan, at the expense of increased I/O during the execution ofANALYZE TABLEfor anInnoDBtable. For more information, see Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”.NoteSetting a high value for
innodb_stats_persistent_sample_pagescould result in lengthyANALYZE TABLEexecution time. To estimate the number of database pages accessed byANALYZE TABLE, see Section 17.8.10.3, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”.innodb_stats_persistent_sample_pagesonly applies wheninnodb_stats_persistentis enabled for a table; wheninnodb_stats_persistentis disabled,innodb_stats_transient_sample_pagesapplies instead.innodb_stats_transient_sample_pagesCommand-Line Format --innodb-stats-transient-sample-pages=#System Variable innodb_stats_transient_sample_pagesScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 8Minimum Value 1Maximum Value 18446744073709551615The number of index pages to sample when estimating cardinality and other statistics for an indexed column, such as those calculated by
ANALYZE TABLE. The default value is 8. Increasing the value improves the accuracy of index statistics, which can improve the query execution plan, at the expense of increased I/O when opening anInnoDBtable or recalculating statistics. For more information, see Section 17.8.10.2, “Configuring Non-Persistent Optimizer Statistics Parameters”.NoteSetting a high value for
innodb_stats_transient_sample_pagescould result in lengthyANALYZE TABLEexecution time. To estimate the number of database pages accessed byANALYZE TABLE, see Section 17.8.10.3, “Estimating ANALYZE TABLE Complexity for InnoDB Tables”.innodb_stats_transient_sample_pagesonly applies wheninnodb_stats_persistentis disabled for a table; wheninnodb_stats_persistentis enabled,innodb_stats_persistent_sample_pagesapplies instead. Takes the place ofinnodb_stats_sample_pagesthat was removed in MySQL 8.0. For more information, see Section 17.8.10.2, “Configuring Non-Persistent Optimizer Statistics Parameters”.-
Command-Line Format --innodb-status-output[={OFF|ON}]System Variable innodb_status_outputScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFEnables or disables periodic output for the standard
InnoDBMonitor. Also used in combination withinnodb_status_output_locksto enable or disable periodic output for theInnoDBLock Monitor. For more information, see Section 17.17.2, “Enabling InnoDB Monitors”. -
Command-Line Format --innodb-status-output-locks[={OFF|ON}]System Variable innodb_status_output_locksScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFEnables or disables the
InnoDBLock Monitor. When enabled, theInnoDBLock Monitor prints additional information about locks inSHOW ENGINE INNODB STATUSoutput and in periodic output printed to the MySQL error log. Periodic output for theInnoDBLock Monitor is printed as part of the standardInnoDBMonitor output. The standardInnoDBMonitor must therefore be enabled for theInnoDBLock Monitor to print data to the MySQL error log periodically. For more information, see Section 17.17.2, “Enabling InnoDB Monitors”. -
Command-Line Format --innodb-strict-mode[={OFF|ON}]System Variable innodb_strict_modeScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONWhen
innodb_strict_modeis enabled,InnoDBreturns errors rather than warnings when checking for invalid or incompatible table options.It checks that
KEY_BLOCK_SIZE,ROW_FORMAT,DATA DIRECTORY,TEMPORARY, andTABLESPACEoptions are compatible with each other and other settings.innodb_strict_mode=ONalso enables a row size check when creating or altering a table, to preventINSERTorUPDATEfrom failing due to the record being too large for the selected page size.You can enable or disable
innodb_strict_modeon the command line when startingmysqld, or in a MySQL configuration file. You can also enable or disableinnodb_strict_modeat runtime with the statementSET [GLOBAL|SESSION] innodb_strict_mode=, wheremodemodeONorOFF. Changing theGLOBALsetting requires privileges sufficient to set global system variables (see Section 7.1.9.1, “System Variable Privileges”) and affects the operation of all clients that subsequently connect. Any client can change theSESSIONsetting forinnodb_strict_mode, and the setting affects only that client.Setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 7.1.9.1, “System Variable Privileges”.
-
Command-Line Format --innodb-sync-array-size=#System Variable innodb_sync_array_sizeScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 1Minimum Value 1Maximum Value 1024Defines the size of the mutex/lock wait array. Increasing the value splits the internal data structure used to coordinate threads, for higher concurrency in workloads with large numbers of waiting threads. This setting must be configured when the MySQL instance is starting up, and cannot be changed afterward. Increasing the value is recommended for workloads that frequently produce a large number of waiting threads, typically greater than 768.
-
Command-Line Format --innodb-sync-spin-loops=#System Variable innodb_sync_spin_loopsScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 30Minimum Value 0Maximum Value 4294967295The number of times a thread waits for an
InnoDBmutex to be freed before the thread is suspended. -
Command-Line Format --innodb-sync-debug[={OFF|ON}]System Variable innodb_sync_debugScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value OFFEnables sync debug checking for the
InnoDBstorage engine. This option is only available if debugging support is compiled in using theWITH_DEBUGCMake option. -
Command-Line Format --innodb-table-locks[={OFF|ON}]System Variable innodb_table_locksScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONIf
autocommit = 0,InnoDBhonorsLOCK TABLES; MySQL does not return fromLOCK TABLES ... WRITEuntil all other threads have released all their locks to the table. The default value ofinnodb_table_locksis 1, which means thatLOCK TABLEScauses InnoDB to lock a table internally ifautocommit = 0.innodb_table_locks = 0has no effect for tables locked explicitly withLOCK TABLES ... WRITE. It does have an effect for tables locked for read or write byLOCK TABLES ... WRITEimplicitly (for example, through triggers) or byLOCK TABLES ... READ.For related information, see Section 17.7, “InnoDB Locking and Transaction Model”.
-
Command-Line Format --innodb-temp-data-file-path=file_nameSystem Variable innodb_temp_data_file_pathScope Global Dynamic No SET_VARHint AppliesNo Type String Default Value ibtmp1:12M:autoextendDefines the relative path, name, size, and attributes of global temporary tablespace data files. The global temporary tablespace stores rollback segments for changes made to user-created temporary tables.
If no value is specified for
innodb_temp_data_file_path, the default behavior is to create a single auto-extending data file namedibtmp1in theinnodb_data_home_dirdirectory. The initial file size is slightly larger than 12MB.The syntax for a global temporary tablespace data file specification includes the file name, file size, and
autoextendandmaxattributes:file_name:file_size[:autoextend[:max:max_file_size]]The global temporary tablespace data file cannot have the same name as another
InnoDBdata file. Any inability or error creating the global temporary tablespace data file is treated as fatal and server startup is refused.File sizes are specified in KB, MB, or GB by appending
K,MorGto the size value. The sum of file sizes must be slightly larger than 12MB.The size limit of individual files is determined by the operating system. File size can be more than 4GB on operating systems that support large files. Use of raw disk partitions for global temporary tablespace data files is not supported.
The
autoextendandmaxattributes can be used only for the data file specified last in theinnodb_temp_data_file_pathsetting. For example:[mysqld] innodb_temp_data_file_path=ibtmp1:50M;ibtmp2:12M:autoextend:max:500MThe
autoextendoption causes the data file to automatically increase in size when it runs out of free space. Theautoextendincrement is 64MB by default. To modify the increment, change theinnodb_autoextend_incrementvariable setting.The directory path for global temporary tablespace data files is formed by concatenating the paths defined by
innodb_data_home_dirandinnodb_temp_data_file_path.Before running
InnoDBin read-only mode, setinnodb_temp_data_file_pathto a location outside of the data directory. The path must be relative to the data directory. For example:--innodb-temp-data-file-path=../../../tmp/ibtmp1:12M:autoextendFor more information, see Global Temporary Tablespace.
-
Command-Line Format --innodb-temp-tablespaces-dir=dir_nameSystem Variable innodb_temp_tablespaces_dirScope Global Dynamic No SET_VARHint AppliesNo Type Directory name Default Value #innodb_tempDefines the location where
InnoDBcreates a pool of session temporary tablespaces at startup. The default location is the#innodb_tempdirectory in the data directory. A fully qualified path or path relative to the data directory is permitted.Session temporary tablespaces always store user-created temporary tables and internal temporary tables created by the optimizer using
InnoDB. (Previously, the on-disk storage engine for internal temporary tables was determined by theinternal_tmp_disk_storage_enginesystem variable, which is no longer supported. See Storage Engine for On-Disk Internal Temporary Tables.)For more information, see Session Temporary Tablespaces.
-
Command-Line Format --innodb-thread-concurrency=#System Variable innodb_thread_concurrencyScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 1000Defines the maximum number of threads permitted inside of
InnoDB. A value of 0 (the default) is interpreted as infinite concurrency (no limit). This variable is intended for performance tuning on high concurrency systems.InnoDBtries to keep the number of threads insideInnoDBless than or equal to theinnodb_thread_concurrencylimit. Threads waiting for locks are not counted in the number of concurrently executing threads.The correct setting depends on workload and computing environment. Consider setting this variable if your MySQL instance shares CPU resources with other applications or if your workload or number of concurrent users is growing. Test a range of values to determine the setting that provides the best performance.
innodb_thread_concurrencyis a dynamic variable, which permits experimenting with different settings on a live test system. If a particular setting performs poorly, you can quickly setinnodb_thread_concurrencyback to 0.Use the following guidelines to help find and maintain an appropriate setting:
If the number of concurrent user threads for a workload is consistently small and does not affect performance, set
innodb_thread_concurrency=0(no limit).If your workload is consistently heavy or occasionally spikes, set an
innodb_thread_concurrencyvalue and adjust it until you find the number of threads that provides the best performance. For example, suppose that your system typically has 40 to 50 users, but periodically the number increases to 60, 70, or more. Through testing, you find that performance remains largely stable with a limit of 80 concurrent users. In this case, setinnodb_thread_concurrencyto 80.If you do not want
InnoDBto use more than a certain number of virtual CPUs for user threads (20 virtual CPUs, for example), setinnodb_thread_concurrencyto this number (or possibly lower, depending on performance testing). If your goal is to isolate MySQL from other applications, consider binding themysqldprocess exclusively to the virtual CPUs. Be aware, however, that exclusive binding can result in non-optimal hardware usage if themysqldprocess is not consistently busy. In this case, you can bind themysqldprocess to the virtual CPUs but allow other applications to use some or all of the virtual CPUs.NoteFrom an operating system perspective, using a resource management solution to manage how CPU time is shared among applications may be preferable to binding the
mysqldprocess. For example, you could assign 90% of virtual CPU time to a given application while other critical processes are not running, and scale that value back to 40% when other critical processes are running.In some cases, the optimal
innodb_thread_concurrencysetting can be smaller than the number of virtual CPUs.An
innodb_thread_concurrencyvalue that is too high can cause performance regression due to increased contention on system internals and resources.Monitor and analyze your system regularly. Changes to workload, number of users, or computing environment may require that you adjust the
innodb_thread_concurrencysetting.
A value of 0 disables the
queries inside InnoDBandqueries in queuecounters in theROW OPERATIONSsection ofSHOW ENGINE INNODB STATUSoutput.For related information, see Section 17.8.4, “Configuring Thread Concurrency for InnoDB”.
-
Command-Line Format --innodb-thread-sleep-delay=#System Variable innodb_thread_sleep_delayScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 10000Minimum Value 0Maximum Value 1000000Unit microseconds How long
InnoDBthreads sleep before joining theInnoDBqueue, in microseconds. The default value is 10000. A value of 0 disables sleep. You can setinnodb_adaptive_max_sleep_delayto the highest value you would allow forinnodb_thread_sleep_delay, andInnoDBautomatically adjustsinnodb_thread_sleep_delayup or down depending on current thread-scheduling activity. This dynamic adjustment helps the thread scheduling mechanism to work smoothly during times when the system is lightly loaded or when it is operating near full capacity.For more information, see Section 17.8.4, “Configuring Thread Concurrency for InnoDB”.
-
Command-Line Format --innodb-tmpdir=dir_nameSystem Variable innodb_tmpdirScope Global, Session Dynamic Yes SET_VARHint AppliesNo Type Directory name Default Value NULLUsed to define an alternate directory for temporary sort files created during online
ALTER TABLEoperations that rebuild the table.Online
ALTER TABLEoperations that rebuild the table also create an intermediate table file in the same directory as the original table. Theinnodb_tmpdiroption is not applicable to intermediate table files.A valid value is any directory path other than the MySQL data directory path. If the value is NULL (the default), temporary files are created MySQL temporary directory (
$TMPDIRon Unix,%TEMP%on Windows, or the directory specified by the--tmpdirconfiguration option). If a directory is specified, existence of the directory and permissions are only checked wheninnodb_tmpdiris configured using aSETstatement. If a symlink is provided in a directory string, the symlink is resolved and stored as an absolute path. The path should not exceed 512 bytes. An onlineALTER TABLEoperation reports an error ifinnodb_tmpdiris set to an invalid directory.innodb_tmpdiroverrides the MySQLtmpdirsetting but only for onlineALTER TABLEoperations.The
FILEprivilege is required to configureinnodb_tmpdir.The
innodb_tmpdiroption was introduced to help avoid overflowing a temporary file directory located on atmpfsfile system. Such overflows could occur as a result of large temporary sort files created during onlineALTER TABLEoperations that rebuild the table.In replication environments, only consider replicating the
innodb_tmpdirsetting if all servers have the same operating system environment. Otherwise, replicating theinnodb_tmpdirsetting could result in a replication failure when running onlineALTER TABLEoperations that rebuild the table. If server operating environments differ, it is recommended that you configureinnodb_tmpdiron each server individually.For more information, see Section 17.12.3, “Online DDL Space Requirements”. For information about online
ALTER TABLEoperations, see Section 17.12, “InnoDB and Online DDL”. innodb_trx_purge_view_update_only_debugCommand-Line Format --innodb-trx-purge-view-update-only-debug[={OFF|ON}]System Variable innodb_trx_purge_view_update_only_debugScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFPauses purging of delete-marked records while allowing the purge view to be updated. This option artificially creates a situation in which the purge view is updated but purges have not yet been performed. This option is only available if debugging support is compiled in using the
WITH_DEBUGCMake option.-
Command-Line Format --innodb-trx-rseg-n-slots-debug=#System Variable innodb_trx_rseg_n_slots_debugScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 0Minimum Value 0Maximum Value 1024Sets a debug flag that limits
TRX_RSEG_N_SLOTSto a given value for thetrx_rsegf_undo_find_freefunction that looks for free slots for undo log segments. This option is only available if debugging support is compiled in using theWITH_DEBUGCMake option. -
Command-Line Format --innodb-undo-directory=dir_nameSystem Variable innodb_undo_directoryScope Global Dynamic No SET_VARHint AppliesNo Type Directory name The path where
InnoDBcreates undo tablespaces. Typically used to place undo tablespaces on a different storage device.There is no default value (it is NULL). If the
innodb_undo_directoryvariable is undefined, undo tablespaces are created in the data directory.The default undo tablespaces (
innodb_undo_001andinnodb_undo_002) created when the MySQL instance is initialized always reside in the directory defined by theinnodb_undo_directoryvariable.Undo tablespaces created using
CREATE UNDO TABLESPACEsyntax are created in the directory defined by theinnodb_undo_directoryvariable if a different path is not specified.For more information, see Section 17.6.3.4, “Undo Tablespaces”.
-
Command-Line Format --innodb-undo-log-encrypt[={OFF|ON}]System Variable innodb_undo_log_encryptScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value OFFControls encryption of undo log data for tables encrypted using the
InnoDBdata-at-rest encryption feature. Only applies to undo logs that reside in separate undo tablespaces. See Section 17.6.3.4, “Undo Tablespaces”. Encryption is not supported for undo log data that resides in the system tablespace. For more information, see Undo Log Encryption. -
Command-Line Format --innodb-undo-log-truncate[={OFF|ON}]System Variable innodb_undo_log_truncateScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONWhen enabled, undo tablespaces that exceed the threshold value defined by
innodb_max_undo_log_sizeare marked for truncation. Only undo tablespaces can be truncated. Truncating undo logs that reside in the system tablespace is not supported. For truncation to occur, there must be at least two undo tablespaces.The
innodb_purge_rseg_truncate_frequencyvariable can be used to expedite truncation of undo tablespaces.For more information, see Truncating Undo Tablespaces.
-
Command-Line Format --innodb-undo-tablespaces=#Deprecated Yes System Variable innodb_undo_tablespacesScope Global Dynamic Yes SET_VARHint AppliesNo Type Integer Default Value 2Minimum Value 2Maximum Value 127Defines the number of undo tablespaces used by
InnoDB. The default and minimum value is 2.NoteThe
innodb_undo_tablespacesvariable is deprecated; setting it has no effect. You should expect it to be removed in a future MySQL release.For more information, see Section 17.6.3.4, “Undo Tablespaces”.
-
Command-Line Format --innodb-use-fdatasync[={OFF|ON}]System Variable innodb_use_fdatasyncScope Global Dynamic Yes SET_VARHint AppliesNo Type Boolean Default Value ONOn platforms that support
fdatasync()system calls, havinginnodb_use_fdatasyncenabled permits usingfdatasync()instead offsync()system calls for operating system flushes. Anfdatasync()call does not flush changes to file metadata unless required for subsequent data retrieval, providing a potential performance benefit.A subset of
innodb_flush_methodsettings such asfsync,O_DSYNC, andO_DIRECTusefsync()system calls. Theinnodb_use_fdatasyncvariable is applicable when using those settings.Before MySQL 8.4, this option was disabled by default.
-
Command-Line Format --innodb-use-native-aio[={OFF|ON}]System Variable innodb_use_native_aioScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value ONSpecifies whether to use the asynchronous I/O subsystem. This variable cannot be changed while the server is running. Normally, you do not need to configure this option, because it is enabled by default.
This feature improves the scalability of heavily I/O-bound systems, which typically show many pending reads/writes in
SHOW ENGINE INNODB STATUSoutput.Running with a large number of
InnoDBI/O threads, and especially running multiple such instances on the same server machine, can exceed capacity limits on Linux systems. In this case, you may receive the following error:EAGAIN: The specified maxevents exceeds the user's limit of available events.You can typically address this error by writing a higher limit to
/proc/sys/fs/aio-max-nr.However, if a problem with the asynchronous I/O subsystem in the OS prevents
InnoDBfrom starting, you can start the server withinnodb_use_native_aio=0. This option may also be disabled automatically during startup ifInnoDBdetects a potential problem such as a combination oftmpdirlocation,tmpfsfile system, and Linux kernel that does not support AIO ontmpfs.For more information, see Section 17.8.6, “Using Asynchronous I/O on Linux”.
innodb_validate_tablespace_pathsCommand-Line Format --innodb-validate-tablespace-paths[={OFF|ON}]System Variable innodb_validate_tablespace_pathsScope Global Dynamic No SET_VARHint AppliesNo Type Boolean Default Value ONControls tablespace file path validation. At startup,
InnoDBvalidates the paths of known tablespace files against tablespace file paths stored in the data dictionary in case tablespace files have been moved to a different location. Theinnodb_validate_tablespace_pathsvariable permits disabling tablespace path validation. This feature is intended for environments where tablespaces files are not moved. Disabling path validation improves startup time on systems with a large number of tablespace files.WarningStarting the server with tablespace path validation disabled after moving tablespace files can lead to undefined behavior.
For more information, see Section 17.6.3.7, “Disabling Tablespace Path Validation”.
The
InnoDBversion number. This is a legacy variable, the value is the same as the MySQL serverversion.-
Command-Line Format --innodb-write-io-threads=#System Variable innodb_write_io_threadsScope Global Dynamic No SET_VARHint AppliesNo Type Integer Default Value 4Minimum Value 1Maximum Value 64The number of I/O threads for write operations in
InnoDB. The default value is 4. Its counterpart for read threads isinnodb_read_io_threads. For more information, see Section 17.8.5, “Configuring the Number of Background InnoDB I/O Threads”. For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.NoteOn Linux systems, running multiple MySQL servers (typically more than 12) with default settings for
innodb_read_io_threads,innodb_write_io_threads, and the Linuxaio-max-nrsetting can exceed system limits. Ideally, increase theaio-max-nrsetting; as a workaround, you might reduce the settings for one or both of the MySQL variables.Also take into consideration the value of
sync_binlog, which controls synchronization of the binary log to disk.For general I/O tuning advice, see Section 10.5.8, “Optimizing InnoDB Disk I/O”.