您好，应该是 region 太多 load region 的时候超时了，属于产品缺陷，会在后面的版本修复。
1 个赞
麻烦看一下 order by {primary key} limit 1，这里是 order by dt, doc_id limit 1，是否有效果，辛苦。
这个是之前的命令执行的慢查询日志和执行计划：
mysql> select *from CLUSTER_SLOW_QUERY where Time>‘2022-07-05 08:47:06’ and Query like ‘%order by updatetime desc%’ \G
*************************** 1. row ***************************
INSTANCE: ip:10081
Time: 2022-07-05 12:27:53.806273
Txn_start_ts: 434369239640440835
User: root
Host: ip
Conn_ID: 40897434507435329
Exec_retry_count: 0
Exec_retry_time: 0
Query_time: 8123.042355201
Parse_time: 0.00009288
Compile_time: 0.000629188
Rewrite_time: 0.000217526
Preproc_subqueries: 0
Preproc_subqueries_time: 0
Optimize_time: 0.000240405
Wait_TS: 0.000009835
Prewrite_time: 0
Wait_prewrite_binlog_time: 0
Commit_time: 0
Get_commit_ts_time: 0
Commit_backoff_time: 0
Backoff_types:
Resolve_lock_time: 0
Local_latch_wait_time: 0
Write_keys: 0
Write_size: 0
Prewrite_region: 0
Txn_retry: 0
Cop_time: 8117.674907775
Process_time: 92433.098
Wait_time: 3868.943
Backoff_time: 39.389
LockKeys_time: 0
Request_count: 4423
Total_keys: 6346321634
Process_keys: 6345497307
Rocksdb_delete_skipped_count: 0
Rocksdb_key_skipped_count: 12652372082
Rocksdb_block_cache_hit_count: 3115834
Rocksdb_block_read_count: 49290287
Rocksdb_block_read_byte: 1102557612505
DB:
Index_names:
Is_internal: 0
Digest: 4dea788f1d488938b13a826075b53c206b2eb3be7ba86e1e0660776be5773e89
Stats: logoutrole:pseudo
Cop_proc_avg: 20.898281257
Cop_proc_p90: 36.912
Cop_proc_max: 59.9
Cop_proc_addr: ip:20161
Cop_wait_avg: 0.87473276
Cop_wait_p90: 2.06
Cop_wait_max: 31.784
Cop_wait_addr: ip:20161
Mem_max: 9222
Disk_max: 0
KV_total: 121758.569134003
PD_total: 4.189410657
Backoff_total: 39.701
Write_sql_response_total: 0
Result_rows: 0
Backoff_Detail: Cop_backoff_regionMiss_total_times: 4155 Cop_backoff_regionMiss_total_time: 8.746 Cop_backoff_regionMiss_max_time: 0.014 Cop_backoff_regionMiss_max_addr: ip:20163 Cop_backoff_regionMiss_avg_time: 0.002209143 Cop_backoff_regionMiss_p90_time: 0.002 Cop_backoff_tikvRPC_total_times: 410 Cop_backoff_tikvRPC_total_time: 30.643 Cop_backoff_tikvRPC_max_time: 0.226 Cop_backoff_tikvRPC_max_addr: ip:20161 Cop_backoff_tikvRPC_avg_time: 0.075475369 Cop_backoff_tikvRPC_p90_time: 0.094
Prepared: 0
Succ: 0
IsExplicitTxn: 0
IsWriteCacheTable: 0
Plan_from_cache: 0
Plan_from_binding: 0
Has_more_results: 0
Plan: id task estRows operator info actRows execution info memory disk
TopN_7 root 1 mars_p1log.logoutrole.updatetime:desc, offset:0, count:1 0 time:2h15m20.3s, loops:1 5.97 KB N/A
└─TableReader_14 root 1 data:TopN_13 4423 time:2h15m20.3s, loops:6, cop_task: {num: 4423, max: 2m8.2s, min: 734.2ms, avg: 24.8s, p95: 59.6s, max_proc_keys: 2173640, p95_proc_keys: 1661001, tot_proc: 25h40m33.1s, tot_wait: 1h4m28.9s, rpc_num: 4958, rpc_time: 33h38m2.7s, copr_cache_hit_ratio: 0.00}, backoff{tikvRPC: 65ms, regionMiss: 2ms} 3.19 KB N/A
└─TopN_13 cop[tikv] 1 mars_p1log.logoutrole.updatetime:desc, offset:0, count:1 4423 tikv_task:{proc max:59.8s, min:724ms, p80:29.4s, p95:43.2s, iters:6198984, tasks:4423}, scan_detail: {total_process_keys: 6345497307, total_process_keys_size: 3162198806097, total_keys: 6346321634, rocksdb: {delete_skipped_count: 0, key_skipped_count: 12652372082, block: {cache_hit_count: 3115834, read_count: 49290287, read_byte: 1026.8 GB}}} N/A N/A
└─TableFullScan_12 cop[tikv] 32624796380 table:logoutrole, keep order:false, stats:pseudo 6345497307 tikv_task:{proc max:59.1s, min:692ms, p80:28.8s, p95:42.6s, iters:6198984, tasks:4423} N/A N/A
Plan_digest: a409ddb05d962f3b3e1af7bffb69d3f24687653d61068adb0bdd6d23f5336023
Prev_stmt:
Query: select *from mars_p1log.logoutrole order by updatetime desc limit 1;
1 row in set (0.34 sec)
order by主键这个，命令执行了差不多30min了但是还没有返回结果，目前还查不到它的慢查询
等出来了我再贴出来。慢查询信息如下：
mysql> select * from INFORMATION_SCHEMA.CLUSTER_SLOW_QUERY where Query like ‘%order by dt, doc_id%’ \G
*************************** 1. row ***************************
INSTANCE: ip:10081
Time: 2022-07-05 18:46:53.833957
Txn_start_ts: 434375223339384837
User: root
Host: ip
Conn_ID: 40897434507453657
Exec_retry_count: 0
Exec_retry_time: 0
Query_time: 8037.060594391
Parse_time: 0.000060838
Compile_time: 0.000590229
Rewrite_time: 0.000158756
Preproc_subqueries: 0
Preproc_subqueries_time: 0
Optimize_time: 0.000322716
Wait_TS: 0.000012893
Prewrite_time: 0
Wait_prewrite_binlog_time: 0
Commit_time: 0
Get_commit_ts_time: 0
Commit_backoff_time: 0
Backoff_types:
Resolve_lock_time: 0
Local_latch_wait_time: 0
Write_keys: 0
Write_size: 0
Prewrite_region: 0
Txn_retry: 0
Cop_time: 8031.034976326
Process_time: 91106.579
Wait_time: 5333.81
Backoff_time: 38.188
LockKeys_time: 0
Request_count: 4247
Total_keys: 6091172373
Process_keys: 6090348222
Rocksdb_delete_skipped_count: 0
Rocksdb_key_skipped_count: 12144647876
Rocksdb_block_cache_hit_count: 2920542
Rocksdb_block_read_count: 47394698
Rocksdb_block_read_byte: 1059776720491
DB:
Index_names:
Is_internal: 0
Digest: a790a1bbb83ada017c9b3a00a6bf35e0077d6b38f662fc1c8574580dd7aa172e
Stats: logoutrole:pseudo
Cop_proc_avg: 21.451984695
Cop_proc_p90: 37.195
Cop_proc_max: 59.572
Cop_proc_addr: ip:20161
Cop_wait_avg: 1.255900635
Cop_wait_p90: 3.8
Cop_wait_max: 34.671
Cop_wait_addr: ip:20161
Mem_max: 9475
Disk_max: 0
KV_total: 120463.492542497
PD_total: 4.530507504
Backoff_total: 38.632
Write_sql_response_total: 0
Result_rows: 0
Backoff_Detail: Cop_backoff_regionMiss_total_times: 3957 Cop_backoff_regionMiss_total_time: 8.18 Cop_backoff_regionMiss_max_time: 0.014 Cop_backoff_regionMiss_max_addr: ip:20161 Cop_backoff_regionMiss_avg_time: 0.002131318 Cop_backoff_regionMiss_p90_time: 0.002 Cop_backoff_tikvRPC_total_times: 387 Cop_backoff_tikvRPC_total_time: 30.008 Cop_backoff_tikvRPC_max_time: 0.272 Cop_backoff_tikvRPC_max_addr: ip:20163 Cop_backoff_tikvRPC_avg_time: 0.080235294 Cop_backoff_tikvRPC_p90_time: 0.096
Prepared: 0
Succ: 0
IsExplicitTxn: 0
IsWriteCacheTable: 0
Plan_from_cache: 0
Plan_from_binding: 0
Has_more_results: 0
Plan: id task estRows operator info actRows execution info memory disk
TopN_7 root 1 mars_p1log.logoutrole.dt, mars_p1log.logoutrole.doc_id, offset:0, count:1 0 time:2h13m53.9s, loops:1 6.00 KB N/A
└─TableReader_14 root 1 data:TopN_13 4247 time:2h13m53.9s, loops:6, cop_task: {num: 4247, max: 2m18.1s, min: 854.6ms, avg: 26.5s, p95: 1m17.9s, max_proc_keys: 2173640, p95_proc_keys: 1660982, tot_proc: 25h18m26.6s, tot_wait: 1h28m53.8s, rpc_num: 4711, rpc_time: 33h17m13.4s, copr_cache_hit_ratio: 0.00}, backoff{tikvRPC: 61ms, regionMiss: 2ms} 3.25 KB N/A
└─TopN_13 cop[tikv] 1 mars_p1log.logoutrole.dt, mars_p1log.logoutrole.doc_id, offset:0, count:1 4247 tikv_task:{proc max:59s, min:832ms, p80:30s, p95:42.9s, iters:5949720, tasks:4247}, scan_detail: {total_process_keys: 6090348222, total_process_keys_size: 3035318514859, total_keys: 6091172373, rocksdb: {delete_skipped_count: 0, key_skipped_count: 12144647876, block: {cache_hit_count: 2920542, read_count: 47394698, read_byte: 987.0 GB}}} N/A N/A
└─TableFullScan_12 cop[tikv] 33419881389 table:logoutrole, keep order:false, stats:pseudo 6090348222 tikv_task:{proc max:58.4s, min:788ms, p80:29.4s, p95:42.2s, iters:5949720, tasks:4247} N/A N/A
Plan_digest: 2bd74e41cabb6604ac56e262bdc629187d081b29252c5efc9543e857554d49b4
Prev_stmt:
Query: select * from mars_p1log.logoutrole order by dt, doc_id limit 1;
1 row in set (1.92 sec)
您好，此处的 workaround 是直接加在原始 sql 上执行的，请在原始 sql 上加上 order by 后执行，再查看执行计划，辛苦。
此处应该是
select * from mars_p1log.logoutrole order by dt, doc_id limit 1 \G
上面贴了两个执行计划，有一个是 order by主键的
此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。