blog
Using the MyRocks Storage Engine with MariaDB Server
MariaDB Server is one of the most popular, open source database servers. It was created by the original developers of MySQL and it became popular for being fast, scalable, and robust. MariaDB has a rich ecosystem of storage engines, plugins, and other tools that make it very versatile for a wide variety of use cases.
Disk space and I/O efficiency requirements of our databases continue to grow higher. This is so we’re able to manage our information growth in a correct way.
As for the MariaDB storage engine, we have different types to choose from such as XtraDB, InnoDB, Aria, or MyISAM. Since MariaDB 10.2.5 version MyRocks has also been available. MyRocks is the type of storage engine that could really help us meet the requirements we mentioned earlier.
In this blog, we’ll learn more information about the new MyRocks engine and how we can use it in a MariaDB Server.
What is MyRocks?
MyRocks is an open source storage engine based on RocksDB which was originally developed by Facebook.
MyRocks can be a good storage solution when you have workloads that require greater compression and I/O efficiency. It uses a Log Structured Merge (LSM) architecture that has better compression than the B-tree algorithms used by the InnoDB engine (2x better compression compared to data compressed by InnoDB). It’s also a write-optimized storage engine (10x less write amplification when compared to InnoDB) and it has faster data loading and replication. MyRocks writes data directly to the bottom-most level, which avoids all compaction overheads when you enable faster data loading for a session.
An LSM works by storing modify operations in a buffer (memtable) and, sorting and storing the data when this buffer is full.
By default, tables and databases are stored in a #rocksdb directory inside the MySQL datadir. This information is stored in .sst files without per-table separation.
MyRocks supports READ COMMITTED and REPEATABLE READ isolated levels and it doesn’t support SERIALIZABLE.
How to Implement MyRocks on a MariaDB Server
Installation
First, we need to install MariaDB server. In this example, we’ll use CentOS Linux release 7.6 as the operating system.
By default, this OS version will try to install MariaDB 5.5, so we’ll add the MariaDB repository to install the MariaDB version 10.3.
$ cat > /etc/yum.repos.d/MariaDB.repo <<- EOF
# MariaDB 10.3 CentOS repository
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOFAnd then, we’ll install the MariaDB Server package:
$ yum install MariaDB-serverThis command will install different package dependencies, not only MariaDB Server.
==========================================================================================================================================================================================================
Package Arch Version Repository Size
==========================================================================================================================================================================================================
Installing:
MariaDB-server x86_64 10.3.15-1.el7.centos mariadb 24 M
Installing for dependencies:
MariaDB-client x86_64 10.3.15-1.el7.centos mariadb 11 M
MariaDB-common x86_64 10.3.15-1.el7.centos mariadb 78 k
MariaDB-compat x86_64 10.3.15-1.el7.centos mariadb 2.8 M
boost-program-options x86_64 1.53.0-27.el7 base 156 k
galera x86_64 25.3.26-1.rhel7.el7.centos mariadb 8.1 M
libaio x86_64 0.3.109-13.el7 base 24 k
lsof x86_64 4.87-6.el7 base 331 k
make x86_64 1:3.82-23.el7 base 420 k
openssl x86_64 1:1.0.2k-16.el7_6.1 updates 493 k
perl-Compress-Raw-Bzip2 x86_64 2.061-3.el7 base 32 k
perl-Compress-Raw-Zlib x86_64 1:2.061-4.el7 base 57 k
perl-DBI x86_64 1.627-4.el7 base 802 k
perl-Data-Dumper x86_64 2.145-3.el7 base 47 k
perl-IO-Compress noarch 2.061-2.el7 base 260 k
perl-Net-Daemon noarch 0.48-5.el7 base 51 k
perl-PlRPC noarch 0.2020-14.el7 base 36 k
Transaction Summary
==========================================================================================================================================================================================================
Install 1 Package (+16 Dependent packages)By default, the MariaDB Server is installed with the InnoDB storage engine, so we must install the RocksDB engine to be able to make use of it.
$ yum install MariaDB-rocksdb-engine
==========================================================================================================================================================================================================
Package Arch Version Repository Size
==========================================================================================================================================================================================================
Installing:
MariaDB-rocksdb-engine x86_64 10.3.15-1.el7.centos mariadb 4.4 M
Installing for dependencies:
libzstd x86_64 1.3.4-1.el7 mariadb 211 k
snappy x86_64 1.1.0-3.el7 base 40 k
Transaction Summary
==========================================================================================================================================================================================================
Install 1 Package (+2 Dependent packages)This command will install some required dependencies and it’ll enable the plugin on the MariaDB Server. It‘ll also create a configuration file in /etc/my.cnf.d/rocksdb.cnf:
[mariadb]
plugin-load-add=ha_rocksdb.soWe can verify this installation by running the command SHOW PLUGINS into the MariaDB Server.
$ MariaDB> SHOW PLUGINS;
+-------------------------------+----------+--------------------+---------------+---------+
| Name | Status | Type | Library | License |
+-------------------------------+----------+--------------------+---------------+---------+
...
| ROCKSDB | ACTIVE | STORAGE ENGINE | ha_rocksdb.so | GPL |
| ROCKSDB_CFSTATS | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_DBSTATS | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_PERF_CONTEXT | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_PERF_CONTEXT_GLOBAL | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_CF_OPTIONS | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_COMPACTION_STATS | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_GLOBAL_INFO | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_DDL | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_SST_PROPS | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_INDEX_FILE_MAP | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_LOCKS | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_TRX | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
| ROCKSDB_DEADLOCK | ACTIVE | INFORMATION SCHEMA | ha_rocksdb.so | GPL |
+-------------------------------+----------+--------------------+---------------+---------+If for some reason we don’t have the plugin enabled, we can install it dynamically by executing INSTALL SONAME or INSTALL PLUGIN command:
$ MariaDB> INSTALL SONAME 'ha_rocksdb';Another option could be restarting the database services. This action should read the /etc/my.cnf.d/rocksdb.cnf file and enable the plugin.
$ service mariadb restartWe can find detailed information about our RocksDB engine by using the following command:
$ SHOW ENGINE ROCKSDB STATUSConfiguration
About the configuration files, the main one is /etc/my.cnf, which includes the directory /etc/my.cnf.d where we can find the rest of the configuration files. In this directory, we’ll have the following configuration files by default:
- enable_encryption.preset: It will enable data at rest encryption.
- mysql-clients.cnf: Here there are configurations for different groups like [mysqladmin], [mysqlcheck], [mysqldump] and more.
- rocksdb.cnf: In this file, we’ll add the specific configuration for MyRocks, like default-storage-engine or rocksdb_block_size.
- server.cnf: Here we have configuration related to the database server like bind-address and binlog_format.
All MyRocks system variables and status variables are prefaced with "rocksdb". Let take a look on this.
System variables:
$ MariaDB> SHOW VARIABLES LIKE 'rocksdb%';
+-------------------------------------------------+------------------------------------------+
| Variable_name | Value |
+-------------------------------------------------+------------------------------------------+
| rocksdb_access_hint_on_compaction_start | 1 |
| rocksdb_advise_random_on_open | ON |
| rocksdb_allow_concurrent_memtable_write | OFF |
| rocksdb_allow_mmap_reads | OFF |
| rocksdb_allow_mmap_writes | OFF |
| rocksdb_allow_to_start_after_corruption | OFF |
| rocksdb_blind_delete_primary_key | OFF |
| rocksdb_block_cache_size | 536870912 |
| rocksdb_block_restart_interval | 16 |
| rocksdb_block_size | 4096 |
…
+-------------------------------------------------+------------------------------------------+Status variables:
$ MariaDB> SHOW STATUS LIKE 'rocksdb%';
+----------------------------------------------------+-------+
| Variable_name | Value |
+----------------------------------------------------+-------+
| Rocksdb_rows_deleted | 0 |
| Rocksdb_rows_inserted | 0 |
| Rocksdb_rows_read | 0 |
| Rocksdb_rows_updated | 0 |
| Rocksdb_rows_deleted_blind | 0 |
| Rocksdb_rows_expired | 0 |
| Rocksdb_rows_filtered | 0 |
| Rocksdb_system_rows_deleted | 0 |
| Rocksdb_system_rows_inserted | 0 |
| Rocksdb_system_rows_read | 0 |
…
+----------------------------------------------------+-------+You can find more information about the status and system variables on the MariaDB website.
Backups for MariaDB Using MyRocks
Backups are a must in all database environments. They’re essential for system recovery, migrations, auditing, testing, and more.
We can categorize the backups in two different types, logical and physical. The logical backup is stored in a human-readable format like SQL, and the physical backup contains the additional binary data.
For logical backups on MariaDB with MyRocks as the database engine, the most common backup tool is the classic mysqldump:
$ mysqldump -hHOST -uUSER -p DATABASE > FILE.SQLAnd for physical backup, we can use Mariabackup which is compatible with MyRocks:
$ mariabackup --backup --target-dir=/backup/ --user=USER --password=PASSWORD --host=HOSTAnother option can be myrocks_hotbackup, created by Facebook. It can be used to take a physical copy from a running MyRocks instance to local or remote server, without stopping the source instance.
Limitations of Using MyRocks for MariaDB
Let’s look at some of the limitations of using the MyRocks engine...
- MariaDB’s optimistic parallel replication may not be supported
- MyRocks is not available for 32-bit platforms
- MariaDB Cluster (Galera Cluster) doesn’t work with MyRocks (Only InnoDB or XtraDB storage engines)
- The transaction must fit in memory
- Requires special settings for loading data
- SERIALIZABLE is not supported
- Transportable Tablespace, Foreign Key, Spatial Index, and Fulltext Index are not supported
Conclusion
MyRocks is available in MariaDB from versions higher than 10.2.5. As we mentioned earlier, this storage engine may be useful to you when you have workloads that require high data compression and greater levels of I/O efficiency. To learn more about MyRocks you can check this out.
