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-connectIf 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
0 Comments