This topic lists all MySQL 8.0 parameters available as of the publication date.
The parameter descriptions are from the official MySQL community.
|
Parameter name |
Major version |
Is dynamic parameter |
Default value |
Value range |
Minor version range |
Description |
Community link |
|
automatic_sp_privileges |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
When this variable is set to 1 (the default), the server automatically grants the EXECUTE and ALTER ROUTINE permissions to the user who created a stored procedure if that user cannot execute, alter, or drop it. The ALTER ROUTINE permission is required to drop a procedure. When the stored procedure is dropped, the server also automatically revokes these permissions from the creator. If automatic_sp_privileges is 0, the server does not automatically grant or revoke these permissions. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_automatic_sp_privileges |
|
auto_increment_increment |
8.0 |
1 |
1 |
[1-65535] |
Unlimited |
The increment value for auto-increment columns. |
|
|
auto_increment_offset |
8.0 |
1 |
1 |
[1-65535] |
Unlimited |
The offset value applied to auto-increment columns when the auto-increment step size (auto-increment-increment) is not equal to 1. |
https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_auto_increment_offset |
|
avoid_temporal_upgrade |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
This variable controls whether ALTER TABLE implicitly upgrades temporal columns in a pre-5.6.4 format. These columns include TIME, DATETIME, and TIMESTAMP columns that do not support fractional seconds precision. Upgrading such columns requires rebuilding the table, which prevents the use of fast alteration methods. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_avoid_temporal_upgrade |
|
back_log |
8.0 |
0 |
3000 |
[0-65535] |
Unlimited |
The number of outstanding connection requests MySQL can have. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_back_log |
|
binlog_cache_size |
8.0 |
1 |
2097152 |
[4096-16777216] |
Unlimited |
The size of the transaction cache for transaction engines that update the binary log. |
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_cache_size |
|
binlog_checksum |
8.0 |
0 |
CRC32 |
[CRC32|NONE] |
Unlimited |
The type of BINLOG_CHECKSUM_ALG. Includes a checksum for log events in the binary log. Possible values are NONE and CRC32. The default value is CRC32. |
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_checksum |
|
binlog_group_commit_sync_delay |
8.0 |
1 |
0 |
[0-1000000] |
Unlimited |
Controls the number of microseconds that a binary log commit waits before synchronizing the binary log file to disk. |
|
|
binlog_group_commit_sync_no_delay_count |
8.0 |
1 |
0 |
[0-100000] |
Unlimited |
The maximum number of transactions to wait for during the current delay specified by binlog_group_commit_sync_delay. If binlog_group_commit_sync_delay is set to 0, this option has no effect. |
|
|
binlog_max_flush_queue_time |
8.0 |
1 |
0 |
[0-100000] |
Unlimited |
binlog_max_flush_queue_time is deprecated and marked for eventual removal in a future MySQL version. Previously, this system variable controlled the time in microseconds to continue reading transactions from the flush queue before performing a group commit. It no longer has any effect. |
|
|
binlog_order_commits |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
When this variable is enabled on the primary server (the default), transactions are externalized in the same order as they are written to the binary log. If disabled, transactions can be committed in parallel. In some cases, disabling this variable can improve performance. |
|
|
binlog_rows_query_log_events |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
The binlog_rows_query_log_events system variable affects only row-based logging. When enabled, it causes the MySQL Server to write informational log events, such as row query log events, to its binary log. This information can be used for debugging and related purposes. For example, you can get the original query issued on the primary server when it cannot be reconstructed from the row updates. |
|
|
binlog_row_image |
8.0 |
1 |
full |
[full|minimal] |
Unlimited |
Specifies whether the binary log saves all columns or only the actually required columns in the binary log image. |
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_binlog_row_image |
|
binlog_stmt_cache_size |
8.0 |
1 |
32768 |
[4096-16777216] |
Unlimited |
The size of the statement cache for non-transactional engines that update the binary log. |
|
|
binlog_transaction_dependency_history_size |
8.0 |
1 |
500000 |
[1-1000000] |
[20210930,99999999) |
The maximum number of rows to keep in the writeset history. |
|
|
binlog_transaction_dependency_tracking |
8.0 |
1 |
WRITESET |
[WRITESET|WRITESET_SESSION|COMMIT_ORDER] |
[20210930,99999999) |
To set the value of binlog_transaction_dependency_tracking to WRITESET or WRITESET_SESSION, you must set transaction_write_set_extraction to specify an algorithm. It cannot be set to OFF. |
|
|
block_encryption_mode |
8.0 |
1 |
"aes-128-ecb" |
["aes-128-ecb"|"aes-192-ecb"|"aes-256-ecb"|"aes-128-cbc"|"aes-192-cbc"|"aes-256-cbc"] |
Unlimited |
This variable controls the block encryption mode for block-based algorithms such as AES. It affects encryption for AES_ENCRYPT() and AES_DECRYPT(). |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_block_encryption_mode |
|
bulk_insert_buffer_size |
8.0 |
1 |
4194304 |
[0-4294967295] |
Unlimited |
MyISAM uses a special tree-like cache to make bulk inserts such as INSERT... SELECT, INSERT... VALUES (...), (...), ..., and LOAD DATA INFILE faster when adding data to non-empty tables. This variable limits the size of the cache tree in bytes per thread. Setting it to 0 disables this optimization. The default value is 8 MB. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_bulk_insert_buffer_size |
|
character_set_filesystem |
8.0 |
1 |
binary |
[utf8|latin1|gbk|binary] |
Unlimited |
The file system character set. This variable is used to interpret string literals that refer to file names, such as in the LOAD DATA INFILE and SELECT...INTO OUTFILE statements, and the LOAD_FILE() function. These file names are converted from character_set_client to character_set_filesystem before the file is opened. The default value is binary, which means no conversion occurs. For systems that allow multi-byte file names, a different value may be more suitable. For example, if the system uses UTF-8 for file names, set character_set_filesystem to 'utf8'. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_character_set_filesystem |
|
character_set_server |
8.0 |
1 |
utf8 |
[utf8|latin1|gbk|gb18030|utf8mb4] |
Unlimited |
The server's default character set. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_character_set_server |
|
completion_type |
8.0 |
1 |
NO_CHAIN |
[NO_CHAIN|CHAIN|RELEASE|0|1|2] |
Unlimited |
The transaction completion type. The variable can be assigned using name values or the corresponding integer values. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_completion_type |
|
concurrent_insert |
8.0 |
1 |
1 |
[0|1|2] |
No limit |
0: Disables concurrent inserts. 1: (Default) Enables concurrent inserts for MyISAM tables that have no holes. 2: Enables concurrent inserts for all MyISAM tables, even those with holes. For tables with holes, new rows are inserted at the end of the table if it is being used by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into a hole. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_concurrent_insert |
|
connect_timeout |
8.0 |
1 |
10 |
[1-3600] |
Unlimited |
The number of seconds the mysqld server waits for a connection packet before responding with a Bad handshake error. The default value is 5 seconds for MySQL 5.1.23 and earlier, and 10 seconds thereafter. If clients frequently encounter errors like "Lost connection to MySQL server at 'XXX', system error: errno", increasing the connect_timeout value might help. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_connect_timeout |
|
cte_max_recursion_depth |
8.0 |
1 |
1000 |
[0-4294967295] |
Unlimited |
The maximum recursion depth for common table expressions (CTEs). The server terminates execution if any CTE has a recursion level that exceeds the value of this variable. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_cte_max_recursion_depth |
|
default_authentication_plugin |
8.0 |
0 |
mysql_native_password |
[mysql_native_password|sha256_password|caching_sha2_password] |
Unlimited |
The default authentication plugin. |
|
|
default_storage_engine |
8.0 |
0 |
InnoDB |
[InnoDB|innodb] |
Unlimited |
The default storage engine for new tables. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_storage_engine |
|
default_time_zone |
8.0 |
0 |
SYSTEM |
[SYSTEM|-12:00|-11:00|-10:00|-9:00|-8:00|-7:00|-6:00|-5:00|-4:00|-3:00|-2:00|-1:00|\+0:00|\+1:00|\+2:00|\+3:00|\+4:00|\+5:00|\+5:30|\+5:45|\+6:00|\+6:30|\+7:00|\+8:00|\+9:00|\+10:00|\+11:00|\+12:00|\+13:00] |
Unlimited |
The default time zone for the database. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
default_week_format |
8.0 |
1 |
0 |
[0-7] |
Unlimited |
The default mode value for the WEEK() function. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_week_format |
|
delayed_insert_limit |
8.0 |
1 |
100 |
[1-4294967295] |
Unlimited |
After inserting delayed_insert_limit delayed rows, the INSERT DELAYED handler thread checks for any pending SELECT statements. If there are any, it allows them to execute before continuing to insert delayed rows. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_delayed_insert_limit |
|
delayed_insert_timeout |
8.0 |
1 |
300 |
[1-3600] |
Unlimited |
The number of seconds an INSERT DELAYED handler thread should wait for INSERT statements before terminating. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_delayed_insert_timeout |
|
delayed_queue_size |
8.0 |
1 |
1000 |
[1-4294967295] |
Unlimited |
This is the limit on the number of rows to queue per table when handling INSERT DELAYED statements. If the queue becomes full, any client that issues an INSERT DELAYED statement must wait until there is space in the queue again. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_delayed_queue_size |
|
delay_key_write |
8.0 |
1 |
ON |
[ON|OFF|ALL] |
Unlimited |
This option applies only to MyISAM tables. It can have one of the following values to affect how the DELAY_KEY_WRITE table option, which can be used in a CREATE TABLE statement, is handled. OFF: The DELAY_KEY_WRITE option is ignored. ON: MySQL honors any DELAY_KEY_WRITE option specified in a CREATE TABLE statement. This is the default value. ALL: All newly opened tables are treated as if they were created with the DELAY_KEY_WRITE option enabled. Important
If you set this variable to ALL, do not use MyISAM tables from other programs, such as another MySQL server or myisamchk. Doing so can cause index corruption. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_delay_key_write |
|
disconnect_on_expired_password |
8.0 |
0 |
ON |
[ON|OFF] |
Unlimited |
This variable controls how the server handles clients with expired passwords. |
|
|
div_precision_increment |
8.0 |
1 |
4 |
[0-30] |
Unlimited |
This variable indicates how many decimal places to increase the result of a division operation performed with the / operator. The default value is 4. The minimum and maximum values are 0 and 30, respectively. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_div_precision_increment |
|
end_markers_in_json |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Whether the optimizer JSON output should add end markers. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_end_markers_in_json |
|
eq_range_index_dive_limit |
8.0 |
1 |
100 |
[0-4294967295] |
Unlimited |
If the number of equality ranges for an index is greater than or equal to this number, the optimizer uses existing index statistics instead of performing an index dive. If set to 0, index dives are always used. |
|
|
event_scheduler |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Enables the event scheduler. Possible values are ON, OFF, and DISABLED. DISABLED completely deactivates the event scheduler, and it cannot be activated at runtime. Note
You cannot modify this parameter for High-availability Edition instances. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_event_scheduler |
|
explicit_defaults_for_timestamp |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Whether the default value for TIMESTAMP columns is the current timestamp. |
|
|
flush_time |
8.0 |
1 |
0 |
[0-31536000] |
Unlimited |
If set to a non-zero value, all tables are closed every flush_time seconds to free up resources and sync unflushed data to disk. This option is best used only on systems with minimal resources. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_flush_time |
|
ft_max_word_len |
8.0 |
0 |
84 |
[10-4294967295] |
Unlimited |
The maximum length of a word to be included in a MyISAM FULLTEXT index. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_ft_max_word_len |
|
ft_min_word_len |
8.0 |
0 |
4 |
[1-3600] |
Unlimited |
The minimum length of a word to be included in a FULLTEXT index. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_ft_min_word_len |
|
ft_query_expansion_limit |
8.0 |
0 |
20 |
[0-1000] |
Unlimited |
The number of top N matches to use when performing a full-text search with WITH QUERY EXPANSION. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_ft_query_expansion_limit |
|
general_log |
8.0 |
0 |
OFF |
[ON|OFF] |
Unlimited |
Whether the general query log is enabled. The log output destination is controlled by the log_output system variable. This feature is not supported on RDS MySQL Basic Edition instances. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_general_log |
|
group_concat_max_len |
8.0 |
1 |
1024 |
[4-1844674407370954752] |
Unlimited |
The maximum result length in bytes allowed for the GROUP_CONCAT() function. The default value is 1024 bytes. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_group_concat_max_len |
|
histogram_generation_max_mem_size |
8.0 |
1 |
20000000 |
[1000000-18446744073709551615] |
Unlimited |
The maximum amount of memory for generating histogram statistics. |
|
|
host_cache_size |
8.0 |
1 |
644 |
[0-65535] |
Unlimited |
The size of the internal host cache. Setting the size to 0 disables the host cache. Changing the cache size at runtime implicitly causes a FLUSH HOSTS operation to clear the host cache and truncate the host_cache table. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_host_cache_size |
|
init_connect |
8.0 |
1 |
'' |
[''|'set names utf8mb4'|'set names utf8'|'set default_collation_for_utf8mb4=utf8mb4_general_ci'|'set default_collation_for_utf8mb4=utf8mb4_general_ci;set names utf8mb4'|'set names utf8mb4 collate utf8mb4_general_ci'|'set names utf8mb4 COLLATE utf8mb4_bin'] |
Unlimited |
Commands to execute for each new connection. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_init_connect |
|
innodb_adaptive_flushing |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
Determines whether to dynamically adjust the rate of flushing dirty pages in the InnoDB buffer pool based on the workload. Dynamically adjusting the flush rate aims to avoid bursts of I/O activity. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_adaptive_flushing |
|
innodb_adaptive_flushing_lwm |
8.0 |
1 |
10 |
[0-70] |
Unlimited |
Defines a low water mark, representing a percentage of the redo log capacity, at which adaptive flushing is enabled. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_adaptive_flushing_lwm |
|
innodb_adaptive_hash_index |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
innodb_adaptive_hash_index is a parameter of the InnoDB storage engine that enables or disables the adaptive hash index. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_adaptive_hash_index |
|
innodb_adaptive_max_sleep_delay |
8.0 |
1 |
150000 |
[1-1000000] |
Unlimited |
innodb_thread_sleep_delay controls how long an InnoDB thread sleeps when there are no waiting events. This parameter allows InnoDB to automatically adjust the value of innodb_thread_sleep_delay based on the current workload to adapt to different load situations. As long as this value is not 0, the automatic dynamic adjustment of the innodb_thread_sleep_delay value is enabled, with the maximum value specified by the innodb_adaptive_max_sleep_delay option. This value represents microseconds. This option can be very useful on busy systems, especially those with more than 16 InnoDB threads. In practice, this option is most valuable on MySQL systems with hundreds or thousands of concurrent connections. |
|
|
innodb_autoextend_increment |
8.0 |
1 |
64 |
[1-1000] |
Unlimited |
The increment size in megabytes for increasing the size of an auto-extending InnoDB system tablespace file when it becomes full. The default value is 64. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_autoextend_increment |
|
innodb_autoinc_lock_mode |
8.0 |
0 |
2 |
[0|1|2] |
Unlimited |
The lock mode used for generating auto-increment values. The allowed values are: 0: traditional 1: consecutive 2: interleaved |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_autoinc_lock_mode |
|
innodb_buffer_pool_dump_at_shutdown |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
Specifies whether to record the pages cached in the InnoDB buffer pool when the MySQL server shuts down to shorten the warm-up process at the next restart. It is typically used with innodb_buffer_pool_load_at_startup. The innodb_buffer_pool_dump_pct option defines the percentage of the most recently used buffer pool pages to be dumped. |
|
|
innodb_buffer_pool_dump_pct |
8.0 |
1 |
25 |
[1-100] |
Unlimited |
Specifies the percentage of the most recently used pages to read and dump for each buffer pool. The value range is 1 to 100. The default value is 25. For example, if there are 4 buffer pools, each with 100 pages, and innodb_buffer_pool_dump_pct is set to 25, the 25 most recently used pages from each buffer pool will be dumped. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_dump_pct |
|
innodb_buffer_pool_instances |
8.0 |
0 |
8 |
[1-64] |
Unlimited |
The number of regions the InnoDB buffer pool is divided into. For systems with multi-gigabyte buffer pools, dividing the buffer pool into separate instances can improve concurrency by reducing contention as different threads read and write to cached pages. Each page stored in or read from the buffer pool is randomly assigned to one of the buffer pool instances using a hash function. Each buffer pool manages its own free lists, flush lists, LRUs, and all other data structures connected to a buffer pool, and is protected by its own buffer pool mutex. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_instances |
|
innodb_buffer_pool_load_at_startup |
8.0 |
0 |
ON |
[ON|OFF] |
Unlimited |
Specifies that the buffer pool is automatically warmed up at MySQL server startup by loading the same pages that were previously saved in the InnoDB buffer pool. It is typically used with innodb_buffer_pool_dump_at_shutdown. |
|
|
innodb_buffer_pool_size |
8.0 |
1 |
{DBInstanceClassMemory*3/4} |
[134217728-18446744073709551615] |
Unlimited |
The size in bytes of the buffer pool, which is the memory area where InnoDB caches table and index data. This parameter can only be set using an expression. Specific numeric values are not supported. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size |
|
innodb_change_buffering |
8.0 |
1 |
all |
[none|inserts|deletes|changes|purges|all] |
Unlimited |
Whether InnoDB performs change buffering, an optimization that delays write operations to secondary indexes so that I/O operations can be performed sequentially. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_change_buffering |
|
innodb_change_buffer_max_size |
8.0 |
1 |
25 |
[0-50] |
Unlimited |
The maximum size of the InnoDB change buffer, as a percentage of the total buffer pool size. You might need to increase this value for a MySQL server with heavy insert, update, and delete activity, or decrease it for a MySQL server with unchanging data used for reporting. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_change_buffer_max_size |
|
innodb_checksum_algorithm |
8.0 |
1 |
crc32 |
[innodb|crc32|none|strict_innodb|strict_crc32|strict_none] |
Unlimited |
Specifies how to generate and verify checksums stored in the disk blocks of InnoDB tablespaces. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_checksum_algorithm |
|
innodb_cmp_per_index_enabled |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Enables per-index compression-related statistics in the INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX table. Because collecting these statistics can be expensive, enable this option only during performance tuning related to InnoDB compressed tables, and only on development, test, or secondary instances. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_cmp_per_index_enabled |
|
innodb_commit_concurrency |
8.0 |
0 |
0 |
[0-1000] |
Unlimited |
The number of threads that can commit at the same time. A value of 0 (the default) allows any number of transactions to commit concurrently. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_commit_concurrency |
|
innodb_compression_failure_threshold_pct |
8.0 |
1 |
5 |
[0-100] |
Unlimited |
Defines the compression failure rate threshold for a table, as a percentage, at which MySQL starts adding padding in compressed pages to avoid expensive compression failures. When this threshold is exceeded, MySQL starts leaving extra free space in each new compressed page, dynamically adjusting the amount of free space up to the percentage of the page size specified by innodb_compression_pad_pct_max. A value of zero disables the mechanism for monitoring compression efficiency and dynamically adjusting the padding amount. |
|
|
innodb_compression_level |
8.0 |
1 |
6 |
[0-9] |
Unlimited |
Specifies the zlib compression level for InnoDB compressed tables and indexes. Higher values let you fit more data onto storage devices but come with more CPU overhead during compression. Lower values can reduce CPU overhead when storage space is not critical or you expect the data is not particularly compressible. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_compression_level |
|
innodb_compression_pad_pct_max |
8.0 |
1 |
50 |
[0-70] |
Unlimited |
Specifies the maximum percentage of free space that can be reserved within each compressed page to provide space for data and modification logs to be reorganized within the page when updating a compressed table or index and the data may need to be recompressed. This applies only when innodb_compression_failure_threshold_pct is set to a non-zero value and the compression failure rate exceeds the cutoff point. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_compression_pad_pct_max |
|
innodb_concurrency_tickets |
8.0 |
1 |
5000 |
[1-4294967295] |
Unlimited |
Determines the number of threads that can enter InnoDB concurrently. A thread is placed in a queue when it tries to enter InnoDB if the number of threads has reached the concurrency limit. When a thread is permitted to enter InnoDB, it is given a number of "tickets" equal to the value of innodb_concurrency_tickets, and the thread can enter and leave InnoDB freely until it has used up its tickets. After that, the thread is again subject to the concurrency check (and possible queuing) the next time it tries to enter InnoDB. The default value is 5000. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_concurrency_tickets |
|
innodb_data_file_purge |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
Whether to enable the asynchronous file deletion mechanism. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
innodb_data_file_purge_interval |
8.0 |
1 |
100 |
[0-10000] |
Unlimited |
The interval for purging files, in milliseconds. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
innodb_data_file_purge_max_size |
8.0 |
1 |
128 |
[16-1073741824] |
Unlimited |
The maximum size (in MB) of a single file to be purged. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
innodb_deadlock_detect |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
This option is used to disable deadlock detection. On high-concurrency systems, deadlock detection can cause a slowdown when numerous threads wait for the same lock. At times, it may be more efficient to disable deadlock detection and rely on the innodb_lock_wait_timeout setting for transaction rollback. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_deadlock_detect |
|
innodb_default_row_format |
8.0 |
1 |
DYNAMIC |
[REDUNDANT|COMPACT|DYNAMIC] |
Unlimited |
The innodb_default_row_format option defines the default row format for InnoDB tables and user-created temporary tables. The default setting is DYNAMIC. Other allowed values are COMPACT and REDUNDANT. The COMPRESSED row format cannot be defined as the default format because it is not supported for use in the system tablespace. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_default_row_format |
|
innodb_disable_sort_file_cache |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
If enabled, disables page caching when InnoDB uses sort files. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_disable_sort_file_cache |
|
innodb_fill_factor |
8.0 |
1 |
100 |
[10-100] |
Unlimited |
When creating or rebuilding an index, InnoDB performs a bulk load. This method of index creation is known as a "sorted index build". |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_fill_factor |
|
innodb_flush_log_at_trx_commit |
8.0 |
1 |
1 |
[0|1|2] |
Unlimited |
The possible values are: 0: Write and flush once per second 1: Write and flush at each commit 2: Write at commit, flush once per second |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
innodb_flush_method |
8.0 |
0 |
O_DIRECT |
[fsync|O_DSYNC|littlesync|nosync|O_DIRECT|O_DIRECT_NO_FSYNC] |
Unlimited |
Defines the method used to flush data to InnoDB data files and log files, which can affect I/O throughput. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_method |
|
innodb_flush_neighbors |
8.0 |
1 |
0 |
[0|1|2] |
Unlimited |
Specifies whether flushing a page from the InnoDB buffer pool also flushes other dirty pages in the same extent. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_neighbors |
|
innodb_flush_sync |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
The innodb_flush_sync parameter is enabled by default, which causes the innodb_io_capacity setting to be ignored during bursts of I/O activity at checkpoints. To adhere to the InnoDB background I/O activity limit defined by the innodb_io_capacity setting, disable innodb_flush_sync. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_sync |
|
innodb_ft_cache_size |
8.0 |
0 |
8000000 |
[1600000-80000000] |
Unlimited |
The memory allocated for the InnoDB FULLTEXT search index cache, in bytes, which holds parsed documents in memory when creating an InnoDB FULLTEXT index. Index inserts and updates are only committed to disk when the innodb_ft_cache_size limit is reached. innodb_ft_cache_size defines the cache size on a per-table basis. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_cache_size |
|
innodb_ft_enable_diag_print |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Whether to enable additional full-text search (FTS) diagnostic output. This option is primarily for advanced FTS debugging and is not of interest to most users. The output is printed to the error log and includes information. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_enable_diag_print |
|
innodb_ft_enable_stopword |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
When creating an InnoDB FULLTEXT index, specifies a set of stopwords to associate with it. If the innodb_ft_user_stopword_table option is set, stopwords are taken from that table. Otherwise, if the innodb_ft_server_stopword_table option is set, stopwords are taken from that table. Otherwise, the built-in default set of stopwords is used. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_enable_stopword |
|
innodb_ft_max_token_size |
8.0 |
0 |
84 |
[10-84] |
Unlimited |
The maximum length of a word stored in an InnoDB FULLTEXT index. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_max_token_size |
|
innodb_ft_min_token_size |
8.0 |
0 |
3 |
[0-16] |
Unlimited |
The minimum length of a word stored in an InnoDB FULLTEXT index. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_min_token_size |
|
innodb_ft_num_word_optimize |
8.0 |
1 |
2000 |
[0-10000] |
Unlimited |
The number of words to process for each OPTIMIZE TABLE operation on an InnoDB FULLTEXT index. Because a large-volume insert or update operation on a table with a full-text search index can require significant index maintenance to merge all changes, you might perform a series of OPTIMIZE TABLE statements, each continuing where the previous one left off. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_num_word_optimize |
|
innodb_ft_result_cache_limit |
8.0 |
1 |
2000000000 |
[1000000-4294967295] |
Unlimited |
The InnoDB full-text search query result cache limit in bytes, per full-text search query or per thread. Intermediate and final InnoDB full-text search query results are processed in memory. Use innodb_ft_result_cache_limit to set a size limit on the full-text search query result cache to avoid excessive memory consumption in case of very large InnoDB full-text search query results, such as millions or hundreds of millions of rows. Memory is allocated as needed when processing a full-text search query. If the result cache size limit is reached, an error is returned, indicating that the query exceeded the maximum allowed memory. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_result_cache_limit |
|
innodb_ft_sort_pll_degree |
8.0 |
0 |
2 |
[1-16] |
Unlimited |
The number of threads used for parallel indexing and tokenizing of text in an InnoDB FULLTEXT index when building a search index. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_sort_pll_degree |
|
innodb_ft_total_cache_size |
8.0 |
0 |
640000000 |
[32000000-1600000000] |
Unlimited |
The total memory allocated for the InnoDB full-text search index cache for all tables, in bytes. Creating multiple tables with FULLTEXT search indexes can consume a significant portion of available memory. innodb_ft_total_cache_size defines a global memory limit for all full-text search indexes to help avoid excessive memory consumption. If an index operation reaches the global limit, a forced synchronization is triggered. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_ft_total_cache_size |
|
innodb_io_capacity |
8.0 |
1 |
20000 |
[0-18446744073709551615] |
Unlimited |
The innodb_io_capacity parameter sets an upper limit on the number of I/O operations per second performed by InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_io_capacity |
|
innodb_io_capacity_max |
8.0 |
1 |
40000 |
[0-18446744073709551615] |
Unlimited |
If flushing activity falls behind, InnoDB can flush at a higher rate than what is enforced by innodb_io_capacity. In this case, innodb_io_capacity_max defines an upper limit on the number of I/O operations per second performed by InnoDB background tasks. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_io_capacity_max |
|
innodb_lock_wait_timeout |
8.0 |
1 |
50 |
[1-1073741824] |
Unlimited |
The timeout in seconds an InnoDB transaction waits for a row lock before giving up. The default value is 50 seconds. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout |
|
innodb_log_buffer_size |
8.0 |
1 |
16777216 |
[1048576-4294967295] |
Unlimited |
The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 16 MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have transactions that update, insert, or delete many rows, increasing the size of the log buffer can save disk I/O. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_buffer_size |
|
innodb_log_checksums |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
Enables or disables checksums for redo log pages. innodb_log_checksums replaces innodb_log_checksum_algorithm. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_checksums |
|
innodb_log_compressed_pages |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Specifies whether images of re-compressed pages are written to the redo log. Re-compression can occur when changes are made to compressed data. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_compressed_pages |
|
innodb_log_file_size |
8.0 |
0 |
1500M |
[4194304-107374182400] |
Unlimited |
Defines the size in bytes of each log file in a log group. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_file_size |
|
innodb_log_spin_cpu_abs_lwm |
8.0 |
1 |
80 |
[0-4294967295] |
Unlimited |
Defines the minimum CPU usage at which user threads no longer spin while waiting for a redo flush. The value represents the sum of CPU core usage. For example, the default value of 80 represents 80% of a single CPU core. On a system with a multi-core processor, a value of 150 would represent 100% usage of one CPU core plus 50% usage of a second CPU core. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_spin_cpu_abs_lwm |
|
innodb_log_spin_cpu_pct_hwm |
8.0 |
1 |
50 |
[0-100] |
Unlimited |
Defines the maximum CPU usage at which user threads no longer spin while waiting for a redo flush. The value represents a percentage of the combined processing power of all CPU cores. The default value is 50%. For example, on a server with four CPU cores, 100% usage of two CPU cores is 50% of the total CPU processing power. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_spin_cpu_pct_hwm |
|
innodb_log_wait_for_flush_spin_hwm |
8.0 |
1 |
400 |
[0-18446744073709551615] |
Unlimited |
Defines the maximum average log flush time beyond which user threads no longer spin while waiting for a redo flush. The default value is 400 microseconds. |
|
|
innodb_lru_scan_depth |
8.0 |
1 |
{LEAST(DBInstanceClassMemory/1048576/8, 8192)} |
[100-18446744073709551615] |
Unlimited |
A parameter that influences the algorithm and heuristics for the InnoDB buffer pool flush operation. It is intended for performance experts tuning I/O-intensive workloads. It specifies how far the page cleaner thread scans into the buffer pool LRU page list for each buffer pool instance to find dirty pages to flush. This is a background operation that runs once per second. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_lru_scan_depth |
|
innodb_max_dirty_pages_pct |
8.0 |
1 |
75 |
[0-99] |
Unlimited |
This is an integer ranging from 0 to 100. The default value for the built-in InnoDB is 90, and the default for the InnoDB plugin is 75. The main thread in InnoDB attempts to write pages from the buffer pool so that the percentage of dirty (not yet written) pages does not exceed this value. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct |
|
innodb_max_dirty_pages_pct_lwm |
8.0 |
1 |
10 |
[0-99] |
Unlimited |
Defines a low water mark, representing a percentage of dirty pages, at which pre-flushing is enabled to control the dirty page ratio. The default value of 0 completely disables the pre-flushing behavior. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_max_dirty_pages_pct_lwm |
|
innodb_max_purge_lag |
8.0 |
1 |
0 |
[0-4294967295] |
Unlimited |
Defines the maximum length of the purge queue. A default value of 0 means no limit (no delay). |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_max_purge_lag |
|
innodb_max_purge_lag_delay |
8.0 |
1 |
0 |
[0-10000000] |
Unlimited |
Specifies the maximum delay time in microseconds for the delay imposed by the innodb_max_purge_lag configuration option. A non-zero value imposes an upper limit on the delay period based on a formula using the innodb_max_purge_lag value. The default value of zero means no upper limit is imposed on the delay interval. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_max_purge_lag_delay |
|
innodb_max_undo_log_size |
8.0 |
1 |
1073741824 |
[10485760-18446744073709551615] |
Unlimited |
Defines the threshold size for an undo tablespace. If an undo tablespace exceeds the threshold, it can be marked for truncation when innodb_undo_log_truncate is enabled. The default value is 1073741824 bytes (1024 MiB). |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_max_undo_log_size |
|
innodb_monitor_disable |
8.0 |
1 |
all |
Unlimited |
Disables InnoDB metric counters. You can query counter data using the INFORMATION_SCHEMA.INNODB_METRICS table. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_monitor_disable |
|
|
innodb_monitor_enable |
8.0 |
1 |
all |
Unlimited |
Enables InnoDB monitor output. When enabled, InnoDB generates detailed runtime information that can be used for performance analysis and troubleshooting. You can view the output using the SHOW ENGINE INNODB STATUS command. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_monitor_enable |
|
|
innodb_monitor_reset |
8.0 |
1 |
[counter|module|pattern|all] |
Unlimited |
This variable acts as a switch to reset the count values of InnoDB metric counters to zero. You can query counter data using the INFORMATION_SCHEMA.INNODB_METRICS table. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_monitor_reset |
|
|
innodb_monitor_reset_all |
8.0 |
1 |
[counter|module|pattern|all] |
Unlimited |
This variable acts as a switch to reset all values (min, max, etc.) of InnoDB metric counters. You can query counter data using the INFORMATION_SCHEMA.INNODB_METRICS table. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_monitor_reset_all |
|
|
innodb_old_blocks_pct |
8.0 |
1 |
37 |
[5-95] |
Unlimited |
(For InnoDB plugin only) Specifies the approximate percentage of the InnoDB buffer pool to be used for the old block sublist. The value range is 5 to 95. The default value is 37 (i.e., 3/8 of the pool). |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_old_blocks_pct |
|
innodb_old_blocks_time |
8.0 |
1 |
1000 |
[0-1024] |
Unlimited |
(For InnoDB plugin only) Specifies how long in milliseconds a block inserted into the old sublist must stay there after its first access before it can be moved to the new sublist. The default value is 0: a block inserted into the old sublist is moved to the new sublist immediately upon its first access, regardless of how long after insertion the access occurs. If the value is greater than 0, blocks remain in the old sublist until an access occurs at least that many milliseconds after the first access. Unit: milliseconds. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_old_blocks_time |
|
innodb_online_alter_log_max_size |
8.0 |
1 |
134217728 |
[134217728-2147483647] |
Unlimited |
The maximum modification log file size for online index creation. |
|
|
innodb_open_files |
8.0 |
0 |
{LEAST(DBInstanceClassCPU*500, 8000)} |
[10-2147483647] |
Unlimited |
This variable is only relevant when using multiple InnoDB tablespaces. It specifies the maximum number of .ibd files that MySQL can keep open at one time. The minimum value is 10. The default is 300. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_open_files |
|
innodb_optimize_fulltext_only |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Changes the way OPTIMIZE TABLE operates on InnoDB tables, so that it can be temporarily enabled during maintenance operations on InnoDB tables with FULLTEXT indexes. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_optimize_fulltext_only |
|
innodb_page_cleaners |
8.0 |
0 |
8 |
[1-64] |
Unlimited |
The number of page cleaner threads that flush dirty pages from buffer pool instances. Page cleaner threads perform flush list and LRU flushing. A single page cleaner thread was introduced in MySQL 5.6 to offload buffer pool flushing work from the InnoDB main thread. In MySQL 5.7, InnoDB provides support for multiple page cleaner threads. A value of 1 maintains the pre-MySQL 5.7 configuration, where there is one page cleaner thread. When multiple page cleaner threads are present, buffer pool flushing tasks for each buffer pool instance are dispatched to idle page cleaner threads. In MySQL 5.7, the default value of innodb_page_cleaners was changed from 1 to 4. If the number of page cleaner threads exceeds the number of buffer pool instances, the extra threads will be idle and will not consume CPU resources. The innodb_page_cleaners variable is dynamic and can be changed online. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_page_cleaners |
|
innodb_print_all_deadlocks |
8.0 |
1 |
OFF |
[OFF|ON] |
Unlimited |
Prints all deadlock information to the MySQL error log (off by default). |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_print_all_deadlocks |
|
innodb_print_ddl_logs |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Enabling this option causes MySQL to write DDL logs to stderr. For more information, see Viewing DDL Logs. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_print_ddl_logs |
|
innodb_purge_batch_size |
8.0 |
1 |
300 |
[1-5000] |
Unlimited |
Indicates the granularity of the purge operation (flushing changed buffer pool blocks to disk) in terms of redo log records. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_purge_batch_size |
|
innodb_purge_rseg_truncate_frequency |
8.0 |
1 |
128 |
[1-128] |
Unlimited |
Defines the frequency at which the purge system releases rollback segments as the number of times purge is invoked. The undo tablespace cannot be truncated until the rollback segments are released. Typically, the purge system releases rollback segments every 128 times purge is invoked. The default value is 128. Decreasing this value increases the frequency at which the purge thread releases rollback segments. |
|
|
innodb_purge_threads |
8.0 |
0 |
{LEAST(DBInstanceClassMemory/1073741824, 8)} |
[1-32] |
Unlimited |
The number of background threads dedicated to InnoDB purge operations. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_purge_threads |
|
innodb_random_read_ahead |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Enables the random read-ahead technique to optimize InnoDB I/O. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_random_read_ahead |
|
innodb_read_ahead_threshold |
8.0 |
1 |
56 |
[0-1024] |
Unlimited |
(For InnoDB plugin only) Controls the sensitivity of InnoDB for linear read-ahead of pages into the buffer pool. If InnoDB reads at least innodb_read_ahead_threshold pages sequentially from an extent (64 pages), it initiates an asynchronous read of the entire subsequent extent. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_read_ahead_threshold |
|
innodb_read_io_threads |
8.0 |
0 |
4 |
[1-64] |
Unlimited |
(For InnoDB plugin only) The number of I/O threads for InnoDB read operations. The default value is 4. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_read_io_threads |
|
innodb_rollback_on_timeout |
8.0 |
0 |
OFF |
ON/OFF |
Unlimited |
By default, InnoDB rolls back only the last statement on a transaction timeout. If --innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction, which is the same behavior as in MySQL 4.1. This variable was added in MySQL 5.1.15. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_rollback_on_timeout |
|
innodb_rollback_segments |
8.0 |
1 |
128 |
[1-128] |
Unlimited |
Defines the number of rollback segments used by InnoDB. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_rollback_segments |
|
innodb_segment_reserve_factor |
8.0 |
2 |
12.5 |
[0.03-40] |
[20220830,99999999] |
Defines the percentage of tablespace file segment pages that are reserved as blank pages. This setting applies to both file-per-table and general tablespaces. The default setting for innodb_segment_reserve_factor is 12.5%, which is the same percentage of pages reserved in previous MySQL versions. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_segment_reserve_factor |
|
innodb_sort_buffer_size |
8.0 |
0 |
1048576 |
[65536-67108864] |
Unlimited |
Specifies the size of the sort buffer used for sorting data during the creation of an InnoDB index. The specified size defines the amount of data read into memory for internal sorting and then written out to disk. This process is called a "run". During the merge phase, a pair of buffers of the specified size are read and merged. A larger setting results in fewer runs and merges. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_sort_buffer_size |
|
innodb_spin_wait_delay |
8.0 |
1 |
6 |
[0-4294967295] |
Unlimited |
The maximum delay for a spin-lock poll. The underlying implementation of this mechanism varies depending on the hardware and operating system combination, so the delay does not correspond to a fixed time interval. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_spin_wait_delay |
|
innodb_stats_auto_recalc |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
Causes InnoDB to automatically recalculate persistent statistics after a significant change in the table's data. The threshold is 10% of the number of rows in the table. This setting applies to tables created when the innodb_stats_persistent option is enabled. You can also configure automatic statistics recalculation by specifying STATS_PERSISTENT = 1 in a CREATE TABLE or ALTER TABLE statement. The amount of sampled data for generating statistics is controlled by the innodb_stats_persistent_sample_pages configuration option. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_auto_recalc |
|
innodb_stats_include_delete_marked |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
By default, InnoDB reads uncommitted data when calculating statistics. In an uncommitted transaction, if rows are deleted from a table, InnoDB excludes the delete-marked records when calculating row estimates and index statistics. This can lead to non-optimal execution plans for other transactions that operate on the table concurrently using a transaction isolation level other than READ UNCOMMITTED. To avoid this, you can enable innodb_stats_include_delete_marked to ensure that InnoDB includes delete-marked records when calculating persistent optimizer statistics. |
|
|
innodb_stats_method |
8.0 |
1 |
nulls_equal |
[nulls_equal|nulls_unequal|nulls_ignored] |
Unlimited |
How the server handles NULL values when collecting statistics on the distribution of index values for InnoDB tables. This variable has three possible values: nulls_equal, nulls_unequal, and nulls_ignored. For nulls_equal, all NULL index values are treated as equal and form a single value group of a size equal to the number of NULL values. For nulls_unequal, NULL values are treated as unequal, and each NULL forms a distinct value group of size 1. For nulls_ignored, NULL values are ignored. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_method |
|
innodb_stats_on_metadata |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
This option applies only when optimizer statistics are configured to be non-persistent. When innodb_stats_persistent is disabled or a single table is created or altered with stats_persistent=0, optimizer statistics are not persisted to disk. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_on_metadata |
|
innodb_stats_persistent |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
Specifies whether InnoDB index statistics are persisted to disk. Otherwise, statistics may be recalculated frequently, which can lead to changes in query execution plans. This setting is stored with each table at creation time. You can set innodb_stats_persistent at the global level before creating a table, or use the STATS_PERSISTENT clause of the CREATE TABLE and ALTER TABLE statements to override the system-wide setting and configure persistent statistics for individual tables. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_stats_persistent |
|
innodb_stats_persistent_sample_pages |
8.0 |
1 |
20 |
[0-4294967295] |
Unlimited |
The number of index pages to sample when estimating cardinality and other statistics for an index column, such as those calculated by ANALYZE TABLE. Increasing this value can improve the accuracy of index statistics, which can improve query execution plans, but it increases the I/O load during the execution of ANALYZE TABLE for InnoDB tables. |
|
|
innodb_stats_transient_sample_pages |
8.0 |
1 |
8 |
[1-4294967295] |
Unlimited |
The number of index pages to sample when estimating cardinality and other statistics for an index column, such as those calculated by ANALYZE TABLE. The default value is 8. Increasing this value can improve the accuracy of index statistics, which can improve query execution plans, but it increases the I/O load when opening an InnoDB table or recalculating statistics. |
|
|
innodb_status_output |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Enables or disables periodic output from the standard InnoDB Monitor. It is also used in conjunction with innodb_status_output_locks to enable or disable periodic output from the InnoDB Lock Monitor. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_status_output |
|
innodb_status_output_locks |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Enables or disables the InnoDB Lock Monitor. When enabled, the InnoDB Lock Monitor prints additional information about locks in the SHOW ENGINE INNODB STATUS output and in the periodic output printed to the MySQL error log. The periodic output of the InnoDB Lock Monitor is printed as part of the standard InnoDB Monitor output. Therefore, the standard InnoDB Monitor must be enabled for the InnoDB Lock Monitor to periodically print data to the MySQL error log. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_status_output_locks |
|
innodb_strict_mode |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
(For InnoDB plugin only) Whether InnoDB returns errors instead of warnings for certain conditions, similar to strict SQL mode. The default value is OFF. For a list of affected conditions, see InnoDB Strict Mode. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_strict_mode |
|
innodb_sync_array_size |
8.0 |
0 |
128 |
[1-1024] |
Unlimited |
Defines the size of the mutex/lock wait array. Increasing this value splits the internal data structure used for coordinating threads, which can improve concurrency for workloads with many waiting threads. This setting must be configured at MySQL instance startup and cannot be changed afterward. It is recommended to increase this value for workloads that frequently generate many waiting threads, typically greater than 768. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_sync_array_size |
|
innodb_sync_spin_loops |
8.0 |
1 |
100 |
[0-4294967295] |
Unlimited |
The number of times a thread waits for an InnoDB mutex to be released before the thread is suspended. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_sync_spin_loops |
|
innodb_table_locks |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
If autocommit = 0, InnoDB honors LOCK TABLES. MySQL does not return from LOCK TABLES ... WRITE until all other threads have released their locks on the table. The default value of innodb_table_locks is 1, which means that if autocommit = 0, LOCK TABLES causes InnoDB to lock the table internally. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_table_locks |
|
innodb_thread_concurrency |
8.0 |
1 |
0 |
[0-1000] |
Unlimited |
InnoDB attempts to keep the number of operating system threads running concurrently within InnoDB less than or equal to the limit given by this variable. Once the number of threads reaches this limit, additional threads are placed in a waiting state, queued for execution (FIFO queue). |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_thread_concurrency |
|
innodb_thread_sleep_delay |
8.0 |
1 |
10000 |
[0-1000000] |
Unlimited |
The time in microseconds an InnoDB thread sleeps before joining the InnoDB queue. A value of 0 disables sleep. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_thread_sleep_delay |
|
innodb_write_io_threads |
8.0 |
0 |
4 |
[1-64] |
Unlimited |
(For InnoDB plugin only) The number of I/O threads for write operations in InnoDB. The default value is 4. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_write_io_threads |
|
interactive_timeout |
8.0 |
1 |
7200 |
[10-86400] |
Unlimited |
The number of seconds the server waits for activity on an interactive connection before closing it. An interactive client is defined as a client that connects to mysql_real_connect() using the CLIENT_INTERACTIVE option. Unit: seconds. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_interactive_timeout |
|
join_buffer_size |
8.0 |
1 |
{LEAST(DBInstanceClassMemory/1048576*128, 262144)} |
[128-4294967295] |
Unlimited |
The minimum size of the buffer used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_join_buffer_size |
|
key_buffer_size |
8.0 |
1 |
8388608 |
[0-18446744073709551615] |
Unlimited |
Index blocks for MyISAM tables are buffered and shared by all threads. key_buffer_size is the size of the buffer used for index blocks. The key buffer is also known as the key cache. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_key_buffer_size |
|
key_cache_age_threshold |
8.0 |
1 |
300 |
[100-4294967295] |
Unlimited |
This value controls the demotion of buffers from the hot sublist to the warm sublist of the key cache. A lower value causes demotion to happen faster. The minimum value is 100. The default value is 300. Unit: seconds. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_key_cache_age_threshold |
|
key_cache_block_size |
8.0 |
1 |
1024 |
[512-16384] |
Unlimited |
The size in bytes of blocks in the key cache. The default value is 1024. Unit: bytes. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_key_cache_block_size |
|
key_cache_division_limit |
8.0 |
1 |
100 |
[1-100] |
Unlimited |
The division point between the hot and warm sublists of the key cache buffer list. This value is the percentage of the buffer list to be used for the warm sublist. The allowed value range is 1 to 100. The default value is 100. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_key_cache_division_limit |
|
lc_time_names |
8.0 |
1 |
en_US |
[ja_JP|pt_BR|en_US] |
Unlimited |
This variable specifies the locale that controls the language used to display day and month names and abbreviations. This variable affects the output of the DATE_FORMAT(), DAYNAME(), and MONTHNAME() functions. Locale names are POSIX-style values, such as 'ja_JP' or 'pt_BR'. The default value is 'en_US', which does not consider the system's locale. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lc_time_names |
|
local_infile |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
This variable controls the server-side LOCAL capability for LOAD DATA statements. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_local_infile |
|
lock_wait_timeout |
8.0 |
1 |
31536000 |
[1-1073741824] |
Unlimited |
This variable specifies the timeout in seconds for attempts to acquire metadata locks. The allowed value range is from 1 to 31536000 (1 year). The default value is 31536000. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_lock_wait_timeout |
|
log_bin_use_v1_row_events |
8.0 |
1 |
1 |
[0|1] |
Unlimited |
If equal to 1, version 1 row events are written to the row-based binary log. If equal to 0, the latest version of the events is written. This option is useful during some upgrades. |
|
|
log_error_verbosity |
8.0 |
1 |
3 |
[1-3] |
Unlimited |
The log_error_verbosity system variable specifies the verbosity for handling events intended for the error log. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_log_error_verbosity |
|
log_queries_not_using_indexes |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Whether to log queries that do not use indexes to the slow query log. |
|
|
log_slow_admin_statements |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
The Redis Slow Log also contains slow-running management statements. |
|
|
log_throttle_queries_not_using_indexes |
8.0 |
1 |
0 |
[0-4294967295] |
Unlimited |
If log_queries_not_using_indexes is enabled, the log_throttle_queries_not_using_indexes variable limits the number of such queries that can be written to the slow query log per minute. |
|
|
long_query_time |
8.0 |
1 |
1 |
[0.1-31536000] |
Unlimited |
If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If the slow query log is enabled, the query is logged to the slow query log file. Unit: seconds. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_long_query_time |
|
loose_binlog_parallel_flush |
8.0 |
1 |
{LEAST(DBInstanceClassCPU/16, 1)} |
[ON|OFF] |
[20230324,99999999) |
Enables binlog_parallel_flush_optimize to reduce flush bottlenecks. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_ccl_queue_bucket_count |
8.0 |
1 |
4 |
[1-64] |
[20201231,99999999] |
The number of groups when using the CCL queue. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_ccl_queue_bucket_size |
8.0 |
1 |
64 |
[1-4096] |
[20201231,99999999] |
The maximum number of concurrent connections allowed when using the CCL queue. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_ccl_queue_hot_delete |
8.0 |
1 |
OFF |
[ON|OFF] |
[20201231,99999999] |
Whether to enter the CCL queue for a single delete operation under certain conditions. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_ccl_queue_hot_update |
8.0 |
1 |
OFF |
[ON|OFF] |
[20201231,99999999] |
Whether to enter the CCL queue for a single update operation under certain conditions. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_force_memory_to_innodb |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Converts the table engine from memory to InnoDB. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_group_replication_flow_control_applier_threshold |
8.0 |
1 |
25000 |
[0-2147483647] |
[20221231,99999999] |
Specifies the number of transactions waiting in the applier queue that triggers flow control. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_group_replication_flow_control_certifier_threshold |
8.0 |
1 |
25000 |
[0-2147483647] |
[20221231,99999999] |
The number of transactions waiting in the certifier queue that triggers flow control. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_group_replication_flow_control_hold_percent |
8.0 |
1 |
10 |
[0-100] |
[20221231,99999999] |
Defines the percentage of the group's quota that remains unused to allow the cluster, which is in a flow control state, to digest the backlog of work. A value of 0 means that no part of the quota is reserved for digesting the work backlog. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_group_replication_flow_control_max_commit_quota |
8.0 |
1 |
0 |
[0-2147483647] |
[20221231,99999999] |
The maximum flow control quota for the group, or the maximum available quota for any period when flow control is enabled. A value of 0 means no maximum quota is set. The value of this system variable cannot be less than group_replication_flow_control_min_quota and group_replication_flow_control_min_recovery_quota. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_group_replication_flow_control_member_quota_percent |
8.0 |
1 |
0 |
[0-100] |
[20221231,99999999] |
The percentage of the quota that a member should assume is available to it when calculating the quota. A value of 0 means the quota should be evenly distributed among the members that were writers in the previous period. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_group_replication_flow_control_min_quota |
8.0 |
1 |
0 |
[0-2147483647] |
[20221231,99999999] |
The minimum flow control quota that can be assigned to a member, regardless of the lowest quota calculated in the previous period. A value of 0 means there is no minimum quota. The value of this system variable cannot be greater than group_replication_flow_control_max_commit_quota. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_group_replication_flow_control_min_recovery_quota |
8.0 |
1 |
0 |
[0-2147483647] |
[20221231,99999999] |
The minimum quota that can be assigned to a member due to another recovering member in the group, regardless of the lowest quota calculated in the previous period. A value of 0 means there is no minimum quota. The value of this system variable cannot be greater than group_replication_flow_control_max_commit_quota. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_group_replication_flow_control_mode |
8.0 |
1 |
DISABLED |
[DISABLED|QUOTA] |
[20221231,99999999] |
The mode used for flow control. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_group_replication_flow_control_period |
8.0 |
1 |
1 |
[1-60] |
[20221231,99999999] |
Defines the number of seconds to wait between flow control iterations, during which flow control messages are sent and flow control management tasks are run. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_group_replication_flow_control_release_percent |
8.0 |
1 |
50 |
[0-1000] |
[20221231,99999999] |
Defines how the group's quota should be released when flow control is no longer needed to limit the writing members. This percentage is the amount the quota is increased by for each flow control period. A value of 0 means the quota is released in a single flow control iteration once the flow control thresholds are within limits. The range allows the quota to be increased by up to 10 times the current quota, which allows for better adaptation, mainly when the flow control period is large and the quota is very small. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_group_replication_transaction_size_limit |
8.0 |
1 |
150000000 |
[0-2147483647] |
[20221231,99999999] |
The maximum transaction size in bytes that the replication group accepts. Transactions larger than this size are rolled back by the receiving member and are not broadcast to the group. When this system variable is set to 0, there is no limit on the transaction size that the replication group accepts. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_ignore_index_hint_error |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Controls whether to ignore index hint errors. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_information_schema_stats_expiry |
8.0 |
1 |
86400 |
[0-31536000] |
[20201231,99999999] |
The number of seconds before the mysqld server fetches data from the storage engine and replaces the data in the cache. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_doublewrite_pages |
8.0 |
0 |
64 |
[0-512] |
[20201231,99999999) |
The number of doublewrite pages per thread. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_log_optimize_ddl |
8.0 |
1 |
OFF |
[ON|OFF] |
[20201231,99999999] |
Reduces redo log recording when creating an index locally or rebuilding a table. Setting this to OFF can avoid delays caused by page flushing and allow for concurrent backups. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_log_write_ahead_size |
8.0 |
1 |
4096 |
[512-16384] |
[20200430,99999999) |
The unit size of the write-ahead log to avoid read-on-write. It should match the operating system's cache block I/O size. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_numa_interleave |
8.0 |
0 |
ON |
[ON|OFF] |
Unlimited |
Enables the NUMA interleaved memory policy for allocating the InnoDB buffer pool. When innodb_numa_interleave is enabled, the NUMA memory policy for the mysqld process is set to MPOL_interleave. After the InnoDB buffer pool is allocated, the NUMA memory policy is set back to MPOL_DEFAULT. For the innodb_numa_interleave option to be available, MySQL must be compiled on a NUMA-enabled Linux system. |
https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_numa_interleave |
|
loose_innodb_parallel_read_threads |
8.0 |
1 |
1 |
[0-256] |
[20200831,99999999) |
The number of threads for parallel reads. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_rds_chunk_flush_interval |
8.0 |
1 |
100 |
[0-100000] |
[20210930,99999999) |
The time interval in milliseconds between each chunk flush operation. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_rds_faster_ddl |
8.0 |
1 |
ON |
[ON|OFF] |
[20200430,99999999) |
A feature switch for faster DDL. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_rds_flashback_task_enabled |
8.0 |
1 |
OFF |
[ON|OFF] |
[20210930,99999999) |
Whether to roll forward new read views by default (default is true). |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_rds_free_resize |
8.0 |
1 |
ON |
[ON|OFF] |
[20210930,99999999) |
The process of handling pages in blocks when resizing the InnoDB buffer pool. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_trx_resurrect_table_lock_accelerate |
8.0 |
1 |
OFF |
[OFF|ON] |
[20221231,99999999) |
Whether to store table information in undo log records. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_undo_retention |
8.0 |
1 |
0 |
[0-172800] |
[20210930,99999999) |
The retention time for undo data in seconds. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_undo_space_reserved_size |
8.0 |
1 |
0 |
[0-20480] |
[20210930,99999999) |
The size of the reserved file space to release (in MiB). |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_innodb_undo_space_supremum_size |
8.0 |
1 |
10240 |
[0-524288] |
[20210930,99999999) |
The upper limit size of the undo file space in MiB. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_json_document_max_depth |
8.0 |
1 |
100 |
[100-1000] |
[20220830, 99999999) |
The maximum allowed nesting level in a JSON document. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_multi_blocks_ddl_count |
8.0 |
1 |
0 |
[0-1024] |
[20201231,99999999] |
The number of pages for multiple blocks at once in some DDL queries. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_optimizer_switch |
8.0 |
1 |
index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on |
.* |
Unlimited |
The optimizer_switch system variable can control the behavior of the optimizer. |
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_optimizer_switch |
|
loose_optimizer_trace |
8.0 |
1 |
enabled=off,one_line=off |
.* |
Unlimited |
The optimizer_trace system variable has these switch options: enabled, one_line. |
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_optimizer_trace |
|
loose_optimizer_trace_features |
8.0 |
1 |
greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
.* |
Unlimited |
These features can be excluded from tracing using the optimizer_trace_features system variable. |
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_optimizer_trace_features |
|
loose_performance_schema_accounts_size |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The number of rows in the accounts table. If this variable is 0, the Performance Schema does not maintain connection statistics in the accounts table, nor does it maintain status variable information in the status_by_account table. |
|
|
loose_performance_schema_digests_size |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The maximum number of rows in the events_statements_summary_by_digest table. If this maximum is exceeded, preventing a digest from being instrumented, the Performance Schema increments the Performance_schema_digest_lost status variable. |
|
|
loose_performance_schema_error_size |
8.0 |
0 |
0 |
[0-1048576] |
Unlimited |
The number of detected server error codes. The default value is the actual number of server error codes. Although the value can be set anywhere between 0 and the maximum, the intended use is to set it to either the default (detect all errors) or 0 (detect no errors). |
|
|
loose_performance_schema_events_stages_history_long_size |
8.0 |
0 |
0 |
[-1-1048576] |
Unlimited |
The number of rows in the events_stages_history_long table. |
|
|
loose_performance_schema_events_stages_history_size |
8.0 |
0 |
0 |
[-1-1024] |
Unlimited |
The number of rows per thread in the events_stages_history table. |
|
|
loose_performance_schema_events_statements_history_long_size |
8.0 |
0 |
0 |
[-1-1048576] |
Unlimited |
The number of rows in the events_statements_history_long table. |
|
|
loose_performance_schema_events_statements_history_size |
8.0 |
0 |
0 |
[-1-1024] |
Unlimited |
The number of rows per thread in the events_statements_history table. |
|
|
loose_performance_schema_events_transactions_history_long_size |
8.0 |
0 |
0 |
[-1-1048576] |
Unlimited |
The number of rows in the events_transactions_history_long table. |
|
|
loose_performance_schema_events_transactions_history_size |
8.0 |
0 |
0 |
[-1-1024] |
Unlimited |
The number of rows per thread in the events_transactions_history table. |
|
|
loose_performance_schema_events_waits_history_long_size |
8.0 |
0 |
0 |
[-1-1048576] |
Unlimited |
The number of rows in the events_waits_history_long table. |
|
|
loose_performance_schema_events_waits_history_size |
8.0 |
0 |
0 |
[-1-1024] |
Unlimited |
The number of rows per thread in the events_waits_history table. |
|
|
loose_performance_schema_hosts_size |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The number of rows in the hosts table. If this variable is 0, the Performance Schema does not maintain connection statistics in the hosts table, nor does it maintain status variable information in the status_by_host table. |
|
|
loose_performance_schema_max_cond_classes |
8.0 |
0 |
150 |
[0-256] |
Unlimited |
The maximum number of condition instruments. |
|
|
loose_performance_schema_max_cond_instances |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The maximum number of instrumented condition objects. |
|
|
loose_performance_schema_max_digest_length |
8.0 |
0 |
0 |
[0-1048576] |
Unlimited |
The maximum number of bytes of memory reserved per statement for computing normalized statement digest values in the Performance Schema. This variable is related to max_digest_length. |
|
|
loose_performance_schema_max_digest_sample_age |
8.0 |
0 |
0 |
[0-1048576] |
Unlimited |
This variable affects statement sampling for the events_statements_summary_by_digest table, where a statement that produces a digest value for a new table row is stored as the current sample statement associated with the digest. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_performance_schema_max_file_classes |
8.0 |
0 |
80 |
[0-256] |
Unlimited |
The maximum number of file instruments. |
|
|
loose_performance_schema_max_file_handles |
8.0 |
0 |
0 |
[-1-32768] |
Unlimited |
The maximum number of opened file objects. |
|
|
loose_performance_schema_max_file_instances |
8.0 |
0 |
1000 |
[-1-1048576] |
Unlimited |
The maximum number of instrumented file objects. |
|
|
loose_performance_schema_max_index_stat |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The maximum number of indexes for which the Performance Schema maintains statistics. If this maximum is exceeded, causing index statistics to be lost, the Performance Schema increments the Performance_schema_index_stat_lost status variable. The default value is autosized using the value of performance_schema_max_table_instances. |
|
|
loose_performance_schema_max_memory_classes |
8.0 |
0 |
500 |
[0-1024] |
Unlimited |
The maximum number of memory instruments. |
|
|
loose_performance_schema_max_metadata_locks |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The maximum number of metadata lock instruments. This value controls the size of the metadata_locks table. If this maximum is exceeded, preventing a metadata lock from being instrumented, the Performance Schema increments the Performance_schema_metadata_lock_lost status variable. |
|
|
loose_performance_schema_max_mutex_classes |
8.0 |
0 |
256 |
[0-256] |
Unlimited |
The maximum number of mutexes. |
|
|
loose_performance_schema_max_mutex_instances |
8.0 |
0 |
10000 |
[-1-104857600] |
Unlimited |
The maximum number of instrumented mutex objects. |
|
|
loose_performance_schema_max_prepared_statements_instances |
8.0 |
0 |
1000 |
[-1-4194304] |
Unlimited |
The maximum number of rows in the prepared_statements_instances table. If this maximum is exceeded, preventing a prepared statement from being instrumented, the Performance Schema increments the Performance_schema_prepared_statements_lost status variable. |
|
|
loose_performance_schema_max_program_instances |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The maximum number of stored programs for which the Performance Schema maintains statistics. If this maximum is exceeded, the Performance Schema increments the Performance_schema_program_lost status variable. |
|
|
loose_performance_schema_max_rwlock_classes |
8.0 |
0 |
100 |
[0-256] |
Unlimited |
The maximum number of read/write lock (rwlock) instruments. |
|
|
loose_performance_schema_max_rwlock_instances |
8.0 |
0 |
10000 |
[-1-104857600] |
Unlimited |
The maximum number of instrumented read/write lock (rwlock) objects. |
|
|
loose_performance_schema_max_socket_classes |
8.0 |
0 |
10 |
[0-256] |
Unlimited |
The maximum number of socket instruments. |
|
|
loose_performance_schema_max_socket_instances |
8.0 |
0 |
1000 |
[-1-1048576] |
Unlimited |
The maximum number of instrumented socket objects. |
|
|
loose_performance_schema_max_sql_text_length |
8.0 |
0 |
0 |
[0-1048576] |
Unlimited |
The maximum number of bytes to store for an SQL statement in the SQL_TEXT column of the events_statements_current, events_statements_history, and events_statements_history_long statement event tables. Bytes exceeding performance_schema_max_sql_text_length are discarded and do not appear in the SQL_TEXT column. Statements that differ only after that many initial bytes are indistinguishable in this column. |
|
|
loose_performance_schema_max_stage_classes |
8.0 |
0 |
200 |
[0-256] |
Unlimited |
The maximum number of stage instruments. |
|
|
loose_performance_schema_max_statement_classes |
8.0 |
0 |
256 |
[0-256] |
Unlimited |
The maximum number of statement instruments. |
|
|
loose_performance_schema_max_statement_stack |
8.0 |
0 |
1 |
[0-256] |
Unlimited |
The maximum nested stored program call depth for which the Performance Schema maintains statistics. When this maximum is exceeded, the Performance Schema increments the Performance_schema_nested_statement_lost status variable for each stored program statement executed. |
|
|
loose_performance_schema_max_table_handles |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The maximum number of opened table objects. This value controls the size of the table_handles table. If this maximum is exceeded, preventing a table handle from being instrumented, the Performance Schema increments the Performance_schema_table_handles_lost status variable. |
|
|
loose_performance_schema_max_table_instances |
8.0 |
0 |
1000 |
[-1-1048576] |
Unlimited |
The maximum number of instrumented table objects. |
|
|
loose_performance_schema_max_table_lock_stat |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The maximum number of tables for which the Performance Schema maintains lock statistics. If this maximum is exceeded, causing table lock statistics to be lost, the Performance Schema increments the Performance_schema_table_lock_stat_lost status variable. |
|
|
loose_performance_schema_max_thread_classes |
8.0 |
0 |
100 |
[0-256] |
Unlimited |
The maximum number of thread instruments. |
|
|
loose_performance_schema_max_thread_instances |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The maximum number of instrumented thread objects. This value controls the size of the threads table. If this maximum is exceeded, preventing a thread from being instrumented, the Performance Schema increments the Performance_schema_thread_instances_lost status variable. |
|
|
loose_performance_schema_session_connect_attrs_size |
8.0 |
0 |
0 |
[-1-1048576] |
Unlimited |
The amount of memory pre-allocated per thread for holding connection attribute key-value pairs. If a client sends connection attribute data with a total size exceeding this value, the Performance Schema truncates the attribute data, increments the Performance_schema_session_connect_attrs_lost status variable, and writes a message to the error log that a truncation occurred if the log_error_verbosity system variable value is greater than 1. |
|
|
loose_performance_schema_setup_actors_size |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The number of rows in the setup_actors table. |
|
|
loose_performance_schema_setup_objects_size |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The number of rows in the setup_objects table. |
|
|
loose_performance_schema_users_size |
8.0 |
0 |
10000 |
[-1-1048576] |
Unlimited |
The number of rows in the users table. If this variable is 0, the Performance Schema does not maintain connection statistics in the users table, nor does it maintain status variable information in the status_by_user table. |
|
|
loose_persist_binlog_to_redo |
8.0 |
1 |
ON |
[ON|OFF] |
[20230324,99999999) |
Persists binlog events for transactions to the redo log to reduce I/O. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_rds_audit_log_event_buffer_size |
8.0 |
1 |
8192 |
[0-32768] |
[20201031,99999999) |
The maximum SQL size in the RDS audit log. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_rds_audit_log_version |
8.0 |
1 |
MYSQL_V1 |
[MYSQL_V1|MYSQL_V3] |
[20210930,99999999) |
The version of the audit log. Currently, MYSQL_V1 and MYSQL_V3 are supported. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_recycle_bin |
8.0 |
1 |
OFF |
ON/OFF |
[20200831,99999999) |
A switch for the recycle bin. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_recycle_bin_retention |
8.0 |
1 |
604800 |
[86400-1209600] |
[20200831,99999999) |
The retention period for the recycle bin in seconds. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_recycle_scheduler |
8.0 |
1 |
OFF |
[OFF|ON] |
[20200831,99999999) |
A switch for the cleanup thread. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_slave_parallel_workers |
8.0 |
1 |
{GREATEST(DBInstanceClassCPU, 8)} |
[0-1024] |
Unlimited |
Sets the number of applier threads for parallel execution of replication transactions.
|
|
|
loose_sql_safe_updates |
8.0 |
1 |
OFF |
[ON|OFF] |
[20200430,99999999) |
If this variable is enabled, an error is returned for UPDATE and DELETE statements without a key in the WHERE or LIMIT clause. |
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_sql_safe_updates |
|
loose_ssl_cipher |
8.0 |
0 |
"ALL:@SECLEVEL=0" |
[""|"ALL:@SECLEVEL=0"] |
[20231215,99999999) |
The SSL cipher to use. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_ssl_cipher |
|
loose_thread_pool_enabled |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
Enables or disables the thread pool. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_thread_pool_oversubscribe |
8.0 |
1 |
32 |
[10-64] |
Unlimited |
The number of extra active worker threads allowed per group. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
loose_thread_pool_size |
8.0 |
1 |
{LEAST(DBInstanceClassCPU*2, 64)} |
[1-16] |
Unlimited |
The number of thread groups in the thread pool. |
https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_thread_pool_size |
|
loose_validate_password_length |
8.0 |
1 |
8 |
[1-12] |
Unlimited |
The minimum password length. If set to 8, the password length must be greater than or equal to 8. |
|
|
low_priority_updates |
8.0 |
1 |
0 |
[0|1] |
Unlimited |
If set to 1, all INSERT, UPDATE, DELETE, and LOCK TABLE WRITE statements wait until there are no pending SELECT or LOCK TABLE READ operations on the affected table. This only affects storage engines that use only table-level locking, such as MyISAM, MEMORY, and MERGE. This variable was formerly named sql_low_priority_updates. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_low_priority_updates |
|
master_verify_checksum |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Enabling this variable causes the primary server to check checksums when reading from the binary log. master_verify_checksum is disabled by default. In this case, the primary server uses the event length in the binary log to verify events, so that only complete events are read from the binary log. |
|
|
max_allowed_packet |
8.0 |
1 |
1073741824 |
[16384-1073741824] |
Unlimited |
The maximum size of a packet or any generated/intermediate string. Unit: bytes. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_allowed_packet |
|
max_binlog_cache_size |
8.0 |
1 |
18446744073709547520 |
[4096-18446744073709547520] |
Unlimited |
max_binlog_cache_size only sets the size of the transaction cache. The session visibility of max_binlog_cache_size matches that of the binlog_cache_size system variable. |
|
|
max_binlog_stmt_cache_size |
8.0 |
1 |
18446744073709547520 |
[4096-18446744073709547520] |
Unlimited |
If non-transactional statements in a transaction require more memory than this value, the server generates an error. The minimum value is 4096. On 32-bit platforms, the maximum and default value is 4 GB. On 64-bit platforms, it is 16 exabytes (EB). |
|
|
max_connections |
8.0 |
1 |
600 |
[1-200000] |
Unlimited |
The maximum number of concurrent client connections allowed. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connections |
|
max_connect_errors |
8.0 |
1 |
100 |
[0-4294967295] |
Unlimited |
If the number of consecutive connection requests from a host that are interrupted without a successful connection exceeds this value, the server blocks that host from further connections. A blocked host can be unblocked by flushing the host cache. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_connect_errors |
|
max_digest_length |
8.0 |
0 |
1024 |
[0-1048576] |
Unlimited |
The maximum number of bytes per session reserved for computing canonical statement digests. Once this amount of space is used during digest computation, truncation occurs: further tokens from the parsed statement are no longer collected and do not contribute to its digest value. Statements that differ only in parsed tokens after that many bytes produce the same canonical statement digest and are treated as identical if compared or used for digest statistics. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_digest_length |
|
max_error_count |
8.0 |
1 |
64 |
[0-65535] |
Unlimited |
The maximum number of error, warning, and information messages to be stored and displayed by the SHOW ERRORS and SHOW WARNINGS statements. This is the same as the number of condition areas in the diagnostics area, so the number of conditions that can be checked with GET DIAGNOSTICS is also the same. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_error_count |
|
max_execution_time |
8.0 |
1 |
0 |
[0-4294967295] |
Unlimited |
If the execution time exceeds this value, the statement will be interrupted. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_execution_time |
|
max_heap_table_size |
8.0 |
1 |
67108864 |
[16384-1844674407370954752] |
Unlimited |
This variable sets the maximum size to which user-created MEMORY tables are allowed to grow. The value of the variable is used to calculate the MAX_ROWS value for MEMORY tables. Setting this variable has no effect on any existing MEMORY table unless the table is re-created with a CREATE TABLE statement or altered with ALTER TABLE or TRUNCATE TABLE. A server restart also sets the maximum size of existing MEMORY tables to the global max_heap_table_size value. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_heap_table_size |
|
max_join_size |
8.0 |
1 |
18446744073709551615 |
[1-18446744073709551615] |
Unlimited |
Disallows statements that would likely need to examine more than max_join_size rows (for single-table statements) or row combinations (for multi-table statements), or that are likely to perform more than max_join_size disk lookups. By setting this value, you can catch statements that do not use keys properly and would probably take a long time. Set this variable if users tend to perform join operations that lack a WHERE clause, take a long time, or return millions of rows. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_join_size |
|
max_length_for_sort_data |
8.0 |
1 |
1024 |
[0-838860] |
Unlimited |
The cutoff point for the size of index values that determines which filesort algorithm to use. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_length_for_sort_data |
|
max_points_in_geometry |
8.0 |
1 |
65536 |
[3-1048576] |
Unlimited |
The maximum value for the points_per_circle parameter in the ST_Buffer_Strategy() function. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_points_in_geometry |
|
max_prepared_stmt_count |
8.0 |
1 |
16382 |
[0-1048576] |
Unlimited |
This variable limits the total number of prepared statements in the server. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_prepared_stmt_count |
|
max_seeks_for_key |
8.0 |
1 |
18446744073709500000 |
[1-18446744073709551615] |
Unlimited |
Limits the assumed maximum number of seeks when looking up rows based on a key. The MySQL optimizer assumes that no more than this number of key seeks are required to find matching rows when scanning an index, regardless of the actual cardinality of the index. By setting this to a low value (say, 100), you can force MySQL to prefer indexes over table scans. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_seeks_for_key |
|
max_sort_length |
8.0 |
1 |
1024 |
[4-8388608] |
Unlimited |
The number of bytes to use when sorting data values. The server uses only the first max_sort_length bytes of each value and ignores the rest. Consequently, values that differ only after the first max_sort_length bytes are treated as equal for GROUP BY, ORDER BY, and DISTINCT operations. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_sort_length |
|
max_sp_recursion_depth |
8.0 |
1 |
0 |
[0-255] |
Unlimited |
The number of times any given stored procedure can be called recursively. The default value for this option is 0, which completely disables recursion in stored procedures. The maximum value is 255. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_sp_recursion_depth |
|
max_user_connections |
8.0 |
1 |
600 |
[10-200000] |
Unlimited |
The maximum number of concurrent connections allowed for any given MySQL user account. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_user_connections |
|
max_write_lock_count |
8.0 |
1 |
102400 |
[1-102400] |
Unlimited |
After this number of write locks, allow some read locks to run in between. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_max_write_lock_count |
|
min_examined_row_limit |
8.0 |
1 |
0 |
[0-4294967295] |
Unlimited |
Queries that examine fewer rows than this number are not logged to the slow query log. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_min_examined_row_limit |
|
myisam_sort_buffer_size |
8.0 |
1 |
262144 |
[262144-16777216] |
Unlimited |
The size of the buffer allocated when sorting MyISAM indexes with REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_myisam_sort_buffer_size |
|
mysql_native_password_proxy_users |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
This variable controls whether the mysql_native_password built-in authentication plugin supports proxy users. It has no effect unless the check_proxy_users system variable is enabled. |
|
|
net_buffer_length |
8.0 |
1 |
16384 |
[1024-1048576] |
Unlimited |
Each client thread is associated with a connection buffer and a result buffer. Both start at the size specified by net_buffer_length but are dynamically enlarged up to max_allowed_packet bytes as needed. After each SQL statement, the result buffer is shrunk to net_buffer_length. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_net_buffer_length |
|
net_read_timeout |
8.0 |
1 |
30 |
[1-18446744073709551615] |
Unlimited |
The number of seconds to wait for more data from a connection before aborting the read. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_net_read_timeout |
|
net_retry_count |
8.0 |
1 |
10 |
[1-4294967295] |
Unlimited |
If a read or write on a communication port is interrupted, retry this many times before giving up. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_net_retry_count |
|
net_write_timeout |
8.0 |
1 |
60 |
[1-18446744073709551615] |
Unlimited |
The number of seconds to wait for a block to be written to a connection before aborting the write. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_net_write_timeout |
|
ngram_token_size |
8.0 |
0 |
2 |
[0-20] |
Unlimited |
Defines the n-gram token size for the n-gram full-text parser. The ngram_token_size option is read-only and can only be modified at startup. The default value is 2 (bigram). The maximum value is 10. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_ngram_token_size |
|
open_files_limit |
8.0 |
0 |
655350 |
[1-2147483647] |
Unlimited |
If this value is not 0, mysqld uses this value to reserve file descriptors for use with setrlimit(). |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_open_files_limit |
|
optimizer_prune_level |
8.0 |
1 |
1 |
[0|1] |
Unlimited |
Controls the heuristics applied during query optimization to prune less-promising partial plans from the optimizer search space. A value of 0 disables the heuristics, causing the optimizer to perform an exhaustive search. A value of 1 causes the optimizer to prune plans based on the number of rows retrieved by intermediate plans. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_optimizer_prune_level |
|
optimizer_search_depth |
8.0 |
1 |
62 |
[0-62] |
Unlimited |
The maximum search depth performed by the query optimizer. Values greater than the number of relations in a query result in a better query plan but take longer to generate an execution plan for a query. Values less than the number of relations in a query return an execution plan more quickly, but the resulting plan may be far from optimal. If set to 0, the system automatically picks a reasonable value. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_optimizer_search_depth |
|
optimizer_trace_limit |
8.0 |
1 |
1 |
[0-4294967295] |
Unlimited |
The maximum number of optimizer traces to display. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_optimizer_trace_limit |
|
optimizer_trace_max_mem_size |
8.0 |
1 |
16384 |
[0-4294967295] |
Unlimited |
The maximum cumulative size of stored optimizer traces. |
|
|
optimizer_trace_offset |
8.0 |
1 |
-1 |
[-2147483648-2147483647] |
Unlimited |
The offset of the optimizer trace to display. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_optimizer_trace_offset |
|
opt_indexstat |
8.0 |
0 |
OFF |
[ON|OFF] |
Unlimited |
Whether to enable index information statistics. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
opt_tablestat |
8.0 |
0 |
OFF |
[ON|OFF] |
Unlimited |
Whether to open table information statistics. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
performance_point_iostat_interval |
8.0 |
1 |
2 |
[0-60] |
Unlimited |
The interval for collecting I/O statistics, in milliseconds. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
performance_point_iostat_volume_size |
8.0 |
0 |
10000 |
[0-100000] |
Unlimited |
The maximum capacity for collecting I/O statistics, in number of slots. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
performance_point_lock_rwlock_enabled |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
Used to enable or disable the performance optimization feature for read/write locks. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html |
|
performance_schema |
8.0 |
0 |
{LEAST(DBInstanceClassMemory/68719476736, 1)} |
[0-1] |
Unlimited |
The value of this variable is ON or OFF, indicating whether the Performance Schema is enabled. The default value is ON. At server startup, you can specify this variable with no value or a value of ON or 1 to enable it, or with a value of OFF or 0 to disable it. |
|
|
preload_buffer_size |
8.0 |
1 |
32768 |
[1024-1073741824] |
Unlimited |
The size of the buffer allocated when preloading indexes. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_preload_buffer_size |
|
query_alloc_block_size |
8.0 |
1 |
8192 |
[1024-16384] |
Unlimited |
The allocation size of memory blocks allocated for creating objects during statement parsing and execution. Unit: bytes. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_query_alloc_block_size |
|
query_prealloc_size |
8.0 |
1 |
8192 |
[8192-1048576] |
Unlimited |
The size of the persistent buffer used during statement parsing and execution. This buffer is not freed between statements. If you are running complex queries, a larger query_prealloc_size value may help improve performance, as it can reduce the need for the server to perform memory allocation during query execution operations. Unit: bytes. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_query_prealloc_size |
|
range_alloc_block_size |
8.0 |
1 |
4096 |
[4096-18446744073709551615] |
Unlimited |
The size of blocks allocated when performing range optimization. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_range_alloc_block_size |
|
range_optimizer_max_mem_size |
8.0 |
1 |
8388608 |
[0-18446744073709551615] |
Unlimited |
The memory consumption limit for the range optimizer. A value of 0 means "no limit". If the optimizer considers an execution plan that uses a range access method, but it estimates that the amount of memory required for this method would exceed the limit, the plan is abandoned and other plans are considered. |
|
|
read_buffer_size |
8.0 |
1 |
{LEAST(DBInstanceClassMemory/1048576*128, 262144)} |
[8200-2147479552] |
Unlimited |
Each thread that performs a sequential scan of a MyISAM table allocates a buffer of this size (in bytes) for each table it scans. If it is set to a value that is not a multiple of 4 KB, its value is rounded down to the nearest multiple of 4 KB. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_buffer_size |
|
read_rnd_buffer_size |
8.0 |
1 |
360448 |
[8200-2147483647] |
Unlimited |
This variable is used for reads from MyISAM tables and, for any storage engine, for multi-range read optimization. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_rnd_buffer_size |
|
regexp_stack_limit |
8.0 |
1 |
8000000 |
[0-2147483647] |
Unlimited |
The maximum available memory in bytes for the internal stack used by regular expression matching operations performed by REGEXP_LIKE() and similar functions. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_regexp_stack_limit |
|
regexp_time_limit |
8.0 |
1 |
32 |
[0-2147483647] |
Unlimited |
The time limit for regular expression matching operations performed by REGEXP_LIKE() and similar functions. The limit represents the maximum number of steps the matching engine performs and only indirectly affects execution time. Typically, it is on the order of milliseconds. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_regexp_time_limit |
|
rpl_read_size |
8.0 |
1 |
8192 |
[8192-4294959104] |
Unlimited |
The rpl_read_size system variable controls the minimum amount of data in bytes to be read from binary log files and relay log files. If disk I/O activity for these files is affecting the performance of the database, increasing the read size may reduce file reads and I/O interruptions because the file data is not currently cached by the operating system. |
https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#sysvar_rpl_read_size |
|
schema_definition_cache |
8.0 |
1 |
256 |
[256-524288] |
Unlimited |
Defines a limit on the number of schema definition objects (both used and unused) retained in the dictionary object cache. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_schema_definition_cache |
|
session_track_gtids |
8.0 |
1 |
OFF |
[OFF|OWN_GTID|ALL_GTIDS] |
Unlimited |
Controls the tracker used to capture GTIDs and return them in the OK packet. Depending on the value of this option, at the end of a transaction, the specified GTID is captured by the tracker and appended to the OK packet. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_session_track_gtids |
|
sha256_password_proxy_users |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
This variable controls whether the sha256_password built-in authentication plugin supports proxy users. It has no effect unless the check_proxy_users system variable is enabled. |
|
|
show_create_table_verbosity |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
SHOW CREATE TABLE normally does not display the ROW_FORMAT table option if the row format is the default. Enabling this variable causes SHOW CREATE TABLE to display ROW_FORMAT regardless of whether it is the default. |
|
|
show_old_temporals |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Whether SHOW CREATE TABLE output includes comments to mark temporal columns found to be in a pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without fractional-second precision support). This variable is disabled by default. If enabled, SHOW CREATE TABLE output includes comments to mark these temporal columns. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_show_old_temporals |
|
skip_show_database |
8.0 |
0 |
OFF |
[ON|OFF] |
Unlimited |
Prevents users from using the SHOW DATABASES statement if they do not have the SHOW DATABASES privilege. This feature can improve security if you are concerned about users being able to see databases belonging to other users. Its effect depends on the SHOW DATABASES privilege: If the variable value is ON, the SHOW DATABASES statement is permitted only to users who have the SHOW DATABASES privilege, and the statement displays all database names. If the value is OFF, SHOW DATABASES is permitted to all users, but displays the names of only those databases for which the user has the SHOW DATABASES or other privilege. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_skip_show_database |
|
slave_net_timeout |
8.0 |
1 |
60 |
[15-300] |
Unlimited |
The number of seconds to wait for more data from the primary server before the secondary server considers the connection broken, aborts the read, and tries to reconnect. |
https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#sysvar_slave_net_timeout |
|
slave_parallel_type |
8.0 |
0 |
LOGICAL_CLOCK |
[DATABASE|LOGICAL_CLOCK] |
Unlimited |
This variable specifies the policy for deciding which transactions to execute in parallel on the secondary server. This variable has no effect on secondary servers that are not multi-threaded. When slave_preserve_commit_order=1 is set, only LOGICAL_CLOCK can be used. |
https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#sysvar_slave_parallel_type |
|
slave_transaction_retries |
8.0 |
1 |
10 |
[0-18446744073709551615] |
Unlimited |
As of MySQL 8.0.26, slave_transaction_retries is deprecated and the alias replica_transaction_retries should be used instead. In versions prior to MySQL 8.0.26, use slave_transaction_retries. |
|
|
slave_type_conversions |
8.0 |
0 |
[s*|ALL_LOSSY|ALL_NON_LOSSY|ALL_SIGNED|ALL_UNSIGNED] |
Unlimited |
Controls the type conversion mode in effect on the secondary server when using row-based replication. |
||
|
slow_launch_time |
8.0 |
1 |
2 |
[1-1024] |
Unlimited |
If creating a thread takes more than this many seconds, the server increments the Slow_launch_threads status variable. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_slow_launch_time |
|
slow_query_log |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
Specifies the initial slow query log state. If there is no parameter or the parameter is 1, the --slow-query-log option enables the log. If omitted or with an argument of 0, the option disables the log. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_slow_query_log |
|
sort_buffer_size |
8.0 |
1 |
262144 |
[32768-4294967295] |
Unlimited |
Each session that must perform a sort allocates a buffer of this size. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sort_buffer_size |
|
sql_mode |
8.0 |
1 |
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
(s*|REAL_AS_FLOAT|PIPES_AS_CONCAT|ANSI_QUOTES|IGNORE_SPACE|ONLY_FULL_GROUP_BY|NO_UNSIGNED_SUBTRACTION|NO_DIR_IN_CREATE|ANSI|NO_AUTO_VALUE_ON_ZERO|NO_BACKSLASH_ESCAPES|STRICT_TRANS_TABLES|STRICT_ALL_TABLES|NO_ZERO_IN_DATE|NO_ZERO_DATE|ALLOW_INVALID_DATES|ERROR_FOR_DIVISION_BY_ZERO|TRADITIONAL|HIGH_NOT_PRECEDENCE|NO_ENGINE_SUBSTITUTION|PAD_CHAR_TO_FULL_LENGTH)(,REAL_AS_FLOAT|,PIPES_AS_CONCAT|,ANSI_QUOTES|,IGNORE_SPACE|,ONLY_FULL_GROUP_BY|,NO_UNSIGNED_SUBTRACTION|,NO_DIR_IN_CREATE|,ANSI|,NO_AUTO_VALUE_ON_ZERO|,NO_BACKSLASH_ESCAPES|,STRICT_TRANS_TABLES|,STRICT_ALL_TABLES|,NO_ZERO_IN_DATE|,NO_ZERO_DATE|,ALLOW_INVALID_DATES|,ERROR_FOR_DIVISION_BY_ZERO|,TRADITIONAL|,HIGH_NOT_PRECEDENCE|,NO_ENGINE_SUBSTITUTION|,PAD_CHAR_TO_FULL_LENGTH)* |
Unlimited |
The mode defines what SQL syntax MySQL should support and what kind of data validation checks it should perform. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_mode |
|
sql_require_primary_key |
8.0 |
1 |
OFF |
[ON|OFF] |
Unlimited |
Whether to enforce that statements that create new tables or alter the structure of existing tables must have a primary key. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sql_require_primary_key |
|
stored_program_cache |
8.0 |
1 |
256 |
[16-524288] |
Unlimited |
Sets a soft upper limit on the number of cached stored procedures per connection. The value of this variable is specified in units of the number of stored procedures held in the two caches that MySQL Server uses for stored procedures and stored functions, respectively. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_stored_program_cache |
|
stored_program_definition_cache |
8.0 |
1 |
256 |
[256-524288] |
Unlimited |
Defines a limit on the number of stored procedure definition objects (both used and unused) retained in the dictionary object cache. |
|
|
sync_binlog |
8.0 |
1 |
1 |
[0-4294967295] |
Unlimited |
Synchronously flushes the binary log to disk after every # writes to the file. A value of 0 (the default) disables synchronous flushing. |
https://dev.mysql.com/doc/refman/8.0/en/replication-options-binary-log.html#sysvar_sync_binlog |
|
sync_master_info |
8.0 |
1 |
10000 |
[0-18446744073709551615] |
Unlimited |
The effect of this variable in a replication secondary server depends on whether the secondary's master_info_repository is set to FILE or TABLE. |
https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#sysvar_sync_master_info |
|
sync_relay_log |
8.0 |
1 |
10000 |
[0-4294967295] |
Unlimited |
If the value of this variable is greater than 0, the MySQL server synchronizes its relay log to disk (using fdatasync()) after every sync_relay_log events are written to the relay log. Setting this variable takes effect immediately for all replication channels, including those that are running. |
https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#sysvar_sync_relay_log |
|
sync_relay_log_info |
8.0 |
1 |
10000 |
[0-18446744073709551615] |
Unlimited |
Setting this variable takes effect immediately for all replication channels, including those that are running. |
https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html#sysvar_sync_relay_log_info |
|
tablespace_definition_cache |
8.0 |
1 |
256 |
[256-524288] |
Unlimited |
Defines a limit on the number of tablespace definition objects (both used and unused) retained in the dictionary object cache. |
|
|
table_definition_cache |
8.0 |
1 |
{LEAST(DBInstanceClassMemory/1073741824*512, 8192)} |
[400-524288] |
Unlimited |
The number of table definitions (from .frm files) that can be stored in the definition cache. If you use many tables, you can create a large table definition cache to speed up the opening of tables. Unlike the normal table cache, the table definition cache takes up less space and does not use file descriptors. The minimum and default values are both 400. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_table_definition_cache |
|
table_open_cache |
8.0 |
1 |
{LEAST(DBInstanceClassMemory/1073741824*1024, 16384)} |
[1-524288] |
Unlimited |
The number of open tables for all threads. Increasing this value increases the number of file descriptors that mysqld requires. You can check whether you need to increase the table cache by checking the Opened_tables status variable. See "Server Status Variables". If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), you should increase the value of the table_open_cache variable. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_table_open_cache |
|
table_open_cache_instances |
8.0 |
0 |
16 |
[1-64] |
Unlimited |
The number of open table cache instances. To improve scalability by reducing contention among sessions, the open table cache can be partitioned into several smaller cache instances of size table_open_cache / table_open_cache_instances. A session needs to lock only one instance to access it for DML statements. This segments cache access among instances, permitting higher performance for operations that use the cache when there are many sessions accessing tables. DDL statements still require a lock on the entire cache, but these statements are much less frequent than DML statements. |
|
|
temptable_max_mmap |
8.0 |
1 |
1073741824 |
[0-18446744073709551615] |
[20210930,99999999] |
Defines the maximum amount of memory in bytes that the TempTable storage engine is permitted to allocate from memory-mapped temporary files before storing data to InnoDB internal temporary tables. A setting of 0 disables memory allocation from memory-mapped temporary files. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_mmap |
|
temptable_max_ram |
8.0 |
1 |
1073741824 |
[2097152-107374182400] |
No limit |
The maximum amount of memory in bytes that the TempTable storage engine is permitted to allocate from main memory (RAM) before it starts storing data to disk. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_temptable_max_ram |
|
thread_cache_size |
8.0 |
1 |
100 |
[0-16384] |
Unlimited |
How many threads the server should cache for reuse. When a client disconnects, the client's threads are put in the cache if there are fewer than thread_cache_size threads in the cache. Thread requests are satisfied by reusing threads taken from the cache if possible, and only when the cache is empty is a new thread created. You can increase this variable to improve performance if you have a lot of new connections. Normally, this does not provide a noticeable performance improvement if you have a good thread implementation. However, if your server sees hundreds of connections per second, you should normally set thread_cache_size high enough so that most new connections use cached threads. By examining the difference between the Connections and Threads_created status variables, you can see how efficient the thread cache is. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_thread_cache_size |
|
thread_stack |
8.0 |
0 |
1048576 |
[131072-2147483647] |
Unlimited |
The stack size for each thread. The default value of 192 KB (256 KB on 64-bit systems) is large enough for normal operation. If the thread stack size is too small, it limits the complexity of the SQL statements that the server can handle, the recursion depth of stored procedures, and other memory-consuming actions. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_thread_stack |
|
tls_version |
8.0 |
0 |
TLSv1,TLSv1.1,TLSv1.2 |
[TLSv1,TLSv1.1,TLSv1.2,TLSv1.3|TLSv1,TLSv1.1|TLSv1.2,TLSv1.3|TLSv1.3] |
Unlimited |
The protocols the server permits for encrypted connections. The value is a comma-separated list of one or more protocol names. The protocols that can be named for this variable depend on the SSL library used to compile MySQL. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tls_version |
|
tmp_table_size |
8.0 |
1 |
2097152 |
[262144-268435456] |
Unlimited |
The maximum size of internal in-memory temporary tables. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmp_table_size |
|
transaction_alloc_block_size |
8.0 |
1 |
8192 |
[1024-131072] |
Unlimited |
The amount in bytes by which to increase the per-transaction memory pool when it needs more memory. See the description for transaction_prealloc_size. |
|
|
transaction_isolation |
8.0 |
1 |
READ-COMMITTED |
[READ-UNCOMMITTED|READ-COMMITTED|REPEATABLE-READ|SERIALIZABLE] |
Unlimited |
The default transaction isolation level. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_transaction_isolation |
|
transaction_prealloc_size |
8.0 |
1 |
4096 |
[1024-131072] |
Unlimited |
There is a per-transaction memory pool from which various transaction-related memory is allocated. The initial size of the pool in bytes is transaction_prealloc_size. For each allocation that cannot be satisfied from the pool due to insufficient memory, the pool is increased by transaction_alloc_block_size bytes. When the transaction ends, the pool is truncated to transaction_prealloc_size bytes. |
|
|
transaction_write_set_extraction |
8.0 |
1 |
XXHASH64 |
[OFF|XXHASH64] |
[20210930,99999999) |
This system variable specifies the hash algorithm used for write operations extracted in a transaction. The default value is XXHASH64. |
|
|
updatable_views_with_limit |
8.0 |
1 |
YES |
[YES|NO] |
Unlimited |
This variable controls whether updates to a view can be made when the view does not contain all columns of the primary key defined in the underlying table, and the update statement includes a LIMIT clause (such updates are often generated by GUI tools). An update can be an UPDATE or DELETE statement. The primary key here refers to a primary key or a unique index in which no column can contain NULL. |
|
|
wait_timeout |
8.0 |
1 |
86400 |
[1-31536000] |
Unlimited |
The number of seconds the server waits for activity on a non-interactive connection before closing it. |
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout |
|
windowing_use_high_precision |
8.0 |
1 |
ON |
[ON|OFF] |
Unlimited |
Whether to compute windowing operations without loss of precision. |
|
|
loose_innodb_log_writer_threads |
8.0 |
1 |
ON |
[ON|OFF] |
Versions from 20201231 and later |
ON: Enables log writer threads, where a dedicated thread is responsible for writing and flushing the log. OFF: Each thread performs its own log writing and flushing operations independently. |
https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_log_writer_threads |
|
loose_log_error_suppression_list |
8.0 |
1 |
MY-010520,MY-013360 |
.* |
Unlimited |
The log_error_suppression_list system variable applies to events intended for the error log and specifies which events with a priority of WARNING or INFORMATION should be suppressed when they occur. For example, if a certain warning type is considered useless "noise" in the error log because it occurs frequently but is not of concern, it can be suppressed. |
|
|
loose_session_track_system_variables |
8.0 |
1 |
No default value |
.* |
Unlimited |
A system variable used to control whether the server tracks and notifies the client about session system variable assignments. By setting this variable, you can specify which variable assignments the server should track and notify the client of the variable name and its new value. |
|
|
loose_rpl_semi_sync_master_timeout |
8.0 |
1 |
1000 |
[0-18446744073709551615] |
Unlimited |
A value in milliseconds that controls how long the primary server waits for a commit acknowledgment from a replica before timing out. After this time, if no acknowledgment has been received, the primary server reverts to asynchronous replication. The default value is 10000 milliseconds (10 seconds). Note
This variable is available only if the semi-synchronous replication plugin is installed on the primary server side. |