Aria System Variables

This page documents system variables related to the Aria storage engine. For options that are not system variables, see Aria Options.

See Server System Variables for instructions on setting system variables.

For a full list of server options, system variables and status variables, see this page.

aria_block_size

• Description: Block size to be used for Aria index pages. Changing this requires dumping, deleting old tables and deleting all log files, and then restoring your Aria tables. If key lookups take too long (and one has to search roughly 8192/2 by default to find each key), can be made smaller, e.g. 4096.

• Command line: --aria-block-size=#

• Scope: Global

• Dynamic: No

• Data Type: numeric

• Default Value: 8192

• Range:

  • = MariaDB 10.4.7: 4096 to 32768 in increments of 1024

  • <= MariaDB 10.4.6: 1024 to 32768 in increments of 1024

aria_checkpoint_interval

• Description: Interval in seconds between automatic checkpoints. 0 means 'no automatic checkpoints' which makes sense only for testing.

• Command line: --aria-checkpoint-interval=#

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 30

• Range: 0 to 4294967295

aria_checkpoint_log_activity

• Description: Number of bytes that the transaction log has to grow between checkpoints before a new checkpoint is written to the log.

• Command line: aria-checkpoint-log-activity=#

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 1048576

• Range 0 to 4294967295

aria_encrypt_tables

• Description: Enables automatic encryption of all user-created Aria tables that have the ROW_FORMAT table option set to PAGE. See Data at Rest Encryption and Enabling Encryption for User-created Tables.

• Command line: aria-encrypt-tables={0|1}

• Scope: Global

• Dynamic: Yes

• Data Type: boolean

• Default Value: OFF

aria_force_start_after_recovery_failures

• Description: Number of consecutive log recovery failures after which logs are automatically deleted to cure the problem; 0 (the default) disables the feature.

• Command line: --aria-force-start-after-recovery-failures=#

• Scope: Global

• Dynamic: No

• Data Type: numeric

• Default Value: 0

aria_group_commit

• Description: Specifies Aria group commit mode.

• Command line: --aria_group_commit="value"

• Alias: maria_group_commit

• Scope: Global

• Dynamic: No

• Data Type: string

• Valid values:

  • none - Group commit is disabled.

  • hard - Wait the number of microseconds specified by aria_group_commit_interval before actually doing the commit. If the interval is 0 then just check if any other threads have requested a commit during the time this commit was preparing (just before sync() file) and send their data to disk also before sync().

  • soft - The service thread will wait the specified time and then sync() to the log. If the interval is 0 then it won't wait for any commits (this is dangerous and should generally not be used in production)

• Default Value: none

aria_group_commit_interval

• Description: Interval between Aria group commits in microseconds (1/1000000 second) for other threads to come and do a commit in "hard" mode and sync()/commit at all in "soft" mode. Option only has effect if aria_group_commit is used.

• Command line: --aria_group_commit_interval=#

• Alias: maria_group_commit_interval

• Scope: Global

• Dynamic: No

• Type: numeric

• Valid Values:

  • Default Value: 0 (no waiting)

  • Range: 0-4294967295

aria_log_dir_path

• Description: Path to the directory where transactional log should be stored

• Command line: --aria-log-dir-path=value

• Scope: Global

• Dynamic: No

• Data Type: string

• Default Value: Same as DATADIR

• Introduced: MariaDB 10.5.20, MariaDB 10.6.13, MariaDB 10.11.3 (as a system variable, existed as an option only before that)

aria_log_file_size

• Description: Limit for Aria transaction log size

• Command line: --aria-log-file-size=#

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 1073741824

aria_log_purge_type

• Description: Specifies how the Aria transactional log are purged. Set to at_flush to keep a copy of the transaction logs (good as an extra backup). The logs will stay until the next FLUSH LOGS;

• Command line: --aria-log-purge-type=name

• Scope: Global

• Dynamic: Yes

• Data Type: enumeration

• Default Value: immediate

• Valid Values: immediate, external, at_flush

aria_max_sort_file_size

• Description: Don't use the fast sort index method to created index if the temporary file would get bigger than this.

• Command line: --aria-max-sort-file-size=#

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 9223372036853727232

• Range: 0 to 9223372036854775807

aria_page_checksum

• Description: Determines whether index and data should use page checksums for extra safety. Can be overridden per table with PAGE_CHECKSUM clause in CREATE TABLE.

• Command line: --aria-page-checksum=#

• Scope: Global

• Dynamic: Yes

• Data Type: boolean

• Default Value: ON

aria_pagecache_age_threshold

• Description: This characterizes the number of hits a hot block has to be untouched until it is considered aged enough to be downgraded to a warm block. This specifies the percentage ratio of that number of hits to the total number of blocks in the page cache.

• Command line: --aria-pagecache-age-threshold=#

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 300

• Range: 100 to 9999900

aria_pagecache_buffer_size

• Description: The size of the buffer used for index and data blocks for Aria tables. This can include explicit Aria tables, system tables, and temporary tables. Increase this to get better handling and measure by looking at aria-status-variables/#aria_pagecache_reads (should be small) vs aria-status-variables/#aria_pagecache_read_requests.

• Command line: --aria-pagecache-buffer-size=#

• Scope: Global

• Dynamic: No

• Data Type: numeric

• Default Value: 134217728 (128MB)

• Range: 131072 (128KB) upwards

aria_pagecache_division_limit

• Description: The minimum percentage of warm blocks in the key cache.

• Command line: --aria-pagecache-division-limit=#

• Scope: Global

• Dynamic: Yes

• Data Type: numeric

• Default Value: 100

• Range: 1 to 100

aria_pagecache_file_hash_size

• Description: Number of hash buckets for open and changed files. If you have many Aria files open you should increase this for faster flushing of changes. A good value is probably 1/10th of the number of possible open Aria files.

• Command line: --aria-pagecache-file-hash-size=#

• Scope: Global

• Dynamic: No

• Data Type: numeric

• Default Value: 512

• Range: 128 to 16384

aria_pagecache_segments

• Description: The number of segments in the page_cache. Each file is put in their own segments of size pagecache_buffer_size / segments. Having many segments improves parallel performance.

• Command line: --aria-pagecache-segments=#

• Scope: Global

• Dynamic: No

• Data Type: numeric

• Default Value: 1

• Range: 1 to 128

• Introduced: MariaDB Community Server 12.1, MariaDB Enterprise Server 11.4.8-5, MariaDB Enterprise Server 11.8

aria_recover

• Description: aria_recover has been renamed to aria_recover_options in MariaDB 10.2.0. See aria_recover_options for the description.

aria_recover_options

• Description: Specifies how corrupted tables should be automatically repaired. More than one option can be specified, for example FORCE,BACKUP.

  • NORMAL: Normal automatic repair, the default until MariaDB 10.2.3

  • OFF: Autorecovery is disabled, the equivalent of not using the option

  • QUICK: Does not check rows in the table if there are no delete blocks.

  • FORCE: Runs the recovery even if it determines that more than one row from the data file are lost.

  • BACKUP: Keeps a backup of the data files.

• Command line: --aria-recover-options[=#]

• Scope: Global

• Dynamic: Yes

• Data Type: enumeration

• Default Value:

  • BACKUP,QUICK (>= MariaDB 10.2.4)

  • NORMAL (<= MariaDB 10.2.3)

• Valid Values: NORMAL, BACKUP, FORCE, QUICK, OFF

• Introduced: MariaDB 10.2.0

aria_repair_threads

• Description: Number of threads to use when repairing Aria tables. The value of 1 disables parallel repair. Increasing from the default will usually result in faster repair, but will use more CPU and memory.

• Command line: --aria-repair-threads=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type: numeric

• Default Value: 1

aria_sort_buffer_size

• Description: The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE.

• Command line: --aria-sort-buffer-size=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type: numeric

• Default Value: 268434432

aria_stats_method

• Description: Determines how NULLs are treated for Aria index statistics purposes. If set to nulls_equal, 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 default, 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. Statistics need to be recalculated after this method is changed. See also Index Statistics, myisam_stats_method and innodb_stats_method.

• Command line: --aria-stats-method=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type: numeric

• Default Value: nulls_unequal

• Valid Values: nulls_equal, nulls_unequal, nulls_ignored

aria_sync_log_dir

• Description: Controls syncing directory after log file growth and new file creation.

• Command line: --aria-sync-log-dir=#

• Scope: Global

• Dynamic: Yes

• Data Type: enumeration

• Default Value: NEWFILE

• Valid Values: NEWFILE, NEVER, ALWAYS

aria_used_for_temp_tables

• Description: Readonly variable indicating whether the Aria storage engine is used for temporary tables. If set to ON, the default, the Aria storage engine is used. If set to OFF, MariaDB reverts to using MyISAM for on-disk temporary tables. The MEMORY storage engine is used for temporary tables regardless of this variable's setting where appropriate. The default can be changed by not using the --with-aria-tmp-tables option when building MariaDB.

• Command line: No

• Scope: Global

• Dynamic: No

• Data Type: boolean

• Default Value: ON

deadlock_search_depth_long

• Description: Long search depth for the two-step deadlock detection. Only used by the Aria storage engine.

• Command line: --deadlock-search-depth-long=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type: numeric

• Default Value: 15

• Range: 0 to 33

deadlock_search_depth_short

• Description: Short search depth for the two-step deadlock detection. Only used by the Aria storage engine.

• Command line: --deadlock-search-depth-short=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type: numeric

• Default Value: 4

• Range: 0 to 32

deadlock_timeout_long

• Description: Long timeout in microseconds for the two-step deadlock detection. Only used by the Aria storage engine.

• Command line: --deadlock-timeout-long=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type: numeric

• Default Value: 50000000

• Range: 0 to 4294967295

deadlock_timeout_short

• Description: Short timeout in microseconds for the two-step deadlock detection. Only used by the Aria storage engine.

• Command line: --deadlock-timeout-short=#

• Scope: Global, Session

• Dynamic: Yes

• Data Type: numeric

• Default Value: 10000

• Range: 0 to 4294967295

_{This page is licensed: CC BY-SA / Gnu FDL}