Getting Started with MyRocks

Download Facebook MySQL 5.6, then build from source

See Build Steps

Set up my.cnf

To enable RocksDB storage engine, you need to set at least the following parameters in my.cnf.

[mysqld]
rocksdb
default-storage-engine=rocksdb
skip-innodb
default-tmp-storage-engine=MyISAM
collation-server=latin1_bin #(or utf8_bin, binary)

log-bin
binlog-format=ROW

If you want to use both InnoDB and MyRocks within the same instance, set "allow-multiple-engines" and remove "skip-innodb" in my.cnf. Using mixed storage engines is not recommended in production, because it is not really transactional. But it is ok for experimental purposes.

Statement based binary logging is allowed on replication slave, but not on master. This is because MyRocks does not support next-key locking.

In addition you may want to set some of the following settings (note - this is not exhaustive):

datadir=<location_for_data_files>
socket=<socket_file>
port=<port>
sql_mode=<mode1>[,<mode2>]

Initialize database with mysql_install_db

mysql_install_db --defaults-file=/path/to/my.cnf

Start mysqld

mysqld_safe --defaults-file=/path/to/my.cnf

Create RocksDB table

Here is an example.

    CREATE TABLE `linktable` (
      `id1` bigint(20) unsigned NOT NULL DEFAULT '0',
      `id1_type` int(10) unsigned NOT NULL DEFAULT '0',
      `id2` bigint(20) unsigned NOT NULL DEFAULT '0',
      `id2_type` int(10) unsigned NOT NULL DEFAULT '0',
      `link_type` bigint(20) unsigned NOT NULL DEFAULT '0',
      `visibility` tinyint(3) NOT NULL DEFAULT '0',
      `data` varchar(255) NOT NULL DEFAULT '',
      `time` bigint(20) unsigned NOT NULL DEFAULT '0',
      `version` int(11) unsigned NOT NULL DEFAULT '0',
      PRIMARY KEY (link_type, `id1`,`id2`) COMMENT 'cf_link_pk',
      KEY `id1_type` (`id1`,`link_type`,`visibility`,`time`,`version`,`data`) COMMENT 'rev:cf_link_id1_type'
    ) ENGINE=RocksDB DEFAULT COLLATE=latin1_bin; 

The example shows some important features and limitations in MyRocks. For limitations, please read MyRocks Limitations for details.

Interpretation of COMMENT associated with key definitions

• MyRocks data is stored in RocksDB, per index basis. RocksDB internally allocates a column family to store indexes. By default all data is stored into default column family. You can change the column family by setting index's COMMENT description. In the example above, the primary key is stored into cf_link_pk column family, and id1_type index data is stored into rev:cf_link_id1_type column family.
• MyRocks has also a feature called reverse column family. Reverse column family is useful if the index is mainly used for descending scan (ORDER BY ... DESC). You can configure reverse column family by setting rev: before the column family name. In this example, id1_type belongs to a reverse column family.

Multiple table partitions and interpretation of COMMENT

We want users to be able to specify the names for the column families on a per partition basis. This means that there won't necessarily be one column family per partition and it should be possible to share a column family between different partitions. Let's look at a simple example:

CREATE TABLE sample (
    c1 INT,
    c2 INT,
    name VARCHAR(25),
    event DATE,
    PRIMARY KEY (`c1`, `c2`)
     COMMENT 'p0_cfname=name_for_cf;p1_cfname=name_for_another_cf;
              p2_cfname=name_for_cf;p3_cfname=rev:name_for_reverse_cf'
) ENGINE=ROCKSDB
PARTITION BY LIST(c1) (
    PARTITION p0 VALUES IN (1, 4, 7),
    PARTITION p1 VALUES IN (2, 5, 8),
    PARTITION p2 VALUES IN (3, 6, 9),
    PARTITION p3 VALUES IN (10, 11, 12),
    PARTITION p4 VALUES IN (20, 30, 40),
);

In this example a table is created with five different partitions: { p0, p1, p2, p3, p4 }. These partitions will have the following column families assigned to them:

p0 = name_for_cf
p1 = name_for_another_cf
p2 = name_for_cf
p3 = rev:name_for_reverse_cf

You'll notice that p0 and p2 will share the same column family; both p1 and p3 will belong to a different one; p4 will have a default column family assigned it to it.

Issues related to validation and specifying per partition qualifiers:

• If the column family for a partition isn't specified then default will be used.
• Overall pattern utilized is partitionname_qualifier=value; where qualifier in the current case is cfname.
• If something like ... PARTITIONS 12; is used as part of the table definition without explicitly specifying partitions names then the user specifying per partition qualifiers needs to be aware of default MySQL naming scheme: p0, p1, p2 etc.

If the partition layout (including both the partition definition and contents of the COMMENT) needs to be changed then standard MySQL sequence consisting of various ALTER TABLE statements will need to be used.