TPCDS-Q95 run slower with 32 CPU TiDB comparing with 16 CPU TiDB

[Yui-Song]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

1. deploy a tidb cluster

• 1 TiDB 32CPU
• 3 TiKV16 CPU
• 1 PD 4 CPU
• 1 Tiflash 32 CPU

2. run TPCDS query 95
3. deploy a second tidb cluster

• 1 TiDB 16 CPU
• 3 TiKV16 CPU
• 1 PD 4 CPU
• 1 Tiflash 32 CPU

4. run TPCDS query 95

2. What did you expect to see? (Required)

The elapsed time of Q95 with 16 CPU is around 97s. Q95 would run faster with 32 CPU TiDB.

3. What did you see instead (Required)

The execution of Q95 was finished in 122s with 32 CPU TiDB.

Olap_Detail_Log_ID: 2409311   Plan_Digest: 605145cd8b6149053d1dd4f7a6124883 Elapsed_Time (s): 122.90 

+-----------------------------------------------+--------------+-----------+--------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| ID                                            | ESTROWS      | ACTROWS   | TASK         | ACCESS OBJECT          | EXECUTION INFO                                                                                                                                                                                                                                                                                                                                                                                                   | OPERATOR INFO                                                                                                                                                                          | MEMORY   | DISK    |
+-----------------------------------------------+--------------+-----------+--------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+
| TopN_77                                       | 1.00         | 1         | root         |                        | time:2m2.9s, loops:2                                                                                                                                                                                                                                                                                                                                                                                             | Column#204, offset:0, count:100                                                                                                                                                        | 3.18 KB  | N/A     |
| └─StreamAgg_83                                | 1.00         | 1         | root         |                        | time:2m2.9s, loops:3                                                                                                                                                                                                                                                                                                                                                                                             | funcs:count(distinct tpcds50.web_sales.ws_order_number)->Column#204, funcs:sum(tpcds50.web_sales.ws_ext_ship_cost)->Column#205, funcs:sum(tpcds50.web_sales.ws_net_profit)->Column#206 | 129.6 KB | N/A     |
|   └─HashJoin_193                              | 30633.76     | 3223      | root         |                        | time:2m2.9s, loops:6, build_hash_table:{total:1m22.1s, fetch:1m22.1s, build:924µs}, probe:{concurrency:5, total:10m14.5s, max:2m2.9s, probe:662.3ms, fetch:10m13.8s}                                                                                                                                                                                                                                             | inner join, equal:[eq(tpcds50.web_sales.ws_order_number, tpcds50.web_returns.wr_order_number)]                                                                                         | 657.4 KB | 0 Bytes |
|     ├─HashJoin_88(Build)                      | 30633.76     | 4539      | root         |                        | time:1m22.1s, loops:6, build_hash_table:{total:584ms, fetch:582.9ms, build:1.07ms}, probe:{concurrency:5, total:6m50.4s, max:1m22.1s, probe:1.51s, fetch:6m48.9s}                                                                                                                                                                                                                                                | inner join, equal:[eq(tpcds50.web_sales.ws_order_number, tpcds50.web_sales.ws_order_number)]                                                                                           | 1.29 MB  | 0 Bytes |
|     │ ├─TableReader_120(Build)                | 30633.76     | 4539      | root         |                        | time:583.4ms, loops:6, cop_task: {num: 24, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                       | data:ExchangeSender_119                                                                                                                                                                | N/A      | N/A     |
|     │ │ └─ExchangeSender_119                  | 30633.76     | 4539      | mpp[tiflash] |                        | tiflash_task:{time:554.7ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                               | ExchangeType: PassThrough                                                                                                                                                              | N/A      | N/A     |
|     │ │   └─HashJoin_89                       | 30633.76     | 4539      | mpp[tiflash] |                        | tiflash_task:{time:540.7ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                               | inner join, equal:[eq(tpcds50.web_sales.ws_ship_addr_sk, tpcds50.customer_address.ca_address_sk)]                                                                                      | N/A      | N/A     |
|     │ │     ├─ExchangeReceiver_118(Build)     | 30633.76     | 30497     | mpp[tiflash] |                        | tiflash_task:{time:89.7ms, loops:6, threads:24}                                                                                                                                                                                                                                                                                                                                                                  |                                                                                                                                                                                        | N/A      | N/A     |
|     │ │     │ └─ExchangeSender_117            | 30633.76     | 30497     | mpp[tiflash] |                        | tiflash_task:{time:15.6ms, loops:6, threads:1}                                                                                                                                                                                                                                                                                                                                                                   | ExchangeType: Broadcast                                                                                                                                                                | N/A      | N/A     |
|     │ │     │   └─Selection_116               | 30633.76     | 30497     | mpp[tiflash] |                        | tiflash_task:{time:15.6ms, loops:6, threads:1}                                                                                                                                                                                                                                                                                                                                                                   | eq(tpcds50.customer_address.ca_state, "TX")                                                                                                                                            | N/A      | N/A     |
|     │ │     │     └─TableFullScan_115         | 383000.00    | 383000    | mpp[tiflash] | table:customer_address | tiflash_task:{time:9.64ms, loops:6, threads:1}                                                                                                                                                                                                                                                                                                                                                                   | keep order:false                                                                                                                                                                       | N/A      | N/A     |
|     │ │     └─HashJoin_103(Probe)             | 102376.77    | 57632     | mpp[tiflash] |                        | tiflash_task:{time:534.7ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                               | inner join, equal:[eq(tpcds50.web_sales.ws_ship_date_sk, tpcds50.date_dim.d_date_sk)]                                                                                                  | N/A      | N/A     |
|     │ │       ├─ExchangeReceiver_114(Build)   | 61.00        | 61        | mpp[tiflash] |                        | tiflash_task:{time:90.7ms, loops:1, threads:24}                                                                                                                                                                                                                                                                                                                                                                  |                                                                                                                                                                                        | N/A      | N/A     |
|     │ │       │ └─ExchangeSender_113          | 61.00        | 61        | mpp[tiflash] |                        | tiflash_task:{time:2.94ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                   | ExchangeType: Broadcast                                                                                                                                                                | N/A      | N/A     |
|     │ │       │   └─Selection_112             | 61.00        | 61        | mpp[tiflash] |                        | tiflash_task:{time:3.94ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                   | ge(tpcds50.date_dim.d_date, 1999-05-01 00:00:00.000000), le(tpcds50.date_dim.d_date, 1999-06-30)                                                                                       | N/A      | N/A     |
|     │ │       │     └─TableFullScan_111       | 73049.00     | 8192      | mpp[tiflash] | table:date_dim         | tiflash_task:{time:2.94ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                   | keep order:false                                                                                                                                                                       | N/A      | N/A     |
|     │ │       └─HashJoin_104(Probe)           | 3263567.27   | 2997937   | mpp[tiflash] |                        | tiflash_task:{time:510.7ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                               | inner join, equal:[eq(tpcds50.web_site.web_site_sk, tpcds50.web_sales.ws_web_site_sk)]                                                                                                 | N/A      | N/A     |
|     │ │         ├─ExchangeReceiver_108(Build) | 2.00         | 2         | mpp[tiflash] |                        | tiflash_task:{time:89.7ms, loops:1, threads:24}                                                                                                                                                                                                                                                                                                                                                                  |                                                                                                                                                                                        | N/A      | N/A     |
|     │ │         │ └─ExchangeSender_107        | 2.00         | 2         | mpp[tiflash] |                        | tiflash_task:{time:1.67ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                   | ExchangeType: Broadcast                                                                                                                                                                | N/A      | N/A     |
|     │ │         │   └─Selection_106           | 2.00         | 2         | mpp[tiflash] |                        | tiflash_task:{time:1.67ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                   | eq(tpcds50.web_site.web_company_name, "pri")                                                                                                                                           | N/A      | N/A     |
|     │ │         │     └─TableFullScan_105     | 22.00        | 22        | mpp[tiflash] | table:web_site         | tiflash_task:{time:1.67ms, loops:1, threads:1}                                                                                                                                                                                                                                                                                                                                                                   | keep order:false                                                                                                                                                                       | N/A      | N/A     |
|     │ │         └─Selection_110(Probe)        | 35970181.33  | 35981281  | mpp[tiflash] |                        | tiflash_task:{time:320.7ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                               | not(isnull(tpcds50.web_sales.ws_ship_addr_sk)), not(isnull(tpcds50.web_sales.ws_ship_date_sk)), not(isnull(tpcds50.web_sales.ws_web_site_sk))                                          | N/A      | N/A     |
|     │ │           └─TableFullScan_109         | 35996850.00  | 35996850  | mpp[tiflash] | table:ws1              | tiflash_task:{time:234.7ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                               | keep order:false                                                                                                                                                                       | N/A      | N/A     |
|     │ └─HashAgg_177(Probe)                    | 2402536.42   | 3000000   | root         |                        | time:1m21.8s, loops:2933, partial_worker:{wall_time:1m19.87680233s, concurrency:5, task_num:347986, tot_wait:6m0.539587527s, tot_exec:37.654757277s, tot_time:6m39.219180565s, max:1m19.876726352s, p95:1m19.876726352s}, final_worker:{wall_time:1m22.082867266s, concurrency:5, task_num:25, tot_wait:6m39.13925395s, tot_exec:11.024193393s, tot_time:6m50.16346278s, max:1m22.08279734s, p95:1m22.08279734s} | group by:tpcds50.web_sales.ws_order_number, funcs:firstrow(tpcds50.web_sales.ws_order_number)->tpcds50.web_sales.ws_order_number                                                       | 1.04 GB  | N/A     |
|     │   └─Selection_178                       | 345006182.27 | 356337144 | root         |                        | time:1m18.6s, loops:347987                                                                                                                                                                                                                                                                                                                                                                                       | not(isnull(tpcds50.web_sales.ws_order_number))                                                                                                                                         | 24.7 KB  | N/A     |
|     │     └─CTEFullScan_179                   | 431257727.84 | 356337144 | root         | CTE:ws_wh              | time:50.3s, loops:348287                                                                                                                                                                                                                                                                                                                                                                                         | data:CTE_0                                                                                                                                                                             | 16.4 GB  | 0 Bytes |
|     └─HashAgg_180(Probe)                      | 2083584.00   | 2119776   | root         |                        | time:2m2.8s, loops:2074, partial_worker:{wall_time:2m1.648780808s, concurrency:5, task_num:455912, tot_wait:9m19.286196323s, tot_exec:47.522441542s, tot_time:10m8.163112057s, max:2m1.648674522s, p95:2m1.648674522s}, final_worker:{wall_time:2m2.898362017s, concurrency:5, task_num:25, tot_wait:10m8.091950295s, tot_exec:6.119080067s, tot_time:10m14.21105335s, max:2m2.898215129s, p95:2m2.898215129s}   | group by:tpcds50.web_returns.wr_order_number, funcs:firstrow(tpcds50.web_returns.wr_order_number)->tpcds50.web_returns.wr_order_number                                                 | 756.0 MB | N/A     |
|       └─HashJoin_182                          | 516808411.86 | 466851188 | root         |                        | time:2m0.5s, loops:455913, build_hash_table:{total:1.13s, fetch:105.3ms, build:1.02s}, probe:{concurrency:5, total:10m6.9s, max:2m1.4s, probe:6m1.4s, fetch:4m5.5s}                                                                                                                                                                                                                                              | inner join, equal:[eq(tpcds50.web_returns.wr_order_number, tpcds50.web_sales.ws_order_number)]                                                                                         | 172.1 MB | 0 Bytes |
|         ├─TableReader_186(Build)              | 3598924.00   | 3598924   | root         |                        | time:83.6ms, loops:3518, cop_task: {num: 8, max: 128.3ms, min: 3.52ms, avg: 90.9ms, p95: 128.3ms, rpc_num: 8, rpc_time: 726.7ms, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}                                                                                                                                                                                                                            | data:TableFullScan_185                                                                                                                                                                 | 27.5 MB  | N/A     |
|         │ └─TableFullScan_185                 | 3598924.00   | 3598924   | cop[tiflash] | table:web_returns      | tiflash_task:{proc max:69.8ms, min:990µs, avg: 47.7ms, p80:62.6ms, p95:69.8ms, iters:69, tasks:8, threads:8}                                                                                                                                                                                                                                                                                                     | keep order:false                                                                                                                                                                       | N/A      | N/A     |
|         └─Selection_189(Probe)                | 345006182.27 | 356337144 | root         |                        | time:1m24.6s, loops:347987                                                                                                                                                                                                                                                                                                                                                                                       | not(isnull(tpcds50.web_sales.ws_order_number))                                                                                                                                         | 24.7 KB  | N/A     |
|           └─CTEFullScan_190                   | 431257727.84 | 356337144 | root         | CTE:ws_wh              | time:48.6s, loops:348287                                                                                                                                                                                                                                                                                                                                                                                         | data:CTE_0                                                                                                                                                                             | 0 Bytes  | 0 Bytes |
| CTE_0                                         | 431257727.84 | 356337144 | root         |                        | time:50.3s, loops:348287                                                                                                                                                                                                                                                                                                                                                                                         | Non-Recursive CTE                                                                                                                                                                      | 16.4 GB  | 0 Bytes |
| └─Projection_35(Seed Part)                    | 431257727.84 | 356337144 | root         |                        | time:25.4s, loops:348286, Concurrency:5                                                                                                                                                                                                                                                                                                                                                                          | tpcds50.web_sales.ws_order_number, tpcds50.web_sales.ws_warehouse_sk, tpcds50.web_sales.ws_warehouse_sk                                                                                | 171.0 KB | N/A     |
|   └─TableReader_48                            | 431257727.84 | 356337144 | root         |                        | time:24.2s, loops:348286, cop_task: {num: 572, max: 0s, min: 0s, avg: 0s, p95: 0s, copr_cache_hit_ratio: 0.00}                                                                                                                                                                                                                                                                                                   | data:ExchangeSender_47                                                                                                                                                                 | N/A      | N/A     |
|     └─ExchangeSender_47                       | 431257727.84 | 356337144 | mpp[tiflash] |                        | tiflash_task:{time:21.8s, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                                 | ExchangeType: PassThrough                                                                                                                                                              | N/A      | N/A     |
|       └─HashJoin_36                           | 431257727.84 | 356337144 | mpp[tiflash] |                        | tiflash_task:{time:2.48s, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                                 | inner join, equal:[eq(tpcds50.web_sales.ws_order_number, tpcds50.web_sales.ws_order_number)], other cond:ne(tpcds50.web_sales.ws_warehouse_sk, tpcds50.web_sales.ws_warehouse_sk)      | N/A      | N/A     |
|         ├─ExchangeReceiver_42(Build)          | 35988060.45  | 35988084  | mpp[tiflash] |                        | tiflash_task:{time:117.6ms, loops:569, threads:24}                                                                                                                                                                                                                                                                                                                                                               |                                                                                                                                                                                        | N/A      | N/A     |
|         │ └─ExchangeSender_41                 | 35988060.45  | 35988084  | mpp[tiflash] |                        | tiflash_task:{time:462.8ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                               | ExchangeType: HashPartition, Hash Cols: [name: tpcds50.web_sales.ws_order_number, collate: N/A]                                                                                        | N/A      | N/A     |
|         │   └─Selection_40                    | 35988060.45  | 35988084  | mpp[tiflash] |                        | tiflash_task:{time:129.8ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                               | not(isnull(tpcds50.web_sales.ws_warehouse_sk))                                                                                                                                         | N/A      | N/A     |
|         │     └─TableFullScan_39              | 35996850.00  | 35996850  | mpp[tiflash] | table:ws1              | tiflash_task:{time:120.8ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                               | keep order:false                                                                                                                                                                       | N/A      | N/A     |
|         └─ExchangeReceiver_46(Probe)          | 35988060.45  | 35988084  | mpp[tiflash] |                        | tiflash_task:{time:36.6ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                                |                                                                                                                                                                                        | N/A      | N/A     |
|           └─ExchangeSender_45                 | 35988060.45  | 35988084  | mpp[tiflash] |                        | tiflash_task:{time:12.7s, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                                 | ExchangeType: HashPartition, Hash Cols: [name: tpcds50.web_sales.ws_order_number, collate: N/A]                                                                                        | N/A      | N/A     |
|             └─Selection_44                    | 35988060.45  | 35988084  | mpp[tiflash] |                        | tiflash_task:{time:126.4ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                               | not(isnull(tpcds50.web_sales.ws_warehouse_sk))                                                                                                                                         | N/A      | N/A     |
|               └─TableFullScan_43              | 35996850.00  | 35996850  | mpp[tiflash] | table:ws2              | tiflash_task:{time:117.4ms, loops:571, threads:24}                                                                                                                                                                                                                                                                                                                                                               | keep order:false                                                                                                                                                                       | N/A      | N/A     |
+-----------------------------------------------+--------------+-----------+--------------+------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+---------+

4. What is your TiDB version? (Required)

commit f0d4a96

[XuHuaiyu]

dup with #36847

[zanmato1984]

As we improved the performance for 6.2 in the fix of #36847, now this issue becomes non-blocking.

This seems to be false-sharing issue, and we consider this a enhancement. Changing labels accordingly.