Migrating from InnoDB to RocksDB

Currently, fb-mysql supports running only one storage engine at a time. There is no online migration framework to move data between storage engines. Users need to dump logical data from source MySQL server with InnoDB engine and load it to destination MySQL server with RocksDB engine. 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"
• Send the files to the destination and load them using "LOAD DATA INFILE"

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

* --sql_log_bin=0
* --foreign_key_checks=0
* --unique_checks=0
* --rocksdb_compaction_sequential_deletes=0
* --rocksdb_compaction_sequential_deletes_window=0
* --rocksdb_write_disable_wal=1
* --rocksdb_bulk_load=1
* --rocksdb_commit_in_the_middle=1
* --rocksdb_max_background_flushes=12
* --rocksdb_max_background_compactions=12
* --rocksdb_base_background_compactions=12

IMPORTANT: The above options are safe in migration scenarios. It is not recommended to use them elsewhere, as the options make assumptions such as primary key ordering for bulk loading, and no duplicate data. See Data loading for more details, and important information on how to drastically improve load speed for Primary Keys using bulk load and Secondary Keys using fast secondary index creation.

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.