How to install MyRocks into mariaDB as a plugin?

Whats is MyRocks?
MyRocks is a storage engine that integrates RocksDB into MySQL with most features of InnoDB and the storage efficiency of RocksDB. It has been said that it provides 3-4x compression when compared to uncompressed InnoDB tables and 10x less write amplification when compared to InnoDB

What is RocksDB?
RocksDB is a embedded key-value data storage(a fork of LevelDB) which stores data in the disk unlike redis(redis stores data in memory). It uses Log-structured merge-tree algorithm that reduces write amplification.
https://en.wikipedia.org/wiki/Log-structured_merge-tree

Now let's come to the post topic. We are going to compile MyRocks engine as a plugin into mariaDB.

Requirements

1) gcc 4.8.1 and above
MyRocks document says it could be installed in centos 6.8 too. But it's a bit tedious since a MyRocks requires a gcc 4.8 which is not available through default repositories in Centos 6.8 . So let's choose Centos 7 which has gcc 4.8.5
2) gflags-devel
3) readline-devel
4) ncurses-devel
5) openssl-devel
6) lz4-devel
7) gdb
8) git

shell> rpm -qa | grep gcc
libgcc-4.8.5-11.el7.x86_64
gcc-4.8.5-11.el7.x86_64
gcc-gfortran-4.8.5-11.el7.x86_64
gcc-c++-4.8.5-11.el7.x86_64
shell> cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)

Installation Steps

1) The first step in the installation process is to compile mariaDB with MyRocks support

shell> yum install -y gflags-devel readline-devel ncurses-devel openssl-devel lz4-devel gdb git shell> cd /usr/local/
shell> git clone https://github.com/MariaDB/server.git mariadb-10.2
shell> cd mariadb-10.2
shell> git checkout bb-10.2-mariarocks
shell> git submodule init
shell> git submodule update
shell> cmake .
shell> make -j10

2) Next is installing the MySQL system tables. We would be using a different command instead of mysql_install_db

shell> cd /usr/local/mariadb-10.2
shell> cd mysql-test; ./mtr alias

The above step creates data directory in the below location(with a name install.db). Nothing to be alarmed with install.db. It's just the name. It's contents are not so different from a conventional MySQL data directory. We can rename it to whatever name we wish.

shell> ls -ltrh mysql-test/var/install.db
total 23M
drwxr-xr-x. 2 root root 6 May 20 11:33 test
-rw-rw----. 1 root root 5.0M May 20 11:33 ib_logfile1
drwxrwx---. 2 root root 20 May 20 11:33 performance_schema
drwxr-xr-x. 2 root root 4.0K May 20 11:33 mysql
drwxrwx---. 2 root root 4.0K May 20 11:33 mtr
-rw-rw----. 1 root root 2.8K May 20 11:33 ib_buffer_pool
-rw-rw----. 1 root root 5.0M May 20 11:33 ib_logfile0
-rw-rw----. 1 root root 12M May 20 11:33 ibdata1

3) Now we can copy the data directory install.db to our location of interest

shell> cp -r mysql-test/var/install.db /data/mysqldata/install.db shell> cp support-files/my-small.cnf /etc/my.cnf

4) Now add the below lines to /etc/my.cnf to enable MyRocks

plugin-dir=/usr/local/mariadb-10.2/storage/rocksdb
language=/usr/local/mariadb-10.2/sql/share/english
datadir=/data/mysqldata/slave2/install.db
plugin-load=ha_rocksdb
default-storage-engine=rocksdb
skip-innodb
default-tmp-storage-engine=MyISAM
collation-server=latin1_bin

5) We have now got everything installed and ready. Let's start the server

shell> ./sql/mysqld --defaults-file=/etc/my.cnf --user=mysql &

[2] 12245 [root@server1 mariadb-10.2]# 2017-05-20 12:52:52 140303005308992 [Note] ./sql/mysqld (mysqld 10.2.6-MariaDB-log) starting as process 12245 ...
2017-05-20 12:52:52 140303005308992 [Note] RocksDB: 2 column families found
2017-05-20 12:52:52 140303005308992 [Note] RocksDB: Column Families at start:
2017-05-20 12:52:52 140303005308992 [Note] cf=default
2017-05-20 12:52:52 140303005308992 [Note] write_buffer_size=67108864
2017-05-20 12:52:52 140303005308992 [Note] target_file_size_base=67108864
2017-05-20 12:52:52 140303005308992 [Note] cf=__system__
2017-05-20 12:52:52 140303005308992 [Note] write_buffer_size=67108864
2017-05-20 12:52:52 140303005308992 [Note] target_file_size_base=67108864
2017-05-20 12:52:52 140303005308992 [Note] RocksDB: Table_store: loaded DDL data for 0 tables
2017-05-20 12:52:52 140303005308992 [Note] RocksDB: global statistics using get_sched_indexer_t indexer
2017-05-20 12:52:52 140303005308992 [Note] RocksDB instance opened
2017-05-20 12:52:52 140303005308992 [Note] Plugin 'InnoDB' is disabled.
2017-05-20 12:52:52 140303005308992 [Note] Plugin 'FEEDBACK' is disabled.
2017-05-20 12:52:52 140303005308992 [Note] Server socket created on IP: '::'.
2017-05-20 12:52:52 140303005308992 [Warning] 'user' entry 'root@server1.ngl.com' ignored in --skip-name-resolve mode.
2017-05-20 12:52:52 140303005308992 [Warning] 'proxies_priv' entry '@% root@server1.ngl.com' ignored in --skip-name-resolve mode.
2017-05-20 12:52:52 140303005308992 [Note] Reading of all Master_info entries succeded
2017-05-20 12:52:52 140303005308992 [Note] Added new Master_info '' to hash table
2017-05-20 12:52:52 140303005308992 [Note] ./sql/mysqld: ready for connections.
Version: '10.2.6-MariaDB-log' socket: '/tmp/mysql3310.sock' port: 3310 Source distribution

Great. We have successfully started mariaDB. From the mysqld message we could see that mariaDB has started with MyRocks engine enabled.

So Simple isn't it ?

Let's login to the MySQL instance and query the information_schema to see if it shows RocksDB

shell> ./sql/mysql -uroot -p
password:
mysql> select ENGINE,SUPPORT,TRANSACTIONS from information_schema.engines where engine='ROCKSDB'\G
*************************** 1. row ***************************
ENGINE: ROCKSDB
SUPPORT: DEFAULT
TRANSACTIONS: YES
1 row in set (0.00 sec)

Yes RocksDB is available as a plugin with support for transactions.

Let's create a RocksDB table

mysql> create database ara;
mysql> \u ara
mysql> create table rocks_test( name varchar(10),age int(10));
mysql> show create table rocks_test\G
*************************** 1. row ***************************
Table: rocks_test
Create Table: CREATE TABLE `rocks_test` (
`name` varchar(10) COLLATE latin1_bin DEFAULT NULL,
`age` int(10) DEFAULT NULL
) ENGINE=ROCKSDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin
1 row in set (0.00 sec)

Let's insert some data

mysql> insert into rocks_test values ('aravinth',30);
mysql> insert into rocks_test values ('raju',31);

mysql> select * from rocks_test\G
*************************** 1. row ***************************
name: aravinth
age: 30
*************************** 2. row ***************************
name: raju
age: 31
2 rows in set (0.00 sec)

That's it. All done..

In the next post we will see if MyRocks actually provides 3-4x compression when compared to uncompress innoDB tables