InnoDB Configuration Parameters

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

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