Open JaySon-Huang opened 1 year ago
mysql> set session tidb_enforce_mpp=ON;
Query OK, 0 rows affected (0.04 sec)
mysql> explain SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM HAT.LINEORDER, HAT.DATE WHERE LO_ORDERDATE = D_DATEKEY AND D_YEAR = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY<25;
+----------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_13 | 1.00 | root | | funcs:sum(Column#37)->Column#36 |
| └─Projection_72 | 4992370.53 | root | | mul(hat.lineorder.lo_extendedprice, cast(hat.lineorder.lo_discount, decimal(20,0) BINARY))->Column#37 |
| └─HashJoin_32 | 4992370.53 | root | | inner join, equal:[eq(hat.date.d_datekey, hat.lineorder.lo_orderdate)] |
| ├─TableReader_51(Build) | 365.00 | root | | data:Selection_50 |
| │ └─Selection_50 | 365.00 | cop[tiflash] | | eq(hat.date.d_year, 1993) |
| │ └─TableFullScan_49 | 2557.00 | cop[tiflash] | table:date | keep order:false |
| └─Projection_45(Probe) | 601488614.00 | root | | hat.lineorder.lo_orderdate, hat.lineorder.lo_quantity, hat.lineorder.lo_extendedprice, hat.lineorder.lo_discount, tidb_shard(hat.lineorder.lo_orderkey) |
| └─TableReader_44 | 4992370.53 | root | | data:Selection_43 |
| └─Selection_43 | 4992370.53 | cop[tiflash] | | ge(hat.lineorder.lo_discount, 1), le(hat.lineorder.lo_discount, 3), lt(hat.lineorder.lo_quantity, 25), not(isnull(hat.lineorder.lo_orderdate)) |
| └─TableFullScan_42 | 601488614.00 | cop[tiflash] | table:lineorder | keep order:false, stats:pseudo |
+----------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set, 1 warning (0.04 sec)
mysql> show warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | MPP mode may be blocked because column `hat.lineorder._v$_uk_ordr_line_0` is a virtual column which is not supported now. |
+---------+------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
related with https://github.com/pingcap/tidb/pull/30659 and https://github.com/pingcap/tidb/pull/36771. The special virtual column tidb_shard('xxx'):
Workaround by setting the tidb_isolation_read_engines
variables in tidb
hat> select @@tidb_isolation_read_engines;
+-------------------------------+
| @@tidb_isolation_read_engines |
+-------------------------------+
| tikv,tiflash,tidb |
+-------------------------------+
hat> explain SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM `LINEORDER`, `DATE` WHERE LO_ORDERDATE = D_DATEKEY AND D_YEAR = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY<25;
+------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_12 | 1.00 | root | | funcs:sum(Column#37)->Column#36 |
| └─Projection_67 | 78562639.16 | root | | mul(hat.lineorder.lo_extendedprice, cast(hat.lineorder.lo_discount, decimal(20,0) BINARY))->Column#37 |
| └─HashJoin_30 | 78562639.16 | root | | inner join, equal:[eq(hat.date.d_datekey, hat.lineorder.lo_orderdate)] |
| ├─TableReader_49(Build) | 365.00 | root | | data:Selection_48 |
| │ └─Selection_48 | 365.00 | cop[tiflash] | | eq(hat.date.d_year, 1993) |
| │ └─TableFullScan_47 | 2557.00 | cop[tiflash] | table:date | keep order:false |
| └─Projection_43(Probe) | 600445758.00 | root | | hat.lineorder.lo_orderdate, hat.lineorder.lo_quantity, hat.lineorder.lo_extendedprice, hat.lineorder.lo_discount, tidb_shard(hat.lineorder.lo_orderkey) |
| └─TableReader_42 | 78562639.16 | root | | data:Selection_41 |
| └─Selection_41 | 78562639.16 | cop[tiflash] | | ge(hat.lineorder.lo_discount, 1), le(hat.lineorder.lo_discount, 3), lt(hat.lineorder.lo_quantity, 25), not(isnull(hat.lineorder.lo_orderdate)) |
| └─TableFullScan_40 | 600445758.00 | cop[tiflash] | table:lineorder | keep order:false |
+------------------------------+--------------+--------------+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------+
hat> set tidb_isolation_read_engines = "tiflash,tidb";
hat> select @@tidb_isolation_read_engines;
+-------------------------------+
| @@tidb_isolation_read_engines |
+-------------------------------+
| tiflash,tidb |
+-------------------------------+
hat> explain SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM `LINEORDER`, `DATE` WHERE LO_ORDERDATE = D_DATEKEY AND D_YEAR = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY<25;
+------------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+------------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_36 | 1.00 | root | | funcs:sum(Column#37)->Column#36 |
| └─TableReader_38 | 1.00 | root | | data:ExchangeSender_37 |
| └─ExchangeSender_37 | 1.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─HashAgg_13 | 1.00 | mpp[tiflash] | | funcs:sum(Column#38)->Column#37 |
| └─Projection_45 | 78597834.48 | mpp[tiflash] | | mul(hat.lineorder.lo_extendedprice, cast(hat.lineorder.lo_discount, decimal(20,0) BINARY))->Column#38 |
| └─Projection_34 | 78597834.48 | mpp[tiflash] | | hat.lineorder.lo_extendedprice, hat.lineorder.lo_discount |
| └─HashJoin_35 | 78597834.48 | mpp[tiflash] | | inner join, equal:[eq(hat.date.d_datekey, hat.lineorder.lo_orderdate)] |
| ├─ExchangeReceiver_23(Build) | 365.00 | mpp[tiflash] | | |
| │ └─ExchangeSender_22 | 365.00 | mpp[tiflash] | | ExchangeType: Broadcast |
| │ └─Selection_21 | 365.00 | mpp[tiflash] | | eq(hat.date.d_year, 1993) |
| │ └─TableFullScan_20 | 2557.00 | mpp[tiflash] | table:date | keep order:false |
| └─Selection_25(Probe) | 78597834.48 | mpp[tiflash] | | ge(hat.lineorder.lo_discount, 1), le(hat.lineorder.lo_discount, 3), lt(hat.lineorder.lo_quantity, 25), not(isnull(hat.lineorder.lo_orderdate)) |
| └─TableFullScan_24 | 600714752.00 | mpp[tiflash] | table:lineorder | keep order:false |
+------------------------------------------+--------------+--------------+-----------------+------------------------------------------------------------------------------------------------------------------------------------------------+
PTAL @winoros
Can't reproduce this issue, please contact me if it occurs again.
@qw4990 The issue still exist in the master branch
When tidb_isolation_read_engines = "tiflash,tidb"
, the HashJoin is executed on TiFlash
TiDB root@127.0.0.1:test> set tidb_isolation_read_engines = "tiflash,tidb";
Query OK, 0 rows affected
Time: 0.001s
TiDB root@127.0.0.1:test> set session tidb_enforce_mpp=ON;
Query OK, 0 rows affected
Time: 0.001s
TiDB root@127.0.0.1:test> explain SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM `LINEORDER`, `DATE` WHERE LO_ORDERDATE = D_DATEKEY AND D_YEAR = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY<25;
+----------------------------------------+---------+--------------+-----------------+-------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------------+---------+--------------+-----------------+-------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_18 | 1.00 | root | | funcs:sum(Column#39)->Column#36 |
| └─Projection_76 | 12.50 | root | | mul(test.lineorder.lo_extendedprice, cast(test.lineorder.lo_discount, decimal(10,0) BINARY))->Column#39 |
| └─TableReader_71 | 12.50 | root | | MppVersion: 2, data:ExchangeSender_70 |
| └─ExchangeSender_70 | 12.50 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Projection_69 | 12.50 | mpp[tiflash] | | test.lineorder.lo_extendedprice, test.lineorder.lo_discount |
| └─HashJoin_68 | 12.50 | mpp[tiflash] | | inner join, equal:[eq(test.date.d_datekey, test.lineorder.lo_orderdate)] |
| ├─ExchangeReceiver_29(Build) | 10.00 | mpp[tiflash] | | |
| │ └─ExchangeSender_28 | 10.00 | mpp[tiflash] | | ExchangeType: Broadcast, Compression: FAST |
| │ └─Projection_25 | 10.00 | mpp[tiflash] | | test.date.d_datekey |
| │ └─TableFullScan_26 | 10.00 | mpp[tiflash] | table:date | pushed down filter:eq(test.date.d_year, 1993), keep order:false, stats:pseudo |
| └─Projection_30(Probe) | 83.00 | mpp[tiflash] | | test.lineorder.lo_extendedprice, test.lineorder.lo_discount, test.lineorder.lo_orderdate |
| └─Selection_32 | 83.00 | mpp[tiflash] | | lt(test.lineorder.lo_quantity, 25), not(isnull(test.lineorder.lo_orderdate)) |
| └─TableFullScan_31 | 250.00 | mpp[tiflash] | table:lineorder | pushed down filter:ge(test.lineorder.lo_discount, 1), le(test.lineorder.lo_discount, 3), keep order:false, stats:pseudo |
+----------------------------------------+---------+--------------+-----------------+-------------------------------------------------------------------------------------------------------------------------+
While when tidb_isolation_read_engines = "tiflash,tidb,tikv";
the HashJoin
is executed in tidb rather than tiflash. This is unexpected and causing performance regression compare to the previous execution plan.
TiDB root@127.0.0.1:test> set tidb_isolation_read_engines = "tiflash,tidb,tikv";
Query OK, 0 rows affected
Time: 0.001s
TiDB root@127.0.0.1:test> set session tidb_enforce_mpp=ON;
Query OK, 0 rows affected
Time: 0.001s
TiDB root@127.0.0.1:test> explain SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS REVENUE FROM `LINEORDER`, `DATE` WHERE LO_ORDERDATE = D_DATEKEY AND D_YEAR = 1993 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY<25;
+--------------------------------+---------+--------------+-----------------+-------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+--------------------------------+---------+--------------+-----------------+-------------------------------------------------------------------------------------------------------------------------+
| StreamAgg_18 | 1.00 | root | | funcs:sum(Column#37)->Column#36 |
| └─Projection_62 | 12.50 | root | | mul(test.lineorder.lo_extendedprice, cast(test.lineorder.lo_discount, decimal(10,0) BINARY))->Column#37 |
| └─HashJoin_59 | 12.50 | root | | inner join, equal:[eq(test.date.d_datekey, test.lineorder.lo_orderdate)] |
| ├─TableReader_42(Build) | 10.00 | root | | MppVersion: 2, data:ExchangeSender_41 |
| │ └─ExchangeSender_41 | 10.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| │ └─Projection_33 | 10.00 | mpp[tiflash] | | test.date.d_datekey |
| │ └─TableFullScan_26 | 10.00 | mpp[tiflash] | table:date | pushed down filter:eq(test.date.d_year, 1993), keep order:false, stats:pseudo |
| └─Projection_43(Probe) | 83.00 | root | | test.lineorder.lo_extendedprice, test.lineorder.lo_discount, test.lineorder.lo_orderdate |
| └─TableReader_52 | 83.00 | root | | MppVersion: 2, data:ExchangeSender_51 |
| └─ExchangeSender_51 | 83.00 | mpp[tiflash] | | ExchangeType: PassThrough |
| └─Selection_50 | 83.00 | mpp[tiflash] | | lt(test.lineorder.lo_quantity, 25), not(isnull(test.lineorder.lo_orderdate)) |
| └─TableFullScan_49 | 250.00 | mpp[tiflash] | table:lineorder | pushed down filter:ge(test.lineorder.lo_discount, 1), le(test.lineorder.lo_discount, 3), keep order:false, stats:pseudo |
+--------------------------------+---------+--------------+-----------------+-------------------------------------------------------------------------------------------------------------------------+
12 rows in set
Time: 0.010s
TiDB root@127.0.0.1:test> show warnings
+---------+------+----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------------------------------------------------------------------------------------+
| Warning | 1105 | MPP mode may be blocked because column `test.lineorder._v$_uk_ordr_line_0` is a virtual column which is not supported now. |
| Warning | 1105 | MPP mode may be blocked because column `test.lineorder._v$_uk_ordr_line_0` is a virtual column which is not supported now. |
+---------+------+----------------------------------------------------------------------------------------------------------------------------+
TiDB root@127.0.0.1:test> select tidb_version() \G
***************************[ 1. row ]***************************
tidb_version() | Release Version: v8.2.0-alpha-22-gcf5c68e
Edition: Community
Git Commit Hash: cf5c68e55587c29d13957ea9db26073f2f7aaf53
Git Branch: HEAD
UTC Build Time: 2024-04-23 16:48:36
GoVersion: go1.21.4
Race Enabled: false
Check Table Before Drop: false
Store: tikv
Bug Report
Please answer these questions before submitting your issue. Thanks!
1. Minimal reproduce step (Required)
2. What did you expect to see? (Required)
3. What did you see instead (Required)
tidb don't choose the mpp plan, causing AP QPS drops
4. What is your TiDB version? (Required)
https://github.com/pingcap/tidb/commit/137041ac743c24e809e96ac5512dae429a02e7b1 @ release-6.2