InnoDB Configuration Parameters

InnoDB has a number of parameters that can control various functional aspects as well as performance.

Parameter Name	Description	Default Value	Suggested Value
innodb_checksum_algorithm	Tablespace page checksum algorithm.	crc32	crc32
innodb_doublewrite	Write data to doublewrite buffer before writing to data file.	On	Data integrity - set to On Performance - set Off
innodb_flush_log_at_trx_commit	Controls log flushing and writing.	1	Data integrity - set to 1 Performance - set to 2
innodb_flush_neighbors	Same extent buffer pool and other dirty pages flushing. Extent is a group of pages within a tablespace.	0 (zero) - disabled)	0 (zero) - disabled
innodb_flush_method	Data (buffer pool) and log files (redo, undo) write to disk (flush) method; O_DIRECT is used during flushing IO skipping fsync() after each write operation. Windows will always use async_unbuffered.	fsync	Linux ≤ 8.0.14 use O_DIRECT (Always use with XFS Filesystem) ≥ 8.0.13 use O_DIRECT_NO_FSYNC
innodb_idle_flush_pct	Buffer pool flushing during idle period as a percentage of innodb_io_capacity. Value of 100 does not limit page flushing when InnoDB is idle.	100	100
innodb_io_capacity	Number of available IOPS to background tasks (dirty page flushing, merging data from the change buffer)	200	≥ 1000
innodb_io_capacity_max	Number of maximum available IOPS to background tasks.	100	≥ 2000
innodb_log_compressed_pages	Redo log page compression (for table compression only).	On	Off
innodb_log_file_size	Size of the log file.	50 MB (503331648 bytes)	≥ 1 GB
innodb_log_files_in_group	Number of log files in the log group.	2	≥16
innodb_use_native_aio	Use asynchronous I/O - Linux only.	On	On
innodb_data_home_dir	The common part of the directory path for InnnoDB system tablespace files.	The MySQL datadir.	Only change this if using a separate layout for data, binary, and transaction log directories.
innodb_data_file_path	Defines the attributes of the InnoDB system tablespace files.	Create a single auto extending data file named ibdata1.	Change this if using a separate layout for data, binary, and transaction log directories and/or setting a different innodb_page_size.
innodb_log_group_home_dir	The directory containing the InnoDB redo log files.	Create two files name ib_logfile0 and ib_logfile1 in the datadir.	Only change this if using a separate layout for data, binary, and transaction log directories.
innodb_undo_directory	The path where innodb creates undo tablespaces.	Undo table spaces are created in the datadir.	Only change this if using a separate layout for data, binary and transaction log directories.
innodb_page_size	Specifies the page size for InnoDB tablespaces. Can only be specified prior to initializing the MySQL instance and cannot be changed afterward. Affects the maximum row length.	The default size is 16K.	The recommendation is to leave this setting as default. The performance profile (Less IOPS more Bandwidth) of storage I/O could change as the block size will increase with larger page sizes.
innodb_file_per_table	If enabled tables are created on a file per table basis. When disabled tables are created in the system tablespace.	ON	Recommend leaving this property ON if attempting to use a separate layout for data, binary and transaction log directories.
innodb_max_dirty_pages_pct	Establishes a target for flushing activity. Pages will be flushed from the buffer pool so that the total amount of dirty pages does not exceed this value.	90	To flush more often make this value smaller.
innodb_max_dirty_pages_pct_lwm	A low watermark representing the percentage of dirty pages at which preflushing is enabled to control the dirty page ratio.	10	Always ensure this value is less than innodb_max_dirty_pages_pct.
innodb_read_io_threads	The number of I/O threads for read operations in InnoDB.	4	If the MySQL server has enough cores increase this value to increase IO throughput.
innodb_write_io_threads	The number of I/O threads for write operations in InnoDB.	4	If the MySQL server has enough cores increase this value to increase IO throughput.

Note:

innodb_log_file_size and innodb_log_files_in_group

The size and number of MySQL redo logs are controlled by innodb_log_file_size and innodb_log_files_in_group parameters. The size of the redo log is a product of innodb_log_file_size and innodb_log_files_in_group values which should be less than 512 GB. By default, there are two 48 GB redo log files in a group for a total of 96 GB. The size of redo log files can be as large as the buffer pool, not to exceed 512 GB. Also, configuring large redo log files may reduce the checkpoint frequency. The general recommendation is to have enough redo log space available to handle more than an hour of write activity. On the other hand, configuring large log files will result in increased crash recovery time.