Learn about the Aria storage engine in MariaDB Server. Understand its features, advantages, and use cases, particularly for crash-safe operations and transactional workloads.
The Aria storage engine is compiled in by default from MariaDB 5.1 and it is required to be 'in use' when MariaDB is started.
From MariaDB 10.4, all system tables are Aria.
Additionally, internal on-disk tables are in the Aria table format instead of the MyISAM table format. This should speed up some GROUP BY and DISTINCT queries because Aria has better caching than MyISAM.
Note: The Aria storage engine was previously called Maria (seeThe Aria Name for details on the rename) and in previous versions of MariaDB the engine was still called Maria.
The following table options to Aria tables in CREATE TABLE and ALTER TABLE:
TRANSACTIONAL= 0 | 1
: If the TRANSACTIONAL
table option is set for an Aria table, then the table will be crash-safe. This is implemented by logging any changes to the table to Aria's transaction log, and syncing those writes at the end of the statement. This will marginally slow down writes and updates. However, the benefit is that if the server dies before the statement ends, all non-durable changes will roll back to the state at the beginning of the statement. This also needs up to 6 bytes more for each row and key to store the transaction id (to allow concurrent insert's and selects).
TRANSACTIONAL=1
is not supported for partitioned tables.
An Aria table's default value for the TRANSACTIONAL
table option depends on the table's value for the ROW_FORMAT
table option. See below for more details.
If the TRANSACTIONAL
table option is set for an Aria table, the table does not actually support transactions. See MDEV-21364 for more information. In this context, transactional just means crash-safe.
PAGE_CHECKSUM= 0 | 1
: If index and data should use
page checksums for extra safety.
TABLE_CHECKSUM= 0 | 1
:
Same as CHECKSUM
in MySQL 5.1
ROW_FORMAT=PAGE | FIXED | DYNAMIC
: The table's row format.
The default value is PAGE
.
To emulate MyISAM, set ROW_FORMAT=FIXED
or ROW_FORMAT=DYNAMIC
The TRANSACTIONAL
and ROW_FORMAT
table options interact as follows:
If TRANSACTIONAL=1
is set, then the only supported row format is PAGE
. If ROW_FORMAT
is set to some other value, then Aria issues a warning, but still forces the row format to be PAGE
.
If TRANSACTIONAL=0
is set, then the table will be not be crash-safe, and any row format is supported.
If TRANSACTIONAL
is not set to any value, then any row format is supported. If ROW_FORMAT
is set, then the table will use that row format. Otherwise, the table will use the default PAGE
row format. In this case, if the table uses the PAGE
row format, then it will be crash-safe. If it uses some other row format, then it will not be crash-safe.
Some other improvements are:
CHECKSUM TABLE now ignores values in NULL fields. This
makes CHECKSUM TABLE
faster and fixes some cases where
same table definition could give different checksum values depending on row
format. The disadvantage is that the value is now different compared to other
MySQL installations. The new checksum calculation is fixed for all table
engines that uses the default way to calculate and MyISAM which does the
calculation internally. Note: Old MyISAM tables with internal checksum will
return the same checksum as before. To fix them to calculate according to new
rules you have to do an ALTER TABLE. You can use the old
ways to calculate checksums by using the option --old
to mariadbdmysqld or set the
system variable '@@old
' to 1
when you
do CHECKSUM TABLE ... EXTENDED;
At startup Aria will check the Aria logs and automatically recover the tables from the last checkpoint if the server was not taken down correctly. See Aria Log Files
For a full list, see Aria System Variables.
In normal operations, the only variables you have to consider are:
This is where all index and data pages are cached. The bigger this is, the faster Aria will work.
The default value 8192, should be ok for most cases. The only problem with a higher value is that it takes longer to find a packed key in the block as one has to search roughly 8192/2 to find each key. We plan to fix this by adding a dictionary at the end of the page to be able to do a binary search within the block before starting a scan. Until this is done and key lookups takes too long time even if you are not hitting disk, then you should consider making this smaller.
Possible values to try are 2048
, 4096
or 8192
Note that you can't change this without dumping, deleting old tables and deleting all log files and then restoring your Aria tables. (This is the only option that requires a dump and load)
Set this to "at_flush
" if you want to keep a copy of the transaction logs
(good as an extra backup). The logs will stay around until you
execute FLUSH ENGINE LOGS.
aria_log_control
file is a very short log file (52 bytes) that contains the current state of all Aria tables related to logging and checkpoints. In particular, it contains the following information:
Aria file version: 1
Block size: 8192
maria_uuid: ee948482-6cb7-11ed-accb-3c7c3ff16468
last_checkpoint_lsn: (1,0x235a)
last_log_number: 1
trid: 28
recovery_failures: 0
The uuid
is a unique identifier per system. All Aria files created will have a copy of this in their .MAI headers. This is mainly used to check if someone has copied an Aria file between MariaDB servers.
last_checkpoint_lsn
and last_log_number
are information about the current aria_log files.
trid
is the highest transaction number seen so far. Used by recovery.
aria_log.*
files contain the log of all operations that change Aria files (including create table, drop table, insert etc..) This is a 'normal' WAL (Write Ahead Log), similar to the InnoDB log file, except that aria_logs contain both redo and undo. Old aria_log files are automatically deleted when they are not needed anymore (Neither the last checkpoint or any running transaction need to refer to the old data anymore).
The error Missing valid id at start of file. File is not a valid aria control file
means that something overwrote at least the first 4 bytes in the file. This can happen due to a problem with the file system (hardware or software), or a bug in which a thread inside MariaDB wrote on the wrong file descriptor (in which case you should report the bug, attaching a copy of the control file to assist).
In the case of a corrupted log file, with the server shut down, one should be able to fix that by deleting all aria_log files. If the control_file is corrupted, then one has to delete the aria_control_file and all aria_log.* files. The effect of this is that on table open of an Aria table, the server will think that it has been moved from another system and do an automatic check and repair of it. If there was no issues, the table will be opened and can be used as normal. See also When is it safe to remove old log files.
This page is licensed: CC BY-SA / Gnu FDL
Since MariaDB 5.2, the Aria storage engine has included a feature to group commits to speed up concurrent threads doing many inserts into the same or different Aria tables.
By default, group commit for Aria is turned off. It is controlled by thearia_group_commit and aria_group_commit_interval system variables.
Information on setting server variables can be found on the Server System Variables page.
A commit
is flush of logs
followed by a sync.
sent to disk
means written to disk but not sync()ed,
flushed
mean sent to disk and synced().
LSN
means log serial number. It's refers to the position in the transaction log.
The thread which first started the commit
is performing the actual flush of logs. Other threads set the new goal (LSN)
of the next pass (if it is maximum) and wait for the pass end or just wait for the pass end.
The effect of this is that a flush (write of logs + sync) will save all data for all threads/transactions that have been waiting since the last flush.
The first thread sends all changed buffers to disk. This is repeated as long as there are new LSNs added. The process can not loop forever because we have a limited number of threads and they will wait for the data to be synced.
Pseudo code:
do
send changed buffers to disk
while new_goal
sync
If less than rate microseconds has passed since the last sync, then after buffers have been sent to disk, wait until rate microseconds has passed since last sync, do sync and return. This ensures that if we call sync infrequently we don't do any waits.
Note that soft group commit should only be used if you can afford to lose a few rows if your machine shuts down hard (as in the case of a power failure).
Works like in non group commit'
but the thread doesn't do any real sync(). If aria_group_commit_interval is not zero, the sync() will be performed by a service thread with the given rate when needed (new LSN appears). If aria_group_commit_interval is zero, there will be no sync() calls.
The code for this can be found in storage/maria/ma_loghandler.c::translog_flush()
This page is licensed: CC BY-SA / Gnu FDL
This page documents status variables related to the Aria storage engine. See Server Status Variables for a complete list of status variables that can be viewed with SHOW STATUS.
See also the Full list of MariaDB options, system and status variables.
Aria_pagecache_blocks_not_flushed
Description: The number of dirty blocks in the Aria page cache. The global value can be flushed by FLUSH STATUS.
Scope: Global
Data Type: numeric
Aria_pagecache_blocks_unused
Description: Free blocks in the Aria page cache. The global value can be flushed by FLUSH STATUS.
Scope: Global
Data Type: numeric
Aria_pagecache_blocks_used
Description: Blocks used in the Aria page cache. The global value can be flushed by FLUSH STATUS.
Scope: Global
Data Type: numeric
Aria_pagecache_read_requests
Description: The number of requests to read something from the Aria page cache.
Scope: Global
Data Type: numeric
Aria_pagecache_reads
Description: The number of Aria page cache read requests that caused a block to be read from the disk.
Scope: Global
Data Type: numeric
Aria_pagecache_write_requests
Description: The number of requests to write a block to the Aria page cache.
Scope: Global
Data Type: numeric
Aria_pagecache_writes
Description: The number of blocks written to disk from the Aria page cache.
Scope: Global
Data Type: numeric
Aria_transaction_log_syncs
Description: The number of Aria log fsyncs.
Scope: Global
Data Type: numeric
This page is licensed: CC BY-SA / Gnu FDL
The Aria storage engine supports three different table storage formats.
These are FIXED, DYNAMIC and PAGE, and they can be set with the ROW FORMAT option in the CREATE TABLE statement. PAGE is the default format, while FIXED and DYNAMIC are essentially the same as the MyISAM formats.
The SHOW TABLE STATUS statement can be used to see the storage format used by a table.
Fixed-length (or static) tables contain records of a fixed-length. Each column is the same length for all records, regardless of the actual contents. It is the default format if a table has no BLOB, TEXT, VARCHAR or VARBINARY fields, and no ROW FORMAT is provided. You can also specify a fixed table with ROW_FORMAT=FIXED in the table definition.
Tables containing BLOB or TEXT fields cannot be FIXED, as by design these are both dynamic fields.
Fixed-length tables have a number of characteristics
fast, since MariaDB will always know where a record begins
easy to cache
take up more space than dynamic tables, as the maximum amount of storage space will be allocated to each record.
reconstructing after a crash is uncomplicated due to the fixed positions
no fragmentation or need to re-organize, unless records have been deleted and you want to free the space up.
Dynamic tables contain records of a variable length. It is the default format if a table has any BLOB, TEXT, VARCHAR or VARBINARY fields, and no ROW FORMAT is provided. You can also specify a DYNAMIC table with ROW_FORMAT=DYNAMIC in the table definition.
Dynamic tables have a number of characteristics
Each row contains a header indicating the length of the row.
Rows tend to become fragmented easily. UPDATING a record to be longer will likely ensure it is stored in different places on the disk.
All string columns with a length of four or more are dynamic.
They require much less space than fixed-length tables.
Restoring after a crash is more complicated than with FIXED tables.
Page format is the default format for Aria tables, and is the only format that can be used if TRANSACTIONAL=1.
Page tables have a number of characteristics:
It's cached by the page cache, which gives better random performance as it uses fewer system calls.
Does not fragment as easily as the DYNAMIC format during UPDATES. The maximum number of fragments are very low.
Updates more quickly than dynamic tables.
Has a slight storage overhead, mainly notable on very small rows
Slower to perform a full table scan
Slower if there are multiple duplicated keys, as Aria will first write a row, then keys, and only then check for duplicates
See Aria Storage Engine for the impact of the TRANSACTIONAL option on the row format.
This page is licensed: CC BY-SA / Gnu FDL
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.
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
to32768
in increments of1024
<= 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 will be 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 will be 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: 134217720
(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 12.1
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 will be 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
The Aria storage engine can automatically detect and deal with deadlocks (see the Wikipedia deadlocks article).
This feature is controlled by four configuration variables, two that control the search depth and two that control the timeout.
If Aria is ever unable to obtain a lock, we might have a deadlock. There are two primary ways for detecting if a deadlock has actually occurred. First is to search a wait-for graph (see the wait-for graph on Wikipedia) and the second is to just wait and let the deadlock exhibit itself. Aria Two-step Deadlock Detection does a combination of both.
First, if the lock request cannot be granted immediately, we do a short search of the wait-for graph with a small search depth as configured by the deadlock_search_depth_short
variable. We have a depth limit because the graph can (theoretically) be arbitrarily big and we don't want to recursively search the graph arbitrarily deep. This initial, short search is very fast and most deadlocks will be detected right away. If no deadlock cycles are found with the short search the system waits for the amount of time configured in deadlock_timeout_short
to see if the lock conflicts will be removed and the lock can be granted. Assuming this did not happen and the lock request still waits, the system then moves on to step two, which is a repeat of the process but this time searching deeper using the deadlock_search_depth_long
. If no deadlock has been detected, it waits deadlock_timeout_long
and times out.
When a deadlock is detected the system uses a weighting algorithm to determine which thread in the deadlock should be killed and then kills it.
This page is licensed: CC BY-SA / Gnu FDL
This FAQ provides information on the Aria storage engine.
The Aria storage engine was previously known as Maria, (see, the Aria Name). In current releases of MariaDB, you can refer to the engine as Maria or Aria. As this will change in future releases, please update references in your scripts and automation to use the correct name.
Aria is a storage engine for MySQL® and MariaDB. It was originally developed with the goal of becoming the default transactional and non-transactional storage engine for MariaDB and MySQL.
It has been in development since 2007 and was first announced on Monty's blog. The same core MySQL engineers who developed the MySQL server and the MyISAM, MERGE, and MEMORY storage engines are also working on Aria.
Originally, the storage engine was called Maria, after Monty's younger daughter. Monty named MySQL after his first child, My and his second child Max gave his name to MaxDB and the MySQL-Max distributions.
In practice, having both MariaDB the database server and Maria the storage engine with such similar names proved confusing. To mitigate this, the decision was made to change the name. A Rename Maria contest was held during the first half of 2010 and names were submitted from around the world. Monty picked the name Aria from a short list of finalist. Chris Tooley, who suggested it, received the prize of a Linux-powered System 76 Meerkat NetTop from Monty Program.
For more information, see the Aria Name.
The current version of Aria is 1.5. The goal of this release is to develop a crash-safe alternative to MyISAM. That is, when MariaDB restarts after a crash, Aria recovers all tables to the state as of the start of a statement or at the start of the last LOCK TABLES
statement.
The current goal is to keep the code stable and fix all bugs.
The next version of Aria is 2.0. The goal for this release is to develop a fully transactional storage engine with at least all the major features of InnoDB.
Currently, Aria 2.0 is on hold as its developers are focusing on improving MariaDB. However, they are interested in working with interested customers and partners to add more features to Aria and eventually release 2.0.
These are some of the goals for Aria 2.0:
ACID compliant
Commit/Rollback
Concurrent updates/deletes
Row locking
Group commit (Already in MariaDB 5.2)
Faster lookup in index pages (Page directory)
Beginning in Aria 2.5, the plan is to focus on improving performance.
Long term, we have the following goals for Aria:
To create a new, ACID and Multi-Version Concurrency Control (MVCC), transactional storage engine that can function as both the default non-transactional and transactional storage engine for MariaDB and MySQL®.
To be a MyISAM replacement. This is possible because Aria can also be run in non-transactional mode, supports the same row formats as MyISAM, and supports or will support all major features of MyISAM.
To be the default non-transactional engine in MariaDB (instead of MyISAM).
Multi-Version Concurrency Control (MVCC) and ACID storage engine.
Optionally non-transactional tables that should be 'as fast and as compact' as MyISAM tables.
Be able to use Aria for internal temporary tables in MariaDB (instead of MyISAM).
All indexes should have equal speed (clustered index is not on our current road map for Aria. If you need clustered index, you should use XtraDB).
Allow 'any' length transactions to work (Having long running transactions will cause more log space to be used).
Allow log shipping; that is, you can do incremental backups of Aria tables just by copying the Aria logs.
Allow copying of Aria tables between different Aria servers (under some well-defined constraints).
Better blob handling (than is currently offered in MyISAM, at a minimum).
No memory copying or extra memory used for blobs on insert/update.
Blobs allocated in big sequential blocks - Less fragmentation over time.
Blobs are stored so that Aria can easily be extended to have access to any part of a blob with a single fetch in the future.
Efficient storage on disk (that is, low row data overhead, low page data overhead and little lost space on pages). Note: There is still some more work to succeed with this goal. The disk layout is fine, but we need more in-memory caches to ensure that we get a higher fill factor on the pages.
Small footprint, to make MariaDB + Aria suitable for desktop and embedded applications.
Flexible memory allocation and scalable algorithms to utilize large amounts of memory efficiently, when it is available.
Documentation is available at Aria and related topics. The project is maintained on GitHub.
If you want to know what happens or be part of developing Aria, you can subscribe to the developers, docs, or discuss mailing lists.
To report and check bugs in Aria, see Reporting Bugs.
You can usually find some of the Maria developers on our Zulip instance at mariadb.zulipchat.com or on the IRC channel #maria at.
The Core Team who develop Aria are:
Technical lead
Michael "Monty" Widenius - Creator of MySQL and MyISAM
Core Developers (in alphabetical order)
Guilhem Bichot - Replication expert, on line backup for MyISAM, etc.
Kristian Nielsen - MySQL build tools, NDB, MySQL server
Oleksandr Byelkin - Query cache, sub-queries, views.
Sergei Golubchik - Server Architect, Full text search, keys for MyISAM-Merge, Plugin architecture, etc.
All except Guilhem Bichot are working for MariaDB Corporation Ab.
Aria follows the same release criteria as for MariaDB. Some clarifications, unique for the Aria storage engine:
Aria index and data file formats should be backwards and forwards compatible to ensure easy upgrades and downgrades.
The log file format should also be compatible, but we don't make any guarantees yet. In some cases when upgrading, you must remove the old aria_log.%
and maria_log.%
files before restarting MariaDB. (So far, this has only occurred in the upgrade from MariaDB 5.1 and MariaDB 5.2).
Aria is now feature complete according to specification.
Aria 1.0 was basically a crash-safe non-transactional version of MyISAM. Aria 1.5 added more concurrency (multiple inserter) and some optimizations.
Aria supports all aspects of MyISAM, except as noted below. This includes external and internal check/repair/compressing of rows, different row formats, different index compress formats, aria_chk etc. After a normal shutdown you can copy Aria files between servers.
Data and indexes are crash safe.
On a crash, changes will be rolled back to state of the start of a statement or a last LOCK TABLES
statement.
Aria can replay almost everything from the log. (Including CREATE
, DROP
, RENAME
, TRUNCATE
tables). Therefore, you make a backup of Aria by just copying the log. The things that can't be replayed (yet) are:
Batch INSERT
into an empty table (This includes LOAD DATA INFILE
, SELECT... INSERT
and INSERT
(many rows)).
ALTER TABLE
. Note that .frm
tables are NOT recreated!
LOAD INDEX
can skip index blocks for unwanted indexes.
Supports all MyISAM ROW
formats and new PAGE
format where data is stored in pages. (default size is 8K).
Multiple concurrent inserters into the same table.
When using PAGE
format (default) row data is cached by page cache.
Aria has unit tests of most parts.
Supports both crash-safe (soon to be transactional) and not transactional tables. (Non-transactional tables are not logged and rows uses less space): CREATE TABLE foo (...) TRANSACTIONAL=0|1 ENGINE=Aria
.
PAGE
is the only crash-safe/transactional row format.
PAGE
format should give a notable speed improvement on systems which have bad data caching. (For example Windows).
From MariaDB 10.5, max key length is 2000 bytes, compared to 1000 bytes in MyISAM.
Aria uses BIG (1G by default) log files.
Aria has a log control file (aria_log_control
) and log files (aria_log.%
). The log files can be automatically purged when not needed or purged on demand (after backup).
Aria uses 8K pages by default (MyISAM uses 1K). This makes Aria a bit faster when using keys of fixed size, but slower when using variable-length packed keys (until we add a directory to index pages).
Aria doesn't support INSERT DELAYED
.
Aria does not support multiple key caches.
Storage of very small rows (< 25 bytes) are not efficient for PAGE
format.
MERGE
tables don't support Aria (should be very easy to add later).
Aria data pages in block format have an overhead of 10 bytes/page and 5 bytes/row. Transaction and multiple concurrent-writer support will use an extra overhead of 7 bytes for new rows, 14 bytes for deleted rows and 0 bytes for old compacted rows.
No external locking (MyISAM has external locking, but this is a rarely used feature).
Aria has one page size for both index and data (defined when Aria is used the first time). MyISAM supports different page sizes per index.
Small overhead (15 bytes) per index page.
Aria doesn't support MySQL internal RAID (disabled in MyISAM too, it's a deprecated feature).
Minimum data file size for PAGE format is 16K (with 8K pages).
Aria doesn't support indexes on virtual fields.
See:
TRANSACTIONAL
keyword now when Aria is not yet transactional?In the current development phase Aria tables created with TRANSACTIONAL=1
are crash safe and atomic but not transactional because changes in Aria tables can't be rolled back with the ROLLBACK
command. As we planned to make Aria tables fully transactional, we decided it was better to use the TRANSACTIONAL
keyword from the start so that applications don't need to be changed later.
See KNOWN_BUGS.txt
for open/design bugs.
See jira.mariadb.org for newly reported bugs. Please report anything you can't find here!
If there is a bug in the Aria recovery code or in the code that generates the logs, or if the logs become corrupted, then mysqld may fail to start because Aria can't execute the logs at start up.
Query cache and concurrent insert using page row format have a bug, please disable query cache while using page row format and MDEV-6817 isn't complete
If Aria doesn't start or you have an unrecoverable table (shouldn't happen):
Remove the aria_log.%
files from the data directory.
Restart mysqld
and run CHECK TABLE, REPAIR TABLE or mariadb-check on your Aria tables.
Alternatively,
Remove logs and run aria_chk on your *.MAI
files.
The LOCK TABLES
statement will not start a crash-safe segment. You should use begin and COMMIT instead.
To make things future safe, you could do this:
BEGIN;
LOCK TABLES ....
UNLOCK TABLES;
COMMIT;
And later you can just remove the LOCK TABLES
and UNLOCK TABLES
statements.
Example:
CREATE TABLE t1 (a int) ROW_FORMAT=FIXED TRANSACTIONAL=0 PAGE_CHECKSUM=0;
CREATE TABLE t2 (a int) ROW_FORMAT=DYNAMIC TRANSACTIONAL=0 PAGE_CHECKSUM=0;
SHOW CREATE TABLE t1;
SHOW CREATE TABLE t2;
Note that the rows are not cached in the page cache for FIXED
or DYNAMIC
format. If you want to have the data cached (something MyISAM doesn't support) you should use ROW_FORMAT=PAGE
:
CREATE TABLE t3 (a int) ROW_FORMAT=PAGE TRANSACTIONAL=0 PAGE_CHECKSUM=0;
SHOW CREATE TABLE t3;
You can use PAGE_CHECKSUM=1
also for non-transactional tables; This puts a page checksums on all index pages. It also puts a checksum on data pages if you use ROW_FORMAT=PAGE
.
You may still have a speed difference (may be slightly positive or negative) between MyISAM and Aria because of different page sizes. You can change the page size for MariaDB with --aria-block-size=\
#, where \
Note that if you change the page size you have to dump all your old tables into text (with mariadb-dump) and remove the old Aria log and files:
# rm datadir/aria_log*
PAGE
format compared to the old MyISAM-like row formats (DYNAMIC
and FIXED
)The MyISAM-like DYNAMIC
and FIXED
format are extremely simple and have very little space overhead, so it's hard to beat them for when it comes to simple scanning of unmodified data. The DYNAMIC
format does however get notably worse over time if you update the row a lot in a manner that increases the size of the row.
The advantages of the PAGE
format (compared to DYNAMIC
or FIXED
) for non-transactional tables are:
It's cached by the Page Cache, which gives better random performance (as it uses less system calls).
Does not fragment as easily as the DYNAMIC
format during UPDATE
statements. The maximum number of fragments are very low.
Code can easily be extended to only read the accessed columns (for example to skip reading blobs).
Faster updates (compared to DYNAMIC
).
The disadvantages are:
Slight storage overhead (should only be notable for very small row sizes)
Slower full table scan time.
When using row_format=PAGE
, (the default), Aria first writes the row, then the keys, at which point the check for duplicate keys happens. This makes PAGE
format slower than DYNAMIC
(or MyISAM) if there is a lot of duplicated keys because of the overhead of writing and removing the row. If this is a problem, you can use row_format=DYNAMIC
to get same behavior as MyISAM.
An Aria table consists of 3 files:
XXX.frm : The definition for the table, used by MySQL.
XXX.MYI : Aria internal information about the structure of the data and index and data for all indexes.
XXX.MAD : The data.
It's safe to copy all the Aria files to another directory or MariaDB instance if any of the following holds:
If you shutdown the MariaDB Server properly with mariadb-admin shutdown, so that there is nothing for Aria to recover when it starts.
or
If you have run a FLUSH TABLES statement and not accessed the table using SQL from that time until the tables have been copied.
In addition, you must adhere the following rule for transactional tables:
You can't copy the table to a location within the same MariaDB server if the new table has existed before and the new table is still active in the Aria recovery log (that is, Aria may need to access the old data during recovery). If you are unsure whether the old name existed, run aria_chk --zerofill on the table before you use it.
After copying a transactional table and before you use the table, we recommend that you run the command:
$ aria_chk --zerofill table_name
This will overwrite all references to the logs (LSN), all transactional references (TRN) and all unused space with 0. It also marks the table as 'movable'. An additional benefit of zerofill is that the Aria files will compress better. No real data is ever removed as part of zerofill.
Aria will automatically notice if you have copied a table from another system and do 'zerofill' for the first access of the table if it was not marked as 'movable'. The reason for using aria_chk --zerofill is that you avoid a delay in the MariaDB server for the first access of the table.
Note that this automatic detection doesn't work if you copy tables within the same MariaDB server!
If you want to remove the Aria log files (aria_log.%
) with rm
or delete, then you must first shut down MariaDB cleanly (for example, with mariadb-admin shutdown) before deleting the old files.
The same rules apply when upgrading MariaDB; When upgrading, first take down MariaDB in a clean way and then upgrade. This will allow you to remove the old log files if there are incompatible problems between releases.
Don't remove the aria_log_control
file! This is not a log file, but a file that contains information about the Aria setup (current transaction id, unique id, next log file number etc.).
If you do, Aria will generate a new aria_log_control
file at startup and will regard all old Aria files as files moved from another system. This means that they have to be 'zerofilled' before they can be used. This will happen automatically at next access of the Aria files, which can take some time if the files are big.
If this happens, you will see things like this in your mysqld.err file:
[Note] Zerofilling moved table: '.\database\xxxx'
As part of zerofilling no vital data is removed.
See Aria Log Files for details.
This page is licensed: CC BY-SA / Gnu FDL
The Aria storage engine used to be called Maria. This page gives the history and background of how and why this name was changed to Aria.
When starting what became the MariaDB project, Monty and the initial developers only planned to work on a next generation MyISAM storage engine replacement. This storage engine would be crash safe and eventually support transactions. Monty named the storage engine, and the project, after his daughter, Maria.
Work began in earnest on the Maria storage engine but the plans quickly expanded and morphed and soon the developers were not just working on a storage engine, but on a complete branch of the MySQL database. Since the project was already called Maria, it made sense to call the whole database server MariaDB.
So now there was the database, MariaDB, and the storage engine, Maria. To end the confusion this caused, the decision was made to rename the storage engine.
Monty's first suggestion was to name it Lucy, after his dog, but few who heard it liked that idea. So the decision was made that the next best thing was for the community to suggest and vote on names.
This was done by running a contest in 2009 through the end of May 2010. After that the best names were voted on by the community and Monty picked andannounced the winner (Aria) at OSCon 2010 in Portland.
The winning entry was submitted by Chris Tooley. He received a Linux-poweredSystem 76 Meerkat NetTop as his prize for suggesting the winning name from Monty Program.
This page is licensed: CC BY-SA / Gnu FDL