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-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.
• 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-use-idempotent-for-recovery : Modes for how replication events should be executed during recovery for ROW based binlog format.
• 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.