【 TiDB 使用环境】生产环境
【 TiDB 版本】tidb 7
【复现路径】
-
现在有2个tidb集群，tidb5 tidb7，tidb5是生产集群，tidb7是用于升级的测试集群。
-
tidb5 和tidb7执行计划相差太多。执行耗时相差1-2h。
-
分别使用SMP
/*+ read_from_storage(tiflash[s]) */、/*+ USE_INDEX(s, IDX_SUMMARY_TYPE) */都不管用 -
大表 st_summary_bill_wide_table 27亿行左右，表已经录入tiflash
sql如下:
SELECT COUNT(DISTINCT(I_UID)) I_USER_COUNT FROM st_summary_bill_wide_table s LEFT JOIN st_region_code_new p ON s.CH_USER_COUNTRY_CODE = p.CH_CODE WHERE (B_IS_POOR=1 AND I_STAT_DEALER_IS_YIYUN=1 AND I_STAT_IS_TEST=0 AND I_SUMMARY_TYPE IN (1,3,5,7,9,11,118,124))
tidb5执行计划：
| id task estRows operator info actRows execution info memory disk
StreamAgg_11 root 1 funcs:count(distinct stat.st_summary_bill_wide_table.i_uid)->Column#124 1 time:3m18s, loops:2 31.7 MB N/A
└─IndexHashJoin_75 root 41121236.71 inner join, inner:IndexLookUp_72, outer key:stat.st_region_code_new.ch_code, inner key:stat.st_summary_bill_wide_table.ch_user_country_code, equal cond:eq(stat.st_region_code_new.ch_code, stat.st_summary_bill_wide_table.ch_user_country_code) 14616977 time:3m16.5s, loops:14276, inner:{total:3m18s, concurrency:8, task:1, construct:65.6µs, fetch:3m9.3s, build:34.6µs, join:8.61s} 615.9 MB N/A
├─TableReader_56 root 52 data:Selection_55 52 time:702.1µs, loops:3, cop_task: {num: 1, max: 695.4µs, proc_keys: 0, rpc_num: 1, rpc_time: 679.3µs, copr_cache_hit_ratio: 1.00} 1.35 KB N/A
│ └─Selection_55 cop[tikv] 52 eq(stat.st_region_code_new.b_is_poor, 1) 52 tikv_task:{time:5ms, loops:11}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
│ └─TableFullScan_54 cop[tikv] 6792 table:p, keep order:false 6792 tikv_task:{time:5ms, loops:11}, scan_detail: {total_process_keys: 0, total_keys: 0, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 0, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─IndexLookUp_72 root 790793.01 14616977 time:3m9.3s, loops:14276, index_task: {total_time: 3m6.4s, fetch_handle: 904.5ms, build: 709.7µs, wait: 3m5.5s}, table_task: {total_time: 25m8.2s, num: 362, concurrency: 8} 104.0 MB N/A
├─IndexRangeScan_69 cop[tikv] 1523035.98 table:s, index:IDX_USER_COUNTY_CODE(CH_USER_COUNTRY_CODE), range: decided by [eq(stat.st_summary_bill_wide_table.ch_user_country_code, stat.st_region_code_new.ch_code)], keep order:false 14617569 time:160.1ms, loops:14296, cop_task: {num: 40, max: 515ms, min: 10.8ms, avg: 201.3ms, p95: 466.4ms, max_proc_keys: 1062909, p95_proc_keys: 903186, tot_proc: 7.49s, tot_wait: 90ms, rpc_num: 40, rpc_time: 8.05s, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:463ms, min:9ms, p80:303ms, p95:428ms, iters:14458, tasks:40}, scan_detail: {total_process_keys: 14617569, total_keys: 14617639, rocksdb: {delete_skipped_count: 15, key_skipped_count: 14617581, block: {cache_hit_count: 910, read_count: 8974, read_byte: 159.2 MB}}} N/A N/A
└─Selection_71 cop[tikv] 790793.01 eq(stat.st_summary_bill_wide_table.i_stat_is_test, 0), in(stat.st_summary_bill_wide_table.i_summary_type, 1, 3, 5, 7, 9, 11, 118, 124) 14616977 time:24m41.3s, loops:14653, cop_task: {num: 1294808, max: 7.85s, min: 239.9µs, avg: 12.9ms, p95: 37.4ms, max_proc_keys: 1365, p95_proc_keys: 38, tot_proc: 2h38m17s, tot_wait: 1h45m12.9s, rpc_num: 1294810, rpc_time: 4h38m8.5s, copr_cache_hit_ratio: 0.00}, backoff{regionMiss: 10ms}, tikv_task:{proc max:221ms, min:0s, p80:3ms, p95:9ms, iters:1398539, tasks:1294808}, scan_detail: {total_process_keys: 14533425, total_keys: 14565903, rocksdb: {delete_skipped_count: 3, key_skipped_count: 100220, block: {cache_hit_count: 189212838, read_count: 9736461, read_byte: 97.5 GB}}} N/A N/A
└─TableRowIDScan_70 cop[tikv] 1523035.98 table:s, keep order:false
tidb 7 执行计划
| id task estRows operator info actRows execution info memory disk
StreamAgg_10 root 1 funcs:count(distinct stat.st_summary_bill_wide_table.i_uid)->Column#124 1 time:2h18m48.9s, loops:2 33.0 MB N/A
└─IndexJoin_70 root 22.14 inner join, inner:IndexLookUp_69, outer key:stat.st_summary_bill_wide_table.ch_user_country_code, inner key:stat.st_region_code_new.ch_code, equal cond:eq(stat.st_summary_bill_wide_table.ch_user_country_code, stat.st_region_code_new.ch_code) 12733799 time:2h18m45.1s, loops:12437, inner:{total:3h37m35.7s, concurrency:5, task:49173, construct:23m0.4s, fetch:3h14m34.4s, build:807.6ms}, probe:1m53.4s 22.9 MB N/A
├─IndexLookUp_61(Build) root 22.14 1258606479 time:2h10m18.4s, loops:1229113, index_task: {total_time: 2h18m47.2s, fetch_handle: 1m7.2s, build: 102.6ms, wait: 2h17m39.9s}, table_task: {total_time: 11h28m51.8s, num: 62407, concurrency: 5}, next: {wait_index: 47.3ms, wait_table_lookup_build: 2.92ms, wait_table_lookup_resp: 2h8m55.6s} 93.9 MB N/A
│ ├─IndexRangeScan_58(Build) cop[tikv] 22135971.82 table:s, index:IDX_SUMMARY_TYPE(I_SUMMARY_TYPE), range:[1,1], [3,3], [5,5], [7,7], [9,9], [11,11], [118,118], [124,124], keep order:false, stats:partial[i_summary_type:unInitialized, i_stat_dealer_is_yiyun:unInitialized, i_stat_is_test:unInitialized] 1277395058 time:2.57s, loops:1252031, cop_task: {num: 36226, max: 1.8s, min: 660.9µs, avg: 35.2ms, p95: 146.2ms, max_proc_keys: 50144, p95_proc_keys: 50144, tot_proc: 10m15s, tot_wait: 36.6s, rpc_num: 36226, rpc_time: 21m15.1s, copr_cache_hit_ratio: 0.00, build_task_duration: 2.46ms, max_distsql_concurrency: 60}, backoff{regionMiss: 2ms}, tikv_task:{proc max:1.79s, min:0s, avg: 31.6ms, p80:26ms, p95:138ms, iters:1391311, tasks:36226}, scan_detail: {total_process_keys: 1277394834, total_process_keys_size: 58760162364, total_keys: 1277481045, get_snapshot_time: 31.8s, rocksdb: {delete_skipped_count: 2156, key_skipped_count: 1277446291, block: {cache_hit_count: 385420, read_count: 1563890, read_byte: 2.59 GB, read_time: 8.55s}}} N/A N/A
│ └─Selection_60(Probe) cop[tikv] 22.14 eq(stat.st_summary_bill_wide_table.i_stat_dealer_is_yiyun, 1), eq(stat.st_summary_bill_wide_table.i_stat_is_test, 0) 1258606479 time:11h21m51.7s, loops:1373707, cop_task: {num: 251343, max: 4.78s, min: 0s, avg: 278.9ms, p95: 886.2ms, max_proc_keys: 20992, p95_proc_keys: 19204, tot_proc: 14h7m2.6s, tot_wait: 7m5.4s, rpc_num: 234062, rpc_time: 19h28m12.4s, copr_cache_hit_ratio: 0.01, build_task_duration: 59.4s, max_distsql_concurrency: 60, max_extra_concurrency: 199, store_batch_num: 17403, store_batch_fallback_num: 1764}, backoff{regionMiss: 50ms}, tikv_task:{proc max:4.71s, min:0s, avg: 271.3ms, p80:485ms, p95:865ms, iters:2231739, tasks:251343}, scan_detail: {total_process_keys: 1276853406, total_process_keys_size: 1348452498966, total_keys: 1501760419, get_snapshot_time: 5m49.3s, rocksdb: {delete_skipped_count: 1545, key_skipped_count: 1127346316, block: {cache_hit_count: 9025552284, read_count: 127214601, read_byte: 1417.5 GB, read_time: 5h59m25.9s}}}N/A N/A
│ └─TableRowIDScan_59 cop[tikv] 22135971.82 table:s, keep order:false, stats:partial[i_summary_type:unInitialized, i_stat_dealer_is_yiyun:unInitialized, i_stat_is_test:unInitialized] 1277395058 tikv_task:{proc max:4.71s, min:0s, avg: 271ms, p80:484ms, p95:864ms, iters:2231739, tasks:251343} N/A N/A
└─IndexLookUp_69(Probe) root 22.14 1925153 time:3h11m56.1s, loops:98345, index_task: {total_time: 2h46m55.8s, fetch_handle: 2h46m55.2s, build: 111.6ms, wait: 515.1ms}, table_task: {total_time: 48m27.1s, num: 98335, concurrency: 5}, next: {wait_index: 2h23m40.3s, wait_table_lookup_build: 20s, wait_table_lookup_resp: 47m54s} 8.02 KB N/A
├─IndexRangeScan_66(Build) cop[tikv] 22.14 table:p, index:UNIQ_CODE(CH_CODE), range: decided by [eq(stat.st_region_code_new.ch_code, stat.st_summary_bill_wide_table.ch_user_country_code)], keep order:false 126747444 time:2h46m49.3s, loops:245625, cop_task: {num: 196576, max: 1.23s, min: 945.3µs, avg: 50.7ms, p95: 166.9ms, max_proc_keys: 1115, p95_proc_keys: 992, tot_proc: 1h13m59.4s, tot_wait: 1m23.3s, rpc_num: 196601, rpc_time: 2h46m5.2s, copr_cache_hit_ratio: 0.00, build_task_duration: 3.38s, max_distsql_concurrency: 1}, backoff{regionMiss: 58ms}, tikv_task:{proc max:1.22s, min:0s, avg: 46.3ms, p80:54ms, p95:160ms, iters:843735, tasks:196576}, scan_detail: {total_process_keys: 126747444, total_process_keys_size: 5576887536, total_keys: 253642294, get_snapshot_time: 32.8s, rocksdb: {key_skipped_count: 207989897, block: {cache_hit_count: 1815330552, read_count: 1284, read_byte: 18.9 MB, read_time: 38.8ms}}} N/A N/A
└─Selection_68(Probe) cop[tikv] 22.14 eq(stat.st_region_code_new.b_is_poor, 1) 1925153 time:48m3.9s, loops:147531, cop_task: {num: 98335, max: 1.2s, min: 795.9µs, avg: 29.1ms, p95: 112.7ms, max_proc_keys: 1787, p95_proc_keys: 1687, tot_proc: 15m6.5s, tot_wait: 39.8s, rpc_num: 98342, rpc_time: 47m43.8s, copr_cache_hit_ratio: 0.00, build_task_duration: 3.82s, max_distsql_concurrency: 1, max_extra_concurrency: 1}, backoff{regionMiss: 14ms}, tikv_task:{proc max:1.2s, min:0s, avg: 26.7ms, p80:21ms, p95:109ms, iters:589696, tasks:98335}, scan_detail: {total_process_keys: 126747444, total_process_keys_size: 21972068432, total_keys: 145629249, get_snapshot_time: 13.4s, rocksdb: {key_skipped_count: 198152920, block: {cache_hit_count: 328545465, read_count: 1080, read_byte: 148.3 MB, read_time: 162.4ms}}} N/A N/A
└─TableRowIDScan_67 cop[tikv] 22.14 table:p, keep order:false 126747444 tikv_task:{proc max:1.2s, min:0s, avg: 26.6ms, p80:21ms, p95:109ms, iters:589696, tasks:98335} N/A N/A |
统计信息结果：大表的统计信息没收到，实际昨天自动统计了1天
mysql> SHOW COLUMN_STATS_USAGE where Table_name='st_summary_bill_wide_table';
Empty set (0.06 sec)
mysql> show global variables like '%tidb_analyze_version%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| tidb_analyze_version | 1 |
+----------------------+-------+
