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 yet, 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 : Size of the memtable used to store writes within RocksDB. This is the size per column family. Once this size is reached, a flush of the memtable to persistent media occurs.
• 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.
• 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-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-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 : Enables the Information Schema ROCKSDB_PERF_CONTEXT plugin for capturing MySQL client's performance/latency data from RocksDB.
• rocksdb-perf-context-global : Enables the Information Schema ROCKSDB_PERF_CONTEXT_GLOBAL for capturing background compaction performance/latency data from RocksDB.
• rocksdb-perf-context-level : Sets the level of information to capture via the perf context plugins.
• rocksdb-rate-limiter-bytes-per-sec : Controls the rate at which RocksDB is allowed to write to media via memtable flushes and compaction.
• 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-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 the RocksDB WAL is allow to grow to. When this size is exceeded rocksdb attempts to flush sufficient memtables to allow for the deletion of the oldest log.
• rocksdb_wal_ttl_seconds : No WAL file older than this value should exist.

InnoDB Server Variables

Variable Name	Description	Scope	Dynamic	Type	Default	Possible values
INNODB_AIO_OLD_USECS	AIO requests are scheduled in file offset order until they are this old.	GLOBAL	DYNAMIC	ULONG	500000	100000 - 5000000
INNODB_AIO_OUTSTANDING_REQUESTS	Maximum number of outstanding AIO requests. Stall aio requests submission ifthis is reached.	GLOBAL	DYNAMIC	ULONG	256	0
INNODB_AIO_SLOW_USECS	Count file read, write and fsync requests as slow that take this long to service. Slow requests are also reported in SHOW STATUS, SHOW INNODB STATUS and table stats.	GLOBAL	DYNAMIC	ULONG	500000	100000 - 5000000
INNODB_ALLOW_IBUF_MERGES	Allow insert buffer merges (for testing)	GLOBAL	DYNAMIC	BOOL	true
INNODB_BUFFER_POOL_CHUNK_SIZE	Unit size for memory allocation for buffer pool. 0 means not to use chunk allocation and buffer pool resize will be disabled. This is used to avoid memory copying when resizing buffer pool.	GLOBAL	READONLY	ULONG	0	0 - LONG_MAX (block size 1024 * 1024)
INNODB_BUFFER_POOL_DUMP_PCT	Dump only the hottest N% of each buffer pool, defaults to 100	GLOBAL	DYNAMIC	ULONG	100	1 - 100
INNODB_BUFFER_POOL_POPULATE	Preallocate (pre-fault) the page frames required for the mapping established by the buffer pool memory region. Disabled by default.	GLOBAL	READONLY	BOOL	false
INNODB_BUFFER_POOL_RESIZING_TIMEOUT	Buffer pool resizing timeout in seconds.	GLOBAL	DYNAMIC	ULONG	10	10 - 60 * 60 * 24
INNODB_BUILD_PREV_VERSION_SLEEP	Sleep this many milliseconds in row_vers_build_for_consistent_read.	GLOBAL	DYNAMIC	UINT	0	0 - 60 * 1000
INNODB_DEADLOCK_DETECT	Enableds deadlock detection checking.	GLOBAL	DYNAMIC	BOOL	true
INNODB_DEFRAGMENT	Enable/disable InnoDB defragmentation. When set to FALSE, all existing defragmentation will continue, and new defragmentation command will fail.	GLOBAL	DYNAMIC	BOOL	true
INNODB_DEFRAGMENT_FILL_FACTOR	A number between [0.7, 1] that tells defragmentation how full it should fill a page. Default is 0.9. Number below 0.7 won't make much sense.This variable, together with innodb_defragment_fill_factor_n_recs, is introduced so defragmentation won't pack the page too full and cause page split on the next insert on every page. The variable indicating more defragmentation gain is the one effective.	GLOBAL	DYNAMIC	DOUBLE	0.9	0.7 - 1
INNODB_DEFRAGMENT_FILL_FACTOR_N_RECS	How many records of space defragmentation should leave on the page. This variable, together with innodb_defragment_fill_factor, is introduced so defragmentation won't pack the page too full and cause page split on the next insert on every page. The variable indicating more defragmentation gain is the one effective.	GLOBAL	DYNAMIC	UINT	20	1 - 100
INNODB_DEFRAGMENT_FREQUENCY	Do not defragment a single index more than this number of time per second.This controls the number of time defragmentation thread can request X_LOCK on an index. Defragmentation thread will check whether 1/defragment_frequency (s) has passed since it worked on this index last time, and put the index back to the queue if not enough time has passed. The actual frequency can only be lower than this given number.	GLOBAL	DYNAMIC	UINT	100	1 - 1000000
INNODB_DEFRAGMENT_MAX_RUNTIME_PCT	Do not defragment a single index more than this pct of clock time. This controls the amount of clock time defrag will consume while starving IO to the index under processing. This is achieved by leaving enough gap between scheduling of defrag to maintain the runtime percentage	GLOBAL	DYNAMIC	UINT	50	0 - 100
INNODB_DEFRAGMENT_N_PAGES	Number of pages considered at once when merging multiple pages to defragment	GLOBAL	DYNAMIC	UINT	7	2 - 32
INNODB_DEFRAGMENT_PAUSE	Pause InnoDB defragmentation. When set to TRUE, all existing defragmentation will be paused, and new defragmentation will queue up.Paused defragmentation commands will resume when this variable is set to FALSE again.	GLOBAL	DYNAMIC	BOOL	FALSE
INNODB_DEFRAGMENT_STATS_ACCURACY	How many defragment stats changes there are before the stats are written to persistent storage. Set to 0 meaning disable defragment stats tracking.	GLOBAL	DYNAMIC	UINT	0	0 - UINT_MAX
INNODB_DOUBLEWRITE	0=Disable InnoDB doublewrite buffer.1=Enable full doublewrite mode(default).2=Enable reduced doublewrite mode.	GLOBAL	DYNAMIC	ULONG	1	0 - 2
INNODB_DUMP_CORE_WITHOUT_LARGE_MEM_BUF	Dump core without large memory buffer. Default value is TRUE. Disable with --skip-innodb-dump-core-without-large-mem-buf.	GLOBAL	READONLY	BOOL	true
INNODB_ENABLE_SLAVE_UPDATE_TABLE_STATS	If false, the replication slave thread will not do table stats updates. By default it is set to false	GLOBAL	DYNAMIC	BOOL	false
INNODB_EVICTED_PAGES_SAMPLING_RATIO	Sampling ratio of reporting ages of evicted pages.	GLOBAL	DYNAMIC	ULONG	0	0 - 1000000
INNODB_FSYNC_FREQ	The value of this variable determines how often InnoDB calls fsync when creating a new file. Default is to call fsync after every time the buffer (1MB) is written. Setting this value to zero would make InnoDB flush the file before closing it.	GLOBAL	DYNAMIC	ULL	1 << 20	0 - ULONG_MAX (block size 1 << 20)
INNODB_HISTOGRAM_STEP_SIZE_ASYNC_READ	Size of the histogram bins required for tracking async read latencies	GLOBAL	DYNAMIC	STR	"16us"	[double](us|ms|s)
INNODB_HISTOGRAM_STEP_SIZE_ASYNC_WRITE	Size of the histogram bins required for tracking async write latencies	GLOBAL	DYNAMIC	STR	"16us"	[double](us|ms|s)
INNODB_HISTOGRAM_STEP_SIZE_DOUBLE_WRITE	Size of the histogram bins required for tracking double latencies	GLOBAL	DYNAMIC	STR	"16us"	[double](us|ms|s)
INNODB_HISTOGRAM_STEP_SIZE_FILE_FLUSH_TIME	Size of the histogram bins required for tracking flush times	GLOBAL	DYNAMIC	STR	"16ms"	[double](us|ms|s)
INNODB_HISTOGRAM_STEP_SIZE_FSYNC	Size of the histogram bins required for tracking fsync latencies	GLOBAL	DYNAMIC	STR	"16ms"	[double](us|ms|s)
INNODB_HISTOGRAM_STEP_SIZE_LOG_WRITE	Size of the histogram bins required for tracking log write latencies	GLOBAL	DYNAMIC	STR	"16us"	[double](us|ms|s)
INNODB_HISTOGRAM_STEP_SIZE_SYNC_READ	Size of the histogram bins required for tracking sync read latencies	GLOBAL	DYNAMIC	STR	"16us"	[double](us|ms|s)
INNODB_HISTOGRAM_STEP_SIZE_SYNC_WRITE	Size of the histogram bins required for tracking sync write latencies	GLOBAL	DYNAMIC	STR	"16us"	[double](us|ms|s)
INNODB_IDLE_FLUSH_PCT	Up to what percentage of dirty pages should be flushed when innodb finds it has spare resources to do so.	GLOBAL	DYNAMIC	ULONG	100	0 - 100
INNODB_LOAD_TABLE_THREAD_NUM	thread num of loading table file during crash recovery.	GLOBAL	DYNAMIC	UINT	10	1 - 256
INNODB_LRA_DEBUG	When set to true, the purge thread stops until the logical read ahead sets this variable to TRUE. Used for testing edge cases regarding the purge thread and logical read ahead.	GLOBAL	DYNAMIC	BOOL	false
INNODB_LRA_N_SPACES	Number of spaces a transaction can access before turning off LRA. Every time a transaction switch to a new space (or switching back to a previously accessed one), LRA will start prefetching from beginning of the index from scratch. Switching off LRA if too many spaces are scanned to avoid a possible performance hit.	SESSION	DYNAMIC	ULONG	3	1 - 16
INNODB_LRA_PAGES_BEFORE_SLEEP	This variable defines the number of node pointer records traversed while holding the index lock before releasing the index lock and sleeping for a short period of time so that the other threads get a chance to x-latch the index lock.	SESSION	DYNAMIC	ULONG	1024	128 - ULONG_MAX
INNODB_LRA_SIZE	The size (in MBs) of the total size of the pages that innodb will prefetch while scanning a table during this session. This is meant to be used only for table scans. The upper limit of this variable is 16384 which corresponds to prefetching 16GB of data. When set to max, this algorithm may use 100M memory.	SESSION	DYNAMIC	ULONG	0	0 - 16384
INNODB_LRA_SLEEP	The time LRA sleeps milliseconds before processing the next batch of lra_pages_before_sleep node pointer records.	SESSION	DYNAMIC	ULONG	50	0 - 1000
INNODB_LRU_MANAGER_MAX_SLEEP_TIME	The maximum time limit for a single LRU tail flush iteration by the lru manager thread in miliseconds	GLOBAL	DYNAMIC	ULONG	1000	0 - UINT_MAX32
INNODB_MONITOR_GAPLOCK_QUERY	Write queries that use gap lock for debugging to log file	GLOBAL	DYNAMIC	BOOL	false
INNODB_MONITOR_GAPLOCK_QUERY_FILENAME	File to write out the SQL queries that use gap lock	GLOBAL	DYNAMIC	STR
INNODB_MONITOR_GAPLOCK_QUERY_PRINT_VERBOSE	Print gap lock information along with query	GLOBAL	DYNAMIC	BOOL	false
INNODB_PAGE_CLEANER_ADAPTIVE_SLEEP	Enable adaptive sleep time calculation for page cleaner thread	GLOBAL	DYNAMIC	BOOL	false
INNODB_PREFIX_INDEX_CLUSTER_OPTIMIZATION	Enable prefix optimization to sometimes avoid cluster index lookups.	GLOBAL	DYNAMIC	BOOL	false
INNODB_RECV_IBUF_OPERATIONS	Enables ibuf record merging during crash recovery	GLOBAL	DYNAMIC	BOOL	false
INNODB_SEGMENT_RESERVE_FACTOR	If this value is x, then if the number of unused but reserved pages in a segment is less than reserved pages * x, and there are at least FSEG_FRAG_LIMIT used pages, then we allow a new empty extent to be added to the segment in fseg_alloc_free_page. Otherwise, we use unused pages of the segment.	GLOBAL	DYNAMIC	DOUBLE	0.01	0.0003 - 0.4
INNODB_SIMULATE_COMP_FAILURES	Simulate compression failures.	GLOBAL	DYNAMIC	UINT	0	0 - 99
INNODB_STATS_RECALC_THRESHOLD	When automatically recalculate persistent statistics, the percentage of rows changed in the table that should trigger a recalculation.	GLOBAL	DYNAMIC	DOUBLE	0.1	0.001 - 1
INNODB_SYNC_CHECKPOINT_LIMIT	This is a percentage and is used to override the sync and async checkpoint limits. This is ignored when set to 0. Those limits determine when dirty pages are flushed to enforce fuzzy checkpoint constraints. Making this greater than 80 might reduce the dirty page flush rate. Assume that max_size is the sum of the transaction log file sizes, then the current code sets the sync checkpoint limit to 0.9 * 0.9 * (15/16) of max_size (about 75% of it) and the async limit to 0.9 * 0.9 * (7/8) of max size (about 70% of it). When this is set to a non-zero value, then the sync limit is set to (sync_checkpoint_limit/100) * 0.95 of max_size and the async limit is set to (sync_checkpoint_limit/100) * 0.90 of max_size. The impact of this is that you get the performance benefits of a larger transaction log file without making it larger.	GLOBAL	READONLY	ULONG	0	0 - 97
INNODB_SYNC_POOL_SIZE	The size of the shared sync pool buffer InnoDB uses to store system lockand condition variables.	GLOBAL	READONLY	ULONG	1024	1 - 1024 * 1024
INNODB_TRX_LOG_WRITE_BLOCK_SIZE	Transaction log write block size. Configure this to be the size of the OS file system block size will reduce the extra read during log writes.Set to 0 to disable this feature.	GLOBAL	DYNAMIC	ULONG	4096	0 - 16384
INNODB_TXLOG_INIT_RATE	The value of this variable determines how fast (in bytes/s) InnoDB initializes the transaction log when creating a new fileSetting this value to 0 means no limit. Default is 128MB	GLOBAL	DYNAMIC	ULL	1 << 27	0 - ULONG_MAX (block size 1 << 20)
INNODB_USE_FDATASYNC	Use fdatasync instead of fsync if supported on this platform.	GLOBAL	DYNAMIC	BOOL	false
INNODB_ZLIB_STRATEGY	This parameter determines the strategy to be used by zlib. Possible values are 0 (DEFAULT), 1 (FILTERED), 2 (HUFFMAN_ONLY), 3 (RLE = run length encoding), and 4 (FIXED = no dynamic huffman codes, faster decompression). This value should not be set to something other than 0 except for testing purposes. In the future we may add the ability to set this per table which should be more useful. Changing this dynamically may break xtrabackup and crash recovery.	GLOBAL	DYNAMIC	UINT	0	0 - 4
INNODB_ZLIB_WRAP	When this parameter is OFF, innodb tells zlib to not compute adler32 values for the compressed data by specifying a negative windowBits value for deflateInit2(). This reduces the size of the compressed data and saves CPU. See the documentation for deflateInit2() at http://zlib.net/manual.html for details. Changing this dynamically may break xtrabackup and crash recovery.	GLOBAL	DYNAMIC	BOOL	false

SQL Server Variables

Variable Name	Description	Scope	Dynamic	Type	Default	Possible values
ADMIN_PORT	Port number to use for connections from admin.	GLOBAL	READONLY	ULONG	0	0 - UINT_MAX32
ADMISSION_CONTROL_FILTER	Commands that are skipped in admission control checks. The legal values are: ALTER, BEGIN, COMMIT, CREATE, DELETE, DROP, INSERT, LOAD, SELECT, SET, REPLACE, ROLLBACK, TRUNCATE, UPDATE and empty string	GLOBAL	DYNAMIC	SET	0	"ALTER", "BEGIN", "COMMIT", "CREATE", "DELETE", "DROP", "INSERT", "LOAD", "SELECT", "SET", "REPLACE", "ROLLBACK", "TRUNCATE", "UPDATE"
ALLOW_DOCUMENT_TYPE	Allows document type when parsing queries, creating and altering tables.	GLOBAL	DYNAMIC	BOOL	false
ALLOW_NONCURRENT_DB_RW	Switch to allow/deny reads and writes to a table not in the current database.	SESSION	DYNAMIC	ENUM	"ON"	"ON", "LOG", "LOG_WARN", "OFF"
BINLOG_FILE_BASEDIR	Path to binlog file base directory.	GLOBAL	READONLY	STR
BINLOG_INDEX_BASEDIR	Path to binlog index base directory.	GLOBAL	READONLY	STR
BLOCK_CREATE_MEMORY	Blocks creation of non-temporary Memory tables outside of mysql schema.	GLOBAL	DYNAMIC	BOOL	false
BLOCK_CREATE_MYISAM	Blocks creation of non-temporary MyISAM tables outside of mysql schema.	GLOBAL	DYNAMIC	BOOL	false
BLOCK_CREATE_NO_PRIMARY_KEY	Block creation of non-temp tables without primary key outside of mysqlschema.	GLOBAL	DYNAMIC	BOOL	false
DISABLE_TRIGGER	Disable triggers for the session.	SESSION	DYNAMIC	BOOL	false
DISABLE_WORKING_SET_SIZE	Do not maintain working set size if on	GLOBAL	DYNAMIC	BOOL	false
ENABLE_GTID_MODE_ON_NEW_SLAVE_WITH_OLD_MASTER	This should be used only for testing purposes. This option allows enabling gtid_mode on new slave replicating from a old master which is not gtid compatible	GLOBAL	READONLY	BOOL	false
EXPAND_FAST_INDEX_CREATION	Enable/disable improvements to the InnoDB fast index creation functionality.	SESSION	DYNAMIC	BOOL	false
GAP_LOCK_EXCEPTIONS	List of tables (using regex) that are excluded from gap lock detection.	GLOBAL	DYNAMIC	STR
GAP_LOCK_LOG_FILE	Log file path where queries using Gap Lock are written. gap_lock_write_log needs to be turned on to write logs	GLOBAL	DYNAMIC	STR
GAP_LOCK_RAISE_ERROR	Raising an error when executing queries relying on Gap Lock. Default is false.	SESSION	DYNAMIC	BOOL	false
GAP_LOCK_WRITE_LOG	Writing to gap_lock_log_file when executing queries relying on Gap Lock. Default is false.	SESSION	DYNAMIC	BOOL	false	NULL
GENERAL_QUERY_THROTTLING_LIMIT	Start throttling queries if running threads high.	GLOBAL	DYNAMIC	UINT	0	0 - 10000
GTID_PRECOMMIT	If true, all auto generated gtid will be added into gtid_executed set before flushing binlog from cache to file.	GLOBAL	DYNAMIC	BOOL	false
HAVE_STATEMENT_TIMEOUT	have_statement_timeout	GLOBAL	READONLY
HISTOGRAM_STEP_SIZE_BINLOG_FSYNC	Step size of the Histogram which is used to track binlog fsync latencies.	GLOBAL	DYNAMIC	STR	"16ms"	[double](us|ms|s)
HISTOGRAM_STEP_SIZE_BINLOG_GROUP_COMMIT	Step size of the histogram used in tracking number of threads involved in the binlog group commit	GLOBAL	DYNAMIC	UINT	1	1 - 1024
HISTOGRAM_STEP_SIZE_CONNECTION_CREATE	Step size of the Histogram which is used to track connection create latencies.	GLOBAL	DYNAMIC	STR	"16ms"	[double](us|ms|s)
HISTOGRAM_STEP_SIZE_DDL_COMMAND	Step size of the Histogram which is used to track DDL command latencies.	GLOBAL	DYNAMIC	STR	"64ms"	[double](us|ms|s)
HISTOGRAM_STEP_SIZE_DELETE_COMMAND	Step size of the Histogram which is used to track delete command latencies.	GLOBAL	DYNAMIC	STR	"64us"	[double](us|ms|s)
HISTOGRAM_STEP_SIZE_HANDLER_COMMAND	Step size of the Histogram which is used to track handler command latencies.	GLOBAL	DYNAMIC	STR	"16ms"	[double](us|ms|s)
HISTOGRAM_STEP_SIZE_INSERT_COMMAND	Step size of the Histogram which is used to track insert command latencies.	GLOBAL	DYNAMIC	STR	"128us"	[double](us|ms|s)
HISTOGRAM_STEP_SIZE_OTHER_COMMAND	Step size of the Histogram which is used to track other command latencies.	GLOBAL	DYNAMIC	STR	"16ms"	[double](us|ms|s)
HISTOGRAM_STEP_SIZE_SELECT_COMMAND	Step size of the Histogram which is used to track select command latencies.	GLOBAL	DYNAMIC	STR	"128us"	[double](us|ms|s)
HISTOGRAM_STEP_SIZE_TRANSACTION_COMMAND	Step size of the Histogram which is used to track transaction command latencies.	GLOBAL	DYNAMIC	STR	"16ms"	[double](us|ms|s)
HISTOGRAM_STEP_SIZE_UPDATE_COMMAND	Step size of the Histogram which is used to track update command latencies.	GLOBAL	DYNAMIC	STR	"16ms"	[double](us|ms|s)
HLL_DATA_SIZE_LOG2	This argument is used to generate the hashtable. Increasing the data_size will increase the accuracy while consuming more memory. A value of k will imply a standard error of roughly (104/sqrt(2^k)) percent, and use O(2^k) memory.	SESSION	DYNAMIC	UINT	14	1 - 32
LOG_COLUMN_NAMES	Writes column name information in table map log events.	GLOBAL	DYNAMIC	BOOL	false
LOG_DATAGRAM	Enable logging queries to a unix local datagram socket	GLOBAL	DYNAMIC	BOOL	false
LOG_DATAGRAM_USECS	Log queries longer than log-datagram-usecs to a unix local datagram socket	GLOBAL	DYNAMIC	ULONG	0	0 - ULONG_MAX
LOG_GLOBAL_VAR_CHANGES	All the value changes of global variables will be logged into server log when this is set to TRUE.	GLOBAL	DYNAMIC	BOOL	false
LOG_GTID_UNSAFE_STATEMENTS	When turned on, logs the gtid unsafe statements in the error log	GLOBAL	DYNAMIC	BOOL	true
LOG_ONLY_QUERY_COMMENTS	Writes only the comments part at the beginning of the query in Rows_query_log_events.	GLOBAL	DYNAMIC	BOOL	true
MAX_RUNNING_QUERIES	The maximum number of running queries allowed for a database. If this value is 0, no such limits are applied.	GLOBAL	DYNAMIC	ULONG	0	0 - 100000
MAX_STATEMENT_TIME	Kill SELECT statement that takes over the specified number of milliseconds	SESSION	DYNAMIC	ULONG	0	0 - ULONG_MAX
MAX_WAITING_QUERIES	The maximum number of waiting queries allowed for a database.If this value is 0, no such limits are applied.	GLOBAL	DYNAMIC	ULONG	0	100000
NET_COMPRESSION_LEVEL	Compression level for compressed master/slave protocol (when enabled) and client connections (when requested). 0 is no compression (for testing), 1 is fastest, 9 is slowest, 6 is default.	GLOBAL	DYNAMIC	UINT	6	0 - 9
PART_SCAN_MAX	The optimizer will scan up to this many partitions for data to estimate rows before resorting to a rough approximation based on the data gathered up to that point.	SESSION	DYNAMIC	UINT	10	1 - UINT_MAX32
PEAK_LAG_SAMPLE_RATE	The rate of sampling replayed events on slave to determine the peak replication lag over some period.	GLOBAL	DYNAMIC	ULONG	100	1 - ULONG_MAX
PEAK_LAG_TIME	The time frame peak lag is measured within, in seconds.	GLOBAL	DYNAMIC	ULONG	60	1 - ULONG_MAX
PER_USER_SESSION_VAR_DEFAULT_VAL	Per user session variable default value	GLOBAL	DYNAMIC	STR	""	eg. "user1:user2:var1=var2"
PROCESS_CAN_DISABLE_BIN_LOG	Allow PROCESS to disable bin log, not just SUPER	GLOBAL	DYNAMIC	BOOL	true
PROTOCOL_MODE	Syntax: protocol-mode=mode. See the manual for the complete list of valid protocol modes	SESSION	DYNAMIC	ENUM	""	"", "MINIMAL_OBJECT_NAMES_IN_RESULT_SET_METADATA"
RANGE_OPTIMIZER_MAX_MEM_SIZE	Maximum amount of memory used by the range optimizer to allocate predicates during range analysis. The larger the number, more memory may be consumed during range analysis. If the value is too low to completed range optimization of a query, index range scan will not be considered for this query. A value of 0 means range optimizer does not have any cap on memory.	SESSION	DYNAMIC	ULONG	0 - ULONG_MAX	1536000
RBR_IDEMPOTENT_TABLES	slave_exec_mode is set to IDEMPOTENT for these list of tables. The table names are assumed to be separated by commas. Note this will take effect only after restarting slave sql thread.	GLOBAL	DYNAMIC	STR		"table1[,table2[...]]"
READ_ONLY_ERROR_MSG_EXTRA	Set this variable to print out extra error information, which will be appended to read_only error messages.	GLOBAL	DYNAMIC	STR	""
READ_ONLY_SLAVE	Blocks disabling read_only if the server is a slave. This is helpful in asserting that read_only is never disabled on a slave. Slave with read_only=0 may generate new GTID on its own breaking replication or may cause a split brain.	GLOBAL	DYNAMIC	BOOL	true
RESET_SECONDS_BEHIND_MASTER	When TRUE reset Seconds_Behind_Master to 0 when SQL thread catches up to the IO thread. This is the original behavior but also causes reported lag to flip-flop between 0 and the real lag when the IO thread is the bottleneck.	GLOBAL	DYNAMIC	BOOL	true
RPL_EVENT_BUFFER_SIZE	The size of the preallocated event buffer for slave connections that avoids calls to malloc & free for events smaller than this.	GLOBAL	DYNAMIC	ULONG	1024 * 1024	16 * 1024 - 128 * 1024 * 1024 (block size 1024)
RPL_READ_SIZE	The size for reads done from the binlog and relay log.	GLOBAL	DYNAMIC	ULONG	4096 * 2	4096 * 2 - ULONG_MAX (block size 4096)
RPL_RECEIVE_BUFFER_SIZE	The size of input buffer for the socket used during receving events from a master.	GLOBAL	DYNAMIC	UINT	2 * 1024 * 1024	1024 - UINT_MAX (block size 1024)
RPL_SEND_BUFFER_SIZE	The size of output buffer for the socket used during sending events to a slave.	GLOBAL	DYNAMIC	UINT	2 * 1024 * 1024	1024 - UINT_MAX (block size 1024)
SELECT_INTO_FILE_FSYNC_SIZE	Do an fsync to disk when the buffer grows by these many bytes for SELECT INTO OUTFILE	SESSION	DYNAMIC	ULONG	0	0 - ULONG_MAX (block size 1024)
SELECT_INTO_FILE_FSYNC_TIMEOUT	The timeout/sleep in milliseconds after each fsync with SELECT INTO OUTFILE	SESSION	DYNAMIC	UINT	0	0 - UINT_MAX
SEND_ERROR_BEFORE_CLOSING_TIMED_OUT_CONNECTION	Send error before closing connections due to timeout.	GLOBAL	DYNAMIC	BOOL	true
SESSION_TRACK_GTIDS	Controls the amount of global transaction ids to be included in the response packet sent by the server.(Default: OFF).	SESSION	DYNAMIC	ENUM	"OFF"	"OFF", "OWN_GTID"
SLAVE_GTID_INFO	Whether SQL threads update mysql.slave_gtid_info table. If this value is OPTIMIZED, updating the table is done inside storage engines to avoid MySQL layer's performance overhead	GLOBAL	DYNAMIC	ENUM	"ON"	"OFF", "ON", "OPTIMIZED"
SLAVE_RUN_TRIGGERS_FOR_RBR	Modes for how triggers in row-base replication on slave side will be executed. Legal values are NO (default), YES and LOGGING. NO means that trigger for RBR will not be running on slave. YES and LOGGING means that triggers will be running on slave, if there was not triggers running on the master for the statement. LOGGING also means results of that the executed triggers work will be written to the binlog.	GLOBAL	DYNAMIC	ENUM	"NO"	"NO", "YES", "LOGGING"
SLOW_LOG_IF_ROWS_EXAMINED_EXCEED	Log queries that examine more than slow_log_if_rows_examined_exceed rows to file.	SESSION	DYNAMIC	ULONG	0	0 - ULONG_MAX
SOCKET_UMASK	Socket umask	GLOBAL	READONLY	STR	"0"
SQL_ASYNC_COMMIT	sql_async_commit	SESSION	DYNAMIC	BOOL	false
SQL_LOG_BIN_TRIGGERS	The row changes generated by execution of triggers are not logged inbinlog if this option is FALSE. Default is TRUE.	SESSION	DYNAMIC	BOOL	true
SUPER_READ_ONLY	Enable read_only, and also block writes by users with the SUPER privilege	GLOBAL	DYNAMIC	BOOL	false
TMP_TABLE_MAX_FILE_SIZE	The max size of a file to use for a temporary table. Raise an error when this is exceeded. 0 means no limit.	SESSION	DYNAMIC	ULL	0	0 - ULONGLONG_MAX
TMP_TABLE_RPL_MAX_FILE_SIZE	The max size of a file to use for a temporary table for replication threads. Raise an error when this is exceeded. 0 means no limit.	GLOBAL	DYNAMIC	ULL	0	0 - ULONGLONG_MAX
UNIQUE_CHECK_LAG_RESET_THRESHOLD	Stop enabling skip_unique_check when lag drops below this threshold.	GLOBAL	DYNAMIC	UINT	2	1 - UINT_MAX
UNIQUE_CHECK_LAG_THRESHOLD	Automatically enable skip_unique_check when lag exceeds this threshold (0 [default] to disable, only affects RocksDB).	GLOBAL	DYNAMIC	UINT	0	0 - UINT_MAX
USE_DB_UUID	If set, MySQL uses database UUID while generating the GTID for a transaction on that database. UUID of a database must be set either while creating the database or using an alter command. If no UUID is associated with a database, server_uuid is used.	GLOBAL	DYNAMIC	BOOL	false
WORKING_DURATION	The period of time for which we want working set size statistics	SESSION	DYNAMIC	ULONG	3600	0 - 1 << 30
WRITE_QUERY_THROTTLING_LIMIT	Start throttling writes if running mutation queries high.	GLOBAL	DYNAMIC	UINT	0	0 - 5000