Issue with global max_execution_time not taking effect

Translated
1

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

Original topic: global max_execution_time不生效问题

| username: zhimadi

[TiDB Usage Environment] Production Environment
[TiDB Version] v5.4.2
[Reproduction Path] SET GLOBAL max_execution_time=8000;
[Encountered Problem: Phenomenon and Impact]
Scheduled tasks set max_execution_time to 8000 during the day and 180000 at night. After setting successfully, the TiDB monitoring panel shows a large number of slow queries during the day with execution times greater than 20 seconds, and even some exceeding 40 seconds, causing a database crash.
mysql> SHOW GLOBAL VARIABLES LIKE ‘max_execution_time’;
±-------------------±------+
| Variable_name | Value |
±-------------------±------+
| max_execution_time | 8000 |
±-------------------±------+
1 row in set

mysql>

What could be the reason for max_execution_time not taking effect? How can we effectively kill slow queries during peak business hours? Thank you.
[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

WeChat Screenshot_20230221110546
WeChat Screenshot_202302211105461338×971 102 KB

2
| username: WalterWj | Original post link

This parameter is not recommended to be configured as global because, for example, backups or other SQL operations may require such a long time.

It is generally recommended to let the application configure session-level modifications in the JDBC connection string.

The reason it didn’t take effect: global configuration requires a reconnection to take effect. Maybe the application didn’t reconnect.

3
| username: tidb菜鸟一只 | Original post link

Uh, it is recommended to optimize the SQL rather than modify this parameter. Additionally, for global parameter changes, the session needs to reconnect to take effect.

4
| username: zhimadi | Original post link

I just found out that a project set a session-level 40 seconds when connecting in the program. Could this be the reason? Isn’t the global level supposed to override the session level?

5
| username: zhimadi | Original post link

Optimizing SQL is a long-term task :joy: Before everything is fully optimized, we can’t let the system crash while it’s being used.

6
| username: tidb菜鸟一只 | Original post link

Global is not higher than session. If the session is reset, then the current session will follow the session parameters.