我手机打字的问题，多打了字。
把那些执行分钟级别的sql执行计划 先发下
id task estRows operator info actRows execution info memory disk
Limit_19 root 0.00 offset:0, count:400 5 time:3m36.4s, loops:2 N/A N/A
└─Projection_74 root 0.00 room_db.t_room_member.room_id, room_db.t_room_info.room_name, room_db.t_room_info.priority, room_db.t_room_info.creator, room_db.t_room_info.queue_size, room_db.t_room_info.description, room_db.t_room_info.speak_limit 5 time:3m36.4s, loops:2, Concurrency:OFF 3.60 KB N/A
└─IndexJoin_81 root 0.00 inner join, inner:IndexLookUp_80, outer key:room_db.t_room_member.room_id, inner key:room_db.t_room_info.room_id, equal cond:eq(room_db.t_room_member.room_id, room_db.t_room_info.room_id) 5 time:3m36.4s, loops:2, inner:{total:1.57ms, concurrency:5, task:1, construct:25.5µs, fetch:1.54ms, build:5.81µs}, probe:6.25µs 131.7 KB N/A
├─IndexReader_58(Build) root 0.01 index:Selection_57 5 time:3m36.3s, loops:3, cop_task: {num: 93, max: 32s, min: 46.5ms, avg: 4.59s, p95: 17.5s, max_proc_keys: 120225, p95_proc_keys: 120225, tot_proc: 7m5.2s, tot_wait: 1.5s, rpc_num: 93, rpc_time: 7m6.8s, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, backoff{regionMiss: 2ms} 3.06 KB N/A
│ └─Selection_57 cop[tikv] 0.01 eq(room_db.t_room_member.member_id, "20000000639306") 5 tikv_task:{proc max:32s, min:48ms, avg: 4.57s, p80:6.98s, p95:17.5s, iters:6102, tasks:93}, scan_detail: {total_process_keys: 5819034, total_process_keys_size: 413151414, total_keys: 5819133, get_snapshot_time: 99.7ms, rocksdb: {delete_skipped_count: 11, key_skipped_count: 5819051, block: {cache_hit_count: 5678}}} N/A N/A
└─IndexLookUp_80(Probe) root 0.00 5 time:1.46ms, loops:2, index_task: {total_time: 714.7µs, fetch_handle: 713.1µs, build: 524ns, wait: 1.11µs}, table_task: {total_time: 608.6µs, num: 1, concurrency: 5} 12.0 KB N/A
├─Selection_78(Build) cop[tikv] 0.01 gt(room_db.t_room_info.room_id, "") 5 time:702.2µs, loops:3, cop_task: {num: 1, max: 662.8µs, proc_keys: 5, rpc_num: 1, rpc_time: 647.4µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 220, total_keys: 10, get_snapshot_time: 8.05µs, rocksdb: {key_skipped_count: 5, block: {cache_hit_count: 20}}} N/A N/A
│ └─IndexRangeScan_76 cop[tikv] 0.01 table:t, index:idx_room_id(room_id), range: decided by [eq(room_db.t_room_info.room_id, room_db.t_room_member.room_id)], keep order:false, stats:pseudo 5 tikv_task:{time:0s, loops:1} N/A N/A
└─Selection_79(Probe) cop[tikv] 0.00 eq(room_db.t_room_info.delete_time, 0) 5 time:531.2µs, loops:2, cop_task: {num: 1, max: 486.1µs, proc_keys: 5, rpc_num: 1, rpc_time: 472.2µs, copr_cache_hit_ratio: 0.00, distsql_concurrency: 15}, tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 5, total_process_keys_size: 627, total_keys: 5, get_snapshot_time: 15.4µs, rocksdb: {block: {cache_hit_count: 11}}} N/A N/A
└─TableRowIDScan_77 cop[tikv] 0.01 table:t, keep order:false, stats:pseudo 5 tikv_task:{time:0s, loops:1} N/A N/A
rpc_time: 7m6.8s, 这个时间很高，看下 node/black exporter 监控的网络情况
所有节点基本和张图相同
操作系统升级前 qps也能达到上面截图那么多吧
升级前是一个insert有问题 写入的延迟非常高
insert的问题和操作系统有关？升完解决了？
操作系统是意外升级的 用fio测试磁盘的时候把系统和数据盘都搞坏了 就重装了
升级完 没有看到insert文件 一上来就一个select 一直查询很慢
我稍后换个操作系统再试试的
我也盲猜一下，你这个sql里member_id的区分度是不是高一些，有时候单独执行一条sql很快，大并发执行同一条sql的查询时，会出现cpu打满的情况（比如一次扫几十万数据，但是你来1000个并发，或者短时间内疯狂执行这条sql）
先做个表分析，再根据执行计划去优化下sql
看看热块吧，我估计这个表t_room_member在sql执行慢的时间段有很多热块
最总将操作系统调整为Ubuntu 20.04 全部重装后正常
此话题已在最后回复的 60 天后被自动关闭。不再允许新回复。