Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: tidb集群版本从v6.1.0升级到v6.5.0 sql执行时间增加
[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version] v6.5
[Reproduction Path] Operations performed that led to the issue
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots / Logs / Monitoring]
Are there any new parameters in version 6.5 that need to be enabled?
The data volume and statements have not changed, and the operations are the same.
Previously, the execution time in version 6.1 was 0.9 seconds.
Now, in version 6.5, the execution time is 2.7 seconds.
The time has increased significantly.
New XLS Worksheet.xls (28.5 KB)
sql EXPLAIN ANALYZE execution process
Is the execution plan of 6.1 the same as that of 6.5?
What operation is being performed? It requires a full table scan…
What does the SQL look like? Both tables are doing full table scans. The visible version is 775, isn’t that a bit high? Is DML very frequent?
tiflash_task:{proc max:321.1ms, min:254.7ms, avg: 287.9ms, p80:321.1ms, p95:321.1ms, iters:2063, tasks:2, threads:8}, tiflash_scan:{dtfile:{total_scanned_packs:12531, total_skipped_packs:11460, total_scanned_rows:102403734, total_skipped_rows:93566903, total_rs_index_load_time: 0ms, total_read_time: 12478ms}, total_create_snapshot_time: 0ms}
total_scanned_packs:12531
total_scanned_rows:102403734
total_read_time: 12478ms
Improve the hit rate… Reading data scans so many rows.
With TiFlash present, it will scan this much.
After canceling TiFlash, the hit rate will increase.
You need to look at the execution efficiency, whether TiKV is higher or TiFlash is higher…
The health of the table also needs attention.
This has already deviated from my original question.
My issue is that performance has degraded after the upgrade.
I want to ask if there are any parameters in 6.5 that were not present in 6.1, which could affect execution time and need to be enabled.
Specific problems require specific analysis… 
In version 6.1, does it use TiKV or TiFlash? Can the speed of index range scans using TiKV match that of previous versions? If so, you can use SPM to bind the SQL execution plan and use the read_from_storage hint to force this SQL to use TiKV.
|
username: TiDBer_Z93z8JhD
|
username: 
|
username: 
|
username: