Abnormal SQL Execution Time

Translated
1

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

Original topic: SQL执行耗时异常

| username: CHENGX

[TiDB Usage Environment] Production Environment
[TiDB Version] v6.5.2
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Problem Phenomenon and Impact] The execution time of the same type of SQL varies greatly, sometimes taking a few hundred milliseconds and sometimes a few minutes. An example is shown in the image below.

0596c6a6a23ae806fc10e2ea0ab6d88
0596c6a6a23ae806fc10e2ea0ab6d88364×1000 56.6 KB

25dd2000f7836071569749e7cad2594
25dd2000f7836071569749e7cad2594504×1000 48.7 KB

[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page

a134387b5d887bb7fc23dbc1c967ef2
a134387b5d887bb7fc23dbc1c967ef21380×542 41.6 KB

[Attachments: Screenshots/Logs/Monitoring]

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

Try executing the two SQL statements manually on the client. Is there a significant difference in speed?

3
| username: TiDBer_小阿飞 | Original post link

How do you determine the same type of SQL?

4
| username: Fly-bird | Original post link

We have encountered this as well. Manual execution should be very fast. This is influenced by the actual execution of other SQL in the environment. What can be done is to optimize this statement as much as possible.

5
| username: WalterWj | Original post link

The same type, but different query conditions, right? Could it be because querying one day’s data versus one year’s data is causing the difference?

6
| username: CHENGX | Original post link

It’s the same SQL with different conditions.

7
| username: CHENGX | Original post link

The query conditions are all primary keys.

8
| username: yeminhua | Original post link

This depends on the execution plan. You need to manually execute it with different conditions to see. It seems that the two execution plans might differ significantly, and the amount of data scanned under different conditions could vary greatly.

9
| username: CHENGX | Original post link

It seems that the optimization for joins is not very reliable with cost model 2. I used STRAIGHT_JOIN to replace JOIN, and the SQL execution performance improved significantly. I also tried using cost model 1, and the SQL execution efficiency was better than with cost model 2.

10
| username: andone | Original post link

Update the statistics.

11
| username: Kongdom | Original post link

It would be best to send the complete execution plan. The information in the screenshot is not very comprehensive, and some information is not visible.

12
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.