Schedule Meeting

a

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             |
| PERFORMANCE_SCHEMA |
| SEQUENCE           |
+--------------------+

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

MariaDB [(none)]> SELECT PLUGIN_NAME, PLUGIN_VERSION, PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_TYPE = 'STORAGE ENGINE' ORDER BY 1;
+--------------------+----------------+---------------+
| PLUGIN_NAME        | PLUGIN_VERSION | PLUGIN_STATUS |
+--------------------+----------------+---------------+
| 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';
UNINSTALL 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 GLOBAL VARIABLES LIKE 'spider\_%';
SHOW GLOBAL STATUS LIKE 'spider\_%';
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)
) ENGINE = ROCKSDB;

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.

Conclusions

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 CreativeCommons Attribution-ShareAlike 4.0 International license. 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: https://creativecommons.org/licenses/by-sa/4.0/

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

The benefits of MariaDB ColumnStore

The benefits of MariaDB ColumnStore

Last week Richard announced our projects on MariaDB ColumnStore. Take a look at his great post, if you didn't already. Since then, I've got some questions from customers, colleagues and friends: why did you guys decide to robustly invest into ColumnStore, and offer...

Let’s go, MariaDB ColumnStore at Vettabase!

Let’s go, MariaDB ColumnStore at Vettabase!

I have been an avid user of Columnar based storage engines for as long as I have been a DBA. The advantage is instantly obvious once you and your teams start to write or convert queries which previously took tens of minutes, are now returning in fractions of a second....

Getting Started with MindsDB and MySQL

Getting Started with MindsDB and MySQL

If you have not already heard, Vettabase is now a partner of MindsDB, working on improving MySQL compatibility. In this post we shall take a look at getting started with MindsDB by connecting to MySQL and some of the improvements to date. We have a ready to go example...

Services

0 Comments

Submit a Comment

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