Storage Engines Overview
Contents
- Overview
- Examples
- Resources
- FAQ
- Can I use more than one storage engine on a server?
- Can I use more than one storage engine in a single query?
- What storage engine should I use for transactional or OLTP workloads?
- What storage engine should I use for analytical or OLAP workloads?
- What storage engine should I use if my application performs both transactional and analytical queries?
- Reference
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 |
---|---|---|---|
Aria | Read-Heavy | Reads | ES 10.5+ |
ColumnStore | Analytics, HTAP | Big Data, Analytical | ES 10.5+ |
InnoDB | General Purpose | Mixed Read/Write | ES 10.5+ |
Memory | Cache, Temp | Temporary Data | ES 10.5+ |
MyISAM | Reads | Reads | ES 10.5+ |
MyRocks | Write-Heavy | I/O Reduction, SSD | ES 10.5+ |
S3 | Cloud | Read-Only | ES 10.5+ |
Spider | Federation | Sharding, Interlink | ES 10.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.
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
- Plugins.
- Information Schema ENGINES table, which shows available storage engines.
- Information Schema TABLES table, which shows storage engine by table.