MariaDB Storage Engines

Summary: in this tutorial, you will learn about the MariaDB storage engines including InnoDB, TokuDB, MyISAM, Aria, and MyRocks.

Storage engines handle data at the physical level. Storage engines are designed to efficiently manage data files, the data, and the index caches.

To show all available storage engines in a MariaDB server, you use the show engines statement:

show engines;
Code language: SQL (Structured Query Language) (sql)

Or you can query the supported storage engines from the information_schema.engines table:

select *
from information_schema.engines;
Code language: SQL (Structured Query Language) (sql)

The output includes a support column that indicates whether the engine is available.

When you create a new table, you need to specify a storage engine. If you don’t, MariaDB will use a default storage engine for the table.

create table table_name(
    ...
)engine=storage_engine;
Code language: SQL (Structured Query Language) (sql)

To show the current storage engine, you use the storage_engine system variable:

select @@global.storage_engine;
Code language: SQL (Structured Query Language) (sql)

MariaDB has made the InnoDB as the default storage engine since version 5.5. In the earlier version, it used Aria as the default storage engine.

If you want to find the storage engine of a table, you can query the engine column from the table information_schema.tables:

select 
    table_name,
    engine 
from 
    information_schema.tables
where 
    table_schema='nation'
    table_name = 'countries';
Code language: SQL (Structured Query Language) (sql)

InnoDB storage engine

InnoDB is a high-performance, general-purpose storage engine that supports transactions with savepoints, XA transactions, and foreign keys. Savepoints are points in the middle of a transaction to which can be restored to if necessary. XA is a special type of transaction designed for transactions that may involve multiple resources, not only SQL databases.

In most cases, the performance of InnoDB is better than other engines, therefore, it is a default storage engine.

TokuDB storage engine

Similar to InnoDB, the TokuDB supports transactions with savepoints and XA transactions. However, it does not support foreign keys and full-text indexes.

Unlike the InnoDB, the TokuDB supports a special structure for indexes called the factual trees. In addition, another important feature of TokuDB is data compression.

The fractal trees and data compression make TokuDB suitable for datasets that are too big to store entirely in memory. In this situation

MyISAM and Aria storage engines

MyISAM was the default storage engine for MySQL and MariaDB before version 5.5. MyISAM is a simple storage engine that is optimized for read-heavy, not write-heavy operations. MyISAM is good for websites and reporting systems which require heavy-read workloads.

Aria is MyISAM’s successor. Aria uses logs that enable data recovery after crashes. Aria is better than MyISAM is the environments that have concurrency.

Note that both MyISAM and Aria do not support transactions and foreign keys. Each statement in Aria is considered as a transaction.

MyRocks storage engine

MyRocks storage engine is based on RocksDB which was originally developed by Facebook. MyRocks storage engine is good for workloads that require high compression and I/O efficiency.

MyRocks storage engine uses a Log Structured Merge (LSM) architecture that has better compression than the B-tree used by InnoDB. It is a write-optimized and has faster data loading and replication.

MyRocks storge engine supports read committed and repeatable read isolated levels. However, it doesn’t support serializable.