New MySQL RocksDB Server Variables

MyRocks Server Variables

A large number of the parameters which can be used to tune RocksDB have been exported out as server variables through the MyRocks storage engine. In addition, some parameters can be enabled to help speed up loading and creating new MyRocks tables for testing.

The following is a list of parameters which MyRocks currently supports.

• rocksdb : Enables or disables the MyRocks plugin.
• rocksdb-access-hint-on-compaction-start : Specify the file access pattern once a compaction is started, applied to all input files of a compaction.
• rocksdb-advise-random-on-open : Sets a hint of random access to the filesystem when a data file is opened.
• rocksdb-allow-concurrent-memtable-write : If enabled, allows multi-writers to update memtables in parallel. Details/limitations here.
• rocksdb-allow-mmap-reads : Allow the OS to mmap a data file for reads.
• rocksdb-allow-mmap-writes : Allow the OS to mmap a data file for writes.
• rocksdb-base-background-compactions : Suggested number of concurrent background compaction jobs, submitted to the default LOW priority thread pool in RocksDB.
• rocksdb-blind-delete-primary-key : Deleting rows by primary key lookup, without reading rows. This only works on tables without secondary keys, as it executes the delete without calling GetForUpdate().
• rocksdb-background-sync : Enables MyRocks to issue fsyncs for the WAL files every second. If WAL files are sync'ed on every commits, then enabling this option is redundant.
• rocksdb-block-cache-size : Size of the LRU block cache in RocksDB. This memory is reserved for the block cache, which is in addition to any filesystem caching that may occur.
• rocksdb-block-restart-interval : Number of keys for each set of delta encoded data.
• rocksdb-block-size : Size of the data block for reading sst files.
• rocksdb-block-size-deviation : If the percentage of free space in the current data block (size specified in rocksdb-block-size) is less than this amount, close the block (and write record to new block).
• rocksdb-bulk-load : When set, MyRocks will ignore checking keys for uniqueness or acquiring locks during transactions. This option should only be used when the application is certain there are no row conflicts, such as when setting up a new MyRocks instance from an existing MySQL dump.
• rocksdb-bulk-load-size : Sets the number of keys to accumulate before committing them to the storage engine during bulk loading.
• rocksdb-bytes-per-sync : Enables the OS to sync out file writes as data files are created.
• rocksdb-cache-index-and-filter-blocks : Requests RocksDB to use the block cache for caching the index and bloomfilter data blocks from each data file. If this is not set, RocksDB will allocate additional memory to maintain these data blocks.
• rocksdb-cf-options : Enables setting options on the column families.
• rocksdb-cfstats : Enables the Information Schema ROCKSDB_CFSTATS for showing column family statistics.
• rocksdb-checksums-pct : Sets the percentage of rows to calculate and set MyRocks checksums.
• rocksdb-collect-sst-properties : Enables collecting statistics of each data file for improving optimizer behavior.
• rocksdb-commit-in-the-middle : Commit rows implicitly every rocksdb-bulk-load-size, during bulk load/insert/update/deletes.
• rocksdb-compact-cf : Triggers a manual compaction within RocksDB of a specific column family.
• rocksdb-compaction-readahead-size : When non-zero, bigger reads are performed during compaction. Useful if running RocksDB on spinning disks, compaction will do sequential instead of random reads.
• rocksdb-compaction-sequential-deletes : Enables triggering of compaction when the number of delete markers in a data file exceeds a certain threshold. Depending on workload patterns, RocksDB can potentially maintain large numbers of delete markers and increase latency of all queries.
• rocksdb-compaction-sequential-deletes-count-sd : If enabled, factor in single deletes as part of rocksdb-compaction-sequential-deletes.
• rocksdb-compaction-sequential-deletes-file-size : Threshold to trigger compaction if the number of sequential keys that are all delete markers exceed this value. While this compaction helps reduce request latency by removing delete markers, it can increase write rates of RocksDB.
• rocksdb-compaction-sequential-deletes-window : Threshold to trigger compaction if, within a sliding window of keys, there exists this parameter's number of delete marker.
• rocksdb-create-checkpoint : Creates a RocksDB checkpoint in the specified directory.
• rocksdb-create-if-missing : Allows creating the RocksDB database if it does not exist.
• rocksdb-create-missing-column-families : Allows creating new column families if they did not exist.
• rocksdb-datadir : Directory to store the data files in.
• rocksdb-db-write-buffer-size : Total allowable size of the memtables used to store writes within RocksDB before triggering a flush to disk. This is the size of all memtables across all column families.
• rocksdb-deadlock-detect : Enables deadlock detection in RocksDB.
• rocksdb-debug-optimizer-no-zero-cardinality : Test only to prevent MyRocks from calculating cardinality
• rocksdb-default-cf-options : Describes the default set of column family options for RocksDB.
• rocksdb-delayed-write-rate : When RocksDB hits the soft limits/thresholds for writes, such as soft_pending_compaction_bytes_limit being hit, or level0_slowdown_writes_trigger being hit, RocksDB will slow the write rate down to the value of this parameter as bytes/second. If this is set to 16 * 1024 * 1024, then it is treated as 16MB/second.
• rocksdb-delete-obsolete-files-period-micros : The periodicity of when obsolete files get deleted, but does not affect files removed through compaction.
• rocksdb-enable-2pc : Enables two phase commit for MyRocks transactions. Otherwise, the prepare phase is skipped. the variable was deprecated in 8.0.28 and removed in 8.0.32.
• rocksdb-enable-bulk-load-api : Enables using the SSTFileWriter feature in RocksDB, which bypasses the memtable, but this requires keys to be inserted into the table in either ascending or descending order. If disabled, bulk loading uses the normal write path via the memtable and does not keys to be inserted in any order.
• rocksdb-enable-thread-tracking : Set to allow RocksDB to track the status of threads accessing the database.
• rocksdb-enable-write-thread-adaptive-yield : Set to allow RocksDB write batch group leader to wait up to the max time allowed before blocking on a mutex, allowing an increase in throughput for concurrent workloads.
• rocksdb-error-if-exists : If set, reports an error if an existing database already exists.
• rocksdb-flush-log-at-trx-commit : Sync'ing on transaction commit similar to innodb-flush-log-at-trx-commit.
  • 0 : never sync
  • 1 : always sync
  • 2 : sync based on a timer controlled via rocksdb-background-sync
• rocksdb-flush-memtable-on-analyze : When analyze table is run, determines of the memtable should be flushed so that data in the memtable is also used for calculating stats.
• rocksdb-force-compute-memtable-stats : When enabled, also include data in the memtables for index statistics calculations used by the query optimizer. Greater accuracy, but requires more cpu.
• rocksdb-force-flush-memtable-now : Triggers MyRocks to flush the memtables out to the data files.
• rocksdb-force-index-records-in-range : When force index is used, a non-zero value here will be used as the number of rows to be returned to the query optimizer when trying to determine the estimated number of rows.
• rocksdb-global-info : Enables the Information Schema ROCKSDB_GLOBAL_INFO plugin for reporting data dictionary information.
• rocksdb-hash-index-allow-collision : Enables RocksDB to allow hashes to collide (uses less memory). Otherwise, the full prefix is stored to prevent hash collisions.
• rocksdb-index-file-map : Enables the Information Schema ROCKSDB_INDEX_FILE_MAP plugin for showing the list of sst data files in RocksDB.
• rocksdb-index-type : Sets the type of indexing (binary search, hash search) used in RocksDB.
• rocksdb-info-log-level : Sets the log level for printing of log messages.
• rocksdb-is-fd-close-on-exec : Disables the child process from inheriting open file handles.
• rocksdb-keep-log-file-num : Sets the maximum number of info LOG files to keep around.
• rocksdb-lock-scanned-rows : If enabled, rows that are scanned during UPDATE remain locked even if they have not been updated.
• rocksdb-lock-wait-timeout : Sets the number of seconds MyRocks will wait to acquire a row lock before aborting the request.
• rocksdb-log-file-time-to-roll : Sets the number of seconds a info LOG file captures before rolling to a new LOG file.
• rocksdb-manifest-preallocation-size : Sets the number of bytes to preallocate for the MANIFEST file in RocksDB and reduce possible random I/O on XFS. MANIFEST files are used to store information about column families, levels, active files, etc.
• rocksdb-master-skip-tx-api : Skips acquiring row locks in a transaction when running on the master. This can significantly improve performance, but only if the application can guarantee the rows are never modified by concurrent transactions. Otherwise, the database can become corrupted and inconsistent.
• rocksdb-max-background-compactions : Sets the number of concurrent background compaction threads.
• rocksdb-max-background-flushes : Sets the number of concurrent background memtable flush threads.
• rocksdb-max-log-file-size : Sets the maximum size on the info LOG files.
• rocksdb-max-manifest-file-size : Sets the maximum size of the MANIFEST data file before rolling it over.
• rocksdb-max-open-files : Sets a limit on the maximum number of file handles opened by RocksDB.
• rocksdb-max-row-locks : Sets a limit on the maximum number of row locks held by a transaction before failing it.
• rocksdb-max-subcompactions : For each compaction job, the maximum threads that will work on it simultaneously (i.e. subcompactions). A value of 1 means no subcompactions.
• rocksdb-max-total-wal-size : Sets a limit on the maximum size of WAL files kept around. Once this limit is hit, RocksDB will force the flushing of memtables to reduce the size of WAL files.
• rocksdb-merge-buf-size : Size (in bytes) of the merge buffers used to accumulate data during secondary key creation. During secondary key creation the data, we avoid updating the new indexes through the memtable and L0 by writing new entries directly to the lowest level in the database. This requires the values to be sorted so we use a merge/sort algorithm. This setting controls how large the merge buffers are. The default is 64Mb.
• rocksdb-merge-combine-read-size : Size (in bytes) of the merge combine buffer used in the merge/sort algorithm as described in rocksdb-merge-buf-size. The default is 1Gb.
• rocksdb-new-table-reader-for-compaction-inputs : Indicates whether RocksDB should create a new file descriptor and table reader for each compaction input. Doing so may use more memory but may allow pre-fetch options to be specified for compaction input files without impacting table readers used for user queries. Default is false.
• rocksdb-no-block-cache : Disables using the block cache for a column family.
• rocksdb-override-cf-options : Option string to enable specific options override for each column family.
• rocksdb-paranoid-checks : Forces RocksDB to re-read a data file that was just created to verify correctness.
• rocksdb-pause-background-work : Test only to start and stop all background compactions within RocksDB.
• rocksdb-perf-context-level : Sets the level of information to capture via the perf context plugins.
• rocksdb-persistent-cache-path : If rocksdb-persistent-cache-size-mb is set, this indicates the path where the persistent cache can store files. This must be set if rocksdb-persistent-cache-size-mb is set.
• rocksdb-persistent-cache-size-mb : The size (in Mb) to allocate to the RocksDB persistent cache if desired. The default is 0 (disabled).
• rocksdb-pin-l0-filter-and-index-blocks-in-cache : If rocksdb-cache-index-and-filter-blocks is true then this controls whether RocksDB 'pins' the filter and index blocks in the cache. Default is true.
• rocksdb-print-snapshot-conflict-queries : If this is true, MyRocks will log queries that generate snapshot conflicts into the .err log. Default is false.
• rocksdb-rate-limiter-bytes-per-sec : Controls the rate at which RocksDB is allowed to write to media via memtable flushes and compaction.
• rocksdb-read-free-rpl-tables : A list of tables (using regex) that will use read-free replication on the slave. Default is empty.
• rocksdb-records-in-range : Test only to override the value returned by records-in-range.
• rocksdb-seconds-between-stat-computes : Sets the number of seconds between recomputation of table statistics for the optimizer.
• rocksdb-signal-drop-index-thread : Test only to signal the MyRocks drop index thread.
• rocksdb-skip-bloom-filter-on-read : Indicates whether the bloom filters should be skipped on reads. Default is false.
• rocksdb-skip-fill-cache : Requests MyRocks to skip caching data on read requests.
• rocksdb-stats-dump-period-sec : Sets the number of seconds to perform a RocksDB stats dump to the info LOG files.
• rocksdb-strict-collation-check : Enables MyRocks to check and verify table indexes have the proper collation settings.
• rocksdb-strict-collation-exceptions : Table names that match this list of regex patterns can skip the collation check settings.
• rocksdb-table-cache-numshardbits : Sets the number of table caches within RocksDB.
• rocksdb-use-adaptive-mutex : Enables adaptive mutexes in RocksDB which spins in user space before resorting to the kernel.
• rocksdb-use-fsync : Requires RocksDB to use fsync instead of fdatasync when requesting a sync of a data file.
• rocksdb-validate-tables : Requires MyRocks to verify all of MySQL's .frm files match tables stored in RocksDB.
• rocksdb-wal-bytes-per-sync : Controls the rate at which RocksDB writes out WAL file data.
• rocksdb-wal-dir : Sets the directory to store RocksDB WAL files.
• rocksdb-wal-recovery-mode : Sets RocksDB's level of tolerance when recovering the WAL files after a system crash.
• rocksdb-whole-key-filtering : Enables the bloomfilter to use the whole key for filtering instead of just the prefix. In order for this to be efficient, lookups should use the whole key for matching.
• rocksdb-write-disable-wal : Disables logging data to the WAL files. Useful for bulk loading.
• rocksdb-write-ignore-missing-column-families : If true, then writes to column families that do not exist is ignored by RocksDB.
• rocksdb-skip-bloom-filter-on-read : Don't use bloom filter on reads.
• rocksdb-skip-unique-check-tables : Skip unique constraint checking for the specified tables.
• rocksdb-store-row-debug-checksums : Include checksums when writing index/table records.
• Include checksums when writing index/table records : Percentage of entries to sample when collecting statistics about table properties. Specify either 0 to sample everything or percentage.
• rocksdb-tmpdir : Directory for temporary files during DDL operations.
• rocksdb-trace-sst-api : Generate trace output in the log for each call to the SstFileWriter.
• rocksdb-unsafe-for-binlog : Allowing statement based binary logging which may break consistency.
• rocksdb-use-direct-reads : Enable direct IO when opening a file for read/write. This means that data will not be cached or buffered.
• rocksdb-use-direct-writes : Similar to rocksdb-use-direct-reads.
• rocksdb-verify-row-debug-checksums : Verify checksums when reading index/table records.
• rocksdb-wal-size-limit-mb : Maximum size of the RocksDB WAL archive. WAL files whose memtables have been flushed are moved to an archive directory. This variable controls the maximum size of that archive.
• rocksdb-wal-ttl-seconds : No WAL file older than this value should exist.