Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: 多表关联 并有多表并行查询 union / distinct 查询不能下推到存储节点执行,db压力过大 查询失败数据量亿别
[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Problem Phenomenon and Impact] 2 TiFlash replicas, index created
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts, screenshot this page
[Attachment: Screenshot/Logs/Monitoring]
demo sql.sql (30.1 KB)
Let’s analyze it by splitting it into segments according to the union and see which segment is slow.
Extracting a single segment, it’s quite fast. Because we have a large member base. Fetching data from TiFlash for db computation puts a lot of pressure on the db. It seems that the db computation is single-node.
Don’t want to look at this SQL 
How about trying the MPP mode?
We don’t even want to look at it ourselves… It’s too much.
If you’re in operations, show it to the developers , if you’re in development, redevelop it .
This thing is a script pieced together by the program! It can only be given to the backend colleagues.
Whoever wrote it should optimize it.
How about directly using EXPLAIN ANALYZE to take a look? However, I do suggest rewriting this SQL.
There is no execution plan either.
Try using the EXPLAIN command to check the execution plan.
I suggest rewriting the SQL…
It definitely needs to be rewritten. Too bloated
The execution plan processes most of the calculations on the DB side. If the data volume itself is large, it will be relatively slow and may even time out.
Add all the involved tables into TiFlash, then force MPP to see how it works.
It would be best if you could provide your current execution plan.
Forcing MPP can only be applied when each subquery reads data using TiFlash. The outer deduplication still has to run on TiDB.
No, there are a few possibilities for this situation:
- Some of your tables do not have TiFlash replicas.
- Some operators in the SQL do not support pushdown, or someone has set a pushdown blacklist, and this operator is in the blacklist.
- Some small tables, usually dimension tables, have been set as cache tables.
Apart from the above situations, TiFlash can directly compute the results, and TiDB can directly fetch the settlement results.
I looked at your SQL, and many of them are repeatedly unioning the same table with different conditions, plus some joins. Under normal circumstances, it should be possible to compute directly from TiFlash.
Why is the execution plan like this when I force MPP?
|
username: TiDBer_卑微打工rer
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: