title | summary | aliases | ||
---|---|---|---|---|
TiDB Configuration File |
Learn the TiDB configuration file options that are not involved in command line options. |
|
The TiDB configuration file supports more options than command-line parameters. You can download the default configuration file config.toml.example
and rename it to config.toml
. This document describes only the options that are not involved in command line options.
- Determines whether to create a separate Region for each table.
- Default value:
true
- It is recommended to set it to
false
if you need to create a large number of tables.
- The number of sessions that can execute requests concurrently.
- Type: Integer
- Default value:
1000
- Minimum value:
1
- Maximum Value (64-bit platforms):
18446744073709551615
- Maximum Value (32-bit platforms):
4294967295
- The maximum memory available for a single SQL statement.
- Default value:
1073741824
(in bytes) - Note: When you upgrade the cluster from v2.0.x or v3.0.x to v4.0.9 or later versions, the default value of this configuration is
34359738368
. - Requests that require more memory than this value are handled based on the behavior defined by
oom-action
. - This value is the initial value of the system variable
tidb_mem_quota_query
.
- Controls whether to enable the temporary storage for some operators when a single SQL statement exceeds the memory quota specified by
mem-quota-query
. - Default value:
true
- Specifies the temporary storage path for some operators when a single SQL statement exceeds the memory quota specified by
mem-quota-query
. - Default value:
<temporary directory of OS>/<OS user ID>_tidb/MC4wLjAuMDo0MDAwLzAuMC4wLjA6MTAwODA=/tmp-storage
.MC4wLjAuMDo0MDAwLzAuMC4wLjA6MTAwODA=
is theBase64
encoding result of<host>:<port>/<statusHost>:<statusPort>
. - This configuration takes effect only when
oom-use-tmp-storage
istrue
.
- Specifies the quota for the storage in
tmp-storage-path
. The unit is byte. - When a single SQL statement uses a temporary disk and the total volume of the temporary disk of the TiDB server exceeds this configuration value, the current SQL operation is cancelled and the
Out of Global Storage Quota!
error is returned. - When the value of this configuration is smaller than
0
, the above check and limit do not apply. - Default value:
-1
- When the remaining available storage in
tmp-storage-path
is lower than the value defined bytmp-storage-quota
, the TiDB server reports an error when it is started, and exits.
- Specifies what operation TiDB performs when a single SQL statement exceeds the memory quota specified by
mem-quota-query
and cannot be spilled over to disk. - Default value:
"cancel"
(In TiDB v4.0.2 and earlier versions, the default value is"log"
) - The valid options are
"log"
and"cancel"
. Whenoom-action="log"
, it prints the log only. Whenoom-action="cancel"
, it cancels the operation and outputs the log.
-
Configures the value of the
lower-case-table-names
system variable. -
Default value:
2
-
For details, see the MySQL description of this variable.
Note:
Currently, TiDB only supports setting the value of this option to
2
. This means it is case-sensitive when you save a table name, but case-insensitive when you compare table names. The comparison is based on the lower case.
- The timeout of the DDL lease.
- Default value:
45s
- Unit: second
- Determines whether to set the
KILL
statement to be MySQL compatible. - Default value:
false
- The behavior of
KILL xxx
in TiDB differs from the behavior in MySQL. TiDB requires theTIDB
keyword, namely,KILL TIDB xxx
. Ifcompatible-kill-query
is set totrue
, theTIDB
keyword is not needed. - This distinction is important because the default behavior of the MySQL command-line client, when the user hits Ctrl+C, is to create a new connection to the backend and execute the
KILL
statement in that new connection. If a load balancer or proxy has sent the new connection to a different TiDB server instance than the original session, the wrong session could be terminated, which could cause interruption to applications using the cluster. Enablecompatible-kill-query
only if you are certain that the connection you refer to in yourKILL
statement is on the same server to which you send theKILL
statement.
- Determines whether to enable the
utf8mb4
character check. When this feature is enabled, if the character set isutf8
and themb4
characters are inserted inutf8
, an error is returned. - Default value:
false
- Determines whether to treat the
utf8
character set in old tables asutf8mb4
. - Default value:
true
- Determines whether to add or remove the primary key constraint to or from a column.
- Default value:
false
- With this default setting, adding or removing the primary key constraint is not supported. You can enable this feature by setting
alter-primary-key
totrue
. However, if a table already exists before the switch is on, and the data type of its primary key column is an integer, dropping the primary key from the column is not possible even if you set this configuration item totrue
.
Note:
This configuration item has been deprecated, and currently takes effect only when the value of
@tidb_enable_clustered_index
isINT_ONLY
. If you need to add or remove the primary key, use theNONCLUSTERED
keyword instead when creating the table. For more details about the primary key of theCLUSTERED
type, refer to clustered index.
- Modifies the version string returned by TiDB in the following situations:
- When the built-in
VERSION()
function is used. - When TiDB establishes the initial connection to the client and returns the initial handshake packet with version string of the server. For details, see MySQL Initial Handshake Packet.
- When the built-in
- Default value: ""
- By default, the format of the TiDB version string is
5.7.${mysql_latest_minor_version}-TiDB-${tidb_version}
.
- Determines whether to enable the untrusted repair mode. When the
repair-mode
is set totrue
, bad tables in therepair-table-list
cannot be loaded. - Default value:
false
- The
repair
syntax is not supported by default. This means that all tables are loaded when TiDB is started.
repair-table-list
is only valid whenrepair-mode
is set totrue
.repair-table-list
is a list of bad tables that need to be repaired in an instance. An example of the list is: ["db.table1","db.table2"...].- Default value: []
- The list is empty by default. This means that there are no bad tables that need to be repaired.
- Enables or disables the new collation support.
- Default value:
false
- Note: This configuration takes effect only for the TiDB cluster that is first initialized. After the initialization, you cannot use this configuration item to enable or disable the new collation support. When a TiDB cluster is upgraded to v4.0, because the cluster has been initialized before, both
true
andfalse
values of this configuration item are taken asfalse
.
- The maximum number of concurrent client connections allowed in TiDB. It is used to control resources.
- Default value:
0
- By default, TiDB does not set limit on the number of concurrent client connections. When the value of this configuration item is greater than
0
and the number of actual client connections reaches this value, the TiDB server rejects new client connections.
- Sets the maximum allowable length of the newly created index.
- Default value:
3072
- Unit: byte
- Currently, the valid value range is
[3072, 3072*4]
. MySQL and TiDB (version < v3.0.11) do not have this configuration item, but both limit the length of the newly created index. This limit in MySQL is3072
. In TiDB (version =< 3.0.7), this limit is3072*4
. In TiDB (3.0.7 < version < 3.0.11), this limit is3072
. This configuration is added to be compatible with MySQL and earlier versions of TiDB.
- Sets the limit on the number of columns in a single table.
- Default value:
1017
- Currently, the valid value range is
[1017, 4096]
.
- Sets the limit on the number of indexes in a single table.
- Default value:
64
- Currently, the valid value range is
[64, 512]
.
- Enables or disables the telemetry collection in TiDB.
- Default value:
true
- When this configuration is set to
false
on all TiDB instances, the telemetry collection in TiDB is disabled and thetidb_enable_telemetry
system variable does not take effect. See Telemetry for details.
- Enables or disables listening on TCP4 only.
- Default value:
false
- Enabling this option is useful when TiDB is used with LVS for load balancing because the real client IP from the TCP header can be correctly parsed by the "tcp4" protocol.
- Determines whether to limit the maximum length of a single
ENUM
element and a singleSET
element. - Default value:
true
- When this configuration value is
true
, the maximum length of a singleENUM
element and a singleSET
element is 255 characters, which is compatible with MySQL 8.0. When this configuration value isfalse
, there is no limit on the length of a single element, which is compatible with TiDB (earlier than v5.0).
- Specifies the number of seconds that TiDB waits when you shut down the server, which allows the clients to disconnect.
- Default value:
0
- When TiDB is waiting for shutdown (in the grace period), the HTTP status will indicate a failure, which allows the load balancers to reroute traffic.
Configuration items related to log.
- Specifies the log output level.
- Value options:
debug
,info
,warn
,error
, andfatal
. - Default value:
info
- Specifies the log output format.
- Value options:
json
,text
andconsole
. - Default value:
text
- Determines whether to enable timestamp output in the log.
- Default value:
true
- If you set the value to
false
, the log does not output timestamp.
Note:
To be backward compatible, the initial
disable-timestamp
configuration item remains valid. But if the value ofdisable-timestamp
semantically conflicts with the value ofenable-timestamp
(for example, if bothenable-timestamp
anddisable-timestamp
are set totrue
), TiDB ignores the value fordisable-timestamp
. In later versions, thedisable-timestamp
configuration will be removed.Discard
disable-timestamp
and useenable-timestamp
which is semantically easier to understand.
- Determines whether to enable the slow query log.
- Default value:
true
- To enable the slow query log, set
enable-slow-log
totrue
. Otherwise, set it tofalse
.
- The file name of the slow query log.
- Default value:
tidb-slow.log
- The format of the slow log is updated in TiDB v2.1.8, so the slow log is output to the slow log file separately. In versions before v2.1.8, this variable is set to "" by default.
- After you set it, the slow query log is output to this file separately.
- Outputs the threshold value of consumed time in the slow log.
- Default value:
300ms
- If the value in a query is larger than the default value, it is a slow query and is output to the slow log.
- Determines whether to record execution plans in the slow log.
- Default value:
1
0
means to disable, and1
(by default) means to enable. The value of this parameter is the initial value of thetidb_record_plan_in_slow_log
system variable.
- Outputs the threshold value of the number of rows for the
expensive
operation. - Default value:
10000
- When the number of query rows (including the intermediate results based on statistics) is larger than this value, it is an
expensive
operation and outputs log with the[EXPENSIVE_QUERY]
prefix.
- The maximum length of SQL output.
- Default value:
4096
- When the length of the statement is longer than
query-log-max-len
, the statement is truncated to output.
Configuration items related to log files.
- The file name of the general log file.
- Default value: ""
- If you set it, the log is output to this file.
- The size limit of the log file.
- Default value: 300MB
- The maximum size is 4GB.
- The maximum number of days that the log is retained.
- Default value:
0
- The log is retained by default. If you set the value, the expired log is cleaned up after
max-days
.
- The maximum number of retained logs.
- Default value:
0
- All the log files are retained by default. If you set it to
7
, seven log files are retained at maximum.
Configuration items related to security.
- Determines whether to require the client to use the secure mode for data transport.
- Default value:
false
- Enables the Security Enhanced Mode (SEM).
- Default value:
false
- The status of SEM is available via the system variable
tidb_enable_enhanced_security
.
- The file path of the trusted CA certificate in the PEM format.
- Default value: ""
- If you set this option and
--ssl-cert
,--ssl-key
at the same time, TiDB authenticates the client certificate based on the list of trusted CAs specified by this option when the client presents the certificate. If the authentication fails, the connection is terminated. - If you set this option but the client does not present the certificate, the secure connection continues without client certificate authentication.
- The file path of the SSL certificate in the PEM format.
- Default value: ""
- If you set this option and
--ssl-key
at the same time, TiDB allows (but not forces) the client to securely connect to TiDB using TLS. - If the specified certificate or private key is invalid, TiDB starts as usual but cannot receive secure connection.
- The file path of the SSL certificate key in the PEM format, that is, the private key of the certificate specified by
--ssl-cert
. - Default value: ""
- Currently, TiDB does not support loading the private keys protected by passwords.
- The CA root certificate used to connect TiKV or PD with TLS.
- Default value: ""
- The path of the SSL certificate file used to connect TiKV or PD with TLS.
- Default value: ""
- The path of the SSL private key file used to connect TiKV or PD with TLS.
- Default value: ""
- Determines the encryption method used for saving the spilled files to disk.
- Default value:
"plaintext"
, which disables encryption. - Optional values:
"plaintext"
and"aes128-ctr"
- Determines whether to automatically generate the TLS certificates on startup.
- Default value:
true
Configuration items related to performance.
- The number of CPUs used by TiDB.
- Default value:
0
- The default
0
indicates using all the CPUs on the machine. You can also set it to n, and then TiDB uses n CPUs.
Warning:
server-memory-quota
is still an experimental feature. It is NOT recommended that you use it in a production environment.
- The memory usage limit of tidb-server instances. This configuration item completely supersedes the previous
max-memory
. - Default value:
0
(in bytes), which means no memory limit.
- TiDB triggers an alarm when the memory usage of tidb-server instance exceeds a certain threshold. The valid value for this configuration item ranges from
0
to1
. If it is configured as0
or1
, this alarm feature is disabled. - Default value:
0.8
- When the memory usage alarm is enabled, if
server-memory-quota
is not set, then the threshold of memory usage isthe `memory-usage-alarm-ratio` value * the system memory size
; ifserver-memory-quota
is set to a value greater than 0, then the threshold of memory usage isthe `memory-usage-alarm-ratio` value * the `server-memory-quota` value
. - When TiDB detects that the memory usage of the tidb-server instance exceeds the threshold, it considers that there might be a risk of OOM. Therefore, it records ten SQL statements with the highest memory usage, ten SQL statements with the longest running time, and the heap profile among all SQL statements currently being executed to the directory
tmp-storage-path/record
and outputs a log containing the keywordtidb-server has the risk of OOM
. - The value of this configuration item is the initial value of the system variable
tidb_memory_usage_alarm_ratio
.
- The longest time that a single transaction can hold locks. If this time is exceeded, the locks of a transaction might be cleared by other transactions so that this transaction cannot be successfully committed.
- Default value:
3600000
- Unit: Millisecond
- The transaction that holds locks longer than this time can only be committed or rolled back. The commit might not be successful.
- The number of goroutines for requests related to executing commit in the commit phase of the single transaction.
- Default value:
16
- If the transaction to commit is too large, the waiting time for the flow control queue when the transaction is committed might be too long. In this situation, you can increase the configuration value to speed up the commit.
- The maximum number of statements allowed in a single TiDB transaction.
- Default value:
5000
- If a transaction does not roll back or commit after the number of statements exceeds
stmt-count-limit
, TiDB returns thestatement count 5001 exceeds the transaction limitation, autocommit = false
error. This configuration takes effect only in the retriable optimistic transaction. If you use the pessimistic transaction or have disabled the transaction retry, the number of statements in a transaction is not limited by this configuration.
- The size limit of a single row of data in TiDB.
- Default value:
6291456
(in bytes) - The size limit of a single key-value record in a transaction. If the size limit is exceeded, TiDB returns the
entry too large
error. The maximum value of this configuration item does not exceed125829120
(120 MB). - Note that TiKV has a similar limit. If the data size of a single write request exceeds
raft-entry-max-size
, which is 8 MB by default, TiKV refuses to process this request. When a table has a row of large size, you need to modify both configurations at the same time.
- The size limit of a single transaction in TiDB.
- Default value:
104857600
(in bytes) - In a single transaction, the total size of key-value records cannot exceed this value. The maximum value of this parameter is
10737418240
(10 GB). Note that if you have used the binlog to serve the downstream consumer Kafka (such as thearbiter
cluster), the value of this parameter must be no more than1073741824
(1 GB). This is because 1 GB is the upper limit of a single message size that Kafka can process. Otherwise, an error is returned if this limit is exceeded.
- Determines whether to enable
keepalive
in the TCP layer. - Default value:
true
- Determines whether to enable TCP_NODELAY at the TCP layer. After it is enabled, TiDB disables the Nagle algorithm in the TCP/IP protocol and allows sending small data packets to reduce network latency. This is suitable for latency-sensitive applications with a small transmission volume of data.
- Default value:
true
- Default value:
true
- TiDB supports executing the
JOIN
statement without any condition (theWHERE
field) of both sides tables by default; if you set the value tofalse
, the server refuses to execute when such aJOIN
statement appears.
- The time interval of reloading statistics, updating the number of table rows, checking whether it is needed to perform the automatic analysis, using feedback to update statistics and loading statistics of columns.
- Default value:
3s
- At intervals of
stats-lease
time, TiDB checks the statistics for updates and updates them to the memory if updates exist. - At intervals of
20 * stats-lease
time, TiDB updates the total number of rows generated by DML and the number of modified rows to the system table. - At intervals of
stats-lease
, TiDB checks for tables and indexes that need to be automatically analyzed. - At intervals of
stats-lease
, TiDB checks for column statistics that need to be loaded to the memory. - At intervals of
200 * stats-lease
, TiDB writes the feedback cached in the memory to the system table. - At intervals of
5 * stats-lease
, TiDB reads the feedback in the system table, and updates the statistics cached in the memory.
- At intervals of
- When
stats-lease
is set to 0s, TiDB periodically reads the feedback in the system table, and updates the statistics cached in the memory every three seconds. But TiDB no longer automatically modifies the following statistics-related system tables:mysql.stats_meta
: TiDB no longer automatically records the number of table rows that are modified by the transaction and updates it to this system table.mysql.stats_histograms
/mysql.stats_buckets
andmysql.stats_top_n
: TiDB no longer automatically analyzes and proactively updates statistics.mysql.stats_feedback
: TiDB no longer updates the statistics of the tables and indexes according to a part of statistics returned by the queried data.
- Determines whether TiDB executes automatic analysis.
- Default value:
true
- The probability that TiDB collects the feedback statistics of each query.
- Default value:
0
- This feature is disabled by default, and it is not recommended to enable this feature. If it is enabled, TiDB collects the feedback of each query at the probability of
feedback-probability
, to update statistics.
- The maximum pieces of query feedback that can be cached in memory. Extra pieces of feedback that exceed this limit are discarded.
- Default value:
1024
- The ratio of (number of modified rows)/(total number of rows) in a table. If the value is exceeded, the system assumes that the statistics have expired and the pseudo statistics will be used.
- Default value:
0.8
- The minimum value is
0
and the maximum value is1
.
- Sets the priority for all statements.
- Default:
NO_PRIORITY
- Optional values:
NO_PRIORITY
,LOW_PRIORITY
,HIGH_PRIORITY
andDELAYED
.
- Determines whether the optimizer executes the operation that pushes down the aggregation function with
Distinct
(such asselect count(distinct a) from t
) to Coprocessors. - Default:
false
- This variable is the initial value of the system variable
tidb_opt_distinct_agg_push_down
.
- The maximum memory usage for the Least Recently Used (LRU) algorithm of the nested loop join cache (in bytes).
- Default value:
20971520
- When
nested-loop-join-cache-capacity
is set to0
, nested loop join cache is disabled by default. When the LRU size is larger than the value ofnested-loop-join-cache-capacity
, the elements in the LRU are removed.
- Determines whether to ignore the optimizer's cost estimation and to forcibly use TiFlash's MPP mode for query execution.
- Default value:
false
- This configuration item controls the initial value of
tidb_enforce_mpp
. For example, when this configuration item is set totrue
, the default value oftidb_enforce_mpp
isON
.
The Plan Cache configuration of the PREPARE
statement.
Warning:
This is still an experimental feature. It is NOT recommended that you use it in the production environment.
- Determines whether to enable Plan Cache of the
PREPARE
statement. - Default value:
false
- The number of cached statements.
- Default value:
100
- The type is
UINT
. Values less than0
are converted to large integers.
- It is used to prevent
performance.max-memory
from being exceeded. Whenmax-memory * (1 - prepared-plan-cache.memory-guard-ratio)
is exceeded, the elements in the LRU are removed. - Default value:
0.1
- The minimum value is
0
; the maximum value is1
.
- The maximum number of connections established with each TiKV.
- Default value:
4
- The
keepalive
time interval of the RPC connection between TiDB and TiKV nodes. If there is no network packet within the specified time interval, the gRPC client executesping
command to TiKV to see if it is alive. - Default:
10
- unit: second
- The timeout of the RPC
keepalive
check between TiDB and TiKV nodes. - Default value:
3
- unit: second
- The maximum timeout when executing a transaction commit.
- Default value:
41s
- It is required to set this value larger than twice of the Raft election timeout.
- The maximum number of RPC packets sent in batch. If the value is not
0
, theBatchCommands
API is used to send requests to TiKV, and the RPC latency can be reduced in the case of high concurrency. It is recommended that you do not modify this value. - Default value:
128
- Waits for
max-batch-wait-time
to encapsulate the data packets into a large packet in batch and send it to the TiKV node. It is valid only when the value oftikv-client.max-batch-size
is greater than0
. It is recommended not to modify this value. - Default value:
0
- unit: nanoseconds
- The maximum number of packets sent to TiKV in batch. It is recommended not to modify this value.
- Default value:
8
- If the value is
0
, this feature is disabled.
- The threshold of the TiKV load. If the TiKV load exceeds this threshold, more
batch
packets are collected to relieve the pressure of TiKV. It is valid only when the value oftikv-client.max-batch-size
is greater than0
. It is recommended not to modify this value. - Default value:
200
This section introduces configuration items related to the Coprocessor Cache feature.
- The total size of the cached data. When the cache space is full, old cache entries are evicted. When the value is
0.0
, the Coprocessor Cache feature is disabled. - Default value:
1000.0
- Unit: MB
- Type: Float
Configuration related to the transaction latch. It is recommended to enable it when many local transaction conflicts occur.
- Determines whether to enable the memory lock of transactions.
- Default value:
false
- The number of slots corresponding to Hash, which automatically adjusts upward to an exponential multiple of 2. Each slot occupies 32 Bytes of memory. If set too small, it might result in slower running speed and poor performance in the scenario where data writing covers a relatively large range (such as importing data).
- Default value:
2048000
Configurations related to TiDB Binlog.
- Enables or disables binlog.
- Default value:
false
- The timeout of writing binlog into Pump. It is not recommended to modify this value.
- Default:
15s
- unit: second
- Determines whether to ignore errors occurred in the process of writing binlog into Pump. It is not recommended to modify this value.
- Default value:
false
- When the value is set to
true
and an error occurs, TiDB stops writing binlog and add1
to the count of thetidb_server_critical_error_total
monitoring item. When the value is set tofalse
, the binlog writing fails and the entire TiDB service is stopped.
- The network address to which binlog is exported.
- Default value: ""
- The strategy of Pump selection when binlog is exported. Currently, only the
hash
andrange
methods are supported. - Default value:
range
Configuration related to the status of TiDB service.
- Enables or disables the HTTP API service.
- Default value:
true
- Determines whether to transmit the database-related QPS metrics to Prometheus.
- Default value:
false
Configurations related to the events_statement_summary_by_digest
table.
- The maximum number of SQL categories allowed to be saved in the
events_statement_summary_by_digest
table. - Default value:
100
- The longest display length for the
DIGEST_TEXT
andQUERY_SAMPLE_TEXT
columns in theevents_statement_summary_by_digest
table. - Default value:
4096
For pessimistic transaction usage, refer to TiDB Pessimistic Transaction Mode.
- The maximum number of retries of each statement in pessimistic transactions. If the number of retries exceeds this limit, an error occurs.
- Default value:
256
- The maximum number of deadlock events that can be recorded in the
INFORMATION_SCHEMA.DEADLOCKS
table of a single TiDB server. If this table is in full volume and an additional deadlock event occurs, the earliest record in the table will be removed to make place for the newest error. - Default value:
10
- Minimum value:
0
- Maximum value:
10000
The experimental
section, introduced in v3.1.0, describes configurations related to the experimental features of TiDB.
- Determines whether to create the expression index.
- Default value:
false