<commit_before>---
title: System Variables
summary: Use system variables to optimize performance or alter running behavior.
aliases: ['/tidb/dev/tidb-specific-system-variables','/docs/dev/system-variables/','/docs/dev/reference/configuration/tidb-server/mysql-variables/', '/docs/dev/tidb-specific-system-variables/','/docs/dev/reference/configuration/tidb-server/tidb-specific-variables/']
---

# System Variables

TiDB system variables behave similar to MySQL, in that settings may apply on a `SESSION`, `GLOBAL` or both `SESSION` and `GLOBAL` scope. Changes to `GLOBAL` scoped variables **only apply** to new connections to TiDB. Variables can be set with the [`SET` statement](/sql-statements/sql-statement-set-variable.md) on a per-session or global basis:

```sql
# 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;
```

> **Note:**
>
> TiDB differs from MySQL in that `GLOBAL` scoped variables **persist** through TiDB server restarts. Changes are also propagated to other TiDB servers every 2 seconds [TiDB #14531](https://github.com/pingcap/tidb/issues/14531).
> Additionally, TiDB presents several MySQL variables from MySQL 5.7 as both readable and settable. This is required for compatibility, since it is common for both applications and connectors to read MySQL variables. For example: JDBC connectors both read and set query cache settings, despite not relying on the behavior.

## Variable Reference

### autocommit

- Scope: SESSION | GLOBAL
- Default value: ON
- Whether automatically commit a transaction.

### ddl_slow_threshold

- Scope: SESSION
- Default value: 300
- DDL operations whose execution time exceeds the threshold value are output to the log. The unit is millisecond.

### foreign_key_checks

- Scope: NONE
- Default value: OFF
- For compatibility, TiDB returns foreign key checks as OFF.

### hostname

- Scope: NONE
- Default value: (system hostname)
- The hostname of the TiDB server as a read-only variable.

### innodb_lock_wait_timeout

- Scope: SESSION | GLOBAL
- Default value: 50
- The lock wait timeout for pessimistic transactions (default) in seconds.

### last_plan_from_cache <span class="version-mark">New in v4.0</span>

- Scope: SESSION
- Default value: 0
- This variable is used to show whether the execution plan used in the previous `execute` statement is taken directly from the plan cache.

### max_execution_time

- Scope: SESSION | GLOBAL
- Default value: 0
- The maximum execution time of a statement in milliseconds. The default value is unlimited (zero).

> **Note:**
>
> Unlike in MySQL, the `max_execution_time` system variable currently works on all kinds of statements in TiDB, not only restricted to the `SELECT` statement. The precision of the timeout value is roughly 100ms. This means the statement might not be terminated in accurate milliseconds as you specify.

### `interactive_timeout`

- Scope: SESSION | GLOBAL
- Default value: 28800
- 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()`](https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-connect.html) API using the `CLIENT_INTERACTIVE` option (for example, MySQL shell client). This variable is fully compatible with MySQL.

### 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](/sql-mode.md) for more information.

### sql_select_limit <span class="version-mark">New in v4.0.2 version</span><commit_msg>system-variable: add explaination about `last_plan_from_binding` (#3673)<commit_after>---
title: System Variables
summary: Use system variables to optimize performance or alter running behavior.
aliases: ['/tidb/dev/tidb-specific-system-variables','/docs/dev/system-variables/','/docs/dev/reference/configuration/tidb-server/mysql-variables/', '/docs/dev/tidb-specific-system-variables/','/docs/dev/reference/configuration/tidb-server/tidb-specific-variables/']
---

# System Variables

TiDB system variables behave similar to MySQL, in that settings may apply on a `SESSION`, `GLOBAL` or both `SESSION` and `GLOBAL` scope. Changes to `GLOBAL` scoped variables **only apply** to new connections to TiDB. Variables can be set with the [`SET` statement](/sql-statements/sql-statement-set-variable.md) on a per-session or global basis:

```sql
# 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;
```

> **Note:**
>
> TiDB differs from MySQL in that `GLOBAL` scoped variables **persist** through TiDB server restarts. Changes are also propagated to other TiDB servers every 2 seconds [TiDB #14531](https://github.com/pingcap/tidb/issues/14531).
> Additionally, TiDB presents several MySQL variables from MySQL 5.7 as both readable and settable. This is required for compatibility, since it is common for both applications and connectors to read MySQL variables. For example: JDBC connectors both read and set query cache settings, despite not relying on the behavior.

## Variable Reference

### autocommit

- Scope: SESSION | GLOBAL
- Default value: ON
- Whether automatically commit a transaction.

### ddl_slow_threshold

- Scope: SESSION
- Default value: 300
- DDL operations whose execution time exceeds the threshold value are output to the log. The unit is millisecond.

### foreign_key_checks

- Scope: NONE
- Default value: OFF
- For compatibility, TiDB returns foreign key checks as OFF.

### hostname

- Scope: NONE
- Default value: (system hostname)
- The hostname of the TiDB server as a read-only variable.

### innodb_lock_wait_timeout

- Scope: SESSION | GLOBAL
- Default value: 50
- The lock wait timeout for pessimistic transactions (default) in seconds.

### last_plan_from_cache <span class="version-mark">New in v4.0</span>

- Scope: SESSION
- Default value: 0
- This variable is used to show whether the execution plan used in the previous `execute` statement is taken directly from the plan cache.

### last_plan_from_binding <span class="version-mark">New in v4.0</span>

- Scope: SESSION
- Default value: 0
- This variable is used to show whether the execution plan used in the previous statement was influenced by a [plan binding](/sql-plan-management.md)

### max_execution_time

- Scope: SESSION | GLOBAL
- Default value: 0
- The maximum execution time of a statement in milliseconds. The default value is unlimited (zero).

> **Note:**
>
> Unlike in MySQL, the `max_execution_time` system variable currently works on all kinds of statements in TiDB, not only restricted to the `SELECT` statement. The precision of the timeout value is roughly 100ms. This means the statement might not be terminated in accurate milliseconds as you specify.

### `interactive_timeout`

- Scope: SESSION | GLOBAL
- Default value: 28800
- 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()`](https://dev.mysql.com/doc/c-api/5.7/en/mysql-real-connect.html) API using the `CLIENT_INTERACTIVE` option (for example, MySQL shell client). This variable is fully compatible with MySQL.

### 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](/sql-mode.md) for more information.

### sql_select_limit <span class="version-mark">New in v4.0.2 version</span>
{'fraction_non_alphanumeric': 0.08831389698736637, 'fraction_numerical': 0.01129737609329446, 'mean_word_length': 4.94011544011544, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 27, 'https://': 4, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '310073', 'n_tokens_mistral': 2421, 'n_tokens_neox': 2297, 'n_words': 923}