Migrating from InnoDB to RocksDB

Migrating from InnoDB instance to MyRocks instance (dump and load)

By default, fb-mysql (5.6) supports running only one storage engine at a time. When we migrated from InnoDB to MyRocks at Facebook, we dumped from InnoDB instance then loaded into MyRocks instance. This can be achieved via a utility such as mysqldump.

The rough outline of the process is:

• Copy all the database and table schema from source to destination. Switch to case sensitive keys if possible
• Dump each table to a file using "SELECT INTO OUTFILE", or piping output to a mysql client
• Run these steps with bulk loading enabled.

To speed up the loading process on the destination, it is recommended to use the following options in mysqldump:

* --order-by-primary
* --rocksdb_bulk_load=1
* --rocksdb_bulk_load_allow_sk=1

--rocksdb-bulk-load and --rocksdb-bulk-load-allow-sk are Facebook MySQL (mysqldump) extension to add rocksdb bulk loading instructions to mysqldump output. It does no-op if target instances are not MyRocks, so the output can be used for any other engine. For MyRocks destination, it should be loaded with --sql_log_bin=0 so that it will not overflow replication.

Once you create a first MyRocks instance, you can clone other MyRocks instances by physical copy, by such as xtrabackup.

Migrating by schema changes

See Schema-Changes for details.

Converting case insensitive (ci) keys to case sensitive (cs) keys

MyRocks adopted "Mem Comparable" keys. The mem comparable key is a performance optimization for case sensitive char/varchar keys. It compares keys just by single memcmp, and avoids comparing keys involving copies. As a side effect, case insensitive keys are less efficient. Case insensitive keys in MyRocks have either of the following disadvantages.

• It stores extra data (storing both original data and "decodable" data) for comparing keys with case insensitive format.
• Can't do index-only scans when used with secondary keys.

Tables that have case insensitive keys can be listed up as follows.

select c.table_name as 'tables with case insensitive keys' from information_schema.columns as c, 
information_schema.statistics as s where c.table_schema=DATABASE() and c.table_schema=s.table_schema and 
c.table_name=s.table_name and c.column_name=s.column_name and c.collation_name like '%ci' 
group by c.table_name order by c.table_name;

If your table is small and rarely accessed, you can just leave the table as case insensitive, since this optimization is just for performance (and space). You can explicitly whitelist such tables in my.cnf like below.

 rocksdb_strict_collation_exceptions='t1,.*ci'

In this case, table "t1" and all tables ending with "ci" are allowed to have case insensitive keys. As you see, the rocksdb_strict_collation_exceptions variable supports regular expressions.