Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: tidb可以禁止某条sql执行吗
|
username: zzw6776
For example, if a certain slow SQL is found to be flooding the screen in the slow SQL logs, is there any good way to prevent this SQL from executing?
It seems that there is currently no solution, and various relational databases may have similar issues. In extreme cases (when the instance is overwhelmed and affects other core services), common solutions include:
These are the solutions I can think of for now. If anyone knows more, please add.
We handle such situations from the source, identifying the corresponding systems, applications, jobs, etc., and then optimizing, adjusting, or directly shutting them down.
Normally, this is how it is handled, but in cases where the developer cannot be found or it will take a long time for the developer to address the issue, a temporary emergency solution can be implemented after communicating with the developer.
You can use SQL binding to handle this, for example:
|
username: 
|
username: 
|
username: 
|
username: create global binding for
select count(1) from sbtest1 a, sbtest1 b
using
select /*+ max_execution_time(1000) */ count(1) from sbtest1 a, sbtest1 b;
mysql> select count(1) from sbtest1 a, sbtest1 b;
ERROR 1317 (70100): Query execution was interrupted
In this way, slow queries cannot run for a long time. The max_execution_time should be accurate to the millisecond level.
Write a monitoring script that kills the SQL if the execution time exceeds a certain duration.
To be honest, we can consider changing the development team~ Our primary concern here is the ability to perform hot updates and partial upgrades~~~
Let’s see what other experts suggest.
MySQL 8.0 has a firewall plugin, TiDB can consider developing in this direction in the future.
If you don’t need this statement to run, just write
create global binding for
select count(1) from sbtest1 a, sbtest1 b
using
select /*+ max_execution_time(1) */ count(1) from sbtest1 a, sbtest1 b;
As far as I know, only Exadata currently has this feature, and other databases are probably developing it gradually. You mean to blacklist this SQL and prohibit it from running, right?
I think so too. The person asking the question is probably in a company environment where development is strong, and they are unable to make changes themselves. However, they still have to handle the situation.
Currently, there is no way to prevent a specific SQL from executing in TiDB. If an unwanted SQL appears, you can only handle it through the following methods:
Generally, if it’s too slow, you can only have the developers optimize the business logic.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: