System Variables
TiDB system variables behave similar to MySQL with some differences, in that settings might apply on a SESSION
, INSTANCE
, or GLOBAL
scope, or on a scope that combines SESSION
, INSTANCE
, or GLOBAL
.
- Changes to
GLOBAL
scoped variables only apply to new connection sessions with TiDB. Currently active connection sessions are not affected. These changes are persisted and valid after restarts. - Changes to
INSTANCE
scoped variables apply to all active or new connection sessions with the current TiDB instance immediately after the changes are made. Other TiDB instances are not affected. These changes are not persisted and become invalid after TiDB restarts. - Variables can also have
NONE
scope. These variables are read-only, and are typically used to convey static information that will not change after a TiDB server has started.
Variables can be set with the SET
statement on a per-session, instance or global basis:
# These two identical statements change a session variable
SET tidb_distsql_scan_concurrency = 10;
SET SESSION tidb_distsql_scan_concurrency = 10;
# These two identical statements change a global variable
SET @@global.tidb_distsql_scan_concurrency = 10;
SET GLOBAL tidb_distsql_scan_concurrency = 10;
Variable Reference
allow_auto_random_explicit_insert New in v4.0.3
- Scope: SESSION | GLOBAL
- Default value:
OFF
- Determines whether to allow explicitly specifying the values of the column with the
AUTO_RANDOM
attribute in theINSERT
statement.
auto_increment_increment
- Scope: SESSION | GLOBAL
- Default value:
1
- Range:
[1, 65535]
- Controls the step size of
AUTO_INCREMENT
values to be allocated to a column. It is often used in combination withauto_increment_offset
.
auto_increment_offset
- Scope: SESSION | GLOBAL
- Default value:
1
- Range:
[1, 65535]
- Controls the initial offset of
AUTO_INCREMENT
values to be allocated to a column. This setting is often used in combination withauto_increment_increment
. For example:
mysql> CREATE TABLE t1 (a int not null primary key auto_increment);
Query OK, 0 rows affected (0.10 sec)
mysql> set auto_increment_offset=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set auto_increment_increment=3;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 VALUES (),(),(),();
Query OK, 4 rows affected (0.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM t1;
+----+
| a |
+----+
| 1 |
| 4 |
| 7 |
| 10 |
+----+
4 rows in set (0.00 sec)
autocommit
- Scope: SESSION | GLOBAL
- Default value:
ON
- Controls whether statements should automatically commit when not in an explicit transaction. See Transaction Overview for more information.
block_encryption_mode
- Scope: SESSION | GLOBAL
- Default value:
aes-128-ecb
- Defines the encryption mode for the
AES_ENCRYPT()
andAES_DECRYPT()
functions.
character_set_client
- Scope: SESSION | GLOBAL
- Default value:
utf8mb4
- The character set for data sent from the client. See Character Set and Collation for details on the use of character sets and collations in TiDB. It is recommended to use
SET NAMES
to change the character set when needed.
character_set_connection
- Scope: SESSION | GLOBAL
- Default value:
utf8mb4
- The character set for string literals that do not have a specified character set.
character_set_database
- Scope: SESSION | GLOBAL
- Default value:
utf8mb4
- This variable indicates the character set of the default database in use. It is NOT recommended to set this variable. When a new default database is selected, the server changes the variable value.
character_set_results
- Scope: SESSION | GLOBAL
- Default value:
utf8mb4
- The character set that is used when data is sent to the client.
character_set_server
- Scope: SESSION | GLOBAL
- Default value:
utf8mb4
- The default character set for the server.
collation_connection
- Scope: SESSION | GLOBAL
- Default value:
utf8mb4_bin
- This variable indicates the collation for string literals that do not have a specified collation.
collation_database
- Scope: SESSION | GLOBAL
- Default value:
utf8mb4_bin
- This variable indicates the collation of the default database in use. It is NOT recommended to set this variable. When a new default database is selected, the server changes the variable value.
collation_server
- Scope: SESSION | GLOBAL
- Default value:
utf8mb4_bin
- The default collation for the server.
cte_max_recursion_depth
- Scope: SESSION | GLOBAL
- Default value:
1000
- Range:
[0, 4294967295]
- Controls the maximum recursion depth in Common Table Expressions.
datadir
- Scope: NONE
- Default value: /tmp/tidb
- This variable indicates the location where data is stored. This location can be a local path or point to a PD server if the data is stored on TiKV.
- A value in the format of
ip_address:port
indicates the PD server that TiDB connects to on startup.
ddl_slow_threshold
- Scope: INSTANCE
- Default value:
300
- DDL operations whose execution time exceeds the threshold value are output to the log. The unit is millisecond.
default_authentication_plugin
- Scope: GLOBAL
- Default value:
mysql_native_password
- Possible values:
mysql_native_password
,caching_sha2_password
- This variable sets the authentication method that the server advertises when the server-client connection is being established. Possible values for this variable are documented in Authentication plugin status.
- Value options:
mysql_native_password
andcaching_sha2_password
. For more details, see Authentication plugin status.
default_week_format
- Scope: SESSION | GLOBAL
- Default value:
0
- Range:
[0, 7]
- Sets the week format used by the
WEEK()
function.
error_count
- Scope: NONE
- Type: Integer
- A read-only variable that indicates the number of errors that resulted from the last statement that generated messages.
foreign_key_checks
- Scope: SESSION | GLOBAL
- Default value:
OFF
- For compatibility, TiDB returns foreign key checks as
OFF
.
group_concat_max_len
- Scope: SESSION | GLOBAL
- Default value:
1024
- Range:
[4, 18446744073709551615]
- The maximum buffer size for items in the
GROUP_CONCAT()
function.
have_openssl
- Scope: NONE
- Default value:
DISABLED
- A read-only variable for MySQL compatibility. Set to
YES
by the server when the server has TLS enabled.
have_ssl
- Scope: NONE
- Default value:
DISABLED
- A read-only variable for MySQL compatibility. Set to
YES
by the server when the server has TLS enabled.
hostname
- Scope: NONE
- Default value: (system hostname)
- The hostname of the TiDB server as a read-only variable.
init_connect
- Scope: GLOBAL
- Default value: ""
- The
init_connect
feature permits a SQL statement to be automatically executed when you first connect to a TiDB server. If you have theCONNECTION_ADMIN
orSUPER
privileges, thisinit_connect
statement will not be executed. If theinit_connect
statement results in an error, your user connection will be terminated.
innodb_lock_wait_timeout
- Scope: SESSION | GLOBAL
- Default value:
50
- Range:
[1, 1073741824]
- The lock wait timeout for pessimistic transactions (default) in seconds.
interactive_timeout
- Scope: SESSION | GLOBAL
- Default value:
28800
- Range:
[1, 31536000]
- Unit: Seconds
- This variable represents the idle timeout of the interactive user session, which is measured in seconds. Interactive user session refers to the session established by calling
mysql_real_connect()
API using theCLIENT_INTERACTIVE
option (for example, MySQL shell client). This variable is fully compatible with MySQL.
last_plan_from_binding New in v4.0
- Scope: SESSION
- Default value:
OFF
- This variable is used to show whether the execution plan used in the previous statement was influenced by a plan binding
last_plan_from_cache New in v4.0
- Scope: SESSION
- Default value:
OFF
- This variable is used to show whether the execution plan used in the previous
execute
statement is taken directly from the plan cache.
license
- Scope: NONE
- Default value:
Apache License 2.0
- This variable indicates the license of your TiDB server installation.
log_bin
- Scope: NONE
- Default value:
OFF
- This variable indicates whether TiDB Binlog is used.
max_allowed_packet
- Scope: GLOBAL | SESSION
- Default value:
67108864
- Range:
[1024, 1073741824]
- The maximum size of a packet for the MySQL protocol.
max_connections
- Scope: GLOBAL
- Persists to cluster: No
- Type: Integer
- Default value:
0
- Range:
[0, 100000]
- The maximum number of connections permitted for a single TiDB instance.
- The value of
0
means no limit.
max_execution_time
- Scope: SESSION | GLOBAL
- Default value:
0
- Range:
[0, 2147483647]
- Unit: Milliseconds
- The maximum execution time of a statement in milliseconds. The default value is unlimited (zero).
plugin_dir
- Scope: INSTANCE
- Default value: ""
- Indicates the directory to load plugins as specified by a command-line flag.
plugin_load
- Scope: INSTANCE
- Default value: ""
- Indicates the plugins to load when TiDB is started. These plugins are specified by a command-line flag and separated by commas.
port
- Scope: NONE
- Default value:
4000
- Range:
[0, 65535]
- The port that the
tidb-server
is listening on when speaking the MySQL protocol.
skip_name_resolve New in v5.2.0
- Scope: GLOBAL
- Default value:
OFF
- This variable controls whether the
tidb-server
instance resolves hostnames as a part of the connection handshake. - When the DNS is unreliable, you can enable this option to improve network performance.
socket
- Scope: NONE
- Default value: ""
- The local unix socket file that the
tidb-server
is listening on when speaking the MySQL protocol.
sql_log_bin
- Scope: SESSION | GLOBAL
- Default value:
ON
- Indicates whether to write changes to TiDB Binlog or not.
sql_mode
- Scope: SESSION | GLOBAL
- Default value:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
- This variable controls a number of MySQL compatibility behaviors. See SQL Mode for more information.
sql_select_limit New in v4.0.2
- Scope: SESSION | GLOBAL
- Default value:
18446744073709551615
- Range:
[0, 18446744073709551615]
- The maximum number of rows returned by the
SELECT
statements.
ssl_ca
- Scope: NONE
- Default value: ""
- The location of the certificate authority file (if there is one). The value of this variable is defined by the TiDB configuration item
ssl-ca
.
ssl_cert
- Scope: NONE
- Default value: ""
- The location of the certificate file (if there is a file) that is used for SSL/TLS connections. The value of this variable is defined by the TiDB configuration item
ssl-cert
.
ssl_key
- Scope: NONE
- Default value: ""
- The location of the private key file (if there is one) that is used for SSL/TLS connections. The value of this variable is defined by TiDB configuration item
ssl-key
.
system_time_zone
- Scope: NONE
- Default value: (system dependent)
- This variable shows the system time zone from when TiDB was first bootstrapped. See also
time_zone
.
tidb_allow_batch_cop New in v4.0
Scope: SESSION | GLOBAL
Default value:
1
Range:
[0, 2]
This variable is used to control how TiDB sends a coprocessor request to TiFlash. It has the following values:
0
: Never send requests in batches1
: Aggregation and join requests are sent in batches2
: All coprocessor requests are sent in batches
tidb_allow_fallback_to_tikv New in v5.0
- Scope: SESSION | GLOBAL
- Default value: ""
- This variable is used to specify a list of storage engines that might fall back to TiKV. If the execution of a SQL statement fails due to a failure of the specified storage engine in the list, TiDB retries executing this SQL statement with TiKV. This variable can be set to "" or "tiflash". When this variable is set to "tiflash", if TiFlash returns a timeout error (error code: ErrTiFlashServerTimeout), TiDB retries executing this SQL statement with TiKV.
tidb_allow_function_for_expression_index New in v5.2.0
- Scope: NONE
- This variable is used to show the functions that are allowed to be used for creating expression indexes.
tidb_allow_mpp New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
ON
- Controls whether to use the MPP mode of TiFlash to execute queries. The value options are as follows:
0
orOFF
, which means that the MPP mode will not be used.1
orON
, which means that the optimizer determines whether to use the MPP mode based on the cost estimation (by default).
MPP is a distributed computing framework provided by the TiFlash engine, which allows data exchange between nodes and provides high-performance, high-throughput SQL algorithms. For details about the selection of the MPP mode, refer to Control whether to select the MPP mode.
tidb_allow_remove_auto_inc New in v2.1.18 and v3.0.4
- Scope: SESSION
- Default value:
OFF
- This variable is used to set whether the
AUTO_INCREMENT
property of a column is allowed to be removed by executingALTER TABLE MODIFY
orALTER TABLE CHANGE
statements. It is not allowed by default.
tidb_analyze_version New in v5.1.0
- Scope: SESSION | GLOBAL
- Range:
[1, 2]
- Controls how TiDB collects statistics.
- The default value of
tidb_analyze_version
in each version of v5.2.x is as follows. For detailed information, see Introduction to Statistics.
Version | Default value |
---|---|
v5.2.0 - v5.2.3 | 2 , which serves as an experimental feature |
v5.2.4 and later v5.2.x versions | 1 |
tidb_auto_analyze_end_time
- Scope: GLOBAL
- Default value:
23:59 +0000
- This variable is used to restrict the time window that the automatic update of statistics is permitted. For example, to only allow automatic statistics updates between 1AM and 3AM, set
tidb_auto_analyze_start_time='01:00 +0000'
andtidb_auto_analyze_end_time='03:00 +0000'
.
tidb_auto_analyze_ratio
- Scope: GLOBAL
- Default value:
0.5
- This variable is used to set the threshold when TiDB automatically executes
ANALYZE TABLE
in a background thread to update table statistics. For example, a value of 0.5 means that auto-analyze is triggered when greater than 50% of the rows in a table have been modified. Auto-analyze can be restricted to only execute during certain hours of the day by specifyingtidb_auto_analyze_start_time
andtidb_auto_analyze_end_time
.
tidb_auto_analyze_start_time
- Scope: GLOBAL
- Default value:
00:00 +0000
- This variable is used to restrict the time window that the automatic update of statistics is permitted. For example, to only allow automatic statistics updates between 1AM and 3AM, set
tidb_auto_analyze_start_time='01:00 +0000'
andtidb_auto_analyze_end_time='03:00 +0000'
.
tidb_backoff_lock_fast
- Scope: SESSION | GLOBAL
- Default value:
100
- Range:
[1, 2147483647]
- This variable is used to set the
backoff
time when the read request meets a lock.
tidb_backoff_weight
Scope: SESSION | GLOBAL
Default value:
2
Range:
[1, 2147483647]
This variable is used to increase the weight of the maximum time of TiDB
backoff
, that is, the maximum retry time for sending a retry request when an internal network or other component (TiKV, PD) failure is encountered. This variable can be used to adjust the maximum retry time and the minimum value is 1.For example, the base timeout for TiDB to take TSO from PD is 15 seconds. When
tidb_backoff_weight = 2
, the maximum timeout for taking TSO is: base time * 2 = 30 seconds.In the case of a poor network environment, appropriately increasing the value of this variable can effectively alleviate error reporting to the application end caused by timeout. If the application end wants to receive the error information more quickly, minimize the value of this variable.
tidb_broadcast_join_threshold_count New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
10240
- Range:
[0, 9223372036854775807]
- The unit of the variable is rows. If the objects of the join operation belong to a subquery, the optimizer cannot estimate the size of the subquery result set. In this situation, the size is determined by the number of rows in the result set. If the estimated number of rows in the subquery is less than the value of this variable, the Broadcast Hash Join algorithm is used. Otherwise, the Shuffled Hash Join algorithm is used.
tidb_broadcast_join_threshold_size New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
104857600
(100 MiB) - Range:
[0, 9223372036854775807]
- Unit: Bytes
- If the table size is less than the value of the variable, the Broadcast Hash Join algorithm is used. Otherwise, the Shuffled Hash Join algorithm is used.
tidb_build_stats_concurrency
- Scope: SESSION | GLOBAL
- Default value:
4
- This variable is used to set the concurrency of executing the
ANALYZE
statement. - When the variable is set to a larger value, the execution performance of other queries is affected.
tidb_capture_plan_baselines New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable is used to control whether to enable the baseline capturing feature. This feature depends on the statement summary, so you need to enable the statement summary before you use baseline capturing.
- After this feature is enabled, the historical SQL statements in the statement summary are traversed periodically, and bindings are automatically created for SQL statements that appear at least twice.
tidb_check_mb4_value_in_utf8
- Scope: INSTANCE
- Default value:
ON
- This variable is used to enforce that the
utf8
character set only stores values from the Basic Multilingual Plane (BMP). To store characters outside the BMP, it is recommended to use theutf8mb4
character set. - You might need to disable this option when upgrading your cluster from an earlier version of TiDB where the
utf8
checking was more relaxed. For details, see FAQs After Upgrade.
tidb_checksum_table_concurrency
- Scope: SESSION
- Default value:
4
- This variable is used to set the scan index concurrency of executing the
ADMIN CHECKSUM TABLE
statement. - When the variable is set to a larger value, the execution performance of other queries is affected.
tidb_config
- Scope: SESSION
- Default value: ""
- This variable is read-only. It is used to obtain the configuration information of the current TiDB server.
tidb_constraint_check_in_place
Scope: SESSION | GLOBAL
Default value:
OFF
This setting only applies to optimistic transactions. When this variable is set to
OFF
, checking for duplicate values in UNIQUE indexes is deferred until the transaction commits. This helps improve performance, but might be an unexpected behavior for some applications. See Constraints for details.When set to zero and using optimistic transactions:
tidb> create table t (i int key); tidb> insert into t values (1); tidb> begin optimistic; tidb> insert into t values (1); Query OK, 1 row affected tidb> commit; -- Check only when a transaction is committed. ERROR 1062 : Duplicate entry '1' for key 'PRIMARY'When set to 1 and using optimistic transactions:
tidb> set @@tidb_constraint_check_in_place=1; tidb> begin optimistic; tidb> insert into t values (1); ERROR 1062 : Duplicate entry '1' for key 'PRIMARY'
Constraint checking is always performed in place for pessimistic transactions (default).
tidb_current_ts
- Scope: SESSION
- Default value:
0
- This variable is read-only. It is used to obtain the timestamp of the current transaction.
tidb_ddl_error_count_limit
- Scope: GLOBAL
- Default value:
512
- Range:
[0, 9223372036854775807]
- This variable is used to set the number of retries when the DDL operation fails. When the number of retries exceeds the parameter value, the wrong DDL operation is canceled.
tidb_ddl_reorg_batch_size
- Scope: GLOBAL
- Default value:
256
- Range:
[32, 10240]
- This variable is used to set the batch size during the
re-organize
phase of the DDL operation. For example, when TiDB executes theADD INDEX
operation, the index data needs to backfilled bytidb_ddl_reorg_worker_cnt
(the number) concurrent workers. Each worker backfills the index data in batches.- If many updating operations such as
UPDATE
andREPLACE
exist during theADD INDEX
operation, a larger batch size indicates a larger probability of transaction conflicts. In this case, you need to adjust the batch size to a smaller value. The minimum value is 32. - If the transaction conflict does not exist, you can set the batch size to a large value. The maximum value is 10240. This can increase the speed of the backfilling data, but the write pressure on TiKV also becomes higher.
- If many updating operations such as
tidb_ddl_reorg_priority
- Scope: SESSION
- Default value:
PRIORITY_LOW
- This variable is used to set the priority of executing the
ADD INDEX
operation in there-organize
phase. - You can set the value of this variable to
PRIORITY_LOW
,PRIORITY_NORMAL
orPRIORITY_HIGH
.
tidb_ddl_reorg_worker_cnt
- Scope: GLOBAL
- Default value:
4
- Range:
[1, 128]
- This variable is used to set the concurrency of the DDL operation in the
re-organize
phase.
tidb_disable_txn_auto_retry
Scope: SESSION | GLOBAL
Default value:
ON
This variable is used to set whether to disable the automatic retry of explicit optimistic transactions. The default value of
ON
means that transactions will not automatically retry in TiDB andCOMMIT
statements might return errors that need to be handled in the application layer.Setting the value to
OFF
means that TiDB will automatically retry transactions, resulting in fewer errors fromCOMMIT
statements. Be careful when making this change, because it might result in lost updates.This variable does not affect automatically committed implicit transactions and internally executed transactions in TiDB. The maximum retry count of these transactions is determined by the value of
tidb_retry_limit
.For more details, see limits of retry.
This variable only applies to optimistic transactions, not to pessimistic transactions. The number of retries for pessimistic transactions is controlled by
max_retry_count
.
tidb_distsql_scan_concurrency
- Scope: SESSION | GLOBAL
- Default value:
15
- Range:
[1, 2147483647]
- This variable is used to set the concurrency of the
scan
operation. - Use a bigger value in OLAP scenarios, and a smaller value in OLTP scenarios.
- For OLAP scenarios, the maximum value cannot exceed the number of CPU cores of all the TiKV nodes.
- If a table has a lot of partitions, you can reduce the variable value appropriately to avoid TiKV becoming out of memory (OOM).
tidb_dml_batch_size
- Scope: SESSION | GLOBAL
- Default value:
0
- Range:
[0, 2147483647]
- When this value is greater than
0
, TiDB will batch commit statements such asINSERT
orLOAD DATA
into smaller transactions. This reduces memory usage and helps ensure that thetxn-total-size-limit
is not reached by bulk modifications. - Only the value
0
provides ACID compliance. Setting this to any other value will break the atomicity and isolation guarantees of TiDB.
tidb_enable_1pc New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
ON
- This variable is used to specify whether to enable the one-phase commit feature for transactions that only affect one Region. Compared with the often-used two-phase commit, one-phase commit can greatly reduce the latency of transaction commit and increase the throughput.
tidb_enable_amend_pessimistic_txn New in v4.0.7
Scope: SESSION | GLOBAL
Default value:
OFF
This variable is used to control whether to enable the
AMEND TRANSACTION
feature. If you enable theAMEND TRANSACTION
feature in a pessimistic transaction, when concurrent DDL operations and SCHEMA VERSION changes exist on tables associated with this transaction, TiDB attempts to amend the transaction. TiDB corrects the transaction commit to make the commit consistent with the latest valid SCHEMA VERSION so that the transaction can be successfully committed without getting theInformation schema is changed
error. This feature is effective on the following concurrent DDL operations:ADD COLUMN
orDROP COLUMN
operations.MODIFY COLUMN
orCHANGE COLUMN
operations which increase the length of a field.ADD INDEX
orDROP INDEX
operations in which the index column is created before the transaction is opened.
tidb_enable_async_commit New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
ON
- This variable controls whether to enable the async commit feature for the second phase of the two-phase transaction commit to perform asynchronously in the background. Enabling this feature can reduce the latency of transaction commit.
tidb_enable_auto_increment_in_generated
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable is used to determine whether to include the
AUTO_INCREMENT
columns when creating a generated column or an expression index.
tidb_enable_change_multi_schema
- Scope: GLOBAL
- Persists to cluster: Yes
- Type: Boolean
- Default value:
OFF
- This variable is used to control whether multiple columns or indexes can be altered in one
ALTER TABLE
statement. When the value of this variable isON
, only the following multi-schema changes are supported:- Add multiple columns. For example,
ALTER TABLE t ADD COLUMN c1 INT, ADD COLUMN c2 INT;
. - Drop multiple columns. For example,
ALTER TABLE t DROP COLUMN c1, DROP COLUMN c2;
. - Drop multiple indexes. For example,
ALTER TABLE t DROP INDEX i1, DROP INDEX i2;
. - Drop a column covered by a single-column index. For example,
ALTER TABLE t DROP COLUMN c1
, in which the schema containsINDEX idx(c1)
.
- Add multiple columns. For example,
tidb_enable_cascades_planner
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable is used to control whether to enable the cascades planner.
tidb_enable_chunk_rpc New in v4.0
- Scope: SESSION
- Default value:
ON
- This variable is used to control whether to enable the
Chunk
data encoding format in Coprocessor.
tidb_enable_clustered_index New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
INT_ONLY
- Possible values:
OFF
,ON
,INT_ONLY
- This variable is used to control whether to create the primary key as a clustered index by default. "By default" here means that the statement does not explicitly specify the keyword
CLUSTERED
/NONCLUSTERED
. Supported values areOFF
,ON
, andINT_ONLY
:OFF
indicates that primary keys are created as non-clustered indexes by default.ON
indicates that primary keys are created as clustered indexes by default.INT_ONLY
indicates that the behavior is controlled by the configuration itemalter-primary-key
. Ifalter-primary-key
is set totrue
, all primary keys are created as non-clustered indexes by default. If it is set tofalse
, only the primary keys which consist of an integer column are created as clustered indexes.
tidb_enable_collect_execution_info
- Scope: INSTANCE
- Default value:
ON
- This variable controls whether to record the execution information of each operator in the slow query log.
tidb_enable_enhanced_security
- Scope: NONE
- Default value:
OFF
- This variable indicates whether the TiDB server you are connected to has the Security Enhanced Mode (SEM) enabled. To change its value, you need to modify the value of
enable-sem
in your TiDB server configuration file and restart the TiDB server. - SEM is inspired by the design of systems such as Security-Enhanced Linux. It reduces the abilities of users with the MySQL
SUPER
privilege and instead requiresRESTRICTED
fine-grained privileges to be granted as a replacement. These fine-grained privileges include:RESTRICTED_TABLES_ADMIN
: The ability to write data to system tables in themysql
schema and to see sensitive columns oninformation_schema
tables.RESTRICTED_STATUS_ADMIN
: The ability to see sensitive variables in the commandSHOW STATUS
.RESTRICTED_VARIABLES_ADMIN
: The ability to see and set sensitive variables inSHOW [GLOBAL] VARIABLES
andSET
.RESTRICTED_USER_ADMIN
: The ability to prevent other users from making changes or dropping a user account.
tidb_enable_fast_analyze
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable is used to set whether to enable the statistics
Fast Analyze
feature. - If the statistics
Fast Analyze
feature is enabled, TiDB randomly samples about 10,000 rows of data as statistics. When the data is distributed unevenly or the data size is small, the statistics accuracy is low. This might lead to a non-optimal execution plan, for example, selecting a wrong index. If the execution time of the regularAnalyze
statement is acceptable, it is recommended to disable theFast Analyze
feature.
tidb_enable_index_merge New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable is used to control whether to enable the index merge feature.
tidb_enable_list_partition New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable is used to set whether to enable the
LIST (COLUMNS) TABLE PARTITION
feature.
tidb_enable_noop_functions New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
OFF
- By default, TiDB returns an error when you attempt to use the syntax for functionality that is not yet implemented. When the variable value is set to
ON
, TiDB silently ignores such cases of unavailable functionality, which is helpful if you cannot make changes to the SQL code. - Enabling
noop
functions controls the following behaviors:get_lock
andrelease_lock
functionsLOCK IN SHARE MODE
syntaxSQL_CALC_FOUND_ROWS
syntaxCREATE TEMPORARY TABLE
syntaxDROP TEMPORARY TABLE
syntaxSTART TRANSACTION READ ONLY
andSET TRANSACTION READ ONLY
syntax- The
tx_read_only
,transaction_read_only
,offline_mode
,super_read_only
andread_only
system variables
tidb_enable_parallel_apply New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable controls whether to enable concurrency for the
Apply
operator. The number of concurrencies is controlled by thetidb_executor_concurrency
variable. TheApply
operator processes correlated subqueries and has no concurrency by default, so the execution speed is slow. Setting this variable value to1
can increase concurrency and speed up execution. Currently, concurrency forApply
is disabled by default.
tidb_enable_rate_limit_action
- Scope: SESSION | GLOBAL
- Default value:
ON
- This variable controls whether to enable the dynamic memory control feature for the operator that reads data. By default, this operator enables the maximum number of threads that
tidb_disql_scan_concurrency
allows to read data. When the memory usage of a single SQL statement exceedstidb_mem_quota_query
each time, the operator that reads data stops one thread. - When the operator that reads data has only one thread left and the memory usage of a single SQL statement continues to exceed
tidb_mem_quota_query
, this SQL statement triggers other memory control behaviors, such as spilling data to disk.
tidb_enable_slow_log
- Scope: INSTANCE
- Default value:
ON
- This variable is used to control whether to enable the slow log feature.
tidb_enable_stmt_summary New in v3.0.4
- Scope: SESSION | GLOBAL
- Default value:
ON
- This variable is used to control whether to enable the statement summary feature. If enabled, SQL execution information like time consumption is recorded to the
information_schema.STATEMENTS_SUMMARY
system table to identify and troubleshoot SQL performance issues.
tidb_enable_strict_double_type_check New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
ON
- This variable is used to control if tables can be created with invalid definitions of type
DOUBLE
. This setting is intended to provide an upgrade path from earlier versions of TiDB, which were less strict in validating types. - The default value of
ON
is compatible with MySQL.
For example, the type DOUBLE(10)
is now considered invalid because the precision of floating point types is not guaranteed. After changing tidb_enable_strict_double_type_check
to OFF
, the table is created:
mysql> CREATE TABLE t1 (id int, c double(10));
ERROR 1149 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
mysql> SET tidb_enable_strict_double_type_check = 'OFF';
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE t1 (id int, c double(10));
Query OK, 0 rows affected (0.09 sec)
tidb_enable_table_partition
- Scope: SESSION | GLOBAL
- Default value:
ON
- Possible values:
OFF
,ON
,AUTO
- This variable is used to set whether to enable the
TABLE PARTITION
feature:ON
indicates enabling Range partitioning, Hash partitioning, and Range column partitioning with one single column.AUTO
functions the same way asON
does.OFF
indicates disabling theTABLE PARTITION
feature. In this case, the syntax that creates a partition table can be executed, but the table created is not a partitioned one.
tidb_enable_telemetry New in v4.0.2
- Scope: GLOBAL
- Default value:
ON
- This variable is used to dynamically control whether the telemetry collection in TiDB is enabled. By setting the value to
OFF
, the telemetry collection is disabled. If theenable-telemetry
TiDB configuration item is set tofalse
on all TiDB instances, the telemetry collection is always disabled and this system variable will not take effect. See Telemetry for details.
tidb_enable_vectorized_expression New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
ON
- This variable is used to control whether to enable vectorized execution.
tidb_enable_window_function
- Scope: SESSION | GLOBAL
- Default value:
ON
- This variable is used to control whether to enable the support for window functions. Note that window functions may use reserved keywords. This might cause SQL statements that could be executed normally cannot be parsed after upgrading TiDB. In this case, you can set
tidb_enable_window_function
toOFF
.
tidb_enforce_mpp New in v5.1
- Scope: SESSION
- Default value:
OFF
- To change this default value, modify the
performance.enforce-mpp
configuration value. - Controls whether to ignore the optimizer's cost estimation and to forcibly use TiFlash's MPP mode for query execution. The value options are as follows:
0
orOFF
, which means that the MPP mode is not forcibly used (by default).1
orON
, which means that the cost estimation is ignored and the MPP mode is forcibly used. Note that this setting only takes effect whentidb_allow_mpp=true
.
MPP is a distributed computing framework provided by the TiFlash engine, which allows data exchange between nodes and provides high-performance, high-throughput SQL algorithms. For details about the selection of the MPP mode, refer to Control whether to select the MPP mode.
tidb_evolve_plan_baselines New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable is used to control whether to enable the baseline evolution feature. For detailed introduction or usage , see Baseline Evolution.
- To reduce the impact of baseline evolution on the cluster, use the following configurations:
- Set
tidb_evolve_plan_task_max_time
to limit the maximum execution time of each execution plan. The default value is 600s. - Set
tidb_evolve_plan_task_start_time
andtidb_evolve_plan_task_end_time
to limit the time window. The default values are respectively00:00 +0000
and23:59 +0000
.
- Set
tidb_evolve_plan_task_end_time New in v4.0
- Scope: GLOBAL
- Default value:
23:59 +0000
- This variable is used to set the end time of baseline evolution in a day.
tidb_evolve_plan_task_max_time New in v4.0
- Scope: GLOBAL
- Default value:
600
- Range:
[-1, 9223372036854775807]
- This variable is used to limit the maximum execution time of each execution plan in the baseline evolution feature. The unit is second.
tidb_evolve_plan_task_start_time New in v4.0
- Scope: GLOBAL
- Default value:
00:00 +0000
- This variable is used to set the start time of baseline evolution in a day.
tidb_executor_concurrency New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
5
- Range:
[1, 2147483647]
This variable is used to set the concurrency of the following SQL operators (to one value):
index lookup
index lookup join
hash join
hash aggregation
(thepartial
andfinal
phases)window
projection
tidb_executor_concurrency
incorporates the following existing system variables as a whole for easier management:
tidb_index_lookup_concurrency
tidb_index_lookup_join_concurrency
tidb_hash_join_concurrency
tidb_hashagg_partial_concurrency
tidb_hashagg_final_concurrency
tidb_projection_concurrency
tidb_window_concurrency
Since v5.0, you can still separately modify the system variables listed above (with a deprecation warning returned) and your modification only affects the corresponding single operators. After that, if you use tidb_executor_concurrency
to modify the operator concurrency, the separately modified operators will not be affected. If you want to use tidb_executor_concurrency
to modify the concurrency of all operators, you can set the values of all variables listed above to -1
.
For a system upgraded to v5.0 from an earlier version, if you have not modified any value of the variables listed above (which means that the tidb_hash_join_concurrency
value is 5
and the values of the rest are 4
), the operator concurrency previously managed by these variables will automatically be managed by tidb_executor_concurrency
. If you have modified any of these variables, the concurrency of the corresponding operators will still be controlled by the modified variables.
tidb_expensive_query_time_threshold
- Scope: INSTANCE
- Default value:
60
- Range:
[10, 2147483647]
- This variable is used to set the threshold value that determines whether to print expensive query logs. The unit is second. The difference between expensive query logs and slow query logs is:
- Slow logs are printed after the statement is executed.
- Expensive query logs print the statements that are being executed, with execution time exceeding the threshold value, and their related information.
tidb_force_priority
- Scope: INSTANCE
- Default value:
NO_PRIORITY
- This variable is used to change the default priority for statements executed on a TiDB server. A use case is to ensure that a particular user that is performing OLAP queries receives lower priority than users performing OLTP queries.
- You can set the value of this variable to
NO_PRIORITY
,LOW_PRIORITY
,DELAYED
orHIGH_PRIORITY
.
tidb_gc_concurrency New in v5.0
- Scope: GLOBAL
- Default value:
-1
- Range:
[1, 128]
- Specifies the number of threads in the Resolve Locks step of GC. A value of
-1
means that TiDB will automatically decide the number of garbage collection threads to use.
tidb_gc_enable New in v5.0
- Scope: GLOBAL
- Default value:
ON
- Enables garbage collection for TiKV. Disabling garbage collection will reduce system performance, as old versions of rows will no longer be purged.
tidb_gc_life_time New in v5.0
- Scope: GLOBAL
- Default value:
10m0s
- Range:
[10m0s, 8760h0m0s]
- The time limit during which data is retained for each GC, in the format of Go Duration. When a GC happens, the current time minus this value is the safe point.
tidb_gc_run_interval New in v5.0
- Scope: GLOBAL
- Default value:
10m0s
- Range:
[10m0s, 8760h0m0s]
- Specifies the GC interval, in the format of Go Duration, for example,
"1h30m"
, and"15m"
tidb_gc_scan_lock_mode New in v5.0
- Scope: GLOBAL
- Default value:
LEGACY
- Possible values:
PHYSICAL
,LEGACY
LEGACY
: Uses the old way of scanning, that is, disable Green GC.PHYSICAL
: Uses the physical scanning method, that is, enable Green GC.
- This variable specifies the way of scanning locks in the Resolve Locks step of GC. When the variable value is set to
LEGACY
, TiDB scans locks by Regions. When the valuePHYSICAL
is used, it enables each TiKV node to bypass the Raft layer and directly scan data, which can effectively mitigate the impact of GC wakening up all Regions when the Hibernate Region feature is enabled, thus improving the execution speed in the Resolve Locks step.
tidb_general_log
- Scope: INSTANCE
- Default value:
OFF
- This variable is used to set whether to record all SQL statements in the log. This feature is disabled by default. If maintenance personnel needs to trace all SQL statements when locating issues, they can enable this feature.
- To see all records of this feature in the log, query the
"GENERAL_LOG"
string. The following information is recorded:conn
: The ID of the current session.user
: The current session user.schemaVersion
: The current schema version.txnStartTS
: The timestamp at which the current transaction starts.forUpdateTS
: In the pessimistic transactional model,forUpdateTS
is the current timestamp of the SQL statement. When a write conflict occurs in the pessimistic transaction, TiDB retries the SQL statement currently being executed and updates this timestamp. You can configure the number of retries viamax-retry-count
. In the optimistic transactional model,forUpdateTS
is equivalent totxnStartTS
.isReadConsistency
: Indicates whether the current transactional isolation level is Read Committed (RC).current_db
: The name of the current database.txn_mode
: The transactional mode. Value options areOPTIMISTIC
andPESSIMISTIC
.sql
: The SQL statement corresponding to the current query.
tidb_hash_join_concurrency
- Scope: SESSION | GLOBAL
- Default value:
-1
- Range:
[1, 2147483647]
- This variable is used to set the concurrency of the
hash join
algorithm. - A value of
-1
means that the value oftidb_executor_concurrency
will be used instead.
tidb_hashagg_final_concurrency
- Scope: SESSION | GLOBAL
- Default value:
-1
- Range:
[1, 2147483647]
- This variable is used to set the concurrency of executing the concurrent
hash aggregation
algorithm in thefinal
phase. - When the parameter of the aggregate function is not distinct,
HashAgg
is run concurrently and respectively in two phases - thepartial
phase and thefinal
phase. - A value of
-1
means that the value oftidb_executor_concurrency
will be used instead.
tidb_hashagg_partial_concurrency
- Scope: SESSION | GLOBAL
- Default value:
-1
- Range:
[1, 2147483647]
- This variable is used to set the concurrency of executing the concurrent
hash aggregation
algorithm in thepartial
phase. - When the parameter of the aggregate function is not distinct,
HashAgg
is run concurrently and respectively in two phases - thepartial
phase and thefinal
phase. - A value of
-1
means that the value oftidb_executor_concurrency
will be used instead.
tidb_index_join_batch_size
- Scope: SESSION | GLOBAL
- Default value:
25000
- Range:
[1, 2147483647]
- This variable is used to set the batch size of the
index lookup join
operation. - Use a bigger value in OLAP scenarios, and a smaller value in OLTP scenarios.
tidb_index_lookup_concurrency
- Scope: SESSION | GLOBAL
- Default value:
-1
- Range:
[1, 2147483647]
- This variable is used to set the concurrency of the
index lookup
operation. - Use a bigger value in OLAP scenarios, and a smaller value in OLTP scenarios.
- A value of
-1
means that the value oftidb_executor_concurrency
will be used instead.
tidb_index_lookup_join_concurrency
- Scope: SESSION | GLOBAL
- Default value:
-1
- Range:
[1, 2147483647]
- This variable is used to set the concurrency of the
index lookup join
algorithm. - A value of
-1
means that the value oftidb_executor_concurrency
will be used instead.
tidb_index_lookup_size
- Scope: SESSION | GLOBAL
- Default value:
20000
- Range:
[1, 2147483647]
- This variable is used to set the batch size of the
index lookup
operation. - Use a bigger value in OLAP scenarios, and a smaller value in OLTP scenarios.
tidb_index_serial_scan_concurrency
- Scope: SESSION | GLOBAL
- Default value:
1
- Range:
[1, 2147483647]
- This variable is used to set the concurrency of the
serial scan
operation. - Use a bigger value in OLAP scenarios, and a smaller value in OLTP scenarios.
tidb_init_chunk_size
- Scope: SESSION | GLOBAL
- Default value:
32
- Range:
[1, 32]
- This variable is used to set the number of rows for the initial chunk during the execution process.
tidb_isolation_read_engines New in v4.0
- Scope: SESSION
- Default value:
tikv,tiflash,tidb
- This variable is used to set the storage engine list that TiDB can use when reading data.
tidb_low_resolution_tso
- Scope: SESSION
- Default value:
OFF
- This variable is used to set whether to enable the low precision TSO feature. After this feature is enabled, new transactions use a timestamp updated every 2 seconds to read data.
- The main applicable scenario is to reduce the overhead of acquiring TSO for small read-only transactions when reading old data is acceptable.
tidb_max_chunk_size
- Scope: SESSION | GLOBAL
- Default value:
1024
- Range:
[32, 2147483647]
- This variable is used to set the maximum number of rows in a chunk during the execution process. Setting to too large of a value may cause cache locality issues.
tidb_max_delta_schema_count New in v2.1.18 and v3.0.5
- Scope: GLOBAL
- Default value:
1024
- Range:
[100, 16384]
- This variable is used to set the maximum number of schema versions (the table IDs modified for corresponding versions) allowed to be cached. The value range is 100 ~ 16384.
tidb_mem_quota_apply_cache New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
33554432
(32 MiB) - Range:
[0, 9223372036854775807]
- Unit: Bytes
- This variable is used to set the memory usage threshold of the local cache in the
Apply
operator. - The local cache in the
Apply
operator is used to speed up the computation of theApply
operator. You can set the variable to0
to disable theApply
cache feature.
tidb_mem_quota_query
- Scope: SESSION
- Default value:
1073741824
(1 GiB) - Range:
[-1, 9223372036854775807]
- Unit: Bytes
- This variable is used to set the threshold value of memory quota for a query.
- If the memory quota of a query during execution exceeds the threshold value, TiDB performs the operation designated by the OOMAction option in the configuration file. The initial value of this variable is configured by
mem-quota-query
.
tidb_memory_usage_alarm_ratio
- Scope: INSTANCE
- Default value:
0.8
- TiDB triggers an alarm when the percentage of the memory it takes exceeds a certain threshold. For the detailed usage description of this feature, see
memory-usage-alarm-ratio
. - You can set the initial value of this variable by configuring
memory-usage-alarm-ratio
.
tidb_metric_query_range_duration New in v4.0
- Scope: SESSION
- Default value:
60
- Range:
[10, 216000]
- This variable is used to set the range duration of the Prometheus statement generated when querying METRIC_SCHEMA. The unit is second.
tidb_metric_query_step New in v4.0
- Scope: SESSION
- Default value:
60
- Range:
[10, 216000]
- This variable is used to set the step of the Prometheus statement generated when querying
METRIC_SCHEMA
. The unit is second.
tidb_multi_statement_mode New in v4.0.11
- Scope: SESSION | GLOBAL
- Default value:
OFF
- Possible values:
OFF
,ON
,WARN
- This variable controls whether to allow multiple queries to be executed in the same
COM_QUERY
call. - To reduce the impact of SQL injection attacks, TiDB now prevents multiple queries from being executed in the same
COM_QUERY
call by default. This variable is intended to be used as part of an upgrade path from earlier versions of TiDB. The following behaviors apply:
Client setting | tidb_multi_statement_mode value | Multiple statements permitted? |
---|---|---|
Multiple Statements = ON | OFF | Yes |
Multiple Statements = ON | ON | Yes |
Multiple Statements = ON | WARN | Yes |
Multiple Statements = OFF | OFF | No |
Multiple Statements = OFF | ON | Yes |
Multiple Statements = OFF | WARN | Yes (+warning returned) |
tidb_opt_agg_push_down
- Scope: SESSION
- Default value:
OFF
- This variable is used to set whether the optimizer executes the optimization operation of pushing down the aggregate function to the position before Join, Projection, and UnionAll.
- When the aggregate operation is slow in query, you can set the variable value to ON.
tidb_opt_correlation_exp_factor
- Scope: SESSION | GLOBAL
- Default value:
1
- Range:
[0, 2147483647]
- When the method that estimates the number of rows based on column order correlation is not available, the heuristic estimation method is used. This variable is used to control the behavior of the heuristic method.
- When the value is 0, the heuristic method is not used.
- When the value is greater than 0:
- A larger value indicates that an index scan will probably be used in the heuristic method.
- A smaller value indicates that a table scan will probably be used in the heuristic method.
tidb_opt_correlation_threshold
- Scope: SESSION | GLOBAL
- Default value:
0.9
- This variable is used to set the threshold value that determines whether to enable estimating the row count by using column order correlation. If the order correlation between the current column and the
handle
column exceeds the threshold value, this method is enabled.
tidb_opt_distinct_agg_push_down
- Scope: SESSION
- Default value:
OFF
- This variable is used to set whether the optimizer executes the optimization operation of pushing down the aggregate function with
distinct
(such asselect count(distinct a) from t
) to Coprocessor. - When the aggregate function with the
distinct
operation is slow in the query, you can set the variable value to1
.
In the following example, before tidb_opt_distinct_agg_push_down
is enabled, TiDB needs to read all data from TiKV and execute distinct
on the TiDB side. After tidb_opt_distinct_agg_push_down
is enabled, distinct a
is pushed down to Coprocessor, and a group by
column test.t.a
is added to HashAgg_5
.
mysql> desc select count(distinct a) from test.t;
+-------------------------+----------+-----------+---------------+------------------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------+----------+-----------+---------------+------------------------------------------+
| StreamAgg_6 | 1.00 | root | | funcs:count(distinct test.t.a)->Column#4 |
| └─TableReader_10 | 10000.00 | root | | data:TableFullScan_9 |
| └─TableFullScan_9 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+-------------------------+----------+-----------+---------------+------------------------------------------+
3 rows in set (0.01 sec)
mysql> set session tidb_opt_distinct_agg_push_down = 1;
Query OK, 0 rows affected (0.00 sec)
mysql> desc select count(distinct a) from test.t;
+---------------------------+----------+-----------+---------------+------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------+----------+-----------+---------------+------------------------------------------+
| HashAgg_8 | 1.00 | root | | funcs:count(distinct test.t.a)->Column#3 |
| └─TableReader_9 | 1.00 | root | | data:HashAgg_5 |
| └─HashAgg_5 | 1.00 | cop[tikv] | | group by:test.t.a, |
| └─TableFullScan_7 | 10000.00 | cop[tikv] | table:t | keep order:false, stats:pseudo |
+---------------------------+----------+-----------+---------------+------------------------------------------+
4 rows in set (0.00 sec)
tidb_opt_enable_correlation_adjustment
- Scope: SESSION | GLOBAL
- Default value:
ON
- This variable is used to control whether the optimizer estimates the number of rows based on column order correlation
tidb_opt_insubq_to_join_and_agg
Scope: SESSION | GLOBAL
Default value:
ON
This variable is used to set whether to enable the optimization rule that converts a subquery to join and aggregation.
For example, after you enable this optimization rule, the subquery is converted as follows:
select * from t where t.a in (select aa from t1);The subquery is converted to join as follows:
select t.* from t, (select aa from t1 group by aa) tmp_t where t.a = tmp_t.aa;If
t1
is limited to beunique
andnot null
in theaa
column. You can use the following statement, without aggregation.select t.* from t, t1 where t.a=t1.aa;
tidb_opt_limit_push_down_threshold
- Scope: SESSION | GLOBAL
- Default value:
100
- Range:
[0, 2147483647]
- This variable is used to set the threshold that determines whether to push the Limit or TopN operator down to TiKV.
- If the value of the Limit or TopN operator is smaller than or equal to this threshold, these operators are forcibly pushed down to TiKV. This variable resolves the issue that the Limit or TopN operator cannot be pushed down to TiKV partly due to wrong estimation.
tidb_opt_prefer_range_scan New in v5.0
- Scope: SESSION
- Default value:
OFF
- After you set the value of this variable to
1
, the optimizer always prefers index scans over full table scans. - In the following example, before you enable
tidb_opt_prefer_range_scan
, the TiDB optimizer performs a full table scan. After you enabletidb_opt_prefer_range_scan
, the optimizer selects an index scan.
explain select * from t where age=5;
+-------------------------+------------+-----------+---------------+-------------------+
| id | estRows | task | access object | operator info |
+-------------------------+------------+-----------+---------------+-------------------+
| TableReader_7 | 1048576.00 | root | | data:Selection_6 |
| └─Selection_6 | 1048576.00 | cop[tikv] | | eq(test.t.age, 5) |
| └─TableFullScan_5 | 1048576.00 | cop[tikv] | table:t | keep order:false |
+-------------------------+------------+-----------+---------------+-------------------+
3 rows in set (0.00 sec)
set session tidb_opt_prefer_range_scan = 1;
explain select * from t where age=5;
+-------------------------------+------------+-----------+-----------------------------+-------------------------------+
| id | estRows | task | access object | operator info |
+-------------------------------+------------+-----------+-----------------------------+-------------------------------+
| IndexLookUp_7 | 1048576.00 | root | | |
| ├─IndexRangeScan_5(Build) | 1048576.00 | cop[tikv] | table:t, index:idx_age(age) | range:[5,5], keep order:false |
| └─TableRowIDScan_6(Probe) | 1048576.00 | cop[tikv] | table:t | keep order:false |
+-------------------------------+------------+-----------+-----------------------------+-------------------------------+
3 rows in set (0.00 sec)
tidb_opt_write_row_id
- Scope: SESSION
- Default value:
OFF
- This variable is used to control whether to allow
INSERT
,REPLACE
, andUPDATE
statements to operate on the_tidb_rowid
column. This variable can be used only when you import data using TiDB tools.
tidb_partition_prune_mode New in v5.1
Currently, the dynamic pruning mode for partitioned tables is an experimental feature. It is not recommended that you use it in the production environment.
- Scope: SESSION | GLOBAL
- Default value:
static
- Specifies whether to enable
dynamic
mode for partitioned tables. For details about the dynamic pruning mode, see Dynamic Pruning Mode for Partitioned Tables.
tidb_pprof_sql_cpu New in v4.0
- Scope: INSTANCE
- Default value:
0
- Range:
[0, 1]
- This variable is used to control whether to mark the corresponding SQL statement in the profile output to identify and troubleshoot performance issues.
tidb_projection_concurrency
- Scope: SESSION | GLOBAL
- Default value:
-1
- Range:
[-1, 2147483647]
- This variable is used to set the concurrency of the
Projection
operator. - A value of
-1
means that the value oftidb_executor_concurrency
will be used instead.
tidb_query_log_max_len
- Scope: INSTANCE
- Default value:
4096
(4 KiB) - Range:
[-1, 9223372036854775807]
- Unit: Bytes
- The maximum length of the SQL statement output. When the output length of a statement is larger than the
tidb_query-log-max-len
value, the statement is truncated to output.
Usage example:
SET tidb_query_log_max_len = 20
tidb_record_plan_in_slow_log
- Scope: INSTANCE
- Default value:
ON
- This variable is used to control whether to include the execution plan of slow queries in the slow log.
tidb_redact_log
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable controls whether to hide user information in the SQL statement being recorded into the TiDB log and slow log.
- When you set the variable to
1
, user information is hidden. For example, if the executed SQL statement isinsert into t values (1,2)
, the statement is recorded asinsert into t values (?,?)
in the log.
tidb_replica_read New in v4.0
- Scope: SESSION
- Default value:
leader
- Possible values:
leader
,follower
,leader-and-follower
- This variable is used to control where TiDB reads data. Here are three options:
- leader: Read only from leader node
- follower: Read only from follower node
- leader-and-follower: Read from leader or follower node
- See follower reads for additional details.
tidb_restricted_read_only New in v5.2.0
- Scope: GLOBAL
- Default value:
0
- Value options:
0
,1
- This variable controls the read-only status of the entire cluster. If the variable is enabled (which means that the value is
1
), all TiDB servers in the entire cluster are in the read-only mode. In this case, TiDB only executes the statements that do not modify data, such asSELECT
,USE
, andSHOW
. For other statements such asINSERT
andUPDATE
, TiDB rejects executing those statements in the read-only mode. - Enabling the read-only mode using this variable only ensures that the entire cluster finally enters the read-only status. If you have changed the value of this variable in a TiDB cluster but the change has not yet propagated to other TiDB servers, the un-updated TiDB servers are still not in the read-only mode.
- When this variable is enabled, the SQL statements being executed are not affected. TiDB only performs the read-only check for the SQL statements to be executed.
- When this variable is enabled, TiDB handles the uncommitted transactions in the following ways:
- For uncommitted read-only transactions, you can commit the transactions normally.
- For uncommitted transactions that are not read-only, SQL statements that perform write operations in these transactions are rejected.
- For uncommitted read-only transactions with modified data, the commit of these transactions is rejected.
- After the read-only mode is enabled, all users (including the users with the
SUPER
privilege) cannot execute the SQL statements that might write data unless the user is explicitly granted theRESTRICTED_REPLICA_WRITER_ADMIN
privilege. - Users with
RESTRICTED_VARIABLES_ADMIN
orSUPER
privileges can modify this variable. However, if the security enhanced mode is enabled, only the users with theRESTRICTED_VARIABLES_ADMIN
privilege can modify this variable.
tidb_retry_limit
- Scope: SESSION | GLOBAL
- Default value:
10
- Range:
[-1, 9223372036854775807]
- This variable is used to set the maximum number of the retries for optimistic transactions. When a transaction encounters retryable errors (such as transaction conflicts, very slow transaction commit, or table schema changes), this transaction is re-executed according to this variable. Note that setting
tidb_retry_limit
to0
disables the automatic retry. This variable only applies to optimistic transactions, not to pessimistic transactions.
tidb_row_format_version
- Scope: GLOBAL
- Default value:
2
- Range:
[1, 2]
- Controls the format version of the newly saved data in the table. In TiDB v4.0, the new storage row format version
2
is used by default to save new data. - If you upgrade from a TiDB version earlier than 4.0.0 to 4.0.0, the format version is not changed, and TiDB continues to use the old format of version
1
to write data to the table, which means that only newly created clusters use the new data format by default. - Note that modifying this variable does not affect the old data that has been saved, but applies the corresponding version format only to the newly written data after modifying this variable.
tidb_scatter_region
- Scope: GLOBAL
- Default value:
OFF
- By default, Regions are split for a new table when it is being created in TiDB. After this variable is enabled, the newly split Regions are scattered immediately during the execution of the
CREATE TABLE
statement. This applies to the scenario where data need to be written in batches right after the tables are created in batches, because the newly split Regions can be scattered in TiKV beforehand and do not have to wait to be scheduled by PD. To ensure the continuous stability of writing data in batches, theCREATE TABLE
statement returns success only after the Regions are successfully scattered. This makes the statement's execution time multiple times longer than that when you disable this variable. - Note that if
SHARD_ROW_ID_BITS
andPRE_SPLIT_REGIONS
have been set when a table is created, the specified number of Regions are evenly split after the table creation.
tidb_skip_ascii_check New in v5.0
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable is used to set whether to skip ASCII validation.
- Validating ASCII characters affects the performance. When you are sure that the input characters are valid ASCII characters, you can set the variable value to
ON
.
tidb_skip_isolation_level_check
- Scope: SESSION | GLOBAL
- Default value:
OFF
- After this switch is enabled, if an isolation level unsupported by TiDB is assigned to
tx_isolation
, no error is reported. This helps improve compatibility with applications that set (but do not depend on) a different isolation level.
tidb> set tx_isolation='serializable';
ERROR 8048 (HY000): The isolation level 'serializable' is not supported. Set tidb_skip_isolation_level_check=1 to skip this error
tidb> set tidb_skip_isolation_level_check=1;
Query OK, 0 rows affected (0.00 sec)
tidb> set tx_isolation='serializable';
Query OK, 0 rows affected, 1 warning (0.00 sec)
tidb_skip_utf8_check
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable is used to set whether to skip UTF-8 validation.
- Validating UTF-8 characters affects the performance. When you are sure that the input characters are valid UTF-8 characters, you can set the variable value to
ON
.
tidb_slow_log_threshold
- Scope: INSTANCE
- Default value:
300
- Range:
[-1, 9223372036854775807]
- Unit: Milliseconds
- This variable outputs the threshold value of the time consumed by the slow log, and is set to 300 milliseconds by default. When the time consumed by a query is larger than this value, this query is considered as a slow query and its log is output to the slow query log. Note that when the output level of
log.level
is"debug"
, all queries are recorded in the slow query log, regardless of the setting of this variable.
Usage example:
SET tidb_slow_log_threshold = 200;
tidb_slow_query_file
- Scope: SESSION
- Default value: ""
- When
INFORMATION_SCHEMA.SLOW_QUERY
is queried, only the slow query log name set byslow-query-file
in the configuration file is parsed. The default slow query log name is "tidb-slow.log". To parse other logs, set thetidb_slow_query_file
session variable to a specific file path, and then queryINFORMATION_SCHEMA.SLOW_QUERY
to parse the slow query log based on the set file path. For details, see Identify Slow Queries.
tidb_snapshot
- Scope: SESSION
- Default value: ""
- This variable is used to set the time point at which the data is read by the session. For example, when you set the variable to "2017-11-11 20:20:20" or a TSO number like "400036290571534337", the current session reads the data of this moment.
tidb_stmt_summary_history_size New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
24
- Range:
[0, 255]
- This variable is used to set the history capacity of statement summary tables.
tidb_stmt_summary_internal_query New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
OFF
- This variable is used to control whether to include the SQL information of TiDB in statement summary tables.
tidb_stmt_summary_max_sql_length New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
4096
- Range:
[0, 2147483647]
- This variable is used to control the length of the SQL string in statement summary tables.
tidb_stmt_summary_max_stmt_count New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
3000
- Range:
[1, 32767]
- This variable is used to set the maximum number of statements that statement summary tables store in memory.
tidb_stmt_summary_refresh_interval New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
1800
- Range:
[1, 2147483647]
- This variable is used to set the refresh time of statement summary tables. The unit is second.
tidb_store_limit New in v3.0.4 and v4.0
- Scope: INSTANCE | GLOBAL
- Default value:
0
- Range:
[0, 9223372036854775807]
- This variable is used to limit the maximum number of requests TiDB can send to TiKV at the same time. 0 means no limit.
tidb_txn_mode
- Scope: SESSION | GLOBAL
- Default value:
pessimistic
- Possible values:
pessimistic
,optimistic
- This variable is used to set the transaction mode. TiDB 3.0 supports the pessimistic transactions. Since TiDB 3.0.8, the pessimistic transaction mode is enabled by default.
- If you upgrade TiDB from v3.0.7 or earlier versions to v3.0.8 or later versions, the default transaction mode does not change. Only the newly created clusters use the pessimistic transaction mode by default.
- If this variable is set to "optimistic" or "", TiDB uses the optimistic transaction mode.
tidb_use_plan_baselines New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
ON
- This variable is used to control whether to enable the execution plan binding feature. It is enabled by default, and can be disabled by assigning the
OFF
value. For the use of the execution plan binding, see Execution Plan Binding.
tidb_wait_split_region_finish
- Scope: SESSION
- Default value:
ON
- It usually takes a long time to scatter Regions, which is determined by PD scheduling and TiKV loads. This variable is used to set whether to return the result to the client after all Regions are scattered completely when the
SPLIT REGION
statement is being executed:ON
requires that theSPLIT REGIONS
statement waits until all Regions are scattered.OFF
permits theSPLIT REGIONS
statement to return before finishing scattering all Regions.
- Note that when scattering Regions, the write and read performances for the Region that is being scattered might be affected. In batch-write or data importing scenarios, it is recommended to import data after Regions scattering is finished.
tidb_wait_split_region_timeout
- Scope: SESSION
- Default value:
300
- Range:
[1, 2147483647]
- This variable is used to set the timeout for executing the
SPLIT REGION
statement. The unit is second. If a statement is not executed completely within the specified time value, a timeout error is returned.
tidb_window_concurrency New in v4.0
- Scope: SESSION | GLOBAL
- Default value:
-1
- Range:
[1, 2147483647]
- This variable is used to set the concurrency degree of the window operator.
- A value of
-1
means that the value oftidb_executor_concurrency
will be used instead.
time_zone
- Scope: SESSION | GLOBAL
- Default value:
SYSTEM
- This variable returns the current time zone. Values can be specified as either an offset such as '-8:00' or a named zone 'America/Los_Angeles'.
- The value
SYSTEM
means that the time zone should be the same as the system host, which is available via thesystem_time_zone
variable.
timestamp
- Scope: SESSION
- Default value: ""
- A non-empty value of this variable indicates the UNIX epoch that is used as the timestamp for
CURRENT_TIMESTAMP()
,NOW()
, and other functions. This variable might be used in data restore or replication.
transaction_isolation
- Scope: SESSION | GLOBAL
- Default value:
REPEATABLE-READ
- Possible values:
READ-UNCOMMITTED
,READ-COMMITTED
,REPEATABLE-READ
,SERIALIZABLE
- This variable sets the transaction isolation. TiDB advertises
REPEATABLE-READ
for compatibility with MySQL, but the actual isolation level is Snapshot Isolation. See transaction isolation levels for further details.
tx_isolation
This variable is an alias for transaction_isolation.
version
- Scope: NONE
- Default value:
5.7.25-TiDB-
(tidb version) - This variable returns the MySQL version, followed by the TiDB version. For example '5.7.25-TiDB-v4.0.0-beta.2-716-g25e003253'.
version_comment
- Scope: NONE
- Default value: (string)
- This variable returns additional details about the TiDB version. For example, 'TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible'.
wait_timeout
- Scope: SESSION | GLOBAL
- Default value:
0
- Range:
[0, 31536000]
- Unit: Seconds
- This variable controls the idle timeout of user sessions. A zero-value means unlimited.
warning_count
- Scope: SESSION
- Default value:
0
- This read-only variable indicates the number of warnings that occurred in the statement that was previously executed.
windowing_use_high_precision
- Scope: SESSION | GLOBAL
- Default value:
ON
- This variable controls whether to use the high precision mode when computing the window functions.