Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: TiFlash开启enforce mpp参数后查询结果与tikv不一致
|
username: army
【TiDB Environment】Production environment or Test environment or POC
【TiDB Version】 5.1.1
【Encountered Problem】
【Reproduction Path】What operations were performed to encounter the problem
【Problem Phenomenon and Impact】
When the tidb_enforce_mpp parameter is enabled and disabled, the query results of the same SQL statement are different. The difference lies in the MPP’s exchangesender.
Attached are the explain analyze results for both cases.
When tidb_enforce_mpp is enabled:
±---------------------------------------±-------------±----------±------------------±--------------±---------------------------------------------------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±---------------------------------------±-------------±----------±------------------±--------------±---------------------------------------------------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
| StreamAgg_21 | 1.00 | 1 | root | | time:6.37s, loops:2 | funcs:count(1)->Column#199 | 8 Bytes | N/A |
| └─TableReader_97 | 0.25 | 0 | root | | time:6.37s, loops:1, cop_task: {num: 75, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:ExchangeSender_96 | N/A | N/A |
| └─ExchangeSender_96 | 0.25 | 1505976 | batchCop[tiflash] | | tiflash_task:{proc max:6.33s, min:6.11s, p80:6.33s, p95:6.33s, iters:5027, tasks:2, threads:41} | ExchangeType: PassThrough | N/A | N/A |
| └─HashJoin_92 | 0.25 | 1505976 | batchCop[tiflash] | | tiflash_task:{proc max:6.33s, min:6.11s, p80:6.33s, p95:6.33s, iters:5027, tasks:2, threads:41} | inner join, equal:[eq(table.uid, table.uid)] | N/A | N/A |
| ├─ExchangeReceiver_61(Build) | 0.25 | 7909364 | batchCop[tiflash] | | tiflash_task:{proc max:3.05s, min:2.97s, p80:3.05s, p95:3.05s, iters:370, tasks:2, threads:40} | | N/A | N/A |
| │ └─ExchangeSender_60 | 0.25 | 3954682 | batchCop[tiflash] | | tiflash_task:{proc max:3.17s, min:3.17s, p80:3.17s, p95:3.17s, iters:4962, tasks:2, threads:42} | ExchangeType: Broadcast | N/A | N/A |
| │ └─Selection_59 | 0.25 | 3954682 | batchCop[tiflash] | | tiflash_task:{proc max:3.17s, min:3.17s, p80:3.17s, p95:3.17s, iters:4962, tasks:2, threads:42} | eq(table.localcountryid, 1), eq(table.message_locale, “zh-hk”), eq(table.upload_crm, 1) | N/A | N/A |
| │ └─TableFullScan_58 | 248870959.00 | 250941738 | batchCop[tiflash] | table:t1 | tiflash_task:{proc max:2.77s, min:2.77s, p80:2.77s, p95:2.77s, iters:5010, tasks:2, threads:42} | keep order:false, stats:pseudo | N/A | N/A |
| └─Selection_63(Probe) | 221052.47 | 60357517 | batchCop[tiflash] | | tiflash_task:{proc max:2.79s, min:2.61s, p80:2.79s, p95:2.79s, iters:5027, tasks:2, threads:41} | eq(table.upload_crm, 1), or(ne(table.message_locale, “zh-hk”), ne(table.localprovinceid, 32)) | N/A | N/A |
| └─TableFullScan_62 | 248870959.00 | 251293130 | batchCop[tiflash] | table:t1 | tiflash_task:{proc max:2.18s, min:2.05s, p80:2.18s, p95:2.18s, iters:5031, tasks:2, threads:41} | keep order:false, stats:pseudo | N/A | N/A |
±---------------------------------------±-------------±----------±------------------±--------------±---------------------------------------------------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±--------±-----+
When tidb_enforce_mpp is disabled:
±-------------------------------±-------------±----------±------------------±--------------±--------------------------------------------------------------------------------------------------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------±--------+
| id | estRows | actRows | task | access object | execution info | operator info | memory | disk |
±-------------------------------±-------------±----------±------------------±--------------±--------------------------------------------------------------------------------------------------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------±--------+
| StreamAgg_20 | 1.00 | 1 | root | | time:11.3s, loops:2 | funcs:count(1)->Column#199 | 8 Bytes | N/A |
| └─HashJoin_26 | 0.25 | 1505968 | root | | time:11.2s, loops:1475, build_hash_table:{total:4.94s, fetch:2.04s, build:2.91s}, probe:{concurrency:5, total:56.5s, max:11.3s, probe:29.5s, fetch:27s} | inner join, equal:[eq(table.uid, table.uid)] | 248.2 MB | 0 Bytes |
| ├─TableReader_37(Build) | 0.25 | 3954661 | root | | time:2.24s, loops:5111, cop_task: {num: 1877, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:Selection_36 | 1.12 MB | N/A |
| │ └─Selection_36 | 0.25 | 3954661 | batchCop[tiflash] | | tiflash_task:{proc max:4.91s, min:3.93s, p80:4.91s, p95:4.91s, iters:4969, tasks:2, threads:40} | eq(table.localcountryid, 1), eq(table.message_locale, “zh-hk”), eq(table.upload_crm, 1) | N/A | N/A |
| │ └─TableFullScan_35 | 248872880.00 | 251855024 | batchCop[tiflash] | table:t1 | tiflash_task:{proc max:4.19s, min:3.4s, p80:4.19s, p95:4.19s, iters:5011, tasks:2, threads:40} | keep order:false, stats:pseudo | N/A | N/A |
| └─TableReader_40(Probe) | 221054.17 | 60358891 | root | | time:708.9ms, loops:61330, cop_task: {num: 4752, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00} | data:Selection_39 | 3.27 MB | N/A |
| └─Selection_39 | 221054.17 | 60358891 | batchCop[tiflash] | | tiflash_task:{proc max:4.88s, min:3.68s, p80:4.88s, p95:4.88s, iters:4997, tasks:2, threads:40} | eq(table.upload_crm, 1), or(ne(table.message_locale, “zh-hk”), ne(table.localprovinceid, 32)) | N/A | N/A |
| └─TableFullScan_38 | 248872880.00 | 250879447 | batchCop[tiflash] | table:t1 | tiflash_task:{proc max:3.88s, min:2.86s, p80:3.88s, p95:3.88s, iters:4999, tasks:2, threads:40} | keep order:false, stats:pseudo | N/A | N/A |
±-------------------------------±-------------±----------±------------------±--------------±--------------------------------------------------------------------------------------------------------------------------------------------------------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------±---------±--------+
【Attachment】 Related logs and monitoring (https://metricstool.pingcap.com/)
If the question is about performance optimization or fault troubleshooting, please download the script and run it. Please select all and copy-paste the terminal output.
|
username: 
|
username: