Why Adding TiFlash to a Table Causes Queries to Slow Down, When is TiFlash Best Used, and the Possibility of Inherent Query Execution Time Variability - Seeking Expert Diagnosis Through Execution Plans

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

Original topic: 给表加入tiflash造成语句变慢是什么原因,tiflash用在什么情况比较好,也有可能语句执行时间本身就时快时慢,希望大佬们通过执行计划把把脉

| username: Jjjjayson_zeng

【TiDB Usage Environment】Production Environment
【TiDB Version】

【Reproduction Path】Operations performed that led to the issue
【Encountered Issue: Issue Phenomenon and Impact】
【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】


test4.sql (13.2 KB)
Execution Plan Before.xlsx (12.3 KB)
Execution Plan After.xlsx (12.3 KB)

| username: Jolyne | Original post link

Is the statement used to query a specific column?

| username: Jjjjayson_zeng | Original post link

test4.sql (13.2 KB)

| username: 托马斯滑板鞋 | Original post link

Execution plan

| username: Jjjjayson_zeng | Original post link

Moreover, the execution of this statement is sometimes slow and sometimes fast, and I don’t know why.

| username: 托马斯滑板鞋 | Original post link

Can you open the Dashboard? Find this SQL and see how many execution plans were generated.

| username: Jjjjayson_zeng | Original post link

I feel that adding TiFlash to this table might not have a significant impact; the main issue is the instability of the query.

| username: Jjjjayson_zeng | Original post link

Not Joined.xlsx (12.3 KB)
After Joining.xlsx (12.3 KB)

| username: Jjjjayson_zeng | Original post link

Could you please take a look at the execution plan? I noticed that the CPU usage on our TiDB is not very high.

| username: 托马斯滑板鞋 | Original post link

I see that after adding it, it’s faster. Without adding, it was 3 minutes 0.3 seconds, and after adding, it’s 1 minute 2.6 seconds. :joy:

| username: Jjjjayson_zeng | Original post link

So it’s unstable. It didn’t add 50 seconds before.

| username: Jjjjayson_zeng | Original post link

I also don’t understand, the TiDB CPU isn’t maxed out, so why does running a query take 3 minutes?

| username: Jjjjayson_zeng | Original post link

Is there a problem with the SQL? It’s sometimes fast and sometimes slow.

| username: 托马斯滑板鞋 | Original post link

The execution plan hasn’t changed, but there are two points where the time consumption differs significantly:

  1. The time taken for aa and bb hash join:
    1min 14.5s and 43.1s

  2. The time taken for hashjoin_108:
    1min and 3min

Check if the memory on several nodes is exhausted.

| username: Jjjjayson_zeng | Original post link

It seems there are still more.

| username: 托马斯滑板鞋 | Original post link

What version of TiDB are you using?

| username: Jjjjayson_zeng | Original post link

The image you provided is not accessible. Please provide the text content you need translated.

| username: 托马斯滑板鞋 | Original post link

:joy:
The main thing is that I can’t understand why there’s such a big difference between the two SQLs in hash join.

└─HashJoin_108(Probe)	
238266.09	
511515	
root		
time:1m1.7s, loops:503, build_hash_table:{total:2.85s, fetch:2.81s, build:40.8ms}, probe:{concurrency:5, total:5m8.3s, max:1m1.7s, probe:2m12.8s, fetch:2m55.6s}	
inner join, equal:[eq(lbx.atdpersonpaycode.personid, lbx.atdemployeecalendar.personid) eq(lbx.atdpersonpaycode.timecarddate, lbx.atdemployeecalendar.calendardate)]	
43.6 MB	
34.6 MB

└─HashJoin_108(Probe)	
238266.09	
511519	
root		
time:2m59.4s, loops:503, build_hash_table:{total:4.45s, fetch:4.28s, build:169.3ms}, probe:{concurrency:5, total:14m57.2s, max:2m59.4s, probe:11m11.5s, fetch:3m45.6s}	
inner join, equal:[eq(lbx.atdpersonpaycode.personid, lbx.atdemployeecalendar.personid) eq(lbx.atdpersonpaycode.timecarddate, lbx.atdemployeecalendar.calendardate)]	
43.8 MB	
34.6 MB

| username: Jjjjayson_zeng | Original post link

I don’t understand either, it’s very strange.

| username: Jjjjayson_zeng | Original post link

You said that if the CPU is very slow, adding another machine would work, but there isn’t one either.