Server — MariaDB — Aria

The previous two articles covered InnoDB and MyISAM: the default engine for application tables and the legacy engine it replaced. Aria is a third engine that sits somewhere between the two, and unlike MyISAM, it is genuinely in use on every MariaDB installation regardless of whether you have touched it.

What Aria is

Aria was developed by the MariaDB team as a crash-safe replacement for MyISAM. It shares MyISAM’s general approach — simpler than InnoDB, lower overhead, no row-level locking — but adds the crash recovery that MyISAM fundamentally lacks. Aria tables survive an unclean shutdown without corruption, because Aria maintains its own transaction log for recovery purposes.

Aria is not a replacement for InnoDB. It does not support full ACID transactions in the same sense that InnoDB does, and it uses table-level locking rather than row-level locking. For application tables on February, InnoDB remains the right choice. Aria’s value is in the role MariaDB has assigned it internally, not in tables you create yourself.

Where Aria lives on February

MariaDB uses Aria for two things that happen transparently in the background.

Internal temporary tables. When a query needs to sort or group a large result set that does not fit in memory, MariaDB spills it to a temporary table on disk. By default, those temporary tables use the Aria storage engine. This happens automatically for complex queries with ORDER BY, GROUP BY, or subqueries against large datasets. On February’s workload the result sets are small and this rarely triggers, but it is always available when needed.

System tables. Some of MariaDB’s own internal tables in the mysql database use Aria. These are managed by MariaDB and are not tables you interact with directly.

To confirm Aria is active:

sudo mariadb -e "SHOW ENGINES;" | grep Aria

The output should show Aria with status ACTIVE or DEFAULT. On MariaDB 10.11 it is always compiled in and always available.

To see which system tables use Aria specifically:

sudo mariadb -e "
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE engine = 'Aria'
ORDER BY table_schema, table_name;"

All results should be in the mysql system database. No application tables should be using Aria unless you explicitly created them that way.

How Aria differs from MyISAM and InnoDB

The clearest way to position Aria is against the two engines it sits between.

Against MyISAM: Aria adds crash safety. A MariaDB server that loses power while writing to a MyISAM table may corrupt that table permanently. An Aria table in the same situation recovers cleanly on the next startup, replaying its transaction log exactly as InnoDB replays its redo log. For the internal temporary tables MariaDB creates on behalf of queries, this matters: a crash mid-query should not leave MariaDB’s own internals in a broken state.

Against InnoDB: Aria is simpler and has lower overhead per operation. It uses table-level locking, which is a concurrency limitation but also means less bookkeeping. It has its own pagecache rather than sharing InnoDB’s buffer pool. For the narrow use case of temporary work tables, Aria’s simpler model is appropriate.

For anything you would store persistently and care about: InnoDB. Aria is for MariaDB’s own housekeeping.

The Aria pagecache

Like InnoDB’s buffer pool, Aria has its own in-memory cache: the pagecache. It caches both index blocks and data blocks for Aria tables, including the temporary tables used during query execution.

The default size is 128 MB, which is the value set in MariaDB’s compiled-in defaults. On February, where complex queries generating large temporary tables are uncommon, this default is generous. It is unlikely to be a bottleneck.

Check the current pagecache configuration:

sudo mariadb -e "SHOW VARIABLES LIKE 'aria_pagecache_buffer_size';"

Check how the pagecache is actually performing:

sudo mariadb -e "SHOW STATUS LIKE 'Aria_pagecache%';"

The key figures are Aria_pagecache_read_requests versus Aria_pagecache_reads, and Aria_pagecache_write_requests versus Aria_pagecache_writes. The reads and writes figures are cache misses: actual disk IO that was required because the requested page was not in the pagecache. The read_requests and write_requests figures are total operations.

On a healthy server, the miss figures should be close to zero relative to the request figures. A pattern like this is ideal:

Aria_pagecache_read_requests   9598
Aria_pagecache_reads           0
Aria_pagecache_write_requests  222
Aria_pagecache_writes          0

Zero misses means everything is being served from the pagecache with no disk reads. If the miss count is a meaningful fraction of the request count, the pagecache is too small for the query workload and temporary tables are spilling to disk more than the cache can absorb.

For February’s workload, the 128 MB default should produce a miss rate of zero or close to it. There is no reason to increase it unless the status variables show otherwise.

Configuration

The Aria pagecache is set in 50-server.cnf. The default is usually not written explicitly in the config file because 128 MB is the compiled-in default. To confirm or adjust it:

aria_pagecache_buffer_size = 128M

For a server with limited RAM where every megabyte counts, the pagecache can be reduced. If the status variables show zero misses with a 64 MB pagecache, the extra 64 MB is available for the InnoDB buffer pool or other uses. On a server with 4 GB or more, the difference is negligible and the default is fine.

The key_buffer_size setting, which controls MyISAM’s key cache, can be set very low since February has no application MyISAM tables:

key_buffer_size = 64K

The default is 128 MB, almost all of which is wasted on a server that uses MyISAM only for a handful of system tables. Setting it to 64K reclaims that memory for other purposes.

Should you create Aria tables?

Occasionally guides suggest Aria as an option for logging or archival tables where you want crash safety without the overhead of full InnoDB transactions. There is a reasonable argument for that in specific circumstances.

For February, that argument does not apply. The databases on February are application databases managed by software that expects InnoDB. Introducing Aria tables into those schemas adds a dependency on MariaDB-specific behaviour that would complicate any future migration or restore. The borgmatic backup handles Aria tables the same way it handles MyISAM tables, via file-level backup of the data directory, which carries the same caveats about consistency.

The practical answer for February: leave Aria to what MariaDB uses it for internally, keep application tables on InnoDB, and set key_buffer_size to a low value to stop MyISAM’s key cache consuming memory it does not need.

The complete memory picture

After the InnoDB, MyISAM, and Aria articles, the MariaDB memory configuration on February looks like this:

Total MariaDB memory: roughly 384 MB plus overhead. On a server with 4 GB of RAM running several other services, this is a reasonable allocation. Adjust the InnoDB buffer pool upward if the hit ratio query from the InnoDB article shows it is undersized, and adjust the Aria pagecache if the status variables show meaningful miss rates. Leave the MyISAM key cache at 64K.