Getting Started with RocksDB in Percona Server for MySQL

September 1, 2022

Author

David Stokes

Insight for DBAs

MySQL

Percona Software

Share this Post:

RocksDB in Percona Server for MySQL You may have read MyRocks Use Case: Big Dataset and been intrigued enough to want to evaluate RocksDB with InnoDB. It has several advantages including requiring less storage space.

So how do you start?

On a fresh install of Percona Server for MySQL, install RocksDB by entering sudo apt install percona-server-rocksdb:

percona@DellXPS:~$ sudo apt install percona-server-rocksdb<br>[sudo] password for percona:<br>Reading package lists... Done<br>Building dependency tree<br>Reading state information... Done<br>The following NEW packages will be installed:<br>percona-server-rocksdb<br>0 upgraded, 1 newly installed, 0 to remove and 32 not upgraded.<br>Need to get 65.3 MB of archives.<br>After this operation, 292 MB of additional disk space will be used.<br>Get:1 http://repo.percona.com/ps-80/apt focal/main amd64 percona-server-rocksdb amd64 8.0.29-21-1.focal [65.3 MB]<br>Fetched 65.3 MB in 8s (8531 kB/s)<br>Selecting previously unselected package percona-server-rocksdb.<br>(Reading database ... 78308 files and directories currently installed.)<br>Preparing to unpack .../percona-server-rocksdb_8.0.29-21-1.focal_amd64.deb ...<br>Unpacking percona-server-rocksdb (8.0.29-21-1.focal) ...<br>Setting up percona-server-rocksdb (8.0.29-21-1.focal) ...<br><br><br>* This release of Percona Server is distributed with RocksDB storage engine.<br>* Run the following script to enable the RocksDB storage engine in Percona Server:<br><br>ps-admin --enable-rocksdb -u &lt;mysql_admin_user&gt; -p[mysql_admin_pass] [-S &lt;socket&gt;] [-h &lt;host&gt; -P &lt;port&gt;]

percona@DellXPS:~$ sudo apt install percona-server-rocksdb [sudo] password for percona: Reading package lists... Done Building dependency tree Reading state information... Done The following NEW packages will be installed: percona-server-rocksdb 0 upgraded, 1 newly installed, 0 to remove and 32 not upgraded. Need to get 65.3 MB of archives. After this operation, 292 MB of additional disk space will be used. Get:1 http://repo.percona.com/ps-80/apt focal/main amd64 percona-server-rocksdb amd64 8.0.29-21-1.focal [65.3 MB] Fetched 65.3 MB in 8s (8531 kB/s) Selecting previously unselected package percona-server-rocksdb. (Reading database ... 78308 files and directories currently installed.) Preparing to unpack .../percona-server-rocksdb_8.0.29-21-1.focal_amd64.deb ... Unpacking percona-server-rocksdb (8.0.29-21-1.focal) ... Setting up percona-server-rocksdb (8.0.29-21-1.focal) ... * This release of Percona Server is distributed with RocksDB storage engine. * Run the following script to enable the RocksDB storage engine in Percona Server: ps-admin --enable-rocksdb -u <mysql_admin_user> -p[mysql_admin_pass] [-S <socket>] [-h <host> -P <port>]

Next, we can enable RocksDB by typing sudo ps-admin –enable-rocksdb -u -p:

percona@DellXPS:~$ sudo ps-admin --enable-rocksdb -u root -p<br>Enter password:<br><br>Checking if RocksDB plugin is available for installation ...<br>INFO: ha_rocksdb.so library for RocksDB found at /usr/lib/mysql/plugin/ha_rocksdb.so.<br><br>Checking RocksDB engine plugin status...<br>INFO: RocksDB engine plugin is not installed.<br><br>Installing RocksDB engine...<br>INFO: Successfully installed RocksDB engine plugin.

percona@DellXPS:~$ sudo ps-admin --enable-rocksdb -u root -p Enter password: Checking if RocksDB plugin is available for installation ... INFO: ha_rocksdb.so library for RocksDB found at /usr/lib/mysql/plugin/ha_rocksdb.so. Checking RocksDB engine plugin status... INFO: RocksDB engine plugin is not installed. Installing RocksDB engine... INFO: Successfully installed RocksDB engine plugin.

Now it is time to fire up a CLI for MySQL to run SHOW ENGINES to double-check that RocksDB is ready.

mysql&gt; show engines;<br>+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+<br>| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |<br>+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+<br>| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |<br>| ROCKSDB            | YES     | RocksDB storage engine                                                     | YES          | YES  | YES        |<br>| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |<br>| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |<br>| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |<br>| FEDERATED          | NO      | Federated MySQL storage engine                                             | NULL         | NULL | NULL       |<br>| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |<br>| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |<br>| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |<br>| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |<br>+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+<br>10 rows in set (0.01 sec)

mysql> show engines; +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | ROCKSDB | YES | RocksDB storage engine | YES | YES | YES | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+ 10 rows in set (0.01 sec)

And now we can use RocksDB.

mysql&gt; create schema rocks;<br>Query OK, 1 row affected (0.01 sec)<br><br>mysql&gt; use rocks;<br>Database changed<br>mysql&gt; create table r1 (id int, foo char(25)) engine=rocksdb;<br>Query OK, 0 rows affected (0.01 sec)<br><br>mysql&gt; insert into r1 (id,foo) values (1,'test'),(2,'wow'),(3,'q');<br>Query OK, 3 rows affected (0.01 sec)<br>Records: 3  Duplicates: 0  Warnings: 0<br><br>mysql&gt; select * from r1;<br>+------+------+<br>| id   | foo  |<br>+------+------+<br>|    1 | test |<br>|    2 | wow  |<br>|    3 | q    |<br>+------+------+<br>3 rows in set (0.00 sec)

mysql> create schema rocks; Query OK, 1 row affected (0.01 sec) mysql> use rocks; Database changed mysql> create table r1 (id int, foo char(25)) engine=rocksdb; Query OK, 0 rows affected (0.01 sec) mysql> insert into r1 (id,foo) values (1,'test'),(2,'wow'),(3,'q'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from r1; +------+------+ | id | foo | +------+------+ | 1 | test | | 2 | wow | | 3 | q | +------+------+ 3 rows in set (0.00 sec)

Now how does this compare to Innodb? Well, I created a table like the one above but using InnoDB. Populated with the same three lines of data, the results do show a difference. I am sure with larger data sets that the saving with RocksDB would be substantially larger.

mysql&gt; SELECT table_name AS `Table`, <br>              round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` <br>       FROM information_schema.TABLES <br>       WHERE table_schema = "rocks";<br>+-------+-----------+<br>| Table | Size (MB) |<br>+-------+-----------+<br>| i1    |      0.02 |<br>| r1    |      0.00 |<br>+-------+-----------+<br>2 rows in set (0.00 sec)<br>

mysql> SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size (MB)` FROM information_schema.TABLES WHERE table_schema = "rocks"; +-------+-----------+ | Table | Size (MB) | +-------+-----------+ | i1 | 0.02 | | r1 | 0.00 | +-------+-----------+ 2 rows in set (0.00 sec)

So give it a try and let me know your results.