TiDB 6.1.0 Execution Plan Does Not Use TiFlash

translator_bot · June 23, 2024, 5:41am

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

Original topic: tidb-6.1.0 执行计划不走tiflash

| username: 是我的海

[TiDB Usage Environment] Test Environment
[TiDB Version] tidb-v6.1.0
[Encountered Problem] Aggregate SQL does not use TiFlash
[Problem Phenomenon and Impact]
Cluster Topology:

Synchronize a table to TiFlash

SQL Execution Plan

MySQL [test]> explain select lesson_id, group_level_8, sum(col_1), sum(col_2), sum(col_3), sum(col_4), sum(col_5), sum(col_6), sum(col_7), sum(col_8), sum(col_9), sum(col_10), sum(col_11), sum(col_12), sum(col_13), sum(col_14), sum(col_15), sum(col_16), sum(col_17), sum(col_18), sum(col_19), sum(col_20), sum(col_21), sum(col_22), sum(col_23), sum(col_24), sum(col_25), sum(col_26), sum(col_27), sum(col_28), sum(col_29), sum(col_30), sum(col_31), sum(col_32), sum(col_33), sum(col_34), sum(col_35), sum(col_36), sum(col_37), sum(col_38), sum(col_39), sum(col_40) from tbldatasopcal_500r_200c where index1 in (1,2,3) and index2 in (1,2,3) and index4 in (1,2,3) and index5 in (1,2,3) group by lesson_id, group_level_8 limit 1\G
*************************** 1. row ***************************
           id: Projection_8
      estRows: 1.00
         task: root
access object: 
operator info: test.tbldatasopcal_500r_200c.lesson_id, test.tbldatasopcal_500r_200c.group_level_8, Column#202, Column#203, Column#204, Column#205, Column#206, Column#207, Column#208, Column#209, Column#210, Column#211, Column#212, Column#213, Column#214, Column#215, Column#216, Column#217, Column#218, Column#219, Column#220, Column#221, Column#222, Column#223, Column#224, Column#225, Column#226, Column#227, Column#228, Column#229, Column#230, Column#231, Column#232, Column#233, Column#234, Column#235, Column#236, Column#237, Column#238, Column#239, Column#240, Column#241
*************************** 2. row ***************************
           id: └─Limit_11
      estRows: 1.00
         task: root
access object: 
operator info: offset:0, count:1
*************************** 3. row ***************************
           id:   └─HashAgg_37
      estRows: 1.00
         task: root
access object: 
operator info: group by:test.tbldatasopcal_500r_200c.group_level_8, test.tbldatasopcal_500r_200c.lesson_id, funcs:sum(Column#288)->Column#202, funcs:sum(Column#289)->Column#203, funcs:sum(Column#290)->Column#204, funcs:sum(Column#291)->Column#205, funcs:sum(Column#292)->Column#206, funcs:sum(Column#293)->Column#207, funcs:sum(Column#294)->Column#208, funcs:sum(Column#295)->Column#209, funcs:sum(Column#296)->Column#210, funcs:sum(Column#297)->Column#211, funcs:sum(Column#298)->Column#212, funcs:sum(Column#299)->Column#213, funcs:sum(Column#300)->Column#214, funcs:sum(Column#301)->Column#215, funcs:sum(Column#302)->Column#216, funcs:sum(Column#303)->Column#217, funcs:sum(Column#304)->Column#218, funcs:sum(Column#305)->Column#219, funcs:sum(Column#306)->Column#220, funcs:sum(Column#307)->Column#221, funcs:sum(Column#308)->Column#222, funcs:sum(Column#309)->Column#223, funcs:sum(Column#310)->Column#224, funcs:sum(Column#311)->Column#225, funcs:sum(Column#312)->Column#226, funcs:sum(Column#313)->Column#227, funcs:sum(Column#314)->Column#228, funcs:sum(Column#315)->Column#229, funcs:sum(Column#316)->Column#230, funcs:sum(Column#317)->Column#231, funcs:sum(Column#318)->Column#232, funcs:sum(Column#319)->Column#233, funcs:sum(Column#320)->Column#234, funcs:sum(Column#321)->Column#235, funcs:sum(Column#322)->Column#236, funcs:sum(Column#323)->Column#237, funcs:sum(Column#324)->Column#238, funcs:sum(Column#325)->Column#239, funcs:sum(Column#326)->Column#240, funcs:sum(Column#327)->Column#241, funcs:firstrow(test.tbldatasopcal_500r_200c.lesson_id)->test.tbldatasopcal_500r_200c.lesson_id, funcs:firstrow(test.tbldatasopcal_500r_200c.group_level_8)->test.tbldatasopcal_500r_200c.group_level_8
*************************** 4. row ***************************
           id:     └─IndexLookUp_38
      estRows: 1.00
         task: root
access object: 
operator info: 
*************************** 5. row ***************************
           id:       ├─IndexRangeScan_25(Build)
      estRows: 749928.18
         task: cop[tikv]
access object: table:tbldatasopcal_500r_200c, index:index1_idx(index1)
operator info: range:[1,1], [2,2], [3,3], keep order:false
*************************** 6. row ***************************
           id:       └─HashAgg_14(Probe)
      estRows: 1.00
         task: cop[tikv]
access object: 
operator info: group by:test.tbldatasopcal_500r_200c.group_level_8, test.tbldatasopcal_500r_200c.lesson_id, funcs:sum(test.tbldatasopcal_500r_200c.col_1)->Column#288, funcs:sum(test.tbldatasopcal_500r_200c.col_2)->Column#289, funcs:sum(test.tbldatasopcal_500r_200c.col_3)->Column#290, funcs:sum(test.tbldatasopcal_500r_200c.col_4)->Column#291, funcs:sum(test.tbldatasopcal_500r_200c.col_5)->Column#292, funcs:sum(test.tbldatasopcal_500r_200c.col_6)->Column#293, funcs:sum(test.tbldatasopcal_500r_200c.col_7)->Column#294, funcs:sum(test.tbldatasopcal_500r_200c.col_8)->Column#295, funcs:sum(test.tbldatasopcal_500r_200c.col_9)->Column#296, funcs:sum(test.tbldatasopcal_500r_200c.col_10)->Column#297, funcs:sum(test.tbldatasopcal_500r_200c.col_11)->Column#298, funcs:sum(test.tbldatasopcal_500r_200c.col_12)->Column#299, funcs:sum(test.tbldatasopcal_500r_200c.col_13)->Column#300, funcs:sum(test.tbldatasopcal_500r_200c.col_14)->Column#301, funcs:sum(test.tbldatasopcal_500r_200c.col_15)->Column#302, funcs:sum(test.tbldatasopcal_500r_200c.col_16)->Column#303, funcs:sum(test.tbldatasopcal_500r_200c.col_17)->Column#304, funcs:sum(test.tbldatasopcal_500r_200c.col_18)->Column#305, funcs:sum(test.tbldatasopcal_500r_200c.col_19)->Column#306, funcs:sum(test.tbldatasopcal_500r_200c.col_20)->Column#307, funcs:sum(test.tbldatasopcal_500r_200c.col_21)->Column#308, funcs:sum(test.tbldatasopcal_500r_200c.col_22)->Column#309, funcs:sum(test.tbldatasopcal_500r_200c.col_23)->Column#310, funcs:sum(test.tbldatasopcal_500r_200c.col_24)->Column#311, funcs:sum(test.tbldatasopcal_500r_200c.col_25)->Column#312, funcs:sum(test.tbldatasopcal_500r_200c.col_26)->Column#313, funcs:sum(test.tbldatasopcal_500r_200c.col_27)->Column#314, funcs:sum(test.tbldatasopcal_500r_200c.col_28)->Column#315, funcs:sum(test.tbldatasopcal_500r_200c.col_29)->Column#316, funcs:sum(test.tbldatasopcal_500r_200c.col_30)->Column#317, funcs:sum(test.tbldatasopcal_500r_200c.col_31)->Column#318, funcs:sum(test.tbldatasopcal_500r_200c.col_32)->Column#319, funcs:sum(test.tbldatasopcal_500r_200c.col_33)->Column#320, funcs:sum(test.tbldatasopcal_500r_200c.col_34)->Column#321, funcs:sum(test.tbldatasopcal_500r_200c.col_35)->Column#322, funcs:sum(test.tbldatasopcal_500r_200c.col_36)->Column#323, funcs:sum(test.tbldatasopcal_500r_200c.col_37)->Column#324, funcs:sum(test.tbldatasopcal_500r_200c.col_38)->Column#325, funcs:sum(test.tbldatasopcal_500r_200c.col_39)->Column#326, funcs:sum(test.tbldatasopcal_500r_200c.col_40)->Column#327
*************************** 7. row ***************************
           id:         └─Selection_27
      estRows: 312767.14
         task: cop[tikv]
access object: 
operator info: in(test.tbldatasopcal_500r_200c.index2, 1, 2, 3), in(test.tbldatasopcal_500r_200c.index4, 1, 2, 3), in(test.tbldatasopcal_500r_200c.index5, 1, 2, 3)
*************************** 8. row ***************************
           id:           └─TableRowIDScan_26
      estRows: 749928.18
         task: cop[tikv]
access object: table:tbldatasopcal_500r_200c
operator info: keep order:false
8 rows in set (0.01 sec)

This cluster was upgraded from 5.4.2 to 6.1.0.
Because during the stress test in 5.4, when the concurrency of the above SQL reached more than 10, the time consumption increased significantly and the CPU consumption was very high. There were also the following logs:

However, after upgrading to 6.1.0, the SQL does not automatically route to TiFlash.

translator_bot · June 23, 2024, 5:41am

| username: ShawnYan | Original post link

Could you please provide the table structure and sample data?

How about turning off MPP and then checking the execution plan?

translator_bot · June 23, 2024, 5:41am

| username: 是我的海 | Original post link

Table structure

CREATE TABLE `tbldatasopcal_1000r_100c` (
  `lesson_id` bigint(20) NOT NULL,
  `assistant_uid` bigint(20) NOT NULL,
  `save_time` bigint(20) NOT NULL,
  `user_type` varchar(100) NOT NULL,
  `transfer_status` bigint(20) NOT NULL,
  `course_id` bigint(20) DEFAULT NULL,
  `update_time` bigint(20) DEFAULT NULL,
  `group_level_1` bigint(20) DEFAULT NULL,
  `group_level_2` bigint(20) DEFAULT NULL,
  `group_level_3` bigint(20) DEFAULT NULL,
  `group_level_4` bigint(20) DEFAULT NULL,
  `group_level_5` bigint(20) DEFAULT NULL,
  `group_level_6` bigint(20) DEFAULT NULL,
  `group_level_7` bigint(20) DEFAULT NULL,
  `group_level_8` bigint(20) DEFAULT NULL,
  `index1` bigint(20) DEFAULT NULL,
  `index2` bigint(20) DEFAULT NULL,
  `index3` bigint(20) DEFAULT NULL,
  `index4` bigint(20) DEFAULT NULL,
  `index5` bigint(20) DEFAULT NULL,
  `index6` bigint(20) DEFAULT NULL,
  `index7` bigint(20) DEFAULT NULL,
  `index8` bigint(20) DEFAULT NULL,
  `index9` bigint(20) DEFAULT NULL,
  `index10` bigint(20) DEFAULT NULL,
  `col_1` bigint(20) DEFAULT NULL,
  `col_2` bigint(20) DEFAULT NULL,
  `col_3` bigint(20) DEFAULT NULL,
  `col_4` bigint(20) DEFAULT NULL,
  `col_5` bigint(20) DEFAULT NULL,
  `col_6` bigint(20) DEFAULT NULL,
  `col_7` bigint(20) DEFAULT NULL,
  `col_8` bigint(20) DEFAULT NULL,
  `col_9` bigint(20) DEFAULT NULL,
  `col_10` bigint(20) DEFAULT NULL,
  `col_11` bigint(20) DEFAULT NULL,
  `col_12` bigint(20) DEFAULT NULL,
  `col_13` bigint(20) DEFAULT NULL,
  `col_14` bigint(20) DEFAULT NULL,
  `col_15` bigint(20) DEFAULT NULL,
  `col_16` bigint(20) DEFAULT NULL,
  `col_17` bigint(20) DEFAULT NULL,
  `col_18` bigint(20) DEFAULT NULL,
  `col_19` bigint(20) DEFAULT NULL,
  `col_20` bigint(20) DEFAULT NULL,
  `col_21` bigint(20) DEFAULT NULL,
  `col_22` bigint(20) DEFAULT NULL,
  `col_23` bigint(20) DEFAULT NULL,
  `col_24` bigint(20) DEFAULT NULL,
  `col_25` bigint(20) DEFAULT NULL,
  `col_26` bigint(20) DEFAULT NULL,
  `col_27` bigint(20) DEFAULT NULL,
  `col_28` bigint(20) DEFAULT NULL,
  `col_29` bigint(20) DEFAULT NULL,
  `col_30` bigint(20) DEFAULT NULL,
  `col_31` bigint(20) DEFAULT NULL,
  `col_32` bigint(20) DEFAULT NULL,
  `col_33` bigint(20) DEFAULT NULL,
  `col_34` bigint(20) DEFAULT NULL,
  `col_35` bigint(20) DEFAULT NULL,
  `col_36` bigint(20) DEFAULT NULL,
  `col_37` bigint(20) DEFAULT NULL,
  `col_38` bigint(20) DEFAULT NULL,
  `col_39` bigint(20) DEFAULT NULL,
  `col_40` bigint(20) DEFAULT NULL,
  `col_41` bigint(20) DEFAULT NULL,
  `col_42` bigint(20) DEFAULT NULL,
  `col_43` bigint(20) DEFAULT NULL,
  `col_44` bigint(20) DEFAULT NULL,
  `col_45` bigint(20) DEFAULT NULL,
  `col_46` bigint(20) DEFAULT NULL,
  `col_47` bigint(20) DEFAULT NULL,
  `col_48` bigint(20) DEFAULT NULL,
  `col_49` bigint(20) DEFAULT NULL,
  `col_50` bigint(20) DEFAULT NULL,
  `col_51` bigint(20) DEFAULT NULL,
  `col_52` bigint(20) DEFAULT NULL,
  `col_53` bigint(20) DEFAULT NULL,
  `col_54` bigint(20) DEFAULT NULL,
  `col_55` bigint(20) DEFAULT NULL,
  `col_56` bigint(20) DEFAULT NULL,
  `col_57` bigint(20) DEFAULT NULL,
  `col_58` bigint(20) DEFAULT NULL,
  `col_59` bigint(20) DEFAULT NULL,
  `col_60` bigint(20) DEFAULT NULL,
  `col_61` bigint(20) DEFAULT NULL,
  `col_62` bigint(20) DEFAULT NULL,
  `col_63` bigint(20) DEFAULT NULL,
  `col_64` bigint(20) DEFAULT NULL,
  `col_65` bigint(20) DEFAULT NULL,
  `col_66` bigint(20) DEFAULT NULL,
  `col_67` bigint(20) DEFAULT NULL,
  `col_68` bigint(20) DEFAULT NULL,
  `col_69` bigint(20) DEFAULT NULL,
  `col_70` bigint(20) DEFAULT NULL,
  `col_71` bigint(20) DEFAULT NULL,
  `col_72` bigint(20) DEFAULT NULL,
  `col_73` bigint(20) DEFAULT NULL,
  `col_74` bigint(20) DEFAULT NULL,
  `col_75` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`lesson_id`,`assistant_uid`,`transfer_status`,`user_type`,`save_time`) /*T![clustered_index] NONCLUSTERED */,
  KEY `course_id_idx` (`course_id`),
  KEY `update_time_idx` (`update_time`),
  KEY `group_level_1_idx` (`group_level_1`),
  KEY `group_level_2_idx` (`group_level_2`),
  KEY `group_level_3_idx` (`group_level_3`),
  KEY `group_level_4_idx` (`group_level_4`),
  KEY `group_level_5_idx` (`group_level_5`),
  KEY `group_level_6_idx` (`group_level_6`),
  KEY `group_level_7_idx` (`group_level_7`),
  KEY `group_level_8_idx` (`group_level_8`),
  KEY `index1_idx` (`index1`),
  KEY `index2_idx` (`index2`),
  KEY `index3_idx` (`index3`),
  KEY `index4_idx` (`index4`),
  KEY `index5_idx` (`index5`),
  KEY `index6_idx` (`index6`),
  KEY `index7_idx` (`index7`),
  KEY `index8_idx` (`index8`),
  KEY `index9_idx` (`index9`),
  KEY `index10_idx` (`index10`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Sample SQL

select lesson_id,group_level_8,sum(col_1),sum(col_2),sum(col_3),sum(col_4),sum(col_5),sum(col_6),sum(col_7),sum(col_8),sum(col_9),sum(col_10),sum(col_11),sum(col_12),sum(col_13),sum(col_14),sum(col_15),sum(col_16),sum(col_17),sum(col_18),sum(col_19),sum(col_20),sum(col_21),sum(col_22),sum(col_23),sum(col_24),sum(col_25),sum(col_26),sum(col_27),sum(col_28),sum(col_29),sum(col_30),sum(col_31),sum(col_32),sum(col_33),sum(col_34),sum(col_35),sum(col_36),sum(col_37),sum(col_38),sum(col_39),sum(col_40) from tbldatasopcal_1000r_100c  where index1 in (1,2,3) and index2 in (1,2,3) and index4 in (1,2,3) and index5 in (1,2,3) group by lesson_id,group_level_8 limit 1\G

I turned off MPP but it still doesn’t work.

translator_bot · June 23, 2024, 5:41am

| username: h5n1 | Original post link

Check the explain analyze results, try recollecting the statistics, and if that doesn’t work, bind the execution plan using SPM.

translator_bot · June 23, 2024, 5:41am

| username: 是我的海 | Original post link

MySQL [test]> explain analyze select lesson_id, group_level_8, sum(col_1), sum(col_2), sum(col_3), sum(col_4), sum(col_5), sum(col_6), sum(col_7), sum(col_8), sum(col_9), sum(col_10), sum(col_11), sum(col_12), sum(col_13), sum(col_14), sum(col_15), sum(col_16), sum(col_17), sum(col_18), sum(col_19), sum(col_20), sum(col_21), sum(col_22), sum(col_23), sum(col_24), sum(col_25), sum(col_26), sum(col_27), sum(col_28), sum(col_29), sum(col_30), sum(col_31), sum(col_32), sum(col_33), sum(col_34), sum(col_35), sum(col_36), sum(col_37), sum(col_38), sum(col_39), sum(col_40) from tbldatasopcal_1000r_100c where index1 in (1,2,3) and index2 in (1,2,3) and index4 in (1,2,3) and index5 in (1,2,3) group by lesson_id, group_level_8 limit 1\G

*************************** 1. row ***************************
            id: Projection_8
       estRows: 1.00
       actRows: 1
          task: root
 access object: 
execution info: time:3.52s, loops:2, Concurrency:OFF
 operator info: test.tbldatasopcal_1000r_100c.lesson_id, test.tbldatasopcal_1000r_100c.group_level_8, Column#102, Column#103, Column#104, Column#105, Column#106, Column#107, Column#108, Column#109, Column#110, Column#111, Column#112, Column#113, Column#114, Column#115, Column#116, Column#117, Column#118, Column#119, Column#120, Column#121, Column#122, Column#123, Column#124, Column#125, Column#126, Column#127, Column#128, Column#129, Column#130, Column#131, Column#132, Column#133, Column#134, Column#135, Column#136, Column#137, Column#138, Column#139, Column#140, Column#141
        memory: 866.8 KB
          disk: N/A
*************************** 2. row ***************************
            id: └─Limit_11
       estRows: 1.00
       actRows: 1
          task: root
 access object: 
execution info: time:3.52s, loops:2
 operator info: offset:0, count:1
        memory: N/A
          disk: N/A
*************************** 3. row ***************************
            id:   └─HashAgg_33
       estRows: 1.00
       actRows: 422
          task: root
 access object: 
execution info: time:3.52s, loops:1, partial_worker:{wall_time:3.51754035s, concurrency:5, task_num:67, tot_wait:17.38629974s, tot_exec:192.316962ms, tot_time:17.579897943s, max:3.517496181s, p95:3.517496181s}, final_worker:{wall_time:3.524737952s, concurrency:5, task_num:25, tot_wait:17.575486944s, tot_exec:44.791716ms, tot_time:17.620290142s, max:3.524709495s, p95:3.524709495s}
 operator info: group by:test.tbldatasopcal_1000r_100c.group_level_8, test.tbldatasopcal_1000r_100c.lesson_id, funcs:sum(Column#188)->Column#102, funcs:sum(Column#189)->Column#103, funcs:sum(Column#190)->Column#104, funcs:sum(Column#191)->Column#105, funcs:sum(Column#192)->Column#106, funcs:sum(Column#193)->Column#107, funcs:sum(Column#194)->Column#108, funcs:sum(Column#195)->Column#109, funcs:sum(Column#196)->Column#110, funcs:sum(Column#197)->Column#111, funcs:sum(Column#198)->Column#112, funcs:sum(Column#199)->Column#113, funcs:sum(Column#200)->Column#114, funcs:sum(Column#201)->Column#115, funcs:sum(Column#202)->Column#116, funcs:sum(Column#203)->Column#117, funcs:sum(Column#204)->Column#118, funcs:sum(Column#205)->Column#119, funcs:sum(Column#206)->Column#120, funcs:sum(Column#207)->Column#121, funcs:sum(Column#208)->Column#122, funcs:sum(Column#209)->Column#123, funcs:sum(Column#210)->Column#124, funcs:sum(Column#211)->Column#125, funcs:sum(Column#212)->Column#126, funcs:sum(Column#213)->Column#127, funcs:sum(Column#214)->Column#128, funcs:sum(Column#215)->Column#129, funcs:sum(Column#216)->Column#130, funcs:sum(Column#217)->Column#131, funcs:sum(Column#218)->Column#132, funcs:sum(Column#219)->Column#133, funcs:sum(Column#220)->Column#134, funcs:sum(Column#221)->Column#135, funcs:sum(Column#222)->Column#136, funcs:sum(Column#223)->Column#137, funcs:sum(Column#224)->Column#138, funcs:sum(Column#225)->Column#139, funcs:sum(Column#226)->Column#140, funcs:sum(Column#227)->Column#141, funcs:firstrow(test.tbldatasopcal_1000r_100c.lesson_id)->test.tbldatasopcal_1000r_100c.lesson_id, funcs:firstrow(test.tbldatasopcal_1000r_100c.group_level_8)->test.tbldatasopcal_1000r_100c.group_level_8
        memory: 36.5 MB
          disk: N/A
*************************** 4. row ***************************
            id:     └─IndexLookUp_34
       estRows: 1.00
       actRows: 67676
          task: root
 access object: 
execution info: time:3.51s, loops:68, index_task: {total_time: 3.25s, fetch_handle: 219.3ms, build: 165.5µs, wait: 3.03s}, table_task: {total_time: 16.4s, num: 77, concurrency: 5}
 operator info: 
        memory: 21.2 MB
          disk: N/A
*************************** 5. row ***************************
            id:       ├─IndexRangeScan_21(Build)
       estRows: 1490744.26
       actRows: 1499535
          task: cop[tikv]
 access object: table:tbldatasopcal_1000r_100c, index:index1_idx(index1)
execution info: time:151.2ms, loops:1468, cop_task: {num: 2, max: 505ms, min: 148ms, avg: 326.5ms, p95: 505ms, max_proc_keys: 1149318, p95_proc_keys: 1149318, tot_proc: 627ms, tot_wait: 1ms, rpc_num: 2, rpc_time: 652.9ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:465ms, min:136ms, p80:465ms, p95:465ms, iters:1474, tasks:2}, scan_detail: {total_process_keys: 1499535, total_process_keys_size: 68978610, total_keys: 1499539, rocksdb: {delete_skipped_count: 0, key_skipped_count: 1499535, block: {cache_hit_count: 942, read_count: 3, read_byte: 161.0 KB}}}
 operator info: range:[1,1], [2,2], [3,3], keep order:false
        memory: N/A
          disk: N/A
*************************** 6. row ***************************
            id:       └─HashAgg_13(Probe)
       estRows: 1.00
       actRows: 67676
          task: cop[tikv]
 access object: 
execution info: time:15.9s, loops:178, cop_task: {num: 316, max: 472.1ms, min: 1.99ms, avg: 116.2ms, p95: 272.6ms, max_proc_keys: 19850, p95_proc_keys: 11144, tot_proc: 35.2s, tot_wait: 66ms, rpc_num: 316, rpc_time: 36.7s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:460ms, min:1ms, p80:161ms, p95:261ms, iters:1622, tasks:316}, scan_detail: {total_process_keys: 1499535, total_process_keys_size: 566250903, total_keys: 1883176, rocksdb: {delete_skipped_count: 13191, key_skipped_count: 1011522, block: {cache_hit_count: 12369987, read_count: 2401, read_byte: 24.9 MB}}}
 operator info: group by:test.tbldatasopcal_1000r_100c.group_level_8, test.tbldatasopcal_1000r_100c.lesson_id, funcs:sum(test.tbldatasopcal_1000r_100c.col_1)->Column#188, funcs:sum(test.tbldatasopcal_1000r_100c.col_2)->Column#189, funcs:sum(test.tbldatasopcal_1000r_100c.col_3)->Column#190, funcs:sum(test.tbldatasopcal_1000r_100c.col_4)->Column#191, funcs:sum(test.tbldatasopcal_1000r_100c.col_5)->Column#192, funcs:sum(test.tbldatasopcal_1000r_100c.col_6)->Column#193, funcs:sum(test.tbldatasopcal_1000r_100c.col_7)->Column#194, funcs:sum(test.tbldatasopcal_1000r_100c.col_8)->Column#195, funcs:sum(test.tbldatasopcal_1000r_100c.col_9)->Column#196, funcs:sum(test.tbldatasopcal_1000r_100c.col_10)->Column#197, funcs:sum(test.tbldatasopcal_1000r_100c.col_11)->Column#198, funcs:sum(test.tbldatasopcal_1000r_100c.col_12)->Column#199, funcs:sum(test.tbldatasopcal_1000r_100c.col_13)->Column#200, funcs:sum(test.tbldatasopcal_1000r_100c.col_14)->Column#201, funcs:sum(test.tbldatasopcal_1000r_100c.col_15)->Column#202, funcs:sum(test.tbldatasopcal_1000r_100c.col_16)->Column#203, funcs:sum(test.tbldatasopcal_1000r_100c.col_17)->Column#204, funcs:sum(test.tbldatasopcal_1000r_100c.col_18)->Column#205, funcs:sum(test.tbldatasopcal_1000r_100c.col_19)->Column#206, funcs:sum(test.tbldatasopcal_1000r_100c.col_20)->Column#207, funcs:sum(test.tbldatasopcal_1000r_100c.col_21)->Column#208, funcs:sum(test.tbldatasopcal_1000r_100c.col_22)->Column#209, funcs:sum(test.tbldatasopcal_1000r_100c.col_23)->Column#210, funcs:sum(test.tbldatasopcal_1000r_100c.col_24)->Column#211, funcs:sum(test.tbldatasopcal_1000r_100c.col_25)->Column#212, funcs:sum(test.tbldatasopcal_1000r_100c.col_26)->Column#213, funcs:sum(test.tbldatasopcal_1000r_100c.col_27)->Column#214, funcs:sum(test.tbldatasopcal_1000r_100c.col_28)->Column#215, funcs:sum(test.tbldatasopcal_1000r_100c.col_29)->Column#216, funcs:sum(test.tbldatasopcal_1000r_100c.col_30)->Column#217, funcs:sum(test.tbldatasopcal_1000r_100c.col_31)->Column#218, funcs:sum(test.tbldatasopcal_1000r_100c.col_32)->Column#219, funcs:sum(test.tbldatasopcal_1000r_100c.col_33)->Column#220, funcs:sum(test.tbldatasopcal_1000r_100c.col_34)->Column#221, funcs:sum(test.tbldatasopcal_1000r_100c.col_35)->Column#222, funcs:sum(test.tbldatasopcal_1000r_100c.col_36)->Column#223, funcs:sum(test.tbldatasopcal_1000r_100c.col_37)->Column#224, funcs:sum(test.tbldatasopcal_1000r_100c.col_38)->Column#225, funcs:sum(test.tbldatasopcal_1000r_100c.col_39)->Column#226, funcs:sum(test.tbldatasopcal_1000r_100c.col_40)->Column#227
        memory: N/A
          disk: N/A
*************************** 7. row ***************************
            id:         └─Selection_23
       estRows: 619518.68
       actRows: 1387537
          task: cop[tikv]
 access object: 
execution info: tikv_task:{proc max:388ms, min:1ms, p80:135ms, p95:221ms, iters:1622, tasks:316}
 operator info: in(test.tbldatasopcal_1000r_100c.index2, 1, 2, 3), in(test.tbldatasopcal_1000r_100c.index4, 1, 2, 3), in(test.tbldatasopcal_1000r_100c.index5, 1, 2, 3)
        memory: N/A
          disk: N/A
*************************** 8. row ***************************
            id:           └─TableRowIDScan_22
       estRows: 1490744.26
       actRows: 1499535
          task: cop[tikv]
 access object: table:tbldatasopcal_1000r_100c
execution info: tikv_task:{proc max:385ms, min:1ms, p80:134ms, p95:220ms, iters:1622, tasks:316}
 operator info: keep order:false
        memory: N/A
          disk: N/A
8 rows in set (3.53 sec)

translator_bot · June 23, 2024, 5:41am

| username: 是我的海 | Original post link

Recollecting statistics didn’t work either. This is a new cluster, and our SQL queries are mostly of this aggregate type. Binding execution plans is not very maintainable. Just wanted to see the reason for this.

Additionally, directly executing count can reach TiFlash.

translator_bot · June 23, 2024, 5:41am

| username: ShawnYan | Original post link

It might also be related to cost calculation. Try forcing it to use TiFlash.
set @@session.tidb_isolation_read_engines = 'tiflash'

translator_bot · June 23, 2024, 5:41am

| username: forever | Original post link

This change has a significant impact; even those that normally don’t need to go through TiFlash will have to.

translator_bot · June 23, 2024, 5:41am

| username: wish-PingCAP | Original post link

If needed, you can use a hint to always require this statement to use TiFlash: 使用 TiDB 读取 TiFlash | PingCAP 文档中心

translator_bot · June 23, 2024, 5:41am

| username: wish-PingCAP | Original post link

If the execution plan is fixed to use TiKV, it means that in the new version, the optimizer’s cost estimation considers that using TiKV index query for this SQL is faster than using TiFlash full table scan. You can observe whether this SQL executes faster or slower compared to before the upgrade.

translator_bot · June 23, 2024, 5:41am

| username: xiaohetao | Original post link

I think you can temporarily create SPM or hit binding, and execute it to see whether TiKV or TiFlash is more efficient. If TiKV is more efficient, it means that the database calculation after KV is more efficient than Flash, so it uses KV.

translator_bot · June 23, 2024, 5:41am

| username: 是我的海 | Original post link

In version 6.1, specifying TiFlash for this query is much faster than using TiKV. Since it’s a new business being tested, specifying a hint right from the start feels quite unfriendly; the database should route to the correct node by itself. The current phenomenon seems to be an optimizer selection issue.

translator_bot · June 23, 2024, 5:41am

| username: 是我的海 | Original post link

It is definitely faster to use TiFlash. In version 5.4, this is the case. In version 6.1, using TiKV takes more than 2 seconds, while specifying TiFlash takes less than 1 second.

translator_bot · June 23, 2024, 5:41am

| username: xiaohetao | Original post link

Is the parameter tidb_enable_index_merge enabled?

translator_bot · June 23, 2024, 5:41am

| username: 是我的海 | Original post link

It is enabled, but I remember not actively enabling it. After closing it and logging in again, it still doesn’t work.

In the same session, with the same SQL, I change the table name, one uses TiFlash, and the other does not.

Querying tbldatasopcal_500r_100c does not use TiFlash;

Image1380×720 98.8 KB
Querying tbldatasopcal_500r_200c uses TiFlash;

Image1380×572 97.8 KB

Both tables are synchronized to TiFlash;

translator_bot · June 23, 2024, 5:41am

| username: xiaohetao | Original post link

Refer to this to check if the execution plan used by SQL is cached.

MySQL [test]> select @@last_plan_from_cache; – Check plan cache
±-----------------------+
| @@last_plan_from_cache |
±-----------------------+
| 1 |
±-----------------------+
1 row in set (0.00 sec)

MySQL [test]> admin flush session plan_cache; – Clear the current session’s plan cache
Query OK, 0 rows affected (0.00 sec)

MySQL [test]> execute stmt;
Empty set (0.00 sec)

MySQL [test]> select @@last_plan_from_cache; – Since the cache was cleared, it cannot be selected again
±-----------------------+
| @@last_plan_from_cache |
±-----------------------+
| 0 |
±-----------------------+
1 row in set (0.00 sec)

translator_bot · June 23, 2024, 5:41am

| username: xiaohetao | Original post link

Cannot clear globally

translator_bot · June 23, 2024, 5:41am

| username: 是我的海 | Original post link

Hmm, still not working, my cache return value is 0. Thanks XD

translator_bot · June 23, 2024, 5:41am

| username: xiaohetao | Original post link

If the cause cannot be found, let’s temporarily create an SMP.

translator_bot · June 23, 2024, 5:41am

| username: h5n1 | Original post link

You can use EXPLAIN FORMAT='verbose' to check the estCost evaluation of the SQL in two different versions.