load data local infile 导致Storage ReadPool CPU飙升到百分800 查询卡住

林先森cC · 2021 年8 月 18 日 06:48

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】
【概述】场景+问题概述
【背景】做过哪些操作
load data local infile xxx.txt 导数据
【现象】业务和数据库现象
发现查询很慢，监控报警
1.Tidb_tikvclient_backoff_seconds_count,TiDB tikvclient_backoff_count error；
2.TiKV_scheduler_command_duration_seconds,TiKV scheduler command duration seconds more than 1s
3.TiDB_schema_error,TiDB schema error

在库里查询发现不带条件查询表很快，
带PRI key做条件查询表非常慢

【业务影响】

业务前端报错

【TiDB 版本】
v4.0.9
【附件】

相关日志和监控

其中一台tikv 的 load一直降不下来最近一次40分钟才自己恢复

TiUP Cluster Display 信息
TiUP Cluster Edit Config 信息
TiDB- Overview 监控

是不是热点读了？？？

对应模块日志（包含问题前后1小时日志）
tidb.log
[2021/08/18 14:34:09.137 +08:00] [WARN] [expensivequery.go:178] [expensive_query] [cost_time=60.040764484s] [conn_id=111586] [user=rptgy] [database=report_rpt_gy] [txn_start_ts=
0] [mem_max=“0 Bytes (0 Bytes)”] [sql="load data local infile ‘/app/azkaban_app/b2d_geyan/geyan_umclogin_day_report_new_mid_hour.txt’ into table geyan_umclogin_hour_report_new\

FIELDS TERMINATED BY ‘|’ (day,hour,app_id,app_name,phone_type,operator_type,pre_getpn_cnt,open_auth_cnt,login_cnt,succ_login_cnt,succ_login_rate)“]
[2021/08/18 14:34:12.912 +08:00] [INFO] [region_cache.go:839] [“switch region leader to specific leader due to kv return NotLeader”] [regionID=28] [currIdx=1] [leaderStoreID=2]
[2021/08/18 14:34:32.269 +08:00] [WARN] [session.go:1222] [“compile SQL failed”] [conn=111220] [error=”[planner:1046]No database selected"] [SQL=“SHOW FULL TABLES FROM LIKE 'BONECPKEEPALIVE'"] [2021/08/18 14:34:32.269 +08:00] [INFO] [conn.go:793] ["command dispatched failed"] [conn=111220] [connInfo="id:111220, addr:172.16.16.192:12825 status:10, collation:utf8_general_ci, user:bi_system"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="SHOW FULL TABLES FROM LIKE ‘BONECPKEEPALIVE’”] [txn_mode=PESSIMISTIC] [err=“[planner:1046]No database selected”]
[2021/08/18 14:35:09.934 +08:00] [WARN] [session.go:1222] [“compile SQL failed”] [conn=111402] [error=“[planner:1046]No database selected”] [SQL=“SHOW FULL TABLES FROM LIKE 'BONECPKEEPALIVE'"] [2021/08/18 14:35:09.934 +08:00] [INFO] [conn.go:793] ["command dispatched failed"] [conn=111402] [connInfo="id:111402, addr:xxxxx:12980 status:10, collation:utf8_general_ci, user:bi_system"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="SHOW FULL TABLES FROM LIKE ‘BONECPKEEPALIVE’”] [txn_mode=PESSIMISTIC] [err=“[planner:1046]No database selected”]
[2021/08/18 14:36:31.124 +08:00] [WARN] [session.go:1222] [“compile SQL failed”] [conn=101501] [error=“[planner:1046]No database selected”] [SQL=“SHOW FULL TABLES FROM LIKE 'BONECPKEEPALIVE'"] [2021/08/18 14:36:31.124 +08:00] [INFO] [conn.go:793] ["command dispatched failed"] [conn=101501] [connInfo="id:101501, addr:172.16.16.192:60292 status:10, collation:utf8_general_ci, user:bi_system"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="SHOW FULL TABLES FROM LIKE ‘BONECPKEEPALIVE’”] [txn_mode=PESSIMISTIC] [err=“[planner:1046]No database selected”]
[2021/08/18 14:37:11.798 +08:00] [WARN] [session.go:1222] [“compile SQL failed”] [conn=110168] [error=“[planner:1046]No database selected”] [SQL=“SHOW FULL TABLES FROM LIKE 'BONECPKEEPALIVE'"] [2021/08/18 14:37:11.798 +08:00] [INFO] [conn.go:793] ["command dispatched failed"] [conn=110168] [connInfo="id:110168, addr:172.16.16.192:11920 status:10, collation:utf8_general_ci, user:bi_system"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="SHOW FULL TABLES FROM LIKE ‘BONECPKEEPALIVE’”] [txn_mode=PESSIMISTIC] [err=“[planner:1046]No database selected”]
[2021/08/18 14:37:15.915 +08:00] [INFO] [region_cache.go:839] [“switch region leader to specific leader due to kv return NotLeader”] [regionID=1076444] [currIdx=0] [leaderStoreID=1]
[2021/08/18 14:38:02.961 +08:00] [INFO] [region_cache.go:839] [“switch region leader to specific leader due to kv return NotLeader”] [regionID=1728] [currIdx=1] [leaderStoreID=2]
[2021/08/18 14:38:11.425 +08:00] [INFO] [region_cache.go:839] [“switch region leader to specific leader due to kv return NotLeader”] [regionID=126848] [currIdx=1] [leaderStoreID=2]
[2021/08/18 14:38:25.378 +08:00] [WARN] [session.go:1222] [“compile SQL failed”] [conn=111925] [error=“[schema:1146]Table ‘report_rpt_mdp.gt_type519_passive_type_pk’ doesn’t exist”] [SQL=“select create_date,sum(wakeuptimes) wakeuptimes
from
(select *
from report_rpt_mdp.type19_gbd_d
where create_date >= ‘2021-08-01’
and type = 3
union all
select *
from report_rpt_mdp.gt_type519_passive_type_pk
where create_date >= ‘2021-08-01’
and type = 3) a
group by create_date”]
[2021/08/18 14:38:25.378 +08:00] [INFO] [conn.go:793] [“command dispatched failed”] [conn=111925] [connInfo=“id:111925, addr:xxxx:39580 status:10, collation:utf8_general_
ci, user:getui_push”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql="select create_date,sum(wakeuptimes) wakeuptimes
from
(select *
from report_rpt_mdp.type19_gbd_d\

where create_date >= ‘2021-08-01’
and type = 3
union all
select *
from report_rpt_mdp.gt_type519_passive_type_pk
where create_date >= ‘2021-08-01’
and type = 3) a
group b
y create_date"] [txn_mode=PESSIMISTIC] [err=“[schema:1146]Table ‘report_rpt_mdp.gt_type519_passive_type_pk’ doesn’t exist
github.com/pingcap/errors.AddStack
\t/home/jenkins/ag
ent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20201029093017-5a7df2af2ac7/errors.go:174
github.com/pingcap/errors.(*Error).GenW
ithStackByArgs
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/errors@v0.11.5-0.20201029093017-5a7df2af2ac7/normalize.go:156
gi
thub.com/pingcap/tidb/infoschema.(*infoSchema).TableByName
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/infoschema/infoschem
a.go:171
github.com/pingcap/tidb/planner/core.(*preprocessor).handleTableName
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/
planner/core/preprocess.go:933
github.com/pingcap/tidb/planner/core.(*preprocessor).Leave
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/p
ingcap/tidb/planner/core/preprocess.go:292
github.com/pingcap/parser/ast.(*TableName).Accept
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/githu
b.com/pingcap/parser@v0.0.0-20201130080042-c3ddfec58248/ast/dml.go:305
github.com/pingcap/parser/ast.(*TableSource).Accept
\t/home/jenkins/agent/workspace/optimization-build-t
idb-linux-amd/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20201130080042-c3ddfec58248/ast/dml.go:442
github.com/pingcap/parser/ast.(*Join).Accept
\t/home/jenkins/agent/workspa
ce/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20201130080042-c3ddfec58248/ast/dml.go:152
github.com/pingcap/parser/ast.(*TableRefsClause).Acc
ept
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20201130080042-c3ddfec58248/ast/dml.go:623
github.com/pingcap
/parser/ast.(*SelectStmt).Accept
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20201130080042-c3ddfec58248/ast/d
ml.go:966
github.com/pingcap/parser/ast.(*UnionSelectList).Accept
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/parser@v0.0.0
-20201130080042-c3ddfec58248/ast/dml.go:1061
github.com/pingcap/parser/ast.(*UnionStmt).Accept
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/git
hub.com/pingcap/parser@v0.0.0-20201130080042-c3ddfec58248/ast/dml.go:1111
github.com/pingcap/parser/ast.(*TableSource).Accept
\t/home/jenkins/agent/workspace/optimization-buil
d-tidb-linux-amd/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20201130080042-c3ddfec58248/ast/dml.go:442
github.com/pingcap/parser/ast.(*Join).Accept
\t/home/jenkins/agent/work
space/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20201130080042-c3ddfec58248/ast/dml.go:152
github.com/pingcap/parser/ast.(*TableRefsClause).
Accept
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20201130080042-c3ddfec58248/ast/dml.go:623
github.com/ping
cap/parser/ast.(*SelectStmt).Accept
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/pkg/mod/github.com/pingcap/parser@v0.0.0-20201130080042-c3ddfec58248/ast/dml.go:966
github.com/pingcap/tidb/planner/core.Preprocess
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/preprocess.go:86
github.com/pingcap/tidb/executor.(*Compiler).Compile
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/compiler.go:57
github.com/pingcap/tidb/session.(*session).execute
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/session/session.go:1215
github.com/pingcap/tidb/session.(*session).Execute
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/session/session.go:1163
github.com/pingcap/tidb/server.(*TiDBContext).Execute
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/driver_tidb.go:248
github.com/pingcap/tidb/server.(*clientConn).handleQuery
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:1374
github.com/pingcap/tidb/server.(*clientConn).dispatch
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:1005
github.com/pingcap/tidb/server.(*clientConn).Run
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:778
github.com/pingcap/tidb/server.(*Server).onConn
\t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/server.go:439
runtime.goexit
\t/usr/local/go/src/runtime/asm_amd64.s:1357”]
[2021/08/18 14:38:41.189 +08:00] [INFO] [coprocessor.go:1034] [“[TIME_COP_PROCESS] resp_time:308.63315ms txnStartTS:427103019559288834 region_id:16144 store_addr:172.18.41.13:20160 kv_process_ms:298 scan_total_write:466497 scan_processed_write:466496 scan_total_data:0 scan_processed_data:0 scan_total_lock:1 scan_processed_lock:0”] [conn=111925]
[2021/08/18 14:39:00.610 +08:00] [INFO] [coprocessor.go:1034] [“[TIME_COP_PROCESS] resp_time:311.389657ms txnStartTS:427103024644882435 region_id:16144 store_addr:172.18.41.13:20160 kv_process_ms:300 scan_total_write:466497 scan_processed_write:466496 scan_total_data:0 scan_processed_data:0 scan_total_lock:1 scan_processed_lock:0”] [conn=111925]
[2021/08/18 14:39:24.943 +08:00] [INFO] [2pc.go:822] [“prewrite encounters lock”] [conn=0] [lock=“key: {tableID=17, handle=331767}, primary: {tableID=17, handle=331767}, txnStartTS: 427103031028088842, lockForUpdateTS:0, ttl: 3000, type: Put”]
2021/08/18 14:39:25.082 +08:00] [INFO] [2pc.go:822] [“prewrite encounters lock”] [conn=0] [lock=“key: {tableID=17, handle=331767}, primary: {tableID=17, handle=331767}, txnStartTS: 427103031028088842, lockForUpdateTS:0, ttl: 3000, type: Put”]
[2021/08/18 14:39:25.479 +08:00] [INFO] [2pc.go:822] [“prewrite encounters lock”] [conn=0] [lock=“key: {tableID=17, handle=331767}, primary: {tableID=17, handle=331767}, txnStartTS: 427103031028088842, lockForUpdateTS:0, ttl: 3000, type: Put”]
[2021/08/18 14:39:25.885 +08:00] [INFO] [2pc.go:822] [“prewrite encounters lock”] [conn=0] [lock=“key: {tableID=17, handle=331767}, primary: {tableID=17, handle=331767}, txnStartTS: 427103031028088842, lockForUpdateTS:0, ttl: 3000, type: Put”]
[2021/08/18 14:39:26.239 +08:00] [WARN] [session.go:1222] [“compile SQL failed”] [conn=111669] [error=“[planner:1046]No database selected”] [SQL=“SHOW FULL TABLES FROM LIKE 'BONECPKEEPALIVE'"] [2021/08/18 14:39:26.239 +08:00] [INFO] [conn.go:793] ["command dispatched failed"] [conn=111669] [connInfo="id:111669, addr:172.16.16.192:13195 status:10, collation:utf8_general_ci, user:bi_system"] [command=Query] [status="inTxn:0, autocommit:1"] [sql="SHOW FULL TABLES FROM LIKE ‘BONECPKEEPALIVE’”] [txn_mode=PESSIMISTIC] [err=“[planner:1046]No database selected”]
[2021/08/18 14:39:27.173 +08:00] [INFO] [2pc.go:822] [“prewrite encounters lock”] [conn=0] [lock=“key: {tableID=17, handle=331767}, primary: {tableID=17, handle=331767}, txnStartTS: 427103031028088842, lockForUpdateTS:0, ttl: 3000, type: Put”]
[2021/08/18 14:39:28.523 +08:00] [INFO] [2pc.go:822] [“prewrite encounters lock”] [conn=0] [lock=“key: {tableID=17, handle=331767}, primary: {tableID=17, handle=331767}, txnStartTS: 427103031028088842, lockForUpdateTS:0, ttl: 3000, type: Put”]
[2021/08/18 14:39:28.524 +08:00] [INFO] [lock_resolver.go:617] [“resolveLock rollback”] [lock=“key: {tableID=17, handle=331767}, primary: {tableID=17, handle=331767}, txnStartTS: 427103031028088842, lockForUpdateTS:0, ttl: 3000, type: Put”]
[2021/08/18 14:39:33.539 +08:00] [WARN] [session.go:488] [“can not retry txn”] [label=internal] [error=“[domain:8027]Information schema is out of date: schema failed to update in 1 lease, please make sure TiDB can connect to TiKV”] [IsBatchInsert=false] [IsPessimistic=false] [InRestrictedSQL=true] [tidb_retry_limit=10] [tidb_disable_txn_auto_retry=true]
[2021/08/18 14:39:33.539 +08:00] [WARN] [session.go:503] [“commit failed”] [“finished txn”=“Txn{state=invalid}”] [error=“[domain:8027]Information schema is out of date: schema failed to update in 1 lease, please make sure TiDB can connect to TiKV”]

最后报错information schema is out of date

tikv.log
[2021/08/18 14:34:10.842 +08:00] [INFO] [raft.rs:1177] [“starting a new election”] [term=105332] [raft_id=29] [region_id=28]
[2021/08/18 14:34:10.842 +08:00] [INFO] [raft.rs:807] [“became candidate at term 105333”] [term=105333] [raft_id=29] [region_id=28]
[2021/08/18 14:34:10.842 +08:00] [INFO] [raft.rs:902] [“29 received message from 29”] [term=105333] [msg=MsgRequestVote] [from=29] [id=29] [raft_id=29] [region_id=28]
[2021/08/18 14:34:10.842 +08:00] [INFO] [raft.rs:923] [“[logterm: 105332, index: 10237728] sent request to 79”] [msg=MsgRequestVote] [term=105333] [id=79] [log_index=10237728] [
log_term=105332] [raft_id=29] [region_id=28]
[2021/08/18 14:34:10.842 +08:00] [INFO] [raft.rs:923] [“[logterm: 105332, index: 10237728] sent request to 59”] [msg=MsgRequestVote] [term=105333] [id=59] [log_index=10237728] [
log_term=105332] [raft_id=29] [region_id=28]
[2021/08/18 14:34:10.843 +08:00] [INFO] [raft.rs:1673] [“received from 59”] [term=105333] [“msg type”=MsgRequestVoteResponse] [from=59] [raft_id=29] [region_id=28]
[2021/08/18 14:34:10.843 +08:00] [INFO] [raft.rs:874] [“became leader at term 105333”] [term=105333] [raft_id=29] [region_id=28]
[2021/08/18 14:35:45.844 +08:00] [INFO] [gc_manager.rs:456] [“gc_worker: finished auto gc”] [processed_regions=9509]
[2021/08/18 14:36:14.057 +08:00] [INFO] [raft.rs:1739] [“[term 228] received MsgTimeoutNow from 1730 and starts an election to get leadership.”] [from=1730] [term=228] [raft_id=
1729] [region_id=1728]
[2021/08/18 14:36:14.057 +08:00] [INFO] [raft.rs:1177] [“starting a new election”] [term=228] [raft_id=1729] [region_id=1728]
[2021/08/18 14:36:14.057 +08:00] [INFO] [raft.rs:807] [“became candidate at term 229”] [term=229] [raft_id=1729] [region_id=1728]
[2021/08/18 14:36:14.057 +08:00] [INFO] [raft.rs:902] [“1729 received message from 1729”] [term=229] [msg=MsgRequestVote] [from=1729] [id=1729] [raft_id=1729] [region_id=1728]
[2021/08/18 14:36:14.057 +08:00] [INFO] [raft.rs:923] [“[logterm: 228, index: 372] sent request to 1730”] [msg=MsgRequestVote] [term=229] [id=1730] [log_index=372] [log_term=228] [raft_id=1729] [region_id=1728]
[2021/08/18 14:36:14.057 +08:00] [INFO] [raft.rs:923] [“[logterm: 228, index: 372] sent request to 1731”] [msg=MsgRequestVote] [term=229] [id=1731] [log_index=372] [log_term=228] [raft_id=1729] [region_id=1728]
[2021/08/18 14:36:14.059 +08:00] [INFO] [raft.rs:1673] [“received from 1731”] [term=229] [“msg type”=MsgRequestVoteResponse] [from=1731] [raft_id=1729] [region_id=1728]
[2021/08/18 14:36:14.059 +08:00] [INFO] [raft.rs:874] [“became leader at term 229”] [term=229] [raft_id=1729] [region_id=1728]
[2021/08/18 14:36:14.321 +08:00] [INFO] [pd.rs:793] [“try to transfer leader”] [to_peer=“id: 115778 store_id: 1”] [from_peer=“id: 115777 store_id: 2”] [region_id=115776]
[2021/08/18 14:36:14.321 +08:00] [INFO] [peer.rs:2110] [“transfer leader”] [peer=“id: 115778 store_id: 1”] [peer_id=115777] [region_id=115776]
[2021/08/18 14:36:14.321 +08:00] [INFO] [raft.rs:1376] [“[term 16] starts to transfer leadership to 115778”] [lead_transferee=115778] [term=16] [raft_id=115777] [region_id=115776]
[2021/08/18 14:36:14.321 +08:00] [INFO] [raft.rs:1389] [“sends MsgTimeoutNow to 115778 immediately as 115778 already has up-to-date log”] [lead_transferee=115778] [raft_id=115777] [region_id=115776]
[2021/08/18 14:36:14.321 +08:00] [INFO] [raft.rs:1003] [“received a message with higher term from 115778”] [“msg type”=MsgRequestVote] [message_term=17] [term=16] [from=115778] [raft_id=115777] [region_id=115776]
[2021/08/18 14:36:14.321 +08:00] [INFO] [raft.rs:783] [“became follower at term 17”] [term=17] [raft_id=115777] [region_id=115776]
[2021/08/18 14:36:14.321 +08:00] [INFO] [raft.rs:1192] [“[logterm: 16, index: 8686, vote: 0] cast vote for 115778 [logterm: 16, index: 8686] at term 17”] [“msg type”=MsgRequestVote] [term=17] [msg_index=8686] [msg_term=16] [from=115778] [vote=0] [log_index=8686] [log_term=16] [raft_id=115777] [region_id=115776]
[2021/08/18 14:37:14.871 +08:00] [INFO] [pd.rs:793] [“try to transfer leader”] [to_peer=“id: 1076446 store_id: 1”] [from_peer=“id: 1076445 store_id: 2”] [region_id=1076444]
[2021/08/18 14:37:14.871 +08:00] [INFO] [peer.rs:2110] [“transfer leader”] [peer=“id: 1076446 store_id: 1”] [peer_id=1076445] [region_id=1076444]
[2021/08/18 14:37:14.871 +08:00] [INFO] [raft.rs:1376] [“[term 303] starts to transfer leadership to 1076446”] [lead_transferee=1076446] [term=303] [raft_id=1076445] [region_id=1076444]
[2021/08/18 14:37:14.871 +08:00] [INFO] [raft.rs:1389] [“sends MsgTimeoutNow to 1076446 immediately as 1076446 already has up-to-date log”] [lead_transferee=1076446] [raft_id=1076445] [region_id=1076444]
[2021/08/18 14:37:14.872 +08:00] [INFO] [raft.rs:1003] [“received a message with higher term from 1076446”] [“msg type”=MsgRequestVote] [message_term=304] [term=303] [from=1076446] [raft_id=1076445] [region_id=1076444]
[2021/08/18 14:37:14.872 +08:00] [INFO] [raft.rs:783] [“became follower at term 304”] [term=304] [raft_id=1076445] [region_id=1076444]
[2021/08/18 14:37:14.872 +08:00] [INFO] [raft.rs:1192] [“[logterm: 303, index: 36250, vote: 0] cast vote for 1076446 [logterm: 303, index: 36250] at term 304”] [“msg type”=MsgRequestVote] [term=304] [msg_index=36250] [msg_term=303] [from=1076446] [vote=0] [log_index=36250] [log_term=303] [raft_id=1076445] [region_id=1076444]
[2021/08/18 14:37:15.570 +08:00] [WARN] [endpoint.rs:530] [error-response] [err=“Region error (will back off and retry) message: "peer is not leader for region 1076444, leader may Some(id: 1076446 store_id: 1)" not_leader { region_id: 1076444 leader { id: 1076446 store_id: 1 } }”]
[2021/08/18 14:37:15.572 +08:00] [WARN] [endpoint.rs:530] [error-response] [err=“Region error (will back off and retry) message: "peer is not leader for region 1076444, leader may Some(id: 1076446 store_id: 1)" not_leader { region_id: 1076444 leader { id: 1076446 store_id: 1 } }”]
[2021/08/18 14:37:15.914 +08:00] [WARN] [endpoint.rs:530] [error-response] [err=“Region error (will back off and retry) message: "peer is not leader for region 1076444, leader may Some(id: 1076446 store_id: 1)" not_leader { region_id: 1076444 leader { id: 1076446 store_id: 1 } }”]
[2021/08/18 14:37:36.248 +08:00] [INFO] [util.rs:419] [“connecting to PD endpoint”] [endpoints=http://xxxx:2379]
[2021/08/18 14:37:36.249 +08:00] [INFO] [] [“New connected subchannel at 0x7fc55323b020 for subchannel 0x7fc553245300”]

songxuecheng · 2021 年8 月 18 日 08:12

由于load 造成TiKV 压力大或网络超时导致tidb无法连接tikv

songxuecheng · 2021 年8 月 18 日 08:16

因loader现在已经不在维护，建议你更换 TiDB Lightning TiDB-backend 或者修改loader参数
pool-size，控制并发线程。

林先森cC · 2021 年8 月 18 日 08:27

你好没有用loader。我们是用了load data local infile命令导入数据的

songxuecheng · 2021 年8 月 18 日 08:29

问题类似，请问load多大的数据量

林先森cC · 2021 年8 月 18 日 08:46

大100多万行 100多M文件小的几十k 请问想知道为什么倒数据会导致某个TiKV Storage ReadPool CPU飙升到百分800 配置Storage ReadPool 是线程是8 看解释 Storage ReadPool是读的线程池而且load kill掉差不多过了40分钟才cpu压力降低下来不知道是什么原因

还有就是在其中一个TIKV节点Storage ReadPool在瓶颈的时候。用主键做条件查询一个小表会卡住不带条件或者带其他字段条件就查询很快

songxuecheng · 2021 年8 月 18 日 09:00

可能你查询的这表刚好在有瓶颈的kv上，导致有问题。

林先森cC · 2021 年8 月 18 日 09:40

您好调高tikv Storage ReadPool 线程池线程数量是否有用呢现在是8 机器是32核为什么导数据会导致其中一个tikv 的主机压力很大性能瓶颈呢表region不是分布式在3个tikv 上吗

林先森cC · 2021 年8 月 18 日 09:44

还有就是不是很理解为啥查询表wher PRIkey就很慢 PRI key是varchar(50) 。不带where 或者用其他字段查询就很快。表就几十行。有告警TiDB schema error。

Kongdom · 2021 年8 月 19 日 00:44

方便提供一下查询sql和explain analyze的查询计划么？

林先森cC · 2021 年8 月 19 日 09:44

你好是这个语句就几千条数据里面 id是int自增主键只有在其中一个Tikv的 readpool cpu被打满的时候才查不动的 cpu降下来后查询正常我看官方文档 tikv是按主键切分数据到region。自增id 是会写到一个region 如果region在性能瓶颈的tikv上是否就造成了读不动

image

Kongdom · 2021 年8 月 20 日 01:58

是，所以建议使用AUTO_RANDOM处理自增主键热点表，适用于代替自增主键，解决自增主键带来的写入热点。

https://docs.pingcap.com/zh/tidb/stable/troubleshoot-hot-spot-issues#表热点

林先森cC · 2021 年8 月 20 日 06:53

您好我们在测试库修改了这个点的自增属性改为AUTO_RANDOM

导入时差不多10-12w条数据的时候就会断开在10秒内断开的

导入原来的表就正常的，请问修改为AUTO_RANDOM后是否有其他参数限制他导入呢

这个是tidb.log日志

林先森cC · 2021 年8 月 30 日 03:57

因为使用 AUTO_RANDOM参数导入时差不多10-12w条数据的时候就会断开在10秒内断开不清楚是否是受哪个参数影响所以目前我们通过使用 SHARD_ROW_ID_BITS 和 PRE_SPLIT_REGIONS 这两个建表选项（参阅 PRE_SPLIT_REGIONS 的详细说明）解决了数据写热点问题

system · 2022 年10 月 31 日 19:22

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。