InnoDB Startup Options and System Variables

• 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 the InnoDB adaptive hash index, you can use --innodb-adaptive-hash-index or --skip-innodb-adaptive-hash-index on the command line, or innodb_adaptive_hash_index or skip_innodb_adaptive_hash_index in an option file.
• System variables that take a numeric value can be specified as --var_name=value on the command line or as var_name=value in option files.
• Many system variables can be changed at runtime (see Section 5.1.8.2, “Dynamic System Variables”).
• For information about GLOBAL and SESSION variable scope modifiers, refer to the SET statement documentation.
• Certain options control the locations and layout of the InnoDB data files. Section 14.8.1, “InnoDB Startup Configuration” explains how to use these options.
• Some options, which you might not use initially, help tune InnoDB performance characteristics based on machine capacity and your database workload.
• For more information on specifying options and system variables, see Section 4.2.2, “Specifying Program Options”.

InnoDB Command Options

--innodb[=value]

Command-Line Format	--innodb[=value]
Deprecated	Yes
Type	Enumeration
Default Value	ON
Valid Values	OFFONFORCE

Controls loading of the InnoDB storage engine, if the server was compiled with InnoDB support. This option has a tristate format, with possible values of OFF, ON, or FORCE. See Section 5.5.1, “Installing and Uninstalling Plugins”.

To disable InnoDB, use --innodb=OFF or --skip-innodb. In this case, because the default storage engine is InnoDB, the server does not start unless you also use --default-storage-engine and --default-tmp-storage-engine to set the default to some other engine for both permanent and TEMPORARY tables.

The InnoDB storage engine can no longer be disabled, and the --innodb=OFF and --skip-innodb options are deprecated and have no effect. Their use results in a warning. You should expect these options to be removed in a future MySQL release.

--innodb-status-file

Command-Line Format	--innodb-status-file[={OFF|ON}]
Type	Boolean
Default Value	OFF

• The --innodb-status-file startup option controls whether InnoDB creates a file named innodb_status.pid in the data directory and writes SHOW ENGINE INNODB STATUS output to it every 15 seconds, approximately.

The innodb_status.pid file is not created by default. To create it, start mysqld with the --innodb-status-file option. InnoDB removes 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-file option is intended for temporary use, as SHOW ENGINE INNODB STATUS output generation can affect performance, and the innodb_status.pid file can become quite large over time.

For related information, see Section 14.18.2, “Enabling InnoDB Monitors”.

• --skip-innodb
• Disable the InnoDB storage engine. See the description of --innodb.

InnoDB System Variables

daemon_memcached_enable_binlog

Command-Line Format	--daemon-memcached-enable-binlog[={OFF|ON}]
System Variable	daemon_memcached_enable_binlog
Scope	Global
Dynamic	No
Type	Boolean
Default Value	OFF

• Enable this option on the source server to use the InnoDB memcached plugin (daemon_memcached) with the MySQL binary log. This option can only be set at server startup. You must also enable the MySQL binary log on the source server using the --log-bin option.
• For more information, see Section 14.21.6, “The InnoDB memcached Plugin and Replication”.

daemon_memcached_engine_lib_name

Command-Line Format	--daemon-memcached-engine-lib-name=file_name
System Variable	daemon_memcached_engine_lib_name
Scope	Global
Dynamic	No
Type	File name
Default Value	innodb_engine.so

• Specifies the shared library that implements the InnoDB memcached plugin.
• For more information, see Section 14.21.3, “Setting Up the InnoDB memcached Plugin”.

daemon_memcached_engine_lib_path

Command-Line Format	--daemon-memcached-engine-lib-path=dir_name
System Variable	daemon_memcached_engine_lib_path
Scope	Global
Dynamic	No
Type	Directory name
Default Value	NULL

• The path of the directory containing the shared library that implements the InnoDB memcached plugin. The default value is NULL, representing the MySQL plugin directory. You should not need to modify this parameter unless specifying a memcached plugin for a different storage engine that is located outside of the MySQL plugin directory.
• For more information, see Section 14.21.3, “Setting Up the InnoDB memcached Plugin”.

daemon_memcached_option

Command-Line Format	--daemon-memcached-option=options
System Variable	daemon_memcached_option
Scope	Global
Dynamic	No
Type	String
Default Value

Used to pass space-separated memcached options to the underlying memcached memory object caching daemon on startup. For example, you might change the port that memcached listens on, reduce the maximum number of simultaneous connections, change the maximum memory size for a key-value pair, or enable debugging messages for the error log.

See Section 14.21.3, “Setting Up the InnoDB memcached Plugin” for usage details. For information about memcached options, refer to the memcached man page.

daemon_memcached_r_batch_size

Command-Line Format	--daemon-memcached-r-batch-size=#
System Variable	daemon_memcached_r_batch_size
Scope	Global
Dynamic	No
Type	Integer
Default Value	1
Minimum Value	1
Maximum Value	1073741824

• Specifies how many memcached read operations (get operations) to perform before doing a COMMIT to start a new transaction. Counterpart of daemon_memcached_w_batch_size.
• This value is set to 1 by default, so that any changes made to the table through SQL statements are immediately visible to memcached operations. You might increase it to reduce the overhead from frequent commits on a system where the underlying table is only being accessed through the memcached interface. If you set the value too large, the amount of undo or redo data could impose some storage overhead, as with any long-running transaction.
• For more information, see Section 14.21.3, “Setting Up the InnoDB memcached Plugin”.

daemon_memcached_w_batch_size

Command-Line Format	--daemon-memcached-w-batch-size=#
System Variable	daemon_memcached_w_batch_size
Scope	Global
Dynamic	No
Type	Integer
Default Value	1
Minimum Value	1
Maximum Value	1048576

Specifies how many memcached write operations, such as add, set, and incr, to perform before doing a COMMIT to start a new transaction. Counterpart of daemon_memcached_r_batch_size.

This value is set to 1 by default, on the assumption that data being stored is important to preserve in case of an outage and should immediately be committed. When storing non-critical data, you might increase this value to reduce the overhead from frequent commits; but then the last N-1 uncommitted write operations could be lost if an unexpected exit occurs.

For more information, see Section 14.21.3, “Setting Up the InnoDB memcached Plugin”.

ignore_builtin_innodb

Command-Line Format	--ignore-builtin-innodb[={OFF|ON}]
Deprecated	Yes
System Variable	ignore_builtin_innodb
Scope	Global
Dynamic	No
Type	Boolean

In earlier versions of MySQL, enabling this variable caused the server to behave as if the built-in InnoDB were not present, which enabled the InnoDB Plugin to be used instead. In MySQL 5.7, InnoDB is the default storage engine and InnoDB Plugin is not used, so this variable is ignored.

innodb_adaptive_flushing

Command-Line Format	--innodb-adaptive-flushing[={OFF|ON}]
System Variable	innodb_adaptive_flushing
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	ON

Specifies whether to dynamically adjust the rate of flushing dirty pages in the InnoDB buffer 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 14.8.3.5, “Configuring Buffer Pool Flushing” for more information. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.

innodb_adaptive_flushing_lwm

Command-Line Format	--innodb-adaptive-flushing-lwm=#
System Variable	innodb_adaptive_flushing_lwm
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	10
Minimum Value	0
Maximum Value	70

Defines the low water mark representing percentage of redo log capacity at which adaptive flushing is enabled. For more information, see Section 14.8.3.5, “Configuring Buffer Pool Flushing”.

innodb_adaptive_hash_index

Command-Line Format	--innodb-adaptive-hash-index[={OFF|ON}]
System Variable	innodb_adaptive_hash_index
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	ON

Whether the InnoDB adaptive 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 14.5.3, “Adaptive Hash Index” for details.

This variable is enabled by default. You can modify this parameter using the SET GLOBAL statement, without restarting the server. Changing the setting at runtime requires privileges sufficient to set global system variables. See Section 5.1.8.1, “System Variable Privileges”. You can also use --skip-innodb-adaptive-hash-index at server startup to disable 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.

innodb_adaptive_hash_index_parts

Command-Line Format	--innodb-adaptive-hash-index-parts=#
System Variable	innodb_adaptive_hash_index_parts
Scope	Global
Dynamic	No
Type	Numeric
Default Value	8
Minimum Value	1
Maximum Value	512

Partitions the adaptive hash index search system. Each index is bound to a specific partition, with each partition protected by a separate latch.

In earlier releases, the adaptive hash index search system was protected by a single latch (btr_search_latch) which could become a point of contention. With the introduction of the innodb_adaptive_hash_index_parts option, the search system is partitioned into 8 parts by default. The maximum setting is 512.

For related information, see Section 14.5.3, “Adaptive Hash Index”.

innodb_adaptive_max_sleep_delay

Command-Line Format	--innodb-adaptive-max-sleep-delay=#
System Variable	innodb_adaptive_max_sleep_delay
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	150000
Minimum Value	0
Maximum Value	1000000
Unit	microseconds

Permits InnoDB to automatically adjust the value of innodb_thread_sleep_delay up or down according to the current workload. Any nonzero value enables automated, dynamic adjustment of the innodb_thread_sleep_delay value, up to the maximum value specified in the innodb_adaptive_max_sleep_delay option. The value represents the number of microseconds. This option can be useful in busy systems, with greater than 16 InnoDB threads. (In practice, it is most valuable for MySQL systems with hundreds or thousands of simultaneous connections.)

For more information, see Section 14.8.5, “Configuring Thread Concurrency for InnoDB”.

innodb_api_bk_commit_interval

Command-Line Format	--innodb-api-bk-commit-interval=#
System Variable	innodb_api_bk_commit_interval
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	5
Minimum Value	1
Maximum Value	1073741824
Unit	seconds

How often to auto-commit idle connections that use the InnoDB memcached interface, in seconds. For more information, see Section 14.21.5.4, “Controlling Transactional Behavior of the InnoDB memcached Plugin”.

innodb_api_disable_rowlock

Command-Line Format	--innodb-api-disable-rowlock[={OFF|ON}]
System Variable	innodb_api_disable_rowlock
Scope	Global
Dynamic	No
Type	Boolean
Default Value	OFF

• Use this option to disable row locks when InnoDB memcached performs DML operations. By default, innodb_api_disable_rowlock is disabled, which means that memcached requests row locks for get and set operations. When innodb_api_disable_rowlock is enabled, memcached requests a table lock instead of row locks.
• innodb_api_disable_rowlock is not dynamic. It must be specified on the mysqld command line or entered in the MySQL configuration file. Configuration takes effect when the plugin is installed, which occurs when the MySQL server is started.
• For more information, see Section 14.21.5.4, “Controlling Transactional Behavior of the InnoDB memcached Plugin”.

innodb_api_enable_binlog

Command-Line Format	--innodb-api-enable-binlog[={OFF|ON}]
System Variable	innodb_api_enable_binlog
Scope	Global
Dynamic	No
Type	Boolean
Default Value	OFF

Lets you use the InnoDB memcached plugin with the MySQL binary log. For more information, see Enabling the InnoDB memcached Binary Log.

innodb_api_enable_mdl

Command-Line Format	--innodb-api-enable-mdl[={OFF|ON}]
System Variable	innodb_api_enable_mdl
Scope	Global
Dynamic	No
Type	Boolean
Default Value	OFF

Locks the table used by the InnoDB memcached plugin, so that it cannot be dropped or altered by DDL through the SQL interface. For more information, see Section 14.21.5.4, “Controlling Transactional Behavior of the InnoDB memcached Plugin”.

innodb_api_trx_level

Command-Line Format	--innodb-api-trx-level=#
System Variable	innodb_api_trx_level
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	0
Minimum Value	0
Maximum Value	3

Controls the transaction isolation level on queries processed by the memcached interface. The constants corresponding to the familiar names are:

• 0 = READ UNCOMMITTED
• 1 = READ COMMITTED
• 2 = REPEATABLE READ
• 3 = SERIALIZABLE

For more information, see Section 14.21.5.4, “Controlling Transactional Behavior of the InnoDB memcached Plugin”.

Command-Line Format	--innodb-autoextend-increment=#
System Variable	innodb_autoextend_increment
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	64
Minimum Value	1
Maximum Value	1000
Unit	megabytes

The increment size (in megabytes) for extending the size of an auto-extending InnoDB system 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_increment setting does not affect file-per-table tablespace files or general tablespace files. These files are auto-extending regardless of the innodb_autoextend_increment setting. The initial extensions are by small amounts, after which extensions occur in increments of 4MB.

innodb_autoinc_lock_mode

Command-Line Format	--innodb-autoinc-lock-mode=#
System Variable	innodb_autoinc_lock_mode
Scope	Global
Dynamic	No
Type	Integer
Default Value	1
Valid Values	012

The 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 1 (consecutive). For the characteristics of each lock mode, see InnoDB AUTO_INCREMENT Lock Modes.

innodb_background_drop_list_empty

Command-Line Format	--innodb-background-drop-list-empty[={OFF|ON}]
Introduced	5.7.10
System Variable	innodb_background_drop_list_empty
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	OFF

Enabling the innodb_background_drop_list_empty debug option helps avoid test case failures by delaying table creation until the background drop list is empty. For example, if test case A places table t1 on the background drop list, test case B waits until the background drop list is empty before creating table t1.

innodb_buffer_pool_chunk_size

Command-Line Format	--innodb-buffer-pool-chunk-size=#
System Variable	innodb_buffer_pool_chunk_size
Scope	Global
Dynamic	No
Type	Integer
Default Value	134217728
Minimum Value	1048576
Maximum Value	innodb_buffer_pool_size / innodb_buffer_pool_instances
Unit	bytes

innodb_buffer_pool_chunk_size defines the chunk size for InnoDB buffer 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_size is 128MB (134217728 bytes). The number of pages contained in a chunk depends on the value of innodb_page_size. innodb_buffer_pool_chunk_size can be increased or decreased in units of 1MB (1048576 bytes).

The following conditions apply when altering the innodb_buffer_pool_chunk_size value:

• If innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances is larger than the current buffer pool size when the buffer pool is initialized, innodb_buffer_pool_chunk_size is truncated to innodb_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 alter innodb_buffer_pool_chunk_size, innodb_buffer_pool_size is automatically rounded to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. The adjustment occurs when the buffer pool is initialized.

Important

Care should be taken when changing innodb_buffer_pool_chunk_size, as changing this value can automatically increase the size of the buffer pool. Before changing innodb_buffer_pool_chunk_size, calculate the effect it has on innodb_buffer_pool_size to 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_size variable 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 of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, and changing either of those variable settings requires restarting the server.

See Section 14.8.3.1, “Configuring InnoDB Buffer Pool Size” for more information.

innodb_buffer_pool_dump_at_shutdown

Command-Line Format	--innodb-buffer-pool-dump-at-shutdown[={OFF|ON}]
System Variable	innodb_buffer_pool_dump_at_shutdown
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	ON

Specifies whether to record the pages cached in the InnoDB buffer pool when the MySQL server is shut down, to shorten the warmup process at the next restart. Typically used in combination with innodb_buffer_pool_load_at_startup. The innodb_buffer_pool_dump_pct option defines the percentage of most recently used buffer pool pages to dump.

Both innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup are enabled by default.

For more information, see Section 14.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_dump_now

Command-Line Format	--innodb-buffer-pool-dump-now[={OFF|ON}]
System Variable	innodb_buffer_pool_dump_now
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	OFF

Immediately records the pages cached in the InnoDB buffer pool. Typically used in combination with innodb_buffer_pool_load_now.

For more information, see Section 14.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_dump_pct

Command-Line Format	--innodb-buffer-pool-dump-pct=#
System Variable	innodb_buffer_pool_dump_pct
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	25
Minimum Value	1
Maximum Value	100

Specifies 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_pct is set to 25, the 25 most recently used pages from each buffer pool are dumped.

The change to the innodb_buffer_pool_dump_pct default value coincides with default value changes for innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup, which are both enabled by default in MySQL 5.7.

innodb_buffer_pool_filename

Command-Line Format	--innodb-buffer-pool-filename=file_name
System Variable	innodb_buffer_pool_filename
Scope	Global
Dynamic	Yes
Type	File name
Default Value	ib_buffer_pool

Specifies the name of the file that holds the list of tablespace IDs and page IDs produced by innodb_buffer_pool_dump_at_shutdown or innodb_buffer_pool_dump_now. Tablespace IDs and page IDs are saved in the following format: space, page_id. By default, the file is named ib_buffer_pool and is located in the InnoDB data directory. A non-default location must be specified relative to the data directory.

A file name can be specified at runtime, using a SET statement:

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 InnoDB returns a startup error indicating that there is no such file or directory.

For more information, see Section 14.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_instances

Command-Line Format	--innodb-buffer-pool-instances=#
System Variable	innodb_buffer_pool_instances
Scope	Global
Dynamic	No
Type	Integer
Default Value (Windows, 32-bit platforms)	(autosized)
Default Value (Other)	8 (or 1 if innodb_buffer_pool_size < 1GB)
Minimum Value	1
Maximum Value	64

The number of regions that the InnoDB buffer pool is divided into. For systems with buffer pools in the multi-gigabyte range, dividing the buffer pool into separate instances can improve concurrency, by reducing contention as different threads read and write to cached pages. 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.

This option only takes effect when setting innodb_buffer_pool_size to 1GB or more. The total buffer pool size is divided among all the buffer pools. For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB.

The default value on 32-bit Windows systems depends on the value of innodb_buffer_pool_size, as described below:

• If innodb_buffer_pool_size is greater than 1.3GB, the default for innodb_buffer_pool_instances is innodb_buffer_pool_size/128MB, with individual memory allocation requests for each chunk. 1.3GB was chosen as the boundary at which there is significant risk for 32-bit Windows to be unable to allocate the contiguous address space needed for a single buffer pool.
• Otherwise, the default is 1.

• On all other platforms, the default value is 8 when innodb_buffer_pool_size is greater than or equal to 1GB. Otherwise, the default is 1.
• For related information, see Section 14.8.3.1, “Configuring InnoDB Buffer Pool Size”.

innodb_buffer_pool_load_abort

Command-Line Format	--innodb-buffer-pool-load-abort[={OFF|ON}]
System Variable	innodb_buffer_pool_load_abort
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	OFF

Interrupts the process of restoring InnoDB buffer pool contents triggered by innodb_buffer_pool_load_at_startup or innodb_buffer_pool_load_now.

For more information, see Section 14.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_load_at_startup

Command-Line Format	--innodb-buffer-pool-load-at-startup[={OFF|ON}]
System Variable	innodb_buffer_pool_load_at_startup
Scope	Global
Dynamic	No
Type	Boolean
Default Value	ON

• Specifies that, on MySQL server startup, the InnoDB buffer pool is automatically warmed up by loading the same pages it held at an earlier time. Typically used in combination with innodb_buffer_pool_dump_at_shutdown.
• Both innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup are enabled by default.
• For more information, see Section 14.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_load_now

Command-Line Format	--innodb-buffer-pool-load-now[={OFF|ON}]
System Variable	innodb_buffer_pool_load_now
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	OFF

Immediately warms up the InnoDB buffer pool by loading a set of 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.

For more information, see Section 14.8.3.6, “Saving and Restoring the Buffer Pool State”.

innodb_buffer_pool_size

Command-Line Format	--innodb-buffer-pool-size=#
System Variable	innodb_buffer_pool_size
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	134217728
Minimum Value	5242880
Maximum Value (64-bit platforms)	2**64-1
Maximum Value (32-bit platforms)	2**32-1
Unit	bytes

The size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The default value is 134217728 bytes (128MB). The maximum value depends on the CPU architecture; the maximum is 4294967295 (2³²-1) on 32-bit systems and 18446744073709551615 (2⁶⁴-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, setting innodb_buffer_pool_instances to 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.
• InnoDB reserves 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 14.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_size variable, 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 of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, buffer pool size is automatically adjusted to a value that is equal to or a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances.
• innodb_buffer_pool_size can be set dynamically, which allows you to resize the buffer pool without restarting the server. The Innodb_buffer_pool_resize_status status variable reports the status of online buffer pool resizing operations. See Section 14.8.3.1, “Configuring InnoDB Buffer Pool Size” for more information.

innodb_change_buffer_max_size

Command-Line Format	--innodb-change-buffer-max-size=#
System Variable	innodb_change_buffer_max_size
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	25
Minimum Value	0
Maximum Value	50

Maximum size for the InnoDB change buffer, as a percentage of the total size of the buffer pool. You might increase this value for a MySQL server with heavy insert, update, and delete activity, or decrease it for a MySQL server with unchanging data used for reporting. For more information, see Section 14.5.2, “Change Buffer”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.

innodb_change_buffering

Command-Line Format	--innodb-change-buffering=value
System Variable	innodb_change_buffering
Scope	Global
Dynamic	Yes
Type	Enumeration
Default Value	all
Valid Values	noneinsertsdeleteschangespurgesall

Whether InnoDB performs 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.

Table 14.19 Permitted Values for innodb_change_buffering

Value	Description
none	Do not buffer any operations.
inserts	Buffer insert operations.
deletes	Buffer delete marking operations; strictly speaking, the writes that mark index records for later deletion during a purge operation.
changes	Buffer inserts and delete-marking operations.
purges	Buffer the physical deletion operations that happen in the background.
all	The default. Buffer inserts, delete-marking operations, and purges.

For more information, see Section 14.5.2, “Change Buffer”. For general I/O tuning advice, see Section 8.5.8, “Optimizing InnoDB Disk I/O”.

innodb_change_buffering_debug

Command-Line Format	--innodb-change-buffering-debug=#
System Variable	innodb_change_buffering_debug
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	0
Minimum Value	0
Maximum Value	2

Sets a debug flag for InnoDB change 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 the WITH_DEBUG CMake option.

innodb_checksum_algorithm

Command-Line Format	--innodb-checksum-algorithm=value
System Variable	innodb_checksum_algorithm
Scope	Global
Dynamic	Yes
Type	Enumeration
Default Value	crc32
Valid Values	crc32strict_crc32innodbstrict_innodbnonestrict_none

Specifies how to generate and verify the checksum stored in the disk blocks of InnoDB tablespaces. crc32 is the default value as of MySQL 5.7.7.

innodb_checksum_algorithm replaces the innodb_checksums option. The following values were provided for compatibility, up to and including MySQL 5.7.6:

• innodb_checksums=ON is the same as innodb_checksum_algorithm=innodb.
• innodb_checksums=OFF is the same as innodb_checksum_algorithm=none.

As of MySQL 5.7.7, with a default innodb_checksum_algorithm value of crc32, innodb_checksums=ON is now the same as innodb_checksum_algorithm=crc32. innodb_checksums=OFF is still the same as innodb_checksum_algorithm=none.

To avoid conflicts, remove references to innodb_checksums from MySQL configuration files and startup scripts.

The value innodb is backward-compatible with earlier versions of MySQL. The value crc32 uses 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 the innodb checksum algorithm, which scans blocks 8 bits at a time. The value none writes 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 the crc32 algorithm, 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.

Note

Prior to MySQL 5.7.8, a strict mode setting for innodb_checksum_algorithm caused InnoDB to halt when encountering a valid but non-matching checksum. In MySQL 5.7.8 and later, only an error message is printed, and the page is accepted as valid if it has a valid innodb, crc32 or none checksum.

The following table shows the difference between the none, innodb, and crc32 option values, and their strict counterparts. none, innodb, and crc32 write 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 all InnoDB data files in an instance are created under an identical innodb_checksum_algorithm value.

Table 14.20 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, or crc32.
innodb	A checksum calculated in software, using the original algorithm from InnoDB.	Any of the checksums generated by none, innodb, or crc32.
crc32	A checksum calculated using the crc32 algorithm, possibly done with a hardware assist.	Any of the checksums generated by none, innodb, or crc32.
strict_none	A constant number	Any of the checksums generated by none, innodb, or crc32. InnoDB prints 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, or crc32. InnoDB prints an error message if a valid but non-matching checksum is encountered.
strict_crc32	A checksum calculated using the crc32 algorithm, possibly done with a hardware assist.	Any of the checksums generated by none, innodb, or crc32. InnoDB prints an error message if a valid but non-matching checksum is encountered.

Versions of MySQL Enterprise Backup up to 3.8.0 do not support backing up tablespaces that use CRC32 checksums. MySQL Enterprise Backup adds CRC32 checksum support in 3.8.1, with some limitations. Refer to the MySQL Enterprise Backup 3.8.1 Change History for more information.

innodb_checksums

Command-Line Format	--innodb-checksums[={OFF|ON}]
Deprecated	Yes
System Variable	innodb_checksums
Scope	Global
Dynamic	No
Type	Boolean
Default Value	ON

InnoDB can use checksum validation on all tablespace pages read from disk to ensure extra fault tolerance against hardware faults or corrupted data files. This validation is enabled by default. Under specialized circumstances (such as when running benchmarks) this safety feature can be disabled with --skip-innodb-checksums. You can specify the method of calculating the checksum using the innodb_checksum_algorithm option.

innodb_checksums is deprecated, replaced by innodb_checksum_algorithm.

Prior to MySQL 5.7.7, innodb_checksums=ON is the same as innodb_checksum_algorithm=innodb. As of MySQL 5.7.7, the innodb_checksum_algorithm default value is crc32, and innodb_checksums=ON is the same as innodb_checksum_algorithm=crc32. innodb_checksums=OFF is the same as innodb_checksum_algorithm=none.

Remove any innodb_checksums options from your configuration files and startup scripts to avoid conflicts with innodb_checksum_algorithm. innodb_checksums=OFF automatically sets innodb_checksum_algorithm=none. innodb_checksums=ON is ignored and overridden by any other setting for innodb_checksum_algorithm.

innodb_cmp_per_index_enabled

Command-Line Format	--innodb-cmp-per-index-enabled[={OFF|ON}]
System Variable	innodb_cmp_per_index_enabled
Scope	Global
Dynamic	Yes
Type	Boolean
Default Value	OFF

Enables per-index compression-related statistics in the INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table. Because these statistics can be expensive to gather, only enable this option on development, test, or replica instances during performance tuning related to InnoDB compressed tables.

For more information, see Section 24.4.7, “The INFORMATION_SCHEMA INNODB_CMP_PER_INDEX and INNODB_CMP_PER_INDEX_RESET Tables”, and Section 14.9.1.4, “Monitoring InnoDB Table Compression at Runtime”.

innodb_commit_concurrency

Command-Line Format	--innodb-commit-concurrency=#
System Variable	innodb_commit_concurrency
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	0
Minimum Value	0
Maximum Value	1000

The 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_concurrency cannot be changed at runtime from zero to nonzero or vice versa. The value can be changed from one nonzero value to another.

innodb_compress_debug

Command-Line Format	--innodb-compress-debug=value
System Variable	innodb_compress_debug
Scope	Global
Dynamic	Yes
Type	Enumeration
Default Value	none
Valid Values	nonezliblz4lz4hc

Compresses all tables using a specified compression algorithm without having to define a COMPRESSION attribute for each table. This option is only available if debugging support is compiled in using the WITH_DEBUG CMake option.

For related information, see Section 14.9.2, “InnoDB Page Compression”.

innodb_compression_failure_threshold_pct

Command-Line Format	--innodb-compression-failure-threshold-pct=#
System Variable	innodb_compression_failure_threshold_pct
Scope	Global
Dynamic	Yes
Type	Integer
Default Value	5
Minimum Value	0
Maximum Value	100

Defines 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 14.9.1.6, “Compression for OLTP Workloads”.

source : https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size