Storage Engines

MariaDB Enterprise Server features pluggable storage engines to allow per-table workload optimization.

Overview

A storage engine is a type of plugin for MariaDB Enterprise Server:

  • Different storage engines may be optimized for different workloads, such as transactional workloads, analytical workloads, or high throughput workloads.

  • Different storage engines may be designed for different use cases, such as federated table access, table sharding, and table archiving in the cloud.

  • Different tables on the same server may use different storage engines.

Engine

Target

Optimization

Availability

Aria

Read-Heavy

Reads

ES10.2+

ColumnStore

Analytics, HTAP

Big Data, Analytical

ES10.4+

InnoDB

General Purpose

Mixed Read/Write

ES10.2+

MyISAM

Reads

Reads

ES10.2+

MyRocks

Write-Heavy

I/O Reduction, SSD

ES10.3+

S3

Cloud

Read-Only

ES10.3+

Spider

Federation

Sharding, Interlink

ES10.3+

Xpand

Distributed SQL

Scale-Out, Fault Tolerance

ES10.5+

Examples

Identify the Default Storage Engine

Identify the server's global default storage engine by using SHOW GLOBAL VARIABLES to query the default_storage_engine system variable:

SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';

+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

Identify the session's default storage engine by using SHOW SESSION VARIABLES:

SHOW SESSION VARIABLES LIKE 'default_storage_engine';

+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+

Set the Default Storage Engine

Global default storage engine:

SET GLOBAL default_storage_engine='MyRocks';

Session default storage engine supersedes global default during this session:

SET SESSION default_storage_engine='MyRocks';

Configure the Default Storage Engine

[mariadb]
...
default_storage_engine=MyRocks

Identify Available Storage Engines

SHOW ENGINES;

Choose Storage Engine for a New Table

Storage engine is specified at time of table creation using a ENGINE = parameter.

CREATE TABLE accounts.messages (
  id INT PRIMARY KEY AUTO_INCREMENT,
  sender_id INT,
  receiver_id INT,
  message TEXT
) ENGINE = MyRocks;

Resources

Engines for System Tables

Standard MariaDB storage engines are used for System Table storage:

FAQ

Can I use more than one storage engine on a server?

Yes, different tables can use different storage engines on the same server.

To create a table with a specific storage engine, specify the ENGINE table option to the CREATE TABLE statement.

Can I use more than one storage engine in a single query?

Yes, a single query can reference tables that use multiple storage engines.

In some cases, special configuration may be required. For example, Enterprise ColumnStore requires cross engine joins to be configured.

Reference

MariaDB Server Reference