Schedule Meeting

a

MariaDB/MySQL: working with storage engines

by | Aug 8, 2023 | MariaDB, MySQL

Need Help?  Click Here for Expert Support

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

Validating rows with CHECK constraints in MariaDB

Validating rows with CHECK constraints in MariaDB

Relational databases provide several ways to validate data. CHECK constraints are a powerful tool for in-database data validation. Their impact on performance is minimal, if any. In this article we'll discuss MariaDB support for CHECK constraints. Note that the CHECK...

MariaDB ColumnStore SQL limitations

MariaDB ColumnStore SQL limitations

MariaDB ColumnStore is an extremely fast and scalable solution for analytics built on MariaDB, which Vettabase supports. MariaDB ColumnStore inherits the MariaDB SQL dialect, and many MariaDB features. However, numerous MariaDB features are not available for the...

Services

Need Help?  Click Here for Expert Support

0 Comments

Submit a Comment

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