InnoDB Configuration Parameters
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