New mysqld server variables

MySQL/InnoDB Server Variables

The following is a list of parameters which MySQL currently support that were added for Facebook's mysqld:

• admin-port : Sets the TCP/IP port to listen on for admin connections. A value of 0, which is the default, means not to listen on a separate port.
• admission-control-by-trx : TBD
• admission-control-filter : A list of statement types that will be skipped during admission control checks. The default is empty. Can be a comma separated list containing any of the following: ALTER, BEGIN, COMMIT, CREATE, DELETE, DROP, INSERT, LOAD, SELECT, SET, REPLACE, ROLLBACK, TRUNCATE, UPDATE, SHOW, USE
• allow-document-type : Indicates whether the DOCUMENT type is allowed. This is required for Docstore. Default is false (i.e. Docstore is off by default).
• allow-noncurrent-db-rw : Controls whether the system will allow a query to access tables that are not in the current database. Options are ON (default), LOG (log the query), LOG-WARN (LOG the query and send a warning to the client), and OFF (log the query and fail the statement).
• binlog-file-basedir : Path to set as base directory for binlog files.
• binlog-index-basedir : Path to set as base directory for binlog indexes.
• block-create-memory : If enabled, blocks the creation of non-temporary tables in the MEMORY storage engine outside of the 'mysql' database. Default is false.
• block-create-myisam : If enabled, blocks the creation of non-temporary tables in the MyISAM storage engine outside of the 'mysql' database. Default is false.
• block-create-no-primary-key : If enabled, blocks the create of any table without a primary key outside of the 'mysql' database. Default is false.
• disable-trigger : If enabled, disables triggers for the session. Default is false.
• disable-working-set-size : If enabled, disables maintaining the working set size. Default is false.
• enable-gtid-mode-on-new-slave-with-old-master : If enabled, allows a slave to enable GTID mode even if the master does not support it. This mode is designed for testing only. Default is false.
• expand-fast-index-creation : Enables or disables improvements to the InnoDB fast index creation functionality. Default is false.
• filesort-max-file-size : Sets the max size of a file to be used by filesort and raises an error if this is hit.
• gap-lock-exceptions : List of table name regex (e.g. 't1,t2,all-tables.*') for queries that require gaplock do not get logged to the gap-lock-log-file if gap-lock-write-log is enabled.
• gap-lock-log-file : Path to the log file for recording queries that use gaplocks.
• gap-lock-raise-error : Queries that run on InnoDB and require a gaplock return an error instead. Since MyRocks does not support gaplocks, this can help catch client applications that depend on gaplocks.
• gap-lock-write-log : Sets whether to log queries that require gaplocks to the gap-lock-log-file.
• general-query-throttling-limit : Enable throttling of general queries for highly stressed systems. Valid values are 0-10000 and indicate the number of simultaneous queries. 0 indicates no throttling and is the default.
• gtid-precommit : If true, all auto generated GTIDs will be added into the gtid-executed set before flushing the binlog. Default is false.
• have-statement-timeout : Specifies whether statements have timeout values. Options are YES, NO, and DISABLED (default).
• histogram-step-size-binlog-fsync : Step size of the histogram used in tracking binlog fsyncs.
• histogram-step-size-binlog-group-commit : Step size of the histogram used in tracking number of threads in binlog group commit.
• histogram-step-size-connection-create : Step size of the histogram used in tracking the number of created connections.
• histogram-step-size-ddl-command : Step size of the histogram used in tracking the number of DDL commands.
• histogram-step-size-delete-command : Step size of the histogram used in tracking the number of delete commands.
• histogram-step-size-handler-command : Step size of the histogram used in tracking the number of handler commands.
• histogram-step-size-insert-command : Step size of the histogram used in tracking the number of insert commands.
• histogram-step-size-other-command : Step size of the histogram used in tracking the number of other commands.
• histogram-step-size-select-command : Step size of the histogram used in tracking the number of select commands.
• histogram-step-size-transaction-command : Step size of the histogram used in tracking the number of transaction commands.
• histogram-step-size-update-command : Step size of the histogram used in tracking the number of update commands.
• hll-data-size-log2 : Specify the size of the hashtable. This argument (which ranges from 1 to 32) causes the hashtable to use 2^ bytes of memory. The default is 14.
• innodb-aio-old-usecs : AIO requests are scheduled in file offset order until they are this old (in microseconds). Values can range from 100000 to 5000000 and the default is 500000.
• innodb-aio-outstanding-requests : The maximum number of outstanding AIO requests. If this number is reached, new ones are stalled until space is available. Values can range from 0 to 1024 and the default is 256.
• innodb-aio-slow-usecs : File read, write, and fsync requests that take this long (in microseconds) are marked as 'slow'. Slow requests are also reported in SHOW STATUS, SHOW INNODB STATUS and table stats. Values can range from 100000 to 5000000 and the default is 500000.
• innodb-allow-ibuf-merges : When enabled (default) this allows insert buffer merges. This option is designed for testing and cannot be disabled on a release build.
• innodb-buffer-pool-chunk-size : Specifies the unit size for memory allocations (in bytes) for the buffer pool. A value of 0 indicates not to use chunk allocation and to disable buffer pool resizing. The default values is 1Mb.
• innodb-buffer-pool-dump-pct : Dumps the hottest N% percent of each buffer pool. Feature from 5.7.
• innodb-buffer-pool-populate : If enabled, preallocate (pre-fault) the page frames required for the mapping established by the buffer pool memory region. Default is false.
• innodb-buffer-pool-resizing-timeout : Specifies a timeout for the buffer pool resizing operation (in seconds) that could be hit waiting for transactions. Values range from 10 to 86400 (1 day) and the default is 10.
• innodb-build-prev-version-sleep : Sleep for this many seconds in row-vers-build-for-consistent-read(). This is a testing/debugging option and does not exist in the release build. Values range from 0 to 6000 and the default is 0.
• innodb-deadlock-detect : Enables/disables deadlock detection checking. Default is true (enabled).
• innodb-defragment : Enables/disables defragmentation. Disabling it does not stop any currently running defragmentation operations, but does block new ones from starting. Default is true (enabled).
• innodb-defragment-fill-factor : A real number from 0.7 to 1.0 (default 0.9) that indicates how full to leave each page during defragmentation. This variable works along with innodb-defragment-fill-factor-n-recs and which ever of the two generates more defragmentation gain is applied.
• innodb-defragment-fill-factor-n-recs : An integer from 1 to 100 (default 20) that indicates how many records of space to leave free per page during defragmentation. This variable works along with innodb-defragment-fill-factor and which ever of the two generates more defragmentation gain is applied.
• innodb-defragment-frequency : Specifies how often the defragmentation thread can request the X-LOCK on an index. The value is an integer from 1 to 1000000 (default 100) and specifies the number of times per second. The defragmentation thread checks to see if 1/N seconds have elapsed since it last worked on this index.
• innodb-defragment-max-runtime-pct : Specifies the maximum amount of the CPU (as percentage) that the defragment thread will use in order to avoid starving IO threads of CPU. The value can range from 0 to 100 and the default is 50.
• innodb-defragment-n-pages : Specifies the number of pages to consider at the same time during a defragment operation. The value can range from 2 to 32 and the default is 7.
• innodb-defragment-pause : Setting this to true will pause any running defragmentation operations and queue up any new ones. Setting this to false will resume any running defragmentation operations. Default is false.
• innodb-defragment-stats-accuracy : Specifies how many defragment statistics changes are allowed before the statistics are written to persistent storage. A value of 0 (the default) disables defragment statistics tracking.
• innodb-dump-core-without-large-mem-buf : Drop the buffer pool when performing a coredump to reduce the size of the corefile.
• innodb-enable-slave-update-table-stats : Records table stats performed by slave threads.
• innodb-evicted-pages-sampling-ratio : Records the age of the evicted page according to this sampling ratio.
• innodb-force-index-records-in-range : When force index is used, overrides the number of records returned by records-in-range() with this value.
• innodb-fsync-freq : Invoke fsync each time this number of bytes have been written to a file when a new file is being created.
• innodb-histogram-step-size-async-read : Size of histogram bins for tracking async reads.
• innodb-histogram-step-size-async-write : Size of the histogram bins for tracking async writes.
• innodb-histogram-step-size-double-write : Size of the histogram bins for tracking double writes.
• innodb-histogram-step-size-file-flush-time : Size of the histogram bins for tracking file flush latency.
• innodb-histogram-step-size-fsync : Size of the histogram bins for tracking fsync latency.
• innodb-histogram-step-size-log-write : Size of the histogram bins for tracking transaction log writes.
• innodb-histogram-step-size-sync-read : Size of the histogram bins for tracking synchronous reads.
• innodb-histogram-step-size-sync-write : Size of the histogram bins for tracking synchronous writes.
• innodb-idle-flush-pct : Percent of buffer pool which should be flushed when InnoDB has the resources for it.
• innodb-load-table-thread : Number of threads to load tables during crash recovery.
• innodb-lra-debug : Debug only test variable.
• innodb-lra-n-spaces : Number of tablespaces a client can access before logical read ahead is disabled for them. Otherwise, logical read ahead starts when a client access a table.
• innodb-lra-pages-before-sleep : Defines the number of node pointer records traversed before sleeping this thread for a short time to allow other threads to access the index lock.
• innodb-lra-size : Size in MB of data to prefetch by the logical read ahead.
• innodb-lra-sleep : Time to sleep before processing the next batch of lra pages.
• innodb-lru-manager-max-sleep-time : Maximum time limit in milliseconds for a single LRU tail flush iteration.
• innodb-monitor-gaplock-query : Write queries that use gap lock for debugging to log file.
• innodb-monitor-gaplock-query-filename : File to write out the SQL queries that use gap lock.
• innodb-monitor-gaplock-query-print-verbose : Print gap lock information along with query.
• innodb-page-cleaner-adaptive-sleep : Enable adaptive sleep time calculation for page cleaner thread.
• innodb-prefix-index-cluster-optimization : Enable prefix optimization to sometimes avoid cluster index lookups.
• innodb-recv-ibuf-operations : Enables ibuf record merging during crash recovery.
• innodb-segment-reserve-factor : If the number of unused, but reserved pages in a segment is below this value * reserved pages, then allocate a new empty extent to the segment.
• innodb-simulate-comp-failures : Debug testing only, simulate compression failures.
• innodb-stats-recalc-threshold : Percentage of rows changed in a table to trigger persistent statistics recalculation.
• innodb-sync-checkpoint-limit : Percentage used to override the sync and async checkpoint limits and increase the % of dirty buffers in the buffer pool.
• innodb-sync-pool-size : The size of the shared sync pool buffer InnoDB uses to store system lock and condition variables.
• 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.
• innodb-txlog-init-rate : Determine how fast (bytes/sec) InnoDB initializes a new transaction log.
• innodb-use-fdatasync : Use fdatasync instead of fsync if supported.
• innodb-zlib-strategy : Debug/Test only to determine zlib strategy.
• innodb-zlib-wrap : Tells zlib not to compute adler32 values. Changing dynamically may cause crash recovery failures.
• legacy-user-name-pattern : Regex pattern string of a legacy user name.
• log-column-names : Writes column name information in table map log events.
• log-datagram : Enable logging queries to a unix local datagram socket. Alternative to slow query log.
• log-datagram-usecs : Log queries longer than log-datagram-usecs to a unix local datagram socket.
• log-global-var-changes : Log all global variable changes to the error log.
• log-gtid-unsafe-statements : Log all unsafe gtid statements to the error log.
• log-legacy-user : Log legacy user names in slow query log.
• log-only-query-comments : Writes only the comments part at the beginning of the query in Rows-query-log-events.
• log-throttle-legacy-user : Log at most this many 'LEGACY-USER' entries per minute to the slow query log.
• max-nonsuper-connections : The maximum number of total active connections for non-super user (0 = no limit).
• max-running-queries : The maximum number of running queries allowed for a database (0 = no limit).
• max-statement-time : Maximum execution time for a statement.
• max-waiting-queries : Maximum number of waiting queries allowed for a database (0 = no limit).
• mts-dependency-replication : Use RBR's row events to find out write-write conflicts (dependencies) between transactions and schedule them to the slave workers. slave_use_idempotent_for_recovery must be enabled for to switch this on.
• mts-dependency-size : Max number of transactions whose dependencies are stored in memory.
• mts-dependency-refill-threshold : Capacity in percentage at which to start refilling the dependency buffer.
• mts-dependency-order-commits : Commit trxs in the same order as the master (per database).
• mts-dynamic-rebalance : Shuffle DB's within workers periodically for load balancing.
• mts-imbalance-threshold : Percent of load on a worker to trigger worker thread rebalancing.
• net-compression-level : Compression level for compressed master/slave protocol (when enabled) and client connections (when requested).
• num-conn-handling-threads : Use these many threads to offload accept threads.
• num-sharded-listen-sockets : Use more than 1 socket to listen on the same mysqld port.
• num-sharded-locks : How many shards to use to reduce lock-contention.
• 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.
• peak-lag-sample-rate : The rate of sampling replayed events on slave to determine the peak replication lag over some period.
• peak-lag-time : The time frame peak lag is measured within, in seconds.
• per-user-session-var-default-val : Per user session variable default value.
• per-user-session-var-user-name-delimiter : Per user session variable user name delimiter.
• process-can-disable-bin-log : Allow PROCESS to disable bin log, not just SUPER.
• protocol-mode : Allows reducing the amount of metadata returned in the protocol.
• range-optimizer-max-mem-size : Controls the max memory for range optimizer. Feature from 5.7.
• rbr-idempotent-tables : List of tables to allow row-based replication to treat as idempotent.
• read-only-error-msg-extra : Set this variable to print out extra error information, which will be appended to read-only error messages.
• read-only-slave : Blocks disabling read-only if the server is a slave.
• 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.
• 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.
• rpl-read-size : The size for reads done from the binlog and relay log.
• rpl-receive-buffer-size : The size of input buffer for the socket used during receving events from a master.
• rpl-semi-sync-master-histogram-trx-wait-step-size : Histogram step size for transaction wait time.
• rpl-send-buffer-size : The size of output buffer for the socket used during sending events to a slave.
• rpl-skip-tx-api : Use write batches for replication thread instead of tx api.
• rpl-wait-for-semi-sync-ack : Wait for events to be acked by a semi-sync slave before sending them to the async slaves.
• select-into-file-fsync-size : Triggers fsync when running SELECT INTO OUTFILE for this number of data bytes.
• select-into-file-fsync-timeout : Timeout/sleep in milliseconds after each fsync with SELECT INTO OUTFILE.
• send-error-before-closing-timed-out-connection : Send error before closing connections due to timeout.
• separate-conn-handling-thread : Use a separate thread from the accept thread to offload connection handling.
• session-track-gtids : Controls the amount of global transaction ids to be included in a response packet. Feature from 5.7.
• slave-gtid-info : Whether SQL threads update mysql.slave-gtid-info table.
• 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.
• slave-tx-isolation : Slave thread's transaction isolation level. Just like tx_isolation but for slave worker threads.
• slave-use-idempotent-for-recovery : Uses RBR's idempotent mode during recovery to replay transactions instead of tracking holes in the GTID sequence. Note that binlog format must be ROW and GTIDs should be enabled for this option to have effect.
• slow-log-if-rows-examined-exceed : Log queries that examine more than slow-log-if-rows-examined-exceed rows to file.
• socket-umask : Socket umask for the unix socket established by mysqld.
• sql-log-bin-triggers : Record row changes generated by execution of triggers in binlog.
• ssl : Enable SSL for connection.
• super-read-only : Enable read-only, and also block writes by users with the SUPER privilege.
• tmp-table-max-file-size : The max size of a file to use for a temporary table and raise an error when exceeded.
• tmp-table-rpl-max-file-size : The max size of a file to use for a temporary table created by a replication thread and raise an error when exceeded.
• unique-check-lag-reset-threshold : Stop enabling skip-unique-check when lag drops below this threshold.
• unique-check-lag-threshold : Automatically enable skip-unique-check when lag exceeds this threshold.
• use-db-uuid : Set to use the database UUID for GTID instead of server-uuid to allow per database shard GTID generation.
• use-fbson-input-format : Uses FBSON format as document type input. This session value is set to true for SQL threads when inserting document fields received through row based replication.
• use-fbson-output-format : Uses FBSON format as document type output.
• use-lock-sharding : Use sharding to reduce contention on certain high contention locks.
• use-socket-sharding : Use multiple listen sockets on the same mysqld port.
• working-duration : The period of time for which we want working set size statistics.
• write-query-throttling-limit : Enable throttling of write queries for highly stressed systems. Valid values are 0-5000 and indicate the number of simultaneous queries. 0 indicates no throttling and is the default.

Server Parameters

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
MTS_DEPENDENCY_REPLICATION	Use dependency based replication using RBR's row events to detect write-write conflicts (dependencies). `slave_use_idempotent_for_recovery` must be enabled for to switch this on.	GLOBAL	DYNAMIC	BOOL	false
MTS_DEPENDENCY_SIZE	Max size of the dependency buffer. Max number of transactions whose dependencies are stored in memory	GLOBAL	DYNAMIC	ULONGLONG	1000	0 - ULONGLONG_MAX
MTS_DEPENDENCY_REFILL_THRESHOLD	Capacity in percentage at which to start refilling the dependency buffer	GLOBAL	DYNAMIC	DOUBLE	60	0 - 100
MTS_DEPENDENCY_ORDER_COMMITS	Commit trxs in the same order as the master (per database)	GLOBAL	DYNAMIC	BOOL	false
MTS_DYNAMIC_REBALANCE	Shuffle DB's within workers periodically for load balancing	GLOBAL	DYNAMIC	BOOL	false
MTS_IMBALANCE_THRESHOLD	Threshold to trigger worker thread rebalancing. This parameter denotes the percent load on the most loaded worker.	GLOBAL	DYNAMIC	DOUBLE	90	0 - 100
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)
RPL_SKIP_TX_API	Use write batches for replication thread instead of tx api	GLOBAL	DYNAMIC	BOOL	false
RPL_WAIT_FOR_SEMI_SYNC_ACK	Wait for events to be acked by a semi-sync slave before sending them to the async slaves	GLOBAL	DYNAMIC	BOOL	false
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"
SLAVE_TX_ISOLATION	Slave thread transaction isolation level. Just like `tx_isolation` but for slave worker threads.	GLOBAL	DYNAMIC	ENUM	"REPEATABLE-READ"	"READ-UNCOMMITTED", "READ-COMMITTED", "REPEATABLE-READ", "SERIALIZABLE"
SLAVE_USE_IDEMPOTENT_FOR_RECOVERY	Uses RBR's idempotent mode during recovery to replay transactions instead of tracking holes in the GTID sequence. Note that binlog format must be ROW and GTIDs should be enabled for this option to have effect.	GLOBAL	DYNAMIC	ENUM	"NO"	"NO", "YES"
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