Storage Engines
This page is part of MariaDB's Documentation.
The parent of this page is: MariaDB ColumnStore (Analytics)MariaDB Server (SQL Database Server)
Topics on this page:
Overview
MariaDB Enterprise Server features pluggable storage engines to allow per-table workload optimization.
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 |
---|---|---|---|
Read-Heavy | Reads | ES 10.2+ | |
Analytics, HTAP | Big Data, Analytical | ES 10.4+ | |
General Purpose | Mixed Read/Write | ES 10.2+ | |
Cache, Temp | Temporary Data | ES 10.2+ | |
Federation | Sharding, Interlink | ES 10.3+ |
Examples
Identify the Default Storage Engine
Identify the server's global default storage engine by using SHOW GLOBAL VARIABLES to query the default_
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:
Aria Storage Engine (for System Tables in ES 10.4 and up)
MyISAM Storage Engine (for System Tables ES pre-10.4)
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.
What storage engine should I use for transactional or OLTP workloads?
InnoDB is the recommended storage engine for transactional or OLTP workloads.
What storage engine should I use for analytical or OLAP workloads?
ColumnStore is the recommended storage engine for analytical or OLAP workloads.
What storage engine should I use if my application performs both transactional and analytical queries?
An application that performs both transactional and analytical queries is known as hybrid transactional-analytical processing (HTAP).
HTAP can be implemented with MariaDB Enterprise Server by using InnoDB for transactional queries and ColumnStore for analytical queries.
Reference
MariaDB Server Reference
Information Schema ENGINES table, which shows available storage engines
Information Schema TABLES table, which shows storage engine by table