How to Persistently Change the Value of the Session Variable @@tidb_enforce_mpp

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: @@tidb_enforce_mpp 是一个session变量我如何持久化的更改他的值

| username: TiDBer_7Q5CQdQd

@@tidb_enforce_mpp is a session variable. How can I persistently change its value?

| username: Jellybean | Original post link

tidb_enforce_mpp Introduced in Version v5.1

  • Scope: SESSION
  • Type: Boolean
  • Default Value: OFF (indicating disabled). To modify the default value of this variable, configure the performance.enforce-mpp parameter.
  • This variable is used to control whether to ignore the optimizer cost estimation and forcibly use TiFlash’s MPP mode to execute queries. The possible values are:
    • 0 or OFF, which means not to forcibly use MPP mode (default)
    • 1 or ON, which means to ignore cost estimation and forcibly use MPP mode. Note: This setting only takes effect when tidb_allow_mpp=true.

Official Documentation: 系统变量 | PingCAP 归档文档站

| username: Kongdom | Original post link

  1. Session variables cannot be persistently changed.
  2. According to the documentation, you can modify the configuration file to achieve persistent changes, specifically by configuring the performance.enforce-mpp parameter.
| username: ljluestc | Original post link

To persist changes to session variables in TiDB, you can use the SET GLOBAL command to set them as global variables. Note that not all session variables can be set as global variables, so ensure that the relevant variable supports global settings before proceeding.

To set @@tidb_enforce_mpp as a global variable, follow these steps:

  1. Open the TiDB client (e.g., using the mysql command-line tool).

  2. Use the following command to set @@tidb_enforce_mpp as a global variable and specify the desired new value:

SET GLOBAL tidb_enforce_mpp = 'new_value';

Replace ‘new_value’ with the actual value you wish to set.

After executing the above command, TiDB will persist the new value of @@tidb_enforce_mpp to ensure it remains effective after a restart.

Please note the following points:

  • Appropriate permissions are required to execute the SET GLOBAL command. Typically, this requires a user with SUPER privileges.
  • Changing certain global variables may affect the performance and behavior of the entire TiDB cluster, so ensure you understand the implications of the variable before making changes.
  • A restart of the TiDB server is required for the changes to global variables to take effect. Consider downtime in a production environment before restarting.
  • If you want these global variable changes to be automatically applied each time the TiDB server starts, you can add them to the TiDB configuration file (usually tidb.toml).
| username: zhanggame1 | Original post link

  • Scope: SESSION cannot be globally modified or persisted.
| username: Kongdom | Original post link

:yum: This looks so much like an AI response.

| username: zhanggame1 | Original post link

It feels absurd to answer that parameters can only be modified at the session level.

| username: 昵称想不起来了 | Original post link

Regarding the setting of [system variables], you can actually search directly on the official documentation System Variables page. It’s convenient and accurate!

| username: Kongdom | Original post link

Yes, it’s just too outrageous, so it feels like AI.