TiFLASH 相同表结构相同sql不同集群执行计划不同

Hacker_ojLJ8Ndr · 2023 年9 月 21 日 11:41

版本：7.1.1

环境一：

id                     |estRows   |actRows|task        |access object           |execution info                                                                                                                                                                                                                                                 |operator info                                                                                                                                                                                                                                                  |memory |disk|
-----------------------+----------+-------+------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------+----+
TableReader_12         |16702.27  |5548   |root        |                        |time:126.5ms, loops:7, RU:0.000000, cop_task: {num: 19, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                        |MppVersion: 1, data:ExchangeSender_11                                                                                                                                                                                                                          |80.4 KB|N/A |
└─ExchangeSender_11    |16702.27  |5548   |mpp[tiflash]|                        |tiflash_task:{proc max:120.6ms, min:90.2ms, avg: 109.8ms, p80:120.6ms, p95:120.6ms, iters:16, tasks:3, threads:96}                                                                                                                                             |ExchangeType: PassThrough                                                                                                                                                                                                                                      |N/A    |N/A |
  └─Projection_5       |16702.27  |5548   |mpp[tiflash]|                        |tiflash_task:{proc max:120.6ms, min:90.2ms, avg: 109.5ms, p80:120.6ms, p95:120.6ms, iters:16, tasks:3, threads:96}                                                                                                                                             |mbase.rel_xhs_brand_note.noteid, cast(mbase.rel_xhs_brand_note.industryid, var_string(11))->Column#18, cast(mbase.rel_xhs_brand_note.categoryid, var_string(11))->Column#19, cast(mbase.rel_xhs_brand_note.brandid, var_string(11))->Column#20, cast(mbase.rel_|N/A    |N/A |
    └─Selection_10     |16702.27  |5548   |mpp[tiflash]|                        |tiflash_task:{proc max:120.6ms, min:90.2ms, avg: 109.5ms, p80:120.6ms, p95:120.6ms, iters:16, tasks:3, threads:96}                                                                                                                                             |eq(mbase.rel_xhs_brand_note.categoryid, 0), eq(mbase.rel_xhs_brand_note.filtered, 1), ge(mbase.rel_xhs_brand_note.updatetime, 2023-09-20 06:54:37.000000), lt(mbase.rel_xhs_brand_note.updatetime, 2023-09-21 06:39:26.207000), not(isnull(mbase.rel_xhs_brand_|N/A    |N/A |
      └─TableFullScan_9|2178551.73|6204   |mpp[tiflash]|table:rel_xhs_brand_note|tiflash_task:{proc max:120.6ms, min:90.2ms, avg: 109.5ms, p80:120.6ms, p95:120.6ms, iters:32, tasks:3, threads:96}, tiflash_scan:{dtfile:{total_scanned_packs:1604, total_skipped_packs:85524, total_scanned_rows:13080590, total_skipped_rows:692340548, total|pushed down filter:in(mbase.rel_xhs_brand_note.brandid, 88, 344, 472, 600, 728, 856, 984, 1112, 1368, 690136, 690264, 690392, 690648, 690776, 630232, 960088, 960344, 960472, 960856, 960984, 961112, 961368, 961496, 961624, 961752, 961880, 962008, 962136, 9|N/A    |N/A |

环境二：

id                   |estRows   |actRows|task        |access object           |execution info                                                                                                                                                                                                                                                 |operator info                                                                                                                                                                                                                                                  |memory  |disk|
---------------------+----------+-------+------------+------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------+----+
Projection_4         |0.00      |5548   |root        |                        |time:2.18s, loops:7, RU:303.886963, Concurrency:OFF                                                                                                                                                                                                            |mbase.rel_xhs_brand_note.noteid, cast(mbase.rel_xhs_brand_note.industryid, var_string(11))->Column#17, cast(mbase.rel_xhs_brand_note.categoryid, var_string(11))->Column#18, cast(mbase.rel_xhs_brand_note.brandid, var_string(11))->Column#19, cast(mbase.rel_|440.9 KB|N/A |
└─TableReader_7      |0.00      |5548   |root        |                        |time:2.18s, loops:7, cop_task: {num: 1165, max: 0s, min: 0s, avg: 57.6ms, p95: 113.3ms, rpc_num: 1167, rpc_time: 1m7.1s, copr_cache_hit_ratio: 0.00, build_task_duration: 4.9ms, max_distsql_concurrency: 32}, backoff{regionMiss: 2ms}                        |data:Selection_6                                                                                                                                                                                                                                               |112.6 KB|N/A |
  └─Selection_6      |0.00      |5548   |cop[tiflash]|                        |tiflash_task:{proc max:205.8ms, min:5.76ms, avg: 51.8ms, p80:76.5ms, p95:108.2ms, iters:97, tasks:1165, threads:1165}                                                                                                                                          |eq(mbase.rel_xhs_brand_note.categoryid, 0), eq(mbase.rel_xhs_brand_note.filtered, 1), ge(mbase.rel_xhs_brand_note.updatetime, 2023-09-20 06:54:37.000000), lt(mbase.rel_xhs_brand_note.updatetime, 2023-09-21 06:39:26.207000), not(isnull(mbase.rel_xhs_brand_|N/A     |N/A |
    └─TableFullScan_5|2062771.00|1433979|cop[tiflash]|table:rel_xhs_brand_note|tiflash_task:{proc max:205.8ms, min:5.76ms, avg: 51.7ms, p80:76.5ms, p95:107.6ms, iters:236, tasks:1165, threads:1165}, tiflash_scan:{dtfile:{total_scanned_packs:50597, total_skipped_packs:342897, total_scanned_rows:411503365, total_skipped_rows:277851415|pushed down filter:in(mbase.rel_xhs_brand_note.brandid, 88, 344, 472, 600, 728, 856, 984, 1112, 1368, 690136, 690264, 690392, 690648, 690776, 630232, 960088, 960344, 960472, 960856, 960984, 961112, 961368, 961496, 961624, 961752, 961880, 962008, 962136, 9|N/A     |N/A |

环境一未开启强制执行的情况下可以走tiflash且没有warning，环境二强制tiflash但没有走且报warning，如下：

Scalar function 'cast'(signature: CastIntAsString, return type: var_string(20)) is not supported to push down to tiflash now.

请问这是什么原因，环境二的问题应该怎么处理呢？

zhanggame1 · 2023 年9 月 21 日 13:06

数据库版本号一样吗

Hacker_ojLJ8Ndr · 2023 年9 月 22 日 01:53

一样的

tidb菜鸟一只 · 2023 年9 月 22 日 01:55

sql方便贴一下吗？看报错

Scalar function 'cast'(signature: CastIntAsString, return type: var_string(20)) is not supported to push down to tiflash now.

是cast 转换数字为字符串不支持下推到tiflash了，但是你的版本是一样的，不应该吧。。。

Hacker_ojLJ8Ndr · 2023 年9 月 22 日 01:58

两个环境的版本：

sql：

SELECT
  noteid,
  cast(industryid AS char) AS industryid,
  cast(categoryid AS char) AS categoryid,
  cast(brandid AS char) AS brandid,
  cast(commodityid AS char),
  uid
FROM
  rel_xhs_brand_note
WHERE
  updatetime >= '2023-09-20T06:54:37'
  AND updatetime < '2023-09-21T06:39:26.207'
  AND filtered = 1
  AND pos IS NOT NULL
  AND categoryid = 0
  AND brandid IN (
    88,
    344,
    472,
    600,
    728,
    856,
    984,
    1112,
    1368,
    690136,
    690264,
    690392,
    690648,
    690776,
    630232,
    960088,
    960344,
    960472,
    960856,
    960984,
    961112,
    961368,
    961496,
    961624,
    961752,
    961880,
    962008,
    962136,
    962264,
    962520,
    962648,
    962776,
    963288,
    963416,
    963544,
    963672,
    964056,
    964312,
    964440,
    964568,
    964696,
    964952,
    965336,
    965464,
    900056,
    900184,
    900312,
    965848,
    900568,
    966104,
    900696,
    966232,
    966360,
    966488,
    966744,
    966872,
    967384,
    967640,
    967768,
    967896,
    968152,
    968536,
    968664,
    968920,
    969176,
    969304,
    969432,
    969560,
    970072,
    970200,
    970456,
    970712,
    970840,
    970968,
    971096,
    971224,
    971352,
    971480,
    971608,
    971864,
    971992,
    972120,
    972376,
    972504,
    972632,
    972760,
    972888,
    973144,
    973272,
    973400,
    973528,
    973656,
    973784,
    973912,
    974040,
    974168,
    974296,
    450008,
    974424,
    974552,
    974680,
    974808,
    974936,
    975576,
    975704,
    975960,
    976088,
    976216,
    976344,
    976472,
    976600,
    976728,
    976856,
    976984,
    977112,
    977240,
    977496,
    977624,
    977752,
    977880,
    978264,
    978392,
    978520,
    978648,
    978776,
    978904,
    979032,
    979160,
    979288,
    979416,
    979544
  );

像风一样的男子 · 2023 年9 月 22 日 02:01

给表做个analyze看看

Hacker_ojLJ8Ndr · 2023 年9 月 22 日 02:01

做过了，一样的结果

tidb菜鸟一只 · 2023 年9 月 22 日 02:41

两边tidb_enforce_mpp参数一样吗？

Hacker_ojLJ8Ndr · 2023 年9 月 22 日 02:43

一样的，tidb_enforce_mpp=off，tidb_allow_mpp=on

Billmay表妹 · 2023 年9 月 22 日 04:14

你查一下两个版本的所有组件是不是版本都是 7.1.1 的。
会不会存在一个集群可能某些组件升级不成功

Hacker_ojLJ8Ndr · 2023 年9 月 22 日 04:29

环境一：

环境二：

两个环境的所有组件都是 7.1.1 版本呢

zhanggame1 · 2023 年9 月 22 日 04:37

查询的表的数据量有差别吗

Hacker_ojLJ8Ndr · 2023 年9 月 22 日 04:54

查询数据量两边一致，都是执行计划中的5548

zhanggame1 · 2023 年9 月 22 日 05:04

重启下tidb server节点试试？会不会缓存的执行计划有问题

Hacker007 · 2023 年9 月 22 日 06:03

两边执行analyze table试下

Hacker_ojLJ8Ndr · 2023 年9 月 22 日 06:04

做过了，一样的结果

xingzhenxiang · 2023 年9 月 22 日 08:46

这也没法升级版本试试了，最新的

有猫万事足 · 2023 年9 月 22 日 15:48

比较认同 @tidb菜鸟一只的说法，不应该一边有警告一边没有。如果参数没有问题，分析也做了，表结构对比一下看看？
是否有一些字段名字一样，但是类型不同？

ShawnYan · 2023 年9 月 22 日 23:04

还是先看下 tiup cluster display 吧

像风一样的男子 · 2023 年9 月 23 日 03:24

还是要找下2个集群之间的不同之处，是否有参数设置不同