Storage Engines

MariaDB Enterprise Server includes storage engines to enable optimization for specific types of workloads. Storage engines are specified on a per-table basis. Storage engines present in MariaDB Enterprise Server include:

Storage Engine

Description

InnoDB

  • Performant

  • General purpose

  • ACID-compliant

  • Transactional

ColumnStore

Xpand

  • Distributed SQL

  • Shared-nothing architecture

  • HA and Fault Tolerant by design

  • Horizontal read and write scale-out

  • ACID-compliant

  • Transactional

MyRocks

  • Based on RocksDB

  • Optimized for high writes and low disk usage

  • ACID-compliant

  • Transactional

Aria

S3

  • Allows for tables to be archived in S3-compatible storage

  • Read-only access after creation

  • Based on Aria

  • Non-transactional

Spider

  • Supports sharding a table's data across multiples backend MariaDB servers

  • Supports federated access to a table stored on a separate MariaDB Server

  • Transactional

Additional information is available in the MariaDB Knowledge Base.

Setting the Default Storage Engine

The default_storage_engine system variable is used to define the default storage engine.

Setting the Global Default Storage Engine

  1. The current global default storage engine can be determined by executing the SHOW GLOBAL VARIABLES statement and specifying the default_storage_engine system variable:

    SHOW GLOBAL VARIABLES LIKE 'default_storage_engine';
    
    +------------------------+--------+
    | Variable_name          | Value  |
    +------------------------+--------+
    | default_storage_engine | InnoDB |
    +------------------------+--------+
    
  2. The global default storage engine can be changed by executing the SET GLOBAL statement and specifying the default_storage_engine system variable's new value:

    SET GLOBAL default_storage_engine='MyRocks';
    
  3. If you want the change to persist server restarts, then you also need to change the value in the configuration file:

    [mariadb]
    ...
    default_storage_engine=MyRocks
    
  4. If a user starts a new session and creates a new table without specifying the ENGINE table option, then the table will use your new global default storage engine:

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

Setting the Session Default Storage Engine

  1. The current session default storage engine can be determined by executing the SHOW SESSION VARIABLES statement and specifying the default_storage_engine system variable:

    SHOW SESSION VARIABLES LIKE 'default_storage_engine';
    
    +------------------------+--------+
    | Variable_name          | Value  |
    +------------------------+--------+
    | default_storage_engine | InnoDB |
    +------------------------+--------+
    
  2. The session default storage engine can be changed by executing the SET SESSION statement and specifying the default_storage_engine system variable's new value:

    SET SESSION default_storage_engine='MyRocks';
    
  3. If you create a new table in the current session without specifying the ENGINE table option, then the table will use your session's new default storage engine:

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

Setting the Storage Engine for Individual Tables

The ENGINE table option can be used to set the storage engine for individual tables.

Setting the Storage Engine for a New Table

  1. The storage engine for a brand new table can be set by executing the CREATE TABLE statement with the ENGINE table option:

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

Setting the Storage Engine for an Existing Table

  1. The storage engine for an existing table can be changed by executing the ALTER TABLE statement with the ENGINE table option:

    ALTER TABLE accounts.messages ENGINE = MyRocks;