MariaDB/MySQL: working with storage engines

by | Aug 8, 2023 | MariaDB, MySQL

MariaDB and MySQL support several storage engines. See MariaDB and MySQL storage engines: an overview for a discussion about existing MariaDB and MySQL storage engines.

Here we’ll see how to work with them. We’ll see how to obtain information about storage engines, install them, configure them, monitor them, use them, or convert existing tables to a different storage engine.

Getting storage engine information

We can see which storage engines are present in our MariaDB or MySQL instance by querying the information_schema.ENGINES table:

MariaDB [(none)]> SELECT ENGINE FROM information_schema.ENGINES ORDER BY 1;
| ENGINE             |
| Aria               |
| CSV                |
| InnoDB             |
| MEMORY             |
| MRG_MyISAM         |
| MyISAM             |
| SEQUENCE           |

Or we can obtain more information (not just the info shown below) by querying the information_schema.PLUGINS table:

| Aria               | 1.5            | ACTIVE        |
| binlog             | 1.0            | ACTIVE        |
| CSV                | 1.0            | ACTIVE        |
| InnoDB             | 10.11          | ACTIVE        |
| MEMORY             | 1.0            | ACTIVE        |
| MRG_MyISAM         | 1.0            | ACTIVE        |
| MyISAM             | 1.0            | ACTIVE        |
| partition          | 1.0            | ACTIVE        |
| PERFORMANCE_SCHEMA | 0.1            | ACTIVE        |
| SEQUENCE           | 1.0            | ACTIVE        |
| SQL_SEQUENCE       | 1.0            | ACTIVE        |

Installing storage engines

Other plugins and storage engines might be present in plugin_dir but not installed:

MariaDB [(none)]> SELECT @@plugin_dir;
| @@plugin_dir           |
| /usr/lib/mysql/plugin/ |

Storage engine files have an ha_ prefix and a .so (Linux) or .dll (Windows) extension. They can generally be installed/uninstalled with a statement like this (MariaDB):

INSTALL SONAME 'ha_spider';

If the storage engine that you want to install is not in the pugin_dir, you should be able to install it on Linux using a package. For example, to install MariaDB CONNECT from official apt repositories:

apt-get install mariadb-plugin-connect

If you don’t use official repositories, it’s possible that some storage engines are not available. Check the list of packages of your Linux distribution.

Configuration and status

Every non-trivial engine comes with system variables to configure it, and status variables to monitor it. There might also be system tables in the information_schema database. We can visualise all these things in this way:

SHOW TABLES IN information_schema LIKE 'SPIDER\_%';

All the system variables, status variables and information_schema tables should be explained in the MariaDB and/or MySQL documentation.

Creating tables with the desired storage engine

When we create a table, we can specify which storage engine we want to use:

CREATE TABLE configuration (
    setting_name VARCHAR(100) NOT NULL,
    setting_value VARCHAR(100)

Depending on the storage engine we use, additional clauses might be permitted (or required) in a CREATE TABLE statement. For example, to create a CONNECT table, we’ll typically specify a TABLE_TYPE.

If we don’t specify the ENGINE clause, the default storage engine is used. Normally, this means InnoDB. But we can change or see the default storage engine in this way:

> SELECT @@default_storage_engine;
| @@default_storage_engine |
| InnoDB                   |
> SET default_storage_engine = BLACKHOLE;

We can also specify a different default storage engine for temporary tables. For example, even if InnoDB is the most sensible default for regular tables, it could make sense to specify MEMORY as a default for temporary tables, so MariaDB/MySQL will avoid the overload of writing ephemeral data on disk. We can do this by changing the default_tmp_storage_engine variable. By default it’s NULL, which means that default_storage_engine applies to temporary tables as well.

SET default_tmp_storage_engine = MEMORY;

Some storage engines don’t allow to create tables. An example is SEQUENCE, which work with special virtual tables that exist for the duration of a query. Setting one of this engines as the default forces us to specify an ENGINE clause for every CREATE TABLE.

I don’t recommend relying on the default storage engines. It’s better to always specify an ENGINE clause in ALTER TABLE.

Note that, depending on the sql_mode, if the specified storage engine doesn’t exist or is not installed, the default is used. This is a dangerous behaviour, so make sure that the sql_mode includes the NO_ENGINE_SUBSTITUTION flag in your configuration file.

Changing a table storage engine

The storage engine used for a table can be changed with ALTER TABLE. Note that this is a blocking operation, and it means that the table is rebuilt.

ALTER TABLE configuration ENGINE = 'RocksDB';

Also, note that some features, such as foreign keys or virtual columns, are not supported by all storage engines. By changing the storage engine used for a table we might lose some table characteristics.


Using this article as a reference, you should be able to do any operation with storage engines. Before checking a storage engine you never used, you should find the time to test it extensively and check the documentation. You can also use our Database Health Checks to find out (amongst other things) if you are using storage engines in the right way, and with proper configuration.

Federico Razzoli

All content in this blog is distributed under the Creative Commons Attribution 4.0 International license (CC BY 4.0). You can use it for your needs and even modify it, but please refer to Vettabase and the author of the original post. Read more about the terms and conditions:

About Federico Razzoli
Federico Razzoli is a database professional, with a preference for open source databases, who has been working with DBMSs since year 2000. In the past 20+ years, he served in a number of companies as a DBA, Database Engineer, Database Consultant and Software Developer.In 2016, Federico summarized his extensive experience with MariaDB in the “Mastering MariaDB” book published by Packt. Being an experienced database events speaker, Federico speaks at professional conferences and meetups and conducts database trainings. He is also a supporter and advocate of open source software.As the Director of Vettabase, Federico does business worldwide but prefers to do it from Scotland where he lives.

Recent Posts

MariaDB and MySQL storage engines: an overview

MariaDB and MySQL storage engines: an overview

MySQL was the first DBMS to introduce the concept of storage engines in the early 2000s. This was one of its main features. Later, MariaDB extended the storage engine API and included some storage engine maintained by third parties as part of its official...

MySQL 8.0.33 : Quick Overview

MySQL 8.0.33 : Quick Overview

The MySQL Team has released MySQL 8.0.33 very recently on April 18, 2023. This release contains a number of bug fixes along with some interesting improvements. In this blog post, I'll cover the most important changes in MySQL 8.0.33. Variables can be set while...

The UUID data type in MariaDB

The UUID data type in MariaDB

MariaDB introduced the UUID data type in version 10.7. The first long-term support (LTS) version to include it is 10.11, which was declared stable in February 2023.



Submit a Comment

Your email address will not be published. Required fields are marked *