Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: total_keys扫描key多
|
username: weibiao
To improve efficiency, please provide the following information. A clear problem description can help solve the issue faster:
[TiDB Usage Environment]
[Overview] Scenario + Problem Overview
File table, currently a single table with 1.1T and 760 million records, indexed with KEY idx_fta (file_extension,transition_state,addtime), but it still takes around 500 milliseconds. GC time is set to 24 hours, and high key scanning is observed: scan_detail: {total_process_keys: 706, total_keys: 4277610, rocksdb: {delete_skipped_count: 2122, key_skipped_count: 4580802
[Background] Actions taken
[Phenomenon] Business and database phenomena
[Problem] Current issues encountered
[Business Impact]
[TiDB Version] v5.2.2
[Application Software and Version]
[Attachments] Relevant logs and configuration information
- TiUP Cluster Display information
- TiUP Cluster Edit config information
Monitoring (https://metricstool.pingcap.com/)
- TiDB-Overview Grafana monitoring
- TiDB Grafana monitoring
- TiKV Grafana monitoring
- PD Grafana monitoring
- Corresponding module logs (including logs one hour before and after the issue)
SQL: desc analyze SELECT count(*) AS sum FROM eeo_files WHERE addtime > 1662974822 AND transition_state IN(0, 1, 4) AND file_extension IN(‘jpeg’, ‘jpg’, ‘png’, ‘bmp’) AND system_del = 0;
id task estRows operator info actRows execution info memory disk
HashAgg_18 root 1 funcs:count(Column#29)->Column#26 1 time:413.5ms, loops:2, partial_worker:{wall_time:413.481424ms, concurrency:5, task_num:1, tot_wait:2.067209901s, tot_exec:26.056µs, tot_time:2.067243399s, max:413.460881ms, p95:413.460881ms}, final_worker:{wall_time:413.538036ms, concurrency:5, task_num:1, tot_wait:2.067462191s, tot_exec:24.719µs, tot_time:2.067489543s, max:413.510606ms, p95:413.510606ms} 9.55 KB N/A
└─IndexLookUp_19 root 1 81 time:413.4ms, loops:2, index_task: {total_time: 396.6ms, fetch_handle: 396.6ms, build: 1.26µs, wait: 3.81µs}, table_task: {total_time: 2s, num: 1, concurrency: 5} 20.5 KB N/A
├─IndexRangeScan_15 cop[tikv] 14.11 table:eeo_files, index:idx_fta(file_extension, transition_state, addtime), range:("bmp" 0 1662988682,"bmp" 0 +inf], ("bmp" 1 1662988682,"bmp" 1 +inf], ("bmp" 4 1662988682,"bmp" 4 +inf], ("jpeg" 0 1662988682,"jpeg" 0 +inf], ("jpeg" 1 1662988682,"jpeg" 1 +inf], ("jpeg" 4 1662988682,"jpeg" 4 +inf], ("jpg" 0 1662988682,"jpg" 0 +inf], ("jpg" 1 1662988682,"jpg" 1 +inf], ("jpg" 4 1662988682,"jpg" 4 +inf], ("png" 0 1662988682,"png" 0 +inf], ("png" 1 1662988682,"png" 1 +inf], ("png" 4 1662988682,"png" 4 +inf], keep order:false 726 time:396.6ms, loops:3, cop_task: {num: 14, max: 396.4ms, min: 842.1µs, avg: 90.3ms, p95: 396.4ms, max_proc_keys: 315, p95_proc_keys: 315, tot_proc: 1.25s, tot_wait: 8ms, rpc_num: 14, rpc_time: 1.26s, copr_cache_hit_ratio: 0.29}, tikv_task:{proc max:438ms, min:0s, p80:284ms, p95:438ms, iters:22, tasks:14}, scan_detail: {total_process_keys: 706, total_keys: 4277610, rocksdb: {delete_skipped_count: 2122, key_skipped_count: 4580802, block: {cache_hit_count: 3655, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─HashAgg_7 cop[tikv] 1 funcs:count(1)->Column#29 81 time:16ms, loops:2, cop_task: {num: 81, max: 10.4ms, min: 547.8µs, avg: 1.23ms, p95: 1.98ms, max_proc_keys: 301, p95_proc_keys: 12, tot_proc: 36ms, tot_wait: 16ms, rpc_num: 81, rpc_time: 98.8ms, copr_cache_hit_ratio: 0.00}, tikv_task:{proc max:9ms, min:0s, p80:1ms, p95:1ms, iters:81, tasks:81}, scan_detail: {total_process_keys: 726, total_keys: 1306, rocksdb: {delete_skipped_count: 330, key_skipped_count: 1834, block: {cache_hit_count: 4349, read_count: 0, read_byte: 0 Bytes}}} N/A N/A
└─Selection_17 cop[tikv] 14.11 eq(eo_osfile.eeo_files.system_del, 0) 726 tikv_task:{proc max:9ms, min:0s, p80:1ms, p95:1ms, iters:81, tasks:81} N/A N/A
└─TableRowIDScan_16 cop[tikv] 14.11 table:eeo_files, keep order:false 726 tikv_task:{proc max:9ms, min:0s, p80:1ms, p95:1ms, iters:81, tasks:81} N/A N/A
|
username: 
|
username: 
|
username: 
|
username: 
|
username: