This page documents system variables related to the XtraDB/InnoDB storage engine. For options that are not system variables, see InnoDB Options.
See Server System Variables for a complete list of system variables and instructions on setting them.
Also see the Full list of MariaDB options, system and status variables.
have_innodb
YES
, otherwise will be set to NO
. Removed in MariaDB 10.0, use the Information Schema PLUGINS table or SHOW ENGINES instead. ignore_builtin_innodb
1
results in the built-in InnoDB storage engine being ignored. In some versions of MariaDB, XtraDB is the default and is always present, so this variable is ignored and setting it results in a warning. From MariaDB 10.0.1 to MariaDB 10.0.8, when InnoDB was the default instead of XtraDB, this variable needed to be set. Usually used in conjunction with the plugin-load=innodb=ha_innodb option to use the InnoDB plugin. --ignore-builtin-innodb
boolean
OFF
innodb_adaptive_checkpoint
modified age / maximum checkpoint age
. Can result in larger transaction log files reflex
Similar to innodb_max_dirty_pages_pct flushing but flushes blocks constantly and contiguously based on the oldest modified age. If the age exceeds 1/2 of the maximum age capacity, flushing will be weak contiguous. If the age exceeds 3/4, flushing will be strong. Strength can be adjusted by the variable innodb_io_capacity. estimate
The default, and independent of innodb_io_capacity. If the oldest modified age exceeds 1/2 of the maximum age capacity, blocks will be flushed every second at a rate determined by the number of modified blocks, LSN progress speed and the average age of all modified blocks. keep_average
Attempts to keep the I/O rate constant by using a shorter loop cycle of one tenth of a second. Designed for SSD cards. --innodb-adaptive-checkpoint=#
string
estimate
none
or 0
, reflex
or 1
, estimate
or 2
, keep_average
or 3
innodb_adaptive_flushing
1
, the default, the server will dynamically adjust the flush rate of dirty pages in the InnoDB buffer pool. This assists to reduce brief bursts of I/O activity. --innodb-adaptive-flushing=#
boolean
ON
innodb_adaptive_flushing_lwm
--innodb-adaptive-flushing-lwm=#
double
10.000000
0
to 70
innodb_adaptive_flushing_method
native
or 0
, the original InnoDB method is used. The maximum checkpoint age is determined by the total length of all transaction log files. When the checkpoint age reaches the maximum checkpoint age, blocks are flushed. This can cause lag if there are many updates per second and many blocks with an almost identical age need to be flushed. If set to estimate
or 1
, the default, the oldest modified age will be compared with the maximum age capacity. If it's more than 1/4 of this age, blocks are flushed every second. The number of blocks flushed is determined by the number of modified blocks, the LSN progress speed and the average age of all modified blocks. It's therefore independent of the innodb_io_capacity for the 1-second loop, but not entirely so for the 10-second loop. If set to keep_average
or 2
, designed specifically for SSD cards, a shorter loop cycle is used in an attempt to keep the I/O rate constant. Removed in MariaDB 10.0/XtraDB 5.6 and replaced with InnoDB flushing method from MySQL 5.6. innodb-adaptive-flushing-method=value
enumeration
estimate
native
or 0
, estimate
or 1
, keep_average
or 2
innodb_adaptive_hash_index
1
, the default, the InnoDB hash index is enabled. --innodb-adaptive-hash-index=#
boolean
ON
innodb_adaptive_hash_index_partitions
1
, no extra partitions are created. XtraDB-only. From MariaDB 10.2.6 (which uses InnoDB as default instead of XtraDB), this is an alias for innodb_adaptive_hash_index_parts to allow for easier upgrades. innodb-adaptive-hash-index-partitions=#
numeric
1
1
to 64
innodb_adaptive_hash_index_parts
1
, no extra partitions are created. innodb-adaptive-hash-index-parts=#
numeric
8
1
to 512
innodb_adaptive_max_sleep_delay
--innodb-adaptive-max-sleep-delay=#
numeric
150000
0
to 1000000
innodb_additional_mem_pool_size
--innodb-additional-mem-pool-size=#
numeric
8388608
2097152
to 4294967295
innodb_api_bk_commit_interval
--innodb-api-bk-commit-interval=#
numeric
5
1
to 1073741824
innodb_api_disable_rowlock
--innodb-api-disable-rowlock=#
boolean
OFF
innodb_api_enable_binlog
--innodb-api-enable-binlog=#
boolean
OFF
innodb_api_enable_mdl
--innodb-api-enable-mdl=#
boolean
OFF
innodb_api_trx_level
--innodb-api-trx-level=#
numeric
0
innodb_auto_lru_dump
--innodb-auto-lru-dump=#
innodb_autoextend_increment
1
, this setting does not apply to the resulting per-table tablespace files, which are automatically extended in their own way. --innodb-autoextend-increment=#
numeric
64
(from MariaDB 10.0) 8
(before MariaDB 10.0), 1
to 1000
innodb_autoinc_lock_mode
AUTO_INCREMENT
values for InnoDB tables. 0
is the traditional lock mode. 1
is the consecutive lock mode. 2
is the interleaved lock mode. 2
. --innodb-autoinc-lock-mode=#
numeric
1
0
to 2
innodb_background_scrub_data_check_interval
--innodb-background-scrub-data-check-interval=#
numeric
3600
1
to 4294967295
innodb_background_scrub_data_compressed
--innodb-background-scrub-data-compressed={0|1}
boolean
0
innodb_background_scrub_data_interval
--innodb-background-scrub-data-interval=#
numeric
604800
1
to 4294967295
innodb_background_scrub_data_uncompressed
--innodb-background-scrub-data-uncompressed={0|1}
boolean
0
innodb_blocking_buffer_pool_restore
1
(0
is default), XtraDB will wait until the least-recently used (LRU) dump is completely restored upon restart before reporting back to the server that it has successfully started up. Available with XtraDB only, not InnoDB. innodb-blocking-buffer-pool-restore={1|2}
boolean
OFF
innodb_buf_dump_status_frequency
10
means that the buffer pool dump status is printed when every 10% of the number of buffer pool pages are dumped. The default is 0
(only start and end status is printed). --innodb-buf-dump-status-frequency=#
numeric
0
0
to 100
innodb_buffer_pool_chunk_size
--innodb-buffer-pool-chunk-size=#
numeric
134217728
1048576
to innodb_buffer_pool_size/innodb_buffer_pool_instances innodb_buffer_pool_dump_at_shutdown
--innodb-buffer-pool-dump-at-shutdown=#
boolean
ON
(>= MariaDB 10.2.2) OFF
(<= MariaDB 10.2.1) innodb_buffer_pool_dump_now
--innodb-buffer-pool-dump-now=#
boolean
OFF
innodb_buffer_pool_dump_pct
--innodb-buffer-pool-dump-pct=#
boolean
25
(>= MariaDB 10.2.2) 100
(<= MariaDB 10.2.1) 1
to 100
innodb_buffer_pool_evict
--innodb-buffer-pool-evict=#
string
""
innodb_buffer_pool_filename
--innodb-buffer-pool-filename=file
string
ib_buffer_pool
innodb_buffer_pool_instances
--innodb-buffer-pool-instances=#
numeric
1
8
, 1
(>= MariaDB 10.2.2 if innodb_buffer_pool_size < 1GB), or dependent on innodb_buffer_pool_size (Windows 32-bit) innodb_buffer_pool_load_abort
--innodb-buffer-pool-load-abort=#
boolean
OFF
innodb_buffer_pool_load_at_startup
--innodb-buffer-pool-load-at-startup=#
boolean
ON
(>= MariaDB 10.2.2) OFF
(<= MariaDB 10.2.1) innodb_buffer_pool_load_now
--innodb-buffer-pool-load-now=#
boolean
OFF
innodb_buffer_pool_populate
1
(0
is default), XtraDB will preallocate pages in the buffer pool on starting up so that NUMA allocation decisions are made while the buffer cache is still clean. XtraDB only. This option was made ineffective in MariaDB 10.0.23. Added as a deprecated and ignored option in MariaDB 10.2.6 (which uses InnoDB as default instead of XtraDB) to allow for easier upgrades. innodb-buffer-pool-populate={0|1}
boolean
OFF
innodb_buffer_pool_restore_at_startup
0
, automatic dumps are not performed, nor automatic restores on startup. Replaced by innodb_buffer_pool_load_at_startup in MariaDB 10.0. innodb-buffer-pool-restore-at-startup
numeric
0
0
to 4294967295
0
to 18446744073709547520
innodb_buffer_pool_shm_checksum
innodb-buffer-pool-shm-checksum={0|1}
boolean
ON
innodb_buffer_pool_shm_key
innodb-buffer-pool-shm-key={0|1}
boolean
0
innodb_buffer_pool_size
--innodb-buffer-pool-size=#
numeric
134217728
(128MB) 5242880
(5MB) to 9223372036854775807
(8192PB) innodb_change_buffer_max_size
--innodb-change-buffer-max-size=#
numeric
25
0
to 50
innodb_change_buffering
--innodb-change-buffering=#
enumeration
(>= MariaDB 10.3.7), string
(<= MariaDB 10.3.6) all
inserts
inserts
, none
, deletes
, purges
, changes
, all
inserts
, none
innodb_change_buffering_debug
1
, an XtraDB/InnoDB Change Buffering debug flag is set. 1
forces all changes to the change buffer, while 2
causes a crash at merge. 0
, the default, indicates no flag is set. Only available in debug builds. --innodb-change-buffering-debug=#
numeric
0
0
to 2
innodb_checkpoint_age_target
0
, has no effect. Removed in MariaDB 10.0/XtraDB 5.6 and replaced with InnoDB flushing method from MySQL 5.6. innodb-checkpoint-age-target=#
numeric
0
0
upwards innodb_checksum_algorithm
innodb
: Backwards compatible with earlier versions. crc32
: A newer, faster algorithm, but incompatible with earlier versions. Tablespace blocks will be converted to the new format over time, meaning that a mix of checksums may be present. full_crc32
: From MariaDB 10.4.3. Permits encryption to be supported over a SPATIAL INDEX, which crc32
does not support. Newly-created data files will carry a flag that indicates that all pages of the file will use a full CRC-32C checksum over the entire page contents (excluding the bytes where the checksum is stored, at the very end of the page). Such files will always use that checksum, no matter what the parameter innodb_checksum_algorithm is assigned to. ROW_FORMAT=COMPRESSED tables will only use the old format. These tables do not support new features, such as larger innodb_page_size or instant ADD/DROP COLUMN. Also cleans up the MariaDB tablespace flags - flags are reserved to store the page_compressed compression algorithm, and to store the compressed payload length, so that checksum can be computed over the compressed (and possibly encrypted) stream and can be validated without decrypting or decompressing the page. In the full_crc32 format, there no longer are separate before-encryption and after-encryption checksums for pages. The single checksum is computed on the page contents that is written to the file.See MDEV-12026 for details. none
: Writes a constant rather than calculate a checksum. strict_*
: The strict_*
options are the same as the regular options, but InnoDB will halt if it comes across a mix of checksum values. These are faster, as both new and old checksum values are not required, but can only be used when setting up tablespaces for the first time. --innodb-checksum-algorithm=#
enumeration
full_crc32
(>= MariaDB 10.5.0) crc32
(>= MariaDB 10.2.2) innodb
(<= MariaDB 10.2.1) innodb
, crc32
, full_crc32
(>= MariaDB 10.4.3), none
, strict_innodb
, strict_crc32
, strict_none
, strict_full_crc32
(>= MariaDB 10.4.3) innodb_checksums
1
in production environments, although setting it to 0
can provide marginal performance improvements. Deprecated and functionality replaced by innodb_checksum_algorithm in MariaDB 10.0, and should be removed to avoid conflicts. ON
is equivalent to --innodb_checksum_algorithm=innodb
and OFF
to --innodb_checksum_algorithm=none
. --innodb-checksums
, --skip-innodb-checksums
boolean
ON
innodb_cleaner_lsn_age_factor
high_checkpoint
, uses the new formula, while the alternative, legacy
, uses the original algorithm. XtraDB only. Added as a deprecated and ignored option in MariaDB 10.2.6 (which uses InnoDB as default instead of XtraDB) to allow for easier upgrades. --innodb-cleaner-lsn-age-factor=value
enum
deprecated
(>= MariaDB 10.2.6) high_checkpoint
(<= MariaDB 10.1) high_checkpoint
, legacy
(<= MariaDB 10.1) deprecated
, high_checkpoint
, legacy
(>= MariaDB 10.2.6) innodb_cmp_per_index_enabled
ON
(OFF
is default), per-index compression statistics are stored in the INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table. These are expensive to record, so this setting should only be changed with care, such as for performance tuning on development or slave servers. --innodb-cmp-per-index-enabled=#
boolean
OFF
innodb_commit_concurrency
--innodb-commit-concurrency=#
numeric
0
0
to 1000
innodb_compression_algorithm
none
: Pages are not compressed. zlib
: Pages are compressed using the bundled zlib
compression algorithm. lz4
: Pages are compressed using the lz4
compression algorithm. lzo
: Pages are compressed using the lzo
compression algorithm. lzma
: Pages are compressed using the lzma
compression algorithm. bzip2
: Pages are compressed using the bzip2
compression algorithm. snappy
: Pages are compressed using the snappy
algorithm. --innodb-compression-algorithm=value
enum
zlib
(>= MariaDB 10.2.4, MariaDB 10.1.22), none
(<= MariaDB 10.2.3, MariaDB 10.1.21) none
, zlib
, lz4
, lzo
, lzma
, bzip2
or snappy
(MariaDB 10.1.3) innodb_compression_default
OFF
, which means new tables are not compressed. --innodb-compression-default={0|1}
boolean
OFF
innodb_compression_failure_threshold_pct
--innodb-compression-failure-threshold-pct=#
numeric
5
0
to 100
innodb_compression_level
1
and 9
. The default compression level is 6
. The range goes from the fastest to the most compact, which means that 1
is the fastest and 9
is the most compact. --innodb-compression-level=#
numeric
6
1
to 9
innodb_compression_pad_pct_max
--innodb-compression-pad-pct-max=#
numeric
50
0
to 75
innodb_concurrency_tickets
--innodb-concurrency-tickets=#
numeric
5000
(from MariaDB 10.0), 500
(before mariaDB 10,0) innodb_corrupt_table_action
assert
, the default, XtraDB will intentionally crash the server when it detects corrupted data in a single-table tablespace, with an assertion failure. warn
, it will pass corruption as corrupt table instead of crashing, and disable all further I/O (except for deletion) on the table file. salvage
, read access is permitted, but corrupted pages are ignored. innodb_file_per_table must be enabled for this option. Previously named innodb_pass_corrupt_table
. innodb-corrupt-table-action=value
enumeration
assert
(<= MariaDB 10.1) deprecated
(<= MariaDB 10.2.6) deprecated
, assert
, warn
, salvage
(>= MariaDB 10.2.6) assert
, warn
, salvage
(<= MariaDB 10.1) innodb_data_file_path
--innodb-data-file-path=name
numeric
ibdata1:12M:autoextend
(from MariaDB 10.0), ibdata1:10M:autoextend
(before MariaDB 10.0) innodb_data_home_dir
--innodb-data-home-dir=path
directory name
The MariaDB data directory
innodb_deadlock_detect
--innodb-deadlock-detect
boolean
1
innodb_default_page_encryption_key
--innodb-default-page-encryption-key=#
numeric
1
1
to 255
innodb_default_encryption_key_id
--innodb-default-encryption-key-id=#
numeric
1
1
to 4294967295
innodb_default_row_format
--innodb-default-row-format=value
enum
dynamic
(>= MariaDB 10.2.2), compact
(>= MariaDB 10.1.32) redundant
, compact
or dynamic
innodb_defragment
1
(the default is 0
), InnoDB defragmentation is enabled. When set to FALSE, all existing defragmentation will be paused and new defragmentation commands will fail. Paused defragmentation commands will resume when this variable is set to true again. See Defragmenting InnoDB Tablespaces. --innodb-defragment=#
boolean
OFF
innodb_defragment_fill_factor
--innodb-defragment-fill-factor=#
double
0.9
0.7
to 1
innodb_defragment_fill_factor_n_recs
--innodb-defragment-fill-factor-n-recs=#
numeric
20
1
to 100
innodb_defragment_frequency
--innodb-defragment-frequency=#
integer
40
1
to 1000
innodb_defragment_n_pages
--innodb-defragment-n-pages=#
numeric
7
2
to 32
innodb_defragment_stats_accuracy
--innodb-defragment-stats-accuracy=#
numeric
0
0
to 4294967295
innodb_dict_size_limit
0
, the default and standard InnoDB behavior, there is no limit to memory usage. Removed in MariaDB 10.0/XtraDB 5.6 and replaced by MySQL 5.6's new table_definition_cache implementation. innodb-dict-size-limit=#
numeric
0
2147483648
9223372036854775807
innodb_disable_sort_file_cache
1
(0
is default), the operating system file system cache for merge-sort temporary files is disabled. --innodb-disable-sort-file-cache=#
boolean
OFF
innodb_disallow_writes
boolean
OFF
innodb_doublewrite
1
, the default, to improve fault tolerance InnoDB first stores data to a doublewrite buffer before writing it to data file. Disabling will provide a marginal peformance improvement. --innodb-doublewrite
, --skip-innodb-doublewrite
boolean
ON
innodb_doublewrite_file
innodb-doublewrite-file=filename
filename
NULL
innodb_empty_free_list_algorithm
backoff
, the default, the new algorithm will be used. If set to legacy
, the original InnoDB algorithm will be used. XtraDB only. Added as a deprecated and ignored option in MariaDB 10.2.6 (which uses InnoDB as default instead of XtraDB) to allow for easier upgrades. See #1651657 for the reasons this was changed back to legacy
in XtraDB 5.6.36-82.0. innodb-empty-free-list-algorithm=value
enum
deprecated
(>= MariaDB 10.2.6) legacy
(>= MariaDB 10.1.24) backoff
(<= MariaDB 10.1.23) deprecated
, backoff
, legacy
(>= MariaDB 10.2.6) backoff
, legacy
(<= MariaDB 10.1) innodb_enable_unsafe_group_commit
0
, the default, InnoDB will keep transactions between the transaction log and binary logs in the same order. Safer, but slower. If set to 1
, transactions can be group-committed, but there is no guarantee of the order being kept, and a small risk of the two logs getting out of sync. In write-intensive environments, can lead to a significant improvement in performance. --innodb-enable-unsafe-group-commit
numeric
0
0
to 1
innodb_encrypt_log
--innodb-encrypt-log
boolean
OFF
innodb_encrypt_tables
OFF
- Disables table encryption for all new and existing tables that have the ENCRYPTED
table option set to DEFAULT
. ON
- Enables table encryption for all new and existing tables that have the ENCRYPTED
table option set to DEFAULT
, but allows unencrypted tables to be created. FORCE
- Enables table encryption for all new and existing tables that have the ENCRYPTED
table option set to DEFAULT
, and doesn't allow unencrypted tables to be created (CREATE TABLE ... ENCRYPTED=NO will fail). --innodb-encrypt-tables=value
boolean
OFF
ON
, OFF
, FORCE
(from MariaDB 10.1.4) innodb_encrypt_temporary_tables
--innodb-encrypt-temporary-tables=value
boolean
OFF
ON
, OFF
innodb_encryption_rotate_key_age
innodb_encrypt_tables
MariaDB won't be able to automatically encrypt any unencrypted tables. --innodb-encryption-rotate-key-age=#
numeric
1
0
to 4294967295
innodb_encryption_rotation_iops
--innodb-encryption-rotation_iops=#
numeric
100
0
to 4294967295
innodb_encryption_threads
innodb_encrypt_tables
MariaDB won't be able to automatically encrypt any unencrypted tables. --innodb-encryption-threads=#
numeric
0
0
to 4294967295
innodb_extra_rsegments
--innodb-extra-rsegments=#
numeric
0
0
to 126
innodb_extra_undoslots
1
expands the available undo slots to 4072. Not recommended unless you get the Warning: cannot find a free slot for an undo log
error in the error log, as it makes data files unusable for ibbackup, or MariaDB servers not run with this option. See also undo log. --innodb-extra-undoslots=#
boolean
OFF
innodb_fake_changes
--innodb-fake-changes={0|1}
boolean
OFF
innodb_fast_checksum
1
on a server with tables that have been created with it set to 0
, reads will be slower, so tables should be recreated (dumped and reloaded). XtraDB will fail to start if set to 0
and there are tables created while set to 1
. Replaced with innodb_checksum_algorithm in MariaDB 10.0/XtraDB 5.6. --innodb-fast-checksum={0|1}
boolean
OFF
innodb_fast_shutdown
0
- InnoDB performs a slow shutdown, including full purge (before MariaDB 10.3.6, not always, due to MDEV-13603) and change buffer merge. Can be very slow, even taking hours in extreme cases. 1
- the default, InnoDB performs a fast shutdown, not performing a full purge or an insert buffer merge. 2
, the InnoDB redo log is flushed and a cold shutdown takes place, similar to a crash. The resulting startup then performs crash recovery. Extremely fast, in cases of emergency, but risks corruption. 3
(from MariaDB 10.3.6) - active transactions will not be rolled back, but all changed pages will be written to data files. The active transactions will be rolled back by a background thread on a subsequent startup. The fastest option that will not involve InnoDB redo log apply on subsequent startup. See MDEV-15832. --innodb-fast-shutdown[=#]
numeric
1
0
to 3
(>= MariaDB 10.3.6), 0
to 2
(<= MariaDB 10.3.5) innodb_fatal_semaphore_wait_threshold
--innodb-fatal-semaphore-wait-threshold=#
numeric
600
1
to 4294967295
innodb_file_format
Antelope
, the default and the original format, or Barracuda
, which supports compression. Note that this value is also used when a table is re-created with an ALTER TABLE which requires a table copy. See XtraDB/InnoDB File Format for more on the file formats. Removed in 10.3.1 and restored as a deprecated and unused variable in 10.4.3 for compatibility purposes. --innodb-file-format=value
string
Barracuda
(>= MariaDB 10.2.2) Antelope
(<= MariaDB 10.2.1) Antelope
, Barracuda
innodb_file_format_check
1
, the default, InnoDB checks the shared tablespace file format tag. If this is higher than the current version supported by XtraDB/InnoDB (for example Barracuda when only Antelope is supported), XtraDB/InnoDB will will not start. If it the value is not higher, XtraDB/InnoDB starts correctly and the innodb_file_format_max value is set to this value. If innodb_file_format_check is set to 0
, no checking is performed. See XtraDB/InnoDB File Format for more on the file formats. --innodb-file-format-check=#
boolean
(>= MariaDB 5.5) ON
(>= MariaDB 5.5) innodb_file_format_max
--innodb-file-format-max=value
string
Antelope
Antelope
, Barracuda
innodb_file_per_table
ON
, then new InnoDB tables are created with their own InnoDB file-per-table tablespaces. If set to OFF
, then new tables are created in the InnoDB system tablespace instead. Page compression is only available with file-per-table tablespaces. Note that this value is also used when a table is re-created with an ALTER TABLE which requires a table copy. --innodb-file-per-table
boolean
ON
(>= MariaDB 5.5), OFF
(<= MariaDB 5.3) innodb_fill_factor
70
, for example, reserves 30% of the space on each B-tree page for the index to grow in future. --innodb-fill-factor=#
numeric
100
10
to 100
innodb_flush_log_at_timeout
numeric
1
0
to 2700
innodb_flush_log_at_trx_commit
1
, along with sync_binlog=1 for the greatest level of fault tolerance. The value of innodb_use_global_flush_log_at_trx_commit determines whether this variable can be reset with a SET statement or not. 1
The default, the log buffer is written to the InnoDB redo log file and a flush to disk performed after each transaction. This is required for full ACID compliance. 0
Nothing is done on commit; rather the log buffer is written and flushed to the InnoDB redo log once a second. This gives better performance, but a server crash can erase the last second of transactions. 2
The log buffer is written to the InnoDB redo log after each commit, but flushing takes place once a second. Performance is slightly better, but a OS or power outage can cause the last second's transactions to be lost. 3
(from MariaDB 10.0) Emulates MariaDB 5.5 group commit (3 syncs per group commit). See Binlog group commit and innodb_flush_log_at_trx_commit. --innodb-flush-log-at-trx-commit[=#]
enumeration
1
0
, 1
, 2
or 3
(from MariaDB 10.0) innodb_flush_method
O_DSYNC
- O_DSYNC is used to open and flush logs, and fsync() to flush the data files. O_DIRECT
- O_DIRECT or directio(), is used to open data files, and fsync() to flush data and logs. fsync
- Default on Unix. Can be specified directly, but if the variable is unset on Unix, fsync() will be used by default. O_DIRECT_NO_FSYNC
- introduced in MariaDB 10.0. Uses O_DIRECT during flushing I/O, but skips fsync() afterwards. Not suitable for XFS filesystems. ALL_O_DIRECT
- introduced in MariaDB 5.5 and available with XtraDB only. Uses O_DIRECT for opening both data and logs and fsync() to flush data but not logs. Use with large InnoDB files only, otherwise may cause a performance degradation. Set innodb_log_block_size to 4096 on ext4 filesystems. This is the default log block size on ext4 and will avoid unaligned AIO/DIO warnings. unbuffered
- Windows-only default async_unbuffered
- Windows-only, alias for unbuffered
normal
- Windows-only, alias for fsync
--innodb-flush-method=name
enumeration
(>= MariaDB 10.3.7), string
(<= MariaDB 10.3.6) fsync
(>= MariaDB 10.3.7) fsync
, O_DSYNC
, O_DIRECT
, O_DIRECT_NO_FSYNC
(>=MariaDB 10.0), ALL_O_DIRECT
(>= MariaDB 5.5 to <= MariaDB 10.1, XtraDB only) unbuffered
, async_unbuffered
, normal
innodb_flush_neighbor_pages
none
, the feature is disabled. If set to area
, the default, the standard InnoDB behavior is used. For each page to be flushed, dirty neighboring pages are flushed too. If there's little head seek delay, such as SSD or large enough write buffer, one of the other two options may be more efficient. If set to cont
, for each page to be flushed, neighboring contiguous blocks are flushed at the same time. Being contiguous, a sequential I/O is used, unlike the random I/O used in area
. Replaced by innodb_flush_neighbors in MariaDB 10.0/XtraDB 5.6. innodb-flush-neighbor-pages=value
enumeration
area
none
or 0
, area
or 1
, cont
or 2
innodb_flush_neighbors
1
: The default, flushes contiguous dirty pages in the same extent from the buffer pool. 0
: No other dirty pages are flushed. 2
: Flushes dirty pages in the same extent from the buffer pool. --innodb-flush-neighbors=#
enumeration
1
0
, 1
, 2
innodb_flush_sync
ON
, the default, the innodb_io_capacity setting is ignored for I/O bursts occuring at checkpoints. --innodb-flush-sync={0|1}
boolean
ON
innodb_flushing_avg_loops
--innodb-flushing-avg-loops=#
numeric
30
1
to 1000
innodb_force_load_corrupted
0
by default, if set to 1
, XtraDB/InnoDB will be permitted to load tables marked as corrupt. Only use this to recover data you can't recover any other way, or in troubleshooting. Always restore to 0
when the returning to regular use. --innodb-force-load-corrupted
boolean
OFF
innodb_force_primary_key
1
(0
is default) CREATE TABLEs without a primary or unique key where all keyparts are NOT NULL will not be accepted, and will return an error. --innodb-force-primary-key
boolean
OFF
innodb_force_recovery
0
is the default. The other modes are for recovery purposes only, and no data can be changed while another mode is active. Some queries relying on indexes are also blocked. See XtraDB/InnoDB Recovery Modes for more on mode specifics. --innodb-force-recovery=#
enumeration
0
0
to 6
innodb_foreground_preflush
exponential_backoff
- thread sleeps while it waits for the flush list flush to occur. The sleep time randomly progressively increases, periodically reset to avoid runaway sleeps. sync_preflush
- thread issues a flush list batch, and waits for it to complete. This is the same as is used when the page cleaner thread is not running. innodb-foreground-preflush=value
enum
deprecated
(>= MariaDB 10.2.6) exponential_backoff
(<= MariaDB 10.1) deprecated
, exponential_backoff
, sync_preflush
(>= MariaDB 10.2.6) exponential_backoff
, sync_preflush
(<= MariaDB 10.1) innodb_ft_aux_table
test/ft_innodb
) of an InnoDB table that has a FULLTEXT index, and after being set the INFORMATION_SCHEMA tables INNODB_FT_INDEX_TABLE, INNODB_FT_INDEX_CACHE, INNODB_FT_CONFIG, INNODB_FT_DELETED, and INNODB_FT_BEING_DELETED will contain search index information for the specified table. --innodb-ft-aux-table=value
string
innodb_ft_cache_size
--innodb-ft-cache-size=#
numeric
8000000
innodb_ft_enable_diag_print
1
, additional full-text search diagnostic output is enabled. --innodb-ft-enable-diag-print=#
boolean
OFF
innodb_ft_enable_stopword
1
, the default, a set of stopwords is associated with an InnoDB FULLTEXT index when it is created. The stopword list comes from the table set by the session variable innodb_ft_user_stopword_table, if set, otherwise the global variable innodb_ft_server_stopword_table, if that is set, or the built-in list if neither variable is set. --innodb-ft-enable-stopword=#
boolean
ON
innodb_ft_max_token_size
--innodb-ft-max-token-size=#
numeric
84
10
to 252
innodb_ft_min_token_size
--innodb-ft-min-token-size=#
numeric
3
0
to 16
innodb_ft_num_word_optimize
--innodb-ft-num-word-optimize=#
numeric
2000
innodb_ft_result_cache_limit
--innodb-ft-result-cache-limit=#
numeric
2000000000
1000000
to 4294967295
(<= MariaDB 10.2.18, MariaDB 10.1.36, MariaDB 10.0.36) 1000000
to 18446744073709551615
(64-bit, >= MariaDB 10.2.19, MariaDB 10.1.37, MariaDB 10.0.37) innodb_ft_server_stopword_table
--innodb-ft-server-stopword-table=db_name/table_name
string
innodb_ft_sort_pll_degree
--innodb-ft-sort-pll-degree=#
numeric
2
1
to 32
innodb_ft_total_cache_size
--innodb-ft-total-cache-size=#
numeric
640000000
32000000
to 1600000000
innodb_ft_user_stopword_table
--innodb-ft-user-stopword-table=db_name/table_name
string
innodb_ibuf_accel_rate
innodb_ibuf_accel_rate
is increased from its default value of 100
, the lowest setting, insert buffer activity is increased. See also innodb_io_capacity. This Percona XtraDB variable has not been ported to XtraDB 5.6. innodb-ibuf-accel-rate=#
numeric
100
100
to 999999999
innodb_ibuf_active_contract
0
, the standard InnoDB method is used, and the buffer is not processed until it's full. If set to 1
, the default, the insert buffer can be processed before it is full. This Percona XtraDB variable has not been ported to XtraDB 5.6. innodb-ibuf-active-contract=#
numeric
1
0
to 1
innodb_ibuf_max_size
0
, the insert buffer is disabled, which will cause all secondary index updates to be performed synchronously, usually at a cost to performance. This Percona XtraDB variable has not been ported to XtraDB 5.6. innodb-ibuf-max-size=#
numeric
0
to 1/2 the size of the InnoDB buffer pool innodb_idle_flush_pct
--innodb-idle-flush-pct=#
numeric
100
0
to 100
innodb_immediate_scrub_data_uncompressed
--innodb-immediate-scrub-data-uncompressed=#
boolean
OFF
innodb_import_table_from_xtrabackup
1
, permits importing of .ibd files exported with the XtraBackup --export option. Previously named innodb_expand_import
. Removed in MariaDB 10.0/XtraDB 5.6 and replaced with MySQL 5.6's transportable tablespaces. innodb-import-table-from-xtrabackup=#
numeric
0
0
to 1
innodb_instrument_semaphores
--innodb-instrument-semaphores={0|1}
boolean
OFF
OFF
) innodb_io_capacity
--innodb-io-capacity=#
numeric
200
100
to 18446744073709551615
(264-1) innodb_io_capacity_max
--innodb-io-capacity-max=#
numeric
2000
100
to 18446744073709551615
(264-1) innodb_kill_idle_transaction
0
(the default), the feature is disabled. Used to prevent accidental user locks. XtraDB only. Added as a deprecated and ignored option in MariaDB 10.2.6 (which uses InnoDB as default instead of XtraDB) to allow for easier upgrades. numeric
0
0
to 9223372036854775807
innodb_large_prefix
1
, tables that use specific [innodb-row-formats-overview|row formats]] are permitted to have index key prefixes up to 3072 bytes. If not set, the limit is 767 bytes. DYNAMIC
and COMPRESSED
row formats. --innodb-large-prefix
boolean
ON
(>= MariaDB 10.2.2) OFF
(<= MariaDB 10.2.1) innodb_lazy_drop_table
innodb_lazy_drop_table
is set to 1
(0
is default), XtraDB attempts to optimize DROP TABLE processing by deferring the dropping of related pages from the buffer pool until there is time, only initially marking them. innodb-lazy-drop-table={0|1}
boolean
0
innodb_lock_schedule_algorithm
FCFS
(First-Come-First-Served) where locks are granted in the order they appear in the lock queue and VATS
(Variance-Aware-Transaction-Scheduling) where locks are granted based on the Eldest-Transaction-First heuristic. Note that VATS
should not be used with Galera. From MariaDB 10.1.30, InnoDB will refuse to start if VATS
is used with Galera. From MariaDB 10.2, VATS
is default, but from MariaDB 10.2.12, the value will be changed to FCFS
and a warning produced when using Galera. --innodb-lock-schedule-algorithm=#
enum
FCFS
, VATS
VATS
(10.2), FCFS
(10.1) innodb_lock_wait_timeout
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
. When this occurs, the statement (not transaction) is rolled back. The whole transaction can be rolled back if the innodb_rollback_on_timeout option is used. Increase this for data warehousing applications or where other long-running operations are common, or decrease for OLTP and other highly interactive applications. This setting does not apply to deadlocks, which InnoDB detects immediately, rolling back a deadlocked transaction. 0
(from MariaDB 10.3.0) means no wait. See WAIT and NOWAIT. --innodb-lock-wait-timeout=#
numeric
50
0
to 1073741824
(>= MariaDB 10.3) 1
to 1073741824
(<= MariaDB 10.2) innodb_locking_fake_changes
OFF
, fake transactions (see innodb_fake_changes) don't take row locks. This is an experimental feature to attempt to deal with drawbacks in fake changes blocking real locks. It is not safe for use in all environments. XtraDB only. Added as a deprecated and ignored option in MariaDB 10.2.6 (which uses InnoDB as default instead of XtraDB) to allow for easier upgrades. --innodb-locking-fake-changes
boolean
ON
innodb_locks_unsafe_for_binlog
0
by default, in which case XtraDB/InnoDB uses gap locking. If set to 1
, gap locking is disabled for searches and index scans. Deprecated in MariaDB 10.0, and removed in MariaDB 10.5, use READ COMMITTED transaction isolation level instead. --innodb-locks-unsafe-for-binlog
boolean
OFF
innodb_log_arch_dir
--innodb-log-arch-dir=name
string
./
innodb_log_arch_expire_sec
--innodb-log-arch-expire-sec=#
numeric
0
innodb_log_archive
--innodb-log-archive=value
boolean
OFF
innodb_log_block_size
512
, the default, or 4096
, are the only two useful values. If the server is restarted and this value is changed, all old log files need to be removed. Should be set to 4096
for SSD cards or if innodb_flush_method is set to ALL_O_DIRECT
on ext4 filesystems. XtraDB only. Added as a deprecated and ignored option in MariaDB 10.2.6 (which uses InnoDB as default instead of XtraDB) to allow for easier upgrades. innodb-log-block-size=#
numeric
512
innodb_log_buffer_size
--innodb-log-buffer-size=#
numeric
16777216
(16MB) >= MariaDB 10.1.9, 8388608
(8MB) <= MariaDB 10.1.8 262144
to 4294967295
(256KB to 4096MB) innodb_log_checksum_algorithm
none
- No checksum. A constant value is instead written to logs, and no checksum validation is performed. innodb
- The default, and the original InnoDB algorithm. This is inefficient, but compatible with all MySQL, MariaDB and Percona versions that don't support other checksum algorithms. crc32
- CRC32© is used for log block checksums, which also permits recent CPUs to use hardware acceleration (on SSE4.2 x86 machines and Power8 or later) for the checksums. strict_*
- Whether or not to accept checksums from other algorithms. If strict mode is used, checksums blocks will be considered corrupt if they don't match the specified algorithm. Normally they are considered corrupt only if no other algorithm matches. innodb-log-checksum-algorithm=value
enum
deprecated
(>= MariaDB 10.2.6) innodb
(<= MariaDB 10.1) deprecated
, innodb
, none
, crc32
, strict_none
, strict_innodb
, strict_crc32
(>= MariaDB 10.2.6) innodb
, none
, crc32
, strict_none
, strict_innodb
, strict_crc32
(<= MariaDB 10.1) innodb_log_checksums
1
, the CRC32C for Innodb or innodb_log_checksum_algorithm
for XtraDB algorithm is used for InnoDB redo log pages. If disabled, the checksum field contents are ignored. From MariaDB 10.5.0, the variable is deprecated, and checksums are always calculated, as previously, the InnoDB redo log used the slow innodb algorithm, but with hardware or SIMD assisted CRC-32C computation being available, there is no reason to allow checksums to be disabled on the redo log. innodb-log-checksums={0|1}
boolean
ON
innodb_log_compressed_pages
--innodb-log-compressed-pages=#
boolean
ON
(>= MariaDB 10.2.4, >= MariaDB 10.1.26, <= MariaDB 10.1.1) OFF
(MariaDB 10.2.0 - MariaDB 10.2.3, MariaDB 10.1.2 - MariaDB 10.1.25) innodb_log_file_size
--innodb-log-file-size=#
numeric
50331648
(48MB) (from MariaDB 10.0), 5242880
(5MB) (before MariaDB 10.0) 1048576
to 512GB
(1MB to 512GB) (>= MariaDB 10.0), 1048576
to 4294967295
(1MB to 4096MB) (<= MariaDB 5.5), innodb_log_files_in_group
--innodb-log-files-in-group=#
numeric
2
1
to 100
(>= MariaDB 10.2.4), 2
to 100
(<= MariaDB 10.2.3) innodb_log_group_home_dir
--innodb-log-group-home-dir=path
directory name
innodb_log_optimize_ddl
--innodb-log-optimize-ddl={0|1}
boolean
ON
innodb_log_write_ahead_size
--innodb-log-write-ahead-size=#
numeric
8192
512
to innodb_page_size innodb_lru_scan_depth
--innodb-lru-scan-depth=#
numeric
1024
100
to 232-1
100
to 264-1
innodb_max_bitmap_file_size
innodb-max-bitmap-file-size=#
numeric
4096
(4KB) 4096
(4KB) to 18446744073709551615
(16EB) innodb_max_changed_pages
innodb_changed_pages_limit
. XtraDB only. innodb-max-changed-pages=#
numeric
1000000
0
to 18446744073709551615
innodb_max_dirty_pages_pct
--innodb-max-dirty-pages-pct=#
numeric
75
0
to 99.999
(from MariaDB 10.0.15), 0
to 99
(before MariaDB 10.0.15) innodb_max_dirty_pages_pct_lwm
0
, the default until MariaDB 10.0.15, preflushing is disabled. This variable was changed to a double in MariaDB 10.0.15. --innodb-max-dirty-pages-pct-lwm=#
numeric
0
(>= MariaDB 10.2.2, <= MariaDB 10.0.14), 0.001000
(>= MariaDB 10.0.15, <= MariaDB 10.2.1) 0
to 99.999
(>= MariaDB 10.0.15), 0
to 99
(<= MariaDB 10.0.14) innodb_max_purge_lag
0
, no lag, the figure is used to calculate a time lag for each INSERT, UPDATE, and DELETE when the system is lagging. XtraDB/InnoDB keeps a list of transactions with delete-marked index records due to UPDATE and DELETE statements. The length of this list is purge_lag
, and the calculation, performed every ten seconds, is as follows: ((purge_lag/innodb_max_purge_lag)×10)–5 milliseconds. --innodb-max-purge-lag=#
numeric
0
0
to 4294967295
innodb_max_purge_lag_delay
0
, the default, there is no maximum. --innodb-max-purge-lag-delay=#
numeric
0
innodb_max_undo_log_size
--innodb-max-undo-log-size=#
numeric
10485760
(>= MariaDB 10.2.6) 1073741824
(<= MariaDB 10.2.5) 10485760
to 18446744073709551615
innodb_merge_sort_block_size
innodb-merge-sort-block-size=#
numeric
1048576
(1M) 1048576
(1M) to 1073741824
(1G) innodb_mirrored_log_groups
innodb_mtflush_threads
innodb_page_cleaners
system variable instead. --innodb-mtflush-threads=#
numeric
8
1
to 64
innodb_monitor_disable
--innodb-monitor-disable=string
string
innodb_monitor_enable
--innodb-monitor-enable=string
string
innodb_monitor_reset
--innodb-monitor-reset=string
string
innodb_monitor_reset_all
---innodb-monitor-reset-all=string
string
innodb_numa_interleave
innodb-numa-interleave={0|1}
boolean
OFF
innodb_old_blocks_pct
--innodb-old-blocks-pct=#
numeric
37
5
to 95
innodb_old_blocks_time
--innodb-old-blocks-time=#
numeric
1000
(from MariaDB 10.0), 0
(before MariaDB 10.0) 0
to 232-1
innodb_online_alter_log_max_size
--innodb-online-alter-log-max-size=#
numeric
134217728
65536
to 264-1
innodb_open_files
10
. --innodb-open-files=#
numeric
autosized
(from MariaDB 10.0), 300
(before MariaDB 10.0) 10
to 4294967295
innodb_optimize_fulltext_only
1
(0
is default), OPTIMIZE TABLE will only process InnoDB FULLTEXT index data. Only intended for use during fulltext index maintenance. --innodb-optimize-fulltext-only=#
boolean
OFF
innodb_page_cleaners
4
, but the value will be set to the number of innodb_buffer_pool_instances if this is lower. If set to 1
, only a single cleaner thread is used, as was the case until MariaDB 10.2.1. Cleaner threads flush dirty pages from the buffer pool, performing flush list and least-recently used (LRU) flushing. --innodb-page-cleaners=#
numeric
4
(or set to innodb_buffer_pool_instances if lower) 1
to 64
innodb_page_size
16k
, is suitable for most uses. 64k
for tables using the following row formats: DYNAMIC
, COMPACT
, and REDUNDANT
. 16k
or less for tables using the COMPRESSED
row format. datadir
has been initialized. InnoDB's page size is set when a MariaDB instance starts, and it remains constant afterwards. --innodb-page-size=#
enumeration
16384
4k
or 4096
, 8k
or 8192
, 16k
or 16384
. MariaDB 10.1.0 and MySQL 5.7.6 also permits 32k
and 64k
. innodb_pass_corrupt_table
innodb_prefix_index_cluster_optimization
--innodb-prefix-index-cluster-optimization=#
boolean
OFF
innodb_print_all_deadlocks
1
(0
is default), all XtraDB/InnoDB transaction deadlock information is written to the error log. --innodb-print-all-deadlocks=#
boolean
OFF
innodb_purge_batch_size
--innodb-purge-batch-size=#
numeric
20
1
to 5000
innodb_purge_rseg_truncate_frequency
-- innodb-purge-rseg-truncate-frequency=#
numeric
128
1
to 128
innodb_purge_threads
1
to 32
. At least one background thread is always used from MariaDB 10.0. The default has been increased from 1
to 4
in MariaDB 10.2.2. Setting to a value greater than 1 creates that many separate purge threads. This can improve efficiency in some cases, such as when performing DML operations on many tables. In MariaDB 5.5, the options are 0
and 1
. If set to 0
, the default, purging is done with the master thread. If set to 1
, purging is done on a separate thread, which could reduce contention. See also innodb_purge_batch_size. --innodb-purge-threads=#
numeric
4
(>= MariaDB 10.2.2) 1
(>=MariaDB 10.0 to <= MariaDB 10.2.1) 0
(MariaDB 5.5) 1
to 32
(>=MariaDB 10.0), 0
to 1
(MariaDB 5.5) innodb_random_read_ahead
innodb_random_read_ahead
permits it to be re-instated if set to 1
(0
) is default. --innodb-random-read-ahead=#
boolean
OFF
innodb_read_ahead
linear
, the default, XtraDB/InnoDB will automatically fetch remaining pages if there are enough within the same extent that can be accessed sequentially. If set to none
, read-ahead is disabled. random
has been removed and is now ignored, while both
sets to both linear
and random
. Also see innodb_read_ahead_threshold for more control on read-aheads. Removed in MariaDB 10.0/XtraDB 5.6 and replaced by MySQL 5.6's innodb_random_read_ahead. innodb-read-ahead=value
enumeration
linear
none
, random
, linear
, both
innodb_read_ahead_threshold
--innodb-read-ahead-threshold=#
numeric
56
0
to 64
innodb_read_io_threads
--innodb-read-io-threads=#
numeric
4
1
to 64
innodb_read_only
1
(0
is default), the server will be read-only. For use in distributed applications, data warehouses or read-only media. --innodb-read-only=#
boolean
OFF
innodb_recovery_stats
1
(0
is default) and recovery is necessary on startup, the server will write detailed recovery statistics to the error log at the end of the recovery process. This Percona XtraDB variable has not been ported to XtraDB 5.6. boolean
OFF
innodb_recovery_update_relay_log
1
(0
is default), the relay log info file will be overwritten on crash recovery if the information differs from the InnoDB record. Should not be used if multiple storage engine types are being replicated. Previously named innodb_overwrite_relay_log_info
. Removed in MariaDB 10.0/XtraDB 5.6 and replaced by MySQL 5.6's relay-log-recovery
innodb-recovery-update-relay-log={0|1}
boolean
OFF
relay-log-recovery
innodb_replication_delay
--innodb-replication-delay=#
numeric
0
0
to 4294967295
innodb_rollback_on_timeout
--innodb-rollback-on-timeout
boolean
0
innodb_rollback_segments
--innodb-rollback-segments=#
numeric
128
1
to 128
innodb_safe_truncate
OFF
. --innodb-safe-truncate={0|1}
boolean
ON
innodb_scrub_log
--innodb-scrub-log
boolean
OFF
innodb_scrub_log_interval
--innodb-scrub-log-interval=#
numeric
56
0
to 50000
innodb_scrub_log_speed
--innodb-scrub-log-speed=#
numeric
256
1
to 50000
innodb_sched_priority_cleaner
0
to 39
corresponds in reverse order to Linux nice values of -20
to 19
. So 0
is the lowest priority (Linux nice value 19
) and 39
is the highest priority (Linux nice value -20
). XtraDB only. Added as a deprecated and ignored option in MariaDB 10.2.6 (which uses InnoDB as default instead of XtraDB) to allow for easier upgrades. innodb-sched-priority-cleaner=#
numeric
19
0
to 39
innodb_show_locks_held
innodb-show-locks-held=#
numeric
10
0
to 1000
innodb_show_verbose_locks
1
, and innodb_status_output_locks is also ON, the traditional InnoDB behavior is followed and locked records will be shown in SHOW ENGINE INNODB STATUS output. If set to 0
, the default, only high-level information about the lock is shown. XtraDB only. Added as a deprecated and ignored option in MariaDB 10.2.6 (which uses InnoDB as default instead of XtraDB) to allow for easier upgrades. innodb-show-verbose-locks=#
numeric
0
0
to 1
innodb_simulate_comp_failures
numeric
0
0
to 99
innodb_sort_buffer_size
--innodb-sort-buffer-size=#
numeric
1048576
(1M) 65536
to 67108864
innodb_spin_wait_delay
6
to 4
in MariaDB 10.3.5, as this was verified to give the best throughput by OLTP update index and read-write benchmarks on Intel Broadwell (2/20/40) and ARM (1/46/46). --innodb-spin-wait-delay=#
numeric
4
(>= MariaDB 10.3.5), 6
(<= MariaDB 10.3.4) 0
to 4294967295
innodb_stats_auto_recalc
1
(the default), persistent statistics are automatically recalculated when the table changes significantly (more than 10% of the rows). Affects tables created or altered with STATS_PERSISTENT=1 (see CREATE TABLE), or when innodb_stats_persistent is enabled. innodb_stats_persistent_sample_pages determines how much data to sample when recalculating. See InnoDB Persistent Statistics. --innodb-stats-auto-recalc=#
boolean
ON
innodb_stats_auto_update
0
(1
is default), index statistics will not be automatically calculated except when an ANALYZE TABLE is run, or the table is first opened. Replaced by innodb_stats_auto_recalc in MariaDB 10.0/XtraDB 5.6. boolean
1
innodb_stats_include_delete_marked
boolean
OFF
innodb_stats_method
nulls_equal
, the default, all NULL index values are treated as a single group. This is usually fine, but if you have large numbers of NULLs the average group size is slanted higher, and the optimizer may miss using the index for ref accesses when it would be useful. If set to nulls_unequal
, the opposite approach is taken, with each NULL forming its own group of one. Conversely, the average group size is slanted lower, and the optimizer may use the index for ref accesses when not suitable. Setting to nulls_ignored
ignores NULLs altogether from index group calculations. See also Index Statistics, aria_stats_method and myisam_stats_method. --innodb-stats-method=name
enumeration
nulls_equal
nulls_equal
, nulls_unequal
, nulls_ignored
innodb_stats_modified_counter
0
, the default, current limits are used. --innodb-stats-modified-counter=#
numeric
0
0
to 18446744073709551615
innodb_stats_on_metadata
1
, the default, XtraDB/InnoDB updates statistics when accessing the INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS tables, and when running metadata statements such as SHOW INDEX or SHOW TABLE STATUS. If set to 0
, statistics are not updated at those times, which can reduce the access time for large schemas, as well as make execution plans more stable. --innodb-stats-on-metadata
boolean
OFF
(from MariaDB 10.0), ON
(before MariaDB 10.0) innodb_stats_persistent
--innodb-stats-persistent=#
boolean
ON
innodb_stats_persistent_sample_pages
--innodb-stats-persistent-sample-pages=#
numeric
20
innodb_stats_sample_pages
innodb_stats_traditional
is enabled, then the exact number of pages configured by this system variable will be sampled for statistics. innodb_stats_traditional
is disabled, then the number of pages to sample for statistics is calculated using a logarithmic algorithm, so the exact number can change depending on the size of the table. This means that more samples may be used for larger tables. innodb_stats_persistent_sample_pages
system variable applies instead. persistent statistics are enabled with the innodb_stats_persistent
system variable. innodb_stats_transient_sample_pages
system variable should be used instead. --innodb-stats-sample-pages=#
numeric
8
1
to 264-1
innodb_stats_traditional
innodb_stats_sample_pages
system variable is used. In MariaDB 10.0 and later, this system variable affects how the innodb_stats_transient_sample_pages
is used. innodb_stats_traditional
is enabled, then the exact number of pages configured by the system variable will be sampled for statistics. innodb_stats_traditional
is disabled, then the number of pages to sample for statistics is calculated using a logarithmic algorithm, so the exact number can change depending on the size of the table. This means that more samples may be used for larger tables. --innodb-stats-traditional=#
boolean
ON
innodb_stats_transient_sample_pages
innodb_stats_traditional
is enabled, then the exact number of pages configured by this system variable will be sampled for statistics. innodb_stats_traditional
is disabled, then the number of pages to sample for statistics is calculated using a logarithmic algorithm, so the exact number can change depending on the size of the table. This means that more samples may be used for larger tables. innodb_stats_persistent_sample_pages
system variable applies instead. persistent statistics are enabled with the innodb_stats_persistent
system variable. --innodb-stats-transient-sample-pages=#
numeric
8
1
to 264-1
innodb_stats_update_need_lock
0
(1
is default) may help reduce contention of the &dict_operation_lock
, but also disables the Data_free option in SHOW TABLE STATUS. This Percona XtraDB variable has not been ported to XtraDB 5.6. boolean
1
innodb_status_output
--innodb-status-output={0|1}
boolean
OFF
innodb_status_output_locks
--innodb-status-output-locks={0|1}
boolean
OFF
innodb_strict_mode
1
(0
is the default before MariaDB 10.2.2), XtraDB/InnoDB will return errors instead of warnings in certain cases, similar to strict SQL mode. --innodb-strict-mode=#
boolean
ON
(>= MariaDB 10.2.2) OFF
(<= MariaDB 10.2.1) innodb_support_xa
1
, the default, XA transactions are supported. XA support ensures data is written to the binary log in the same order to the actual database, which is critical for replication and disaster recovery, but comes at a small performance cost. If your database is set up to only permit one thread to change data (for example, on a replication slave with only the replication thread writing), it is safe to turn this option off. Removed in MariaDB 10.3, XA transactions are always supported. --innodb-support-xa
boolean
ON
innodb_sync_array_size
1
, can be increased to split internal thread co-ordinating, giving higher concurrency when there are many waiting threads. --innodb-sync-array-size=#
numeric
1
1
to 1024
innodb_sync_spin_loops
--innodb-sync-spin-loops=#
numeric
30
0
to 4294967295
innodb_table_locks
0
(1
is default), setting innodb_table_locks to 1
, the default, will cause XtraDB/InnoDB to lock a table internally upon a LOCK TABLE. --innodb-table-locks
boolean
ON
innodb_thread_concurrency
0
, the default, permits as many threads as necessary. A suggested setting is twice the number of CPU's plus the number of disks. --innodb-thread-concurrency=#
numeric
0
0
to 1000
innodb_thread_concurrency_timer_based
1
, thread concurrency will be handled in a lock-free timer-based manner rather than the default mutex-based method. Depends on atomic op builtins being available. This Percona XtraDB variable has not been ported to XtraDB 5.6. innodb-thread-concurrency-timer-based={0|1}
boolean
OFF
innodb_thread_sleep_delay
0
disables sleep. --innodb-thread-sleep-delay=#
numeric
10000
innodb_temp_data_file_path
--innodb-temp-data-file-path=path
string
ibtmp1:12M:autoextend
innodb_tmpdir
--innodb-tmpdir=path
string
innodb_track_changed_pages
innodb-track-changed-pages={0|1}
boolean
OFF
innodb_track_redo_log_now
innodb-track-redo-log-now={0|1}
boolean
OFF
innodb_undo_directory
.
(the default value before 10.2.2) leaves the undo logs in the same directory as the other log files. From MariaDB 10.2.2, the default value is NULL, and if no path is specified, undo tablespaces will be created in the directory defined by datadir. Use together with innodb_undo_logs and innodb_undo_tablespaces. Undo logs are most usefully placed on a separate storage device. --innodb-undo-directory=name
string
.
(<= 10.2.1) innodb_undo_log_truncate
--innodb-undo-log-truncate[={0|1}]
boolean
OFF
innodb_undo_logs
128
, it can be reduced to avoid allocating unneeded rollback segments. See the Innodb_available_undo_logs status variable for the number of undo logs available. See also innodb_undo_directory and innodb_undo_tablespaces. Replaces innodb_rollback_segments in MariaDB 10.0. The Information Schema XTRADB_RSEG Table contains information about the XtraDB rollback segments. Deprecated and ignored in MariaDB 10.5.0, as it always makes sense to use the maximum number of rollback segments. --innodb-undo-logs=#
numeric
128
0
to 128
innodb_undo_tablespaces
innodb_undo_tablespaces
setting. When the undo logs can grow large, splitting them over multiple tablespaces will reduce the size of any single tablespace. Must be set before InnoDB is initialized, or else MariaDB will fail to start, with an error saying that InnoDB did not find the expected number of undo tablespaces
. The files are created in the directory specified by innodb_undo_directory, and are named undoN
, N being an integer. The default size of an undo tablespace is 10MB. innodb_undo_logs must have a non-zero setting for innodb_undo_tablespaces
to take effect. --innodb-undo-tablespaces=#
numeric
0
0
to 95
(>= MariaDB 10.2.2), 0
to 126
(<= MariaDB 10.2.1) innodb_use_atomic_writes
innodb-use-atomic-writes={0|1}
boolean
ON
(>= MariaDB 10.2.4), OFF
(<= MariaDB 10.2.3) innodb_use_fallocate
1
when innodb_use_atomic_writes is set - see FusionIO DirectFS atomic write support. innodb-use-fallocate={0|1}
boolean
OFF
ON
) innodb_use_global_flush_log_at_trx_commit
1
, a user cannot reset the value with a SET command, while if set to 1
, a user can reset the value of innodb_flush_log_at_trx_commit
. XtraDB only. Added as a deprecated and ignored option in MariaDB 10.2.6 (which uses InnoDB as default instead of XtraDB) to allow for easier upgrades. innodb-use-global-flush-log-at-trx_commit={0|1}
boolean
ON
innodb_use_mtflush
innodb_page_cleaners
system variable instead. --innodb-use-mtflush=#
boolean
OFF
innodb_use_native_aio
1
by default, it may be changed to 0
at startup if XtraDB/InnoDB detects a problem --innodb-use-native-aio=#
boolean
ON
innodb_use_purge_thread
--innodb-use-purge-thread=#
numeric
1
0
to 32
innodb_use_stacktrace
ON
(OFF
is default), a signal handler for SIGUSR2 is installed when the InnoDB server starts. When a long semaphore wait is detected at sync/sync0array.c, a SIGUSR2 signal is sent to the waiting thread and thread that has acquired the RW-latch. For both threads a full stacktrace is produced as well as if possible. XtraDB only. Added as a deprecated and ignored option in MariaDB 10.2.6 (which uses InnoDB as default instead of XtraDB) to allow for easier upgrades. --innodb-use-stacktrace=#
boolean
OFF
innodb_use_sys_malloc
1
, the default, XtraDB/InnoDB will use the operating system's memory allocator. If set to 0
it will use its own. Deprecated in MariaDB 10.0 and removed in MariaDB 10.2 along with InnoDB's internal memory allocator. --innodb-use-sys-malloc=#
boolean
ON
innodb_use_sys_stats_table
1
(0
is default), XtraDB will use the SYS_STATS system table for extra table index statistics. When a table is opened for the first time, statistics will then be loaded from SYS_STATS instead of sampling the index pages. Statistics are designed to be maintained only by running an ANALYZE TABLE. Replaced by MySQL 5.6's Persistent Optimizer Statistics. innodb-use-sys-stats-table={0|1}
boolean
0
innodb_use_trim
--innodb-use-trim=#
boolean
ON
(>= MariaDB 10.2.4), OFF
(<= MariaDB 10.2.3) innodb_version
string
innodb_write_io_threads
--innodb-write-io-threads=#
numeric
4
1
to 64
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/xtradbinnodb-server-system-variables/