Skip to main content
Pure Technical Services

InnoDB Configuration Parameters

Currently viewing public documentation. Please login to access the full scope of documentation.

InnoDB is one of the storage engines available for MariaDB deployments. From MariaDB 10.2 InnoDB is the default storage engine. 

More information about the InnoDB storage engine can be found in the MariaDB Server Documentation. 

If there is a requirement to explicitly set the storage engine default this can be done by adding the bel;ow for the [mysqld] section of the MariaDB option file :

default-storage-engine = InnoDB

Most of these configuration options can be used with the XtraDB storage engine. 

InnoDB Configuration Parameters  

Parameter Name Description Default Value  Suggested Value 
innodb_data_file_path Specifies the location of individual innodb_data files, paths and their associated sizes.  ibdata1:12M:autoextend (from MariaDB 10.0), ibdata1:10M:autoextend (before MariaDB 10.0) Retain the defaults unless otherwise required, but keep all of the files in a single FlashArray volume. 
innodb_write_io_threads Number of I/O threads to use for write operations.  4 If the MariaDB server has enough cores increase this value to increase IO throughput. 
innodb_read_io_threads Number of I/O threads to use for read operations.  4 If the MariaDB server has enough cores increase this value to increase IO throughput. 
innodb_flush_log_at_trx_commit Controls log flushing and writing  1

Data integrity - set to 1

Performance - set to 2

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 nmore 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 onnodb_max_dirty_pages_pct.
innodb_flush_method

Controls the flushing method. Microsoft Windows will always use async unbuffered.

For Linux and Unix systems adjusting this value can increase performance depending on the underlying filesystem and storage configuration. 

O_DIRECT

Linux

XFS : Use O_DIRECT

EXT4 : O_DIRECT_NO_FSYNC for higher performance. 

innodb_file_per_table If set to on will create new InnoDB tables in their own file per table tablespace.  ON ON
innodb_doublewrite Write data to doublewrite buffer before writing to data file. ON

Data integrity - set to On

Performance - set Off

innodb_open_files Maximum number of IBD files MariaDB can have open at the same time.   if innodb_file_per_table is disabled then 300 or the value of table_open_cache , whichever is higher.  > 512 but also depends on application design. 
innodb_use_native_aio Specifies in the linux asynchronous I/O subsystem is used.  1 (ON) 1 (ON)
innodb_checksum_algorithm Specifies how the nnoDB tablespace checksum is generated and verified.  full_crc32 (>= MariaDB 10.5.0)
crc32 (>= MariaDB 10.2.2)
innodb (<= MariaDB 10.2.1)
strict_crc32 (Can only be used when setting up tablespaces for the first time)
innodb_io_capacity Sets a value for limiting InnoDB background tasks.  200 Set to a high value ( 2000 or more) and then use FlashArray volume QOS to control IO. 
innodb_adaptive_flushing If on (1) will dynamically adjust the flush rate of dirty pages in the InnoDB buffer pool. If set to off (0) adaptive flushing will only take place when the limit specified by innodb_adaptive_flushing_lvm is reached.  1 1
innodb_adaptive_flushing_lwm Adaptive flushing is enabled when this low water mark percentage of the InnoDB redo log is reached.  10 10
innodb_flush_neighbors Determines if flushing a page from the buffer pool will flush other dirty pages in the same extent.  1 0

MySQL option file examples  

Linux  

#
# include *.cnf from the config directory
#
!includedir /etc/my.cnf.d
#
# This group is read both by the client and the server
# use it for options that affect everything
#
[client-server]
socket=/var/lib/mysql/mysql.sock
[client]
port = 3306

[mysqld]
#####################################
#         Server management         #
#####################################
port = 3306
bind-address = 0.0.0.0
back_log = 0
max_connections = 4000
wait_timeout = 1024
max_connect_errors = 10
table_open_cache = 8000
table_open_cache_instances = 16
max_allowed_packet = 32M
max_heap_table_size = 512M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
sort_buffer_size = 64M
join_buffer_size = 64M
thread_cache_size = 512
thread_stack = 240K
default-storage-engine = InnoDB
transaction_isolation = REPEATABLE-READ
log-error=/var/log/mysqld.log

#####################################
#        InnoDB Configuration       #
#####################################
innodb_buffer_pool_size = 100G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 64
innodb_read_io_threads = 64
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 1GB
innodb_change_buffer_max_size = 25
innodb_max_dirty_pages_pct=90
innodb_max_dirty_pages_pct_lwm=10
innodb_log_file_size=32G
innodb_lock_wait_timeout = 256
innodb_flush_method = O_DIRECT
innodb_file_per_table
innodb_doublewrite=1
innodb_open_files=32768
innodb_use_native_aio=1
innodb_spin_wait_delay=6
innodb_max_purge_lag_delay=300000
innodb_max_purge_lag=0
innodb_checksum_algorithm=strict_crc32
innodb_io_capacity=10000
innodb_io_capacity_max=30000
innodb_change_buffering=none
innodb_read_only=0
innodb_undo_log_truncate=off
innodb_adaptive_flushing=0
innodb_adaptive_flushing_lwm=10
innodb_flush_neighbors=0