Column Families in MyRocks

myrocks column familiesIn my webinar How To Rock with MyRocks I briefly mentioned the column families feature in MyRocks, that allows a fine tuning for indexes and primary keys.

Let’s review it in more detail.

To recap, MyRocks is based on the RocksDB library, which stores all data in [key => value] pairs, so when it translates to MySQL, all Primary Keys (data) and secondary keys (indexes) are stored in [ key => value ] pairs, which by default are assigned to “default” Column Family.

Each column family has individual set of

  • SST files, and their parameters
  • Memtable and its parameters
  • Bloom filters, and their parameters
  • Compression settings

There is a N:1 relation between tables and indexes to column family, so schematically it looks like this:

column families myrocks

How do you assign tables and indexes to a column family?

It is defined in the COMMENT section for a key or primary key:

Now, if you want to define individual parameters for column families, you should use rocksdb_override_cf_options 

For example:

Be careful of defining too many column families: as I mentioned, each column family will use an individual memtable, which takes 64MB of memory by default.

There is also an individual set of SST tables. You can see how they perform with SHOW ENGINE ROCKSDB STATUS :

To check the current column families and their settings you can use:

As a reminder MyRocks is available in Percona Server 5.7 and Percona Server 8.0, you can try it and share your experience!

Photo by Debby Hudson on Unsplash

Share this post

Leave a Reply