Poor insert performance

[Yves Trudeau]

Hi,
   I am trying to understand how RocksDB works and see where it can be a good fit. I cannot get what causes this configuration to be very slow at inserts after about 27M rows:

rocksdb_block_cache_size=128M 
rocksdb_flush_log_at_trx_commit=2
rocksdb_block_size=16384 

# Insert rate went from 2M/min to less than 30k/min after 27M rows
rocksdb_default_cf_options=write_buffer_size=128m;target_file_size_base=32m;max_bytes_for_level_base=512m;level0_file_num_compaction_trigger=4;level0_slowdown_writes_trigger=10;level0_stop_writes_trigger=15;max_write_buffer_number=4;compression_per_level=kLZ4Compression;bottommost_compression=kZSTD;compression_opts=-14:1:0;block_based_table_factory={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=0};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true;compaction_pri=kMinOverlappingRatio;memtable_prefix_bloom_size_ratio=0.05;prefix_extractor=capped:12;


While this one is good fine, inserts wise at least:

rocksdb_block_cache_size=128M 
rocksdb_flush_log_at_trx_commit=2
rocksdb_block_size=16384 

# (stable rate, 1.8M/min)
rocksdb_default_cf_options=write_buffer_size=128m;target_file_size_base=32m;max_bytes_for_level_base=512m;level0_file_num_compaction_trigger=4;level0_slowdown_writes_trigger=10;level0_stop_writes_trigger=15;max_write_buffer_number=4;compression_per_level=kLZ4Compression;bottommost_compression=kZSTD;compression_opts=-14:1:0;


I tried various combinations of parameters from the block_based_table_factory but got most of the time a crashing or not starting instance.  BTW, I am use PS 5.7 with MyRocks and the top rocksdb_default_cf_options is a recommendation I found in the MyRocks wiki although I am using a smaller block cache.  Someone has an explanation?

Regards,

Yves

[Mark Callaghan]

Hello, I have been away from this group for a while. 

My hope is that most options have good default values, so future users don't have to set as many. Where aren't there yet.

It looks like you started with the my.cnf for linkbench based on the use of prefix bloom filters. This page has one for linkbench and one for things other than linkbench - https://github.com/facebook/mysql-5.6/wiki/my.cnf-tuning

---
Compaction IO statistics and details on stalls are reported in SHOW ENGINE ROCKSDB STATUS. That might help to explain the problem. I have never used rocksdb_block_cache_size as small as 128M but I don't think that would be a problem. For the settings that were different between your good and bad result. Interesting output includes the following which might help explain why the write rate degrades:

Cumulative stall: 00:00:0.000 H:M:S, 0.0 percent

Stalls(count): 0 level0_slowdown, 0 level0_slowdown_with_compaction, 0 level0_numfiles, 0 level0_numfiles_with_compaction, 0 stop for pending_compaction_bytes, 0 slowdown for pending_compaction_bytes, 0 memtable_compaction, 0 memtable_slowdown, interval 0 total count

---

block_based_table_factory={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=0}

cache_index_and_filter_blocks - this is enabled by default and with it (most) index and bloom filter blocks are stored in the block cache. When set to 0 they are pinned in memory and don't use the block cache. Pinning them avoids the overhead of finding them in the block cache at the risk of using too much memory. 

pin_l0_filter_and_index_blocks_in_cache is also useful and enabled by default. With it the index and filter blocks for the L0 are pinned in memory.

For more see https://github.com/facebook/rocksdb/wiki/Memory-usage-in-RocksDB but "default in RocksDB" is not always "default in MyRocks".

filter_policy=bloomfilter:10:false;whole_key_filtering=0 -> this means to use a bloom filter with 10 bits/key 
memtable_prefix_bloom_size_ratio=0.05 -> 0 by default, when > 0 then create prefix bloom on memtable, that might slow down inserts

prefix_extractor=capped:12 -> and this means the bloom filter is created using the first 12 bytes of the index key

---

level_compaction_dynamic_level_bytes=true -> false by default, you want to use this see https://rocksdb.org/blog/2015/07/23/dynamic-level.html

optimize_filters_for_hits=true -> false by default, when true there is not bloom filter on the max level. If inserts must do check for duplicates because of unique/PK then a bloom filter on the max level might make inserts go faster, especially when your workload doesn't have dups. The reason is that the bloom filter can satisfy the check. Without it the data block in the max level might be read. But there is a benefit from not having bloom filters on the max level. Without them there is less data that needs to be in the block cache and the block cache that you used is small (128m).

compaction_pri=kMinOverlappingRatio -> default is kByCompensatedSize but kMinOverlappingRatio is usually a better choice