In 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:
How do you assign tables and indexes to a column family?
It is defined in the COMMENT section for a key or primary key:
CREATE TABLE tab1( a INT, b INT, PRIMARY KEY (a) COMMENT ‘cfname=cf1’, KEY key_b (b) COMMENT ‘cfname=cf2’) )
Now, if you want to define individual parameters for column families, you should use
rocksdb_override_cf_options
For example:
rocksdb_override_cf_options=’cf1={compression=kNoCompression}; cf2={compression=kLZ4Compression,bottommost_compression==kZSTD}’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
:
Type: CF_COMPACTION Name: cf1 Status: ** Compaction Stats [cf1] ** Level Files Size Score Read(GB) Rn(GB) Rnp1(GB) Write(GB) Wnew(GB) Moved(GB) W-Amp Rd(MB/s) Wr(MB/s) Comp(sec) Comp(cnt) Avg(sec) KeyIn KeyDrop ---------------------------------------------------------------------------------------------------------------------------------------------------------- L0 0/0 0.00 KB 0.0 0.0 0.0 0.0 1.5 1.5 0.0 1.0 0.0 99.1 15 37 0.410 0 0 L5 3/0 197.05 MB 0.8 0.4 0.4 0.0 0.4 0.4 0.0 1.0 75.6 75.6 6 1 5.923 8862K 0 L6 7/0 341.24 MB 0.0 1.7 1.3 0.5 0.8 0.3 0.0 0.6 42.8 19.5 42 7 5.933 61M 0 Sum 10/0 538.29 MB 0.0 2.2 1.7 0.5 2.7 2.2 0.0 1.8 35.5 44.1 63 45 1.392 70M 0 Int 0/0 0.00 KB 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0.000 0 0 Type: CF_COMPACTION Name: cf2 Status: ** Compaction Stats [cf2] ** Level Files Size Score Read(GB) Rn(GB) Rnp1(GB) Write(GB) Wnew(GB) Moved(GB) W-Amp Rd(MB/s) Wr(MB/s) Comp(sec) Comp(cnt) Avg(sec) KeyIn KeyDrop ---------------------------------------------------------------------------------------------------------------------------------------------------------- L0 0/0 0.00 KB 0.0 0.0 0.0 0.0 0.3 0.3 0.0 1.0 0.0 13.5 22 22 1.023 0 0 L6 4/0 178.61 MB 0.0 0.6 0.3 0.3 0.4 0.2 0.0 1.5 9.3 7.3 61 5 12.243 72M 0 Sum 4/0 178.61 MB 0.0 0.6 0.3 0.3 0.7 0.5 0.0 2.5 6.8 9.0 84 27 3.100 72M 0 Int 0/0 0.00 KB 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0 0.000 0 0
To check the current column families and their settings you can use:
SELECT * FROM INFORMATION_SCHEMA.ROCKSDB_CF_OPTIONS:
| cf1 | COMPARATOR | RocksDB_SE_v3.10
|
| cf1 | MERGE_OPERATOR | NULL |
| cf1 | COMPACTION_FILTER | NULL
| cf1 | COMPACTION_FILTER_FACTORY | Rdb_compact_filter_factory
| cf1 | WRITE_BUFFER_SIZE | 67108864
| cf1 | MAX_WRITE_BUFFER_NUMBER | 2
| cf1 | MIN_WRITE_BUFFER_NUMBER_TO_MERGE | 1
| cf1 | NUM_LEVELS | 7
| cf1 | LEVEL0_FILE_NUM_COMPACTION_TRIGGER | 4
| cf1 | LEVEL0_SLOWDOWN_WRITES_TRIGGER | 20
| cf1 | LEVEL0_STOP_WRITES_TRIGGER | 36
| cf1 | MAX_MEM_COMPACTION_LEVEL | 0
| cf1 | TARGET_FILE_SIZE_BASE | 67108864
| cf1 | TARGET_FILE_SIZE_MULTIPLIER | 1
| cf1 | MAX_BYTES_FOR_LEVEL_BASE | 268435456
| cf1 | LEVEL_COMPACTION_DYNAMIC_LEVEL_BYTES | ON
| cf1 | MAX_BYTES_FOR_LEVEL_MULTIPLIER | 10.000000
| cf1 | SOFT_RATE_LIMIT | 0.000000
| cf1 | HARD_RATE_LIMIT | 0.000000
| cf1 | RATE_LIMIT_DELAY_MAX_MILLISECONDS | 100
| cf1 | ARENA_BLOCK_SIZE | 0
| cf1 | DISABLE_AUTO_COMPACTIONS | OFF
| cf1 | PURGE_REDUNDANT_KVS_WHILE_FLUSH | ON
| cf1 | MAX_SEQUENTIAL_SKIP_IN_ITERATIONS | 8
| cf1 | MEMTABLE_FACTORY | SkipListFactory
| cf1 | INPLACE_UPDATE_SUPPORT | OFF
| cf1 | INPLACE_UPDATE_NUM_LOCKS | ON
| cf1 | MEMTABLE_PREFIX_BLOOM_BITS_RATIO | 0.000000
| cf1 | MEMTABLE_PREFIX_BLOOM_HUGE_PAGE_TLB_SIZE | 0
| cf1 | BLOOM_LOCALITY | 0
| cf1 | MAX_SUCCESSIVE_MERGES | 0
| cf1 | OPTIMIZE_FILTERS_FOR_HITS | ON
| cf1 | MAX_BYTES_FOR_LEVEL_MULTIPLIER_ADDITIONAL | 1:1:1:1:1:1:1
| cf1 | COMPRESSION_TYPE | kNoCompression
| cf1 | COMPRESSION_PER_LEVEL | NUL
| cf1 | COMPRESSION_OPTS | -14:-1:0
| cf1 | BOTTOMMOST_COMPRESSION | kLZ4Compression
| cf1 | PREFIX_EXTRACTOR | NULL
| cf1 | COMPACTION_STYLE | kCompactionStyleLevel
| cf1 | COMPACTION_OPTIONS_UNIVERSAL | {SIZE_RATIO=1; MIN_MERGE_WIDTH=2; MAX_MERGE_WIDTH=4294967295; MAX_SIZE_AMPLIFICATION_PERCENT=200; COMPRESSION_SIZE_PERCENT=-1; STOP_STYLE=kCompactionStopStyleTotalSize} |
| cf1 | COMPACTION_OPTION_FIFO::MAX_TABLE_FILES_SIZE | 1073741824
| cf1 | TABLE_FACTORY::FLUSH_BLOCK_POLICY_FACTORY | FlushBlockBySizePolicyFactory(0x4715df0)
| cf1 | TABLE_FACTORY::CACHE_INDEX_AND_FILTER_BLOCKS | 1
| cf1 | TABLE_FACTORY::CACHE_INDEX_AND_FILTER_BLOCKS_WITH_HIGH_PRIORITY | 0
| cf1 | TABLE_FACTORY::PIN_L0_FILTER_AND_INDEX_BLOCKS_IN_CACHE | 1
| cf1 | TABLE_FACTORY::INDEX_TYPE | 0
| cf1 | TABLE_FACTORY::HASH_INDEX_ALLOW_COLLISION | 1
| cf1 | TABLE_FACTORY::CHECKSUM | 1
| cf1 | TABLE_FACTORY::NO_BLOCK_CACHE | 0
| cf1 | TABLE_FACTORY::BLOCK_CACHE | 0x470c880
| cf1 | TABLE_FACTORY::BLOCK_CACHE_NAME | LRUCache
| cf1 | TABLE_FACTORY::BLOCK_CACHE_OPTIONS |
| cf1 | TABLE_FACTORY::CAPACITY | 536870912
| cf1 | TABLE_FACTORY::NUM_SHARD_BITS | 6
| cf1 | TABLE_FACTORY::STRICT_CAPACITY_LIMIT | 0
| cf1 | TABLE_FACTORY::HIGH_PRI_POOL_RATIO | 0.000
| cf1 | TABLE_FACTORY::BLOCK_CACHE_COMPRESSED | (nil)
| cf1 | TABLE_FACTORY::PERSISTENT_CACHE | (nil)
| cf1 | TABLE_FACTORY::BLOCK_SIZE | 16384
| cf1 | TABLE_FACTORY::BLOCK_SIZE_DEVIATION | 10
| cf1 | TABLE_FACTORY::BLOCK_RESTART_INTERVAL | 16
| cf1 | TABLE_FACTORY::INDEX_BLOCK_RESTART_INTERVAL | 1
| cf1 | TABLE_FACTORY::METADATA_BLOCK_SIZE | 4096
| cf1 | TABLE_FACTORY::PARTITION_FILTERS | 0
| cf1 | TABLE_FACTORY::USE_DELTA_ENCODING | 1
| cf1 | TABLE_FACTORY::FILTER_POLICY | rocksdb.BuiltinBloomFilter
| cf1 | TABLE_FACTORY::WHOLE_KEY_FILTERING | 1
| cf1 | TABLE_FACTORY::VERIFY_COMPRESSION | 0
| cf1 | TABLE_FACTORY::READ_AMP_BYTES_PER_BIT | 0
| cf1 | TABLE_FACTORY::FORMAT_VERSION | 2
| cf1 | TABLE_FACTORY::ENABLE_INDEX_COMPRESSION | 1
| cf1 | TABLE_FACTORY::BLOCK_ALIGN | 0
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