部分TIKV节点CPU高

🪐 TiDB 技术问题 性能调优
1

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【 TiDB 使用环境】
我们集群环境如下

image
image1036×291 61.6 KB

【概述】 场景 + 问题概述
某段时间三个tikv节点中有两个节点cpu很高，我们抓到了两条sql，这是两条for update语句。我们有两个问题，请帮忙解答一下，谢谢！

1，这次的cpu高是否跟for update语句有关？如果是，那在tidb中会有哪些好的代替方案，如果能够发我们一些资料或是链接，我们会非常感谢
2，三个tikv节点，有一个正常，是不是表中region分布不均匀，我们要如何查看数据库对象（特指表）的region分布情况呢？

相关信息如下：

在10.122.69.125:

3559897 | lenovo_sc_usr_test_message | 10.99.80.254:46110 | lenovo_sc_db_test_message | Query | 0 | in transaction | SELECT * FROM QRTZ_LOCKS WHERE SCHED_NAME = ‘MessageScheduler’ AND LOCK_NAME = ‘TRIGGER_ACCESS’ FOR

in transaction | SELECT * FROM QRTZ_LOCKS WHERE SCHED_NAME = ‘MessageScheduler’ AND LOCK_NAME = ‘TRIGGER_ACCESS’ FOR

SELECT
id,
parent_path,
path,
count,
create_time,
update_time
FROM
id_generator_record
WHERE
parent_path = ‘/parts/ids’
AND path = ‘/receiveOrderWare’ FOR UPDATE;

SELECT
id,
parent_path,
path,
count,
create_time,
update_time
FROM
id_generator_record
WHERE
parent_path = ‘/parts/ids’
AND path = ‘/receiveOrderWare’ FOR UPDATE;

cpu使用情况如下：

image
image1008×365 87.1 KB

日志：

[2021/09/18 14:17:19.169 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005585F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F

7010054005200490047FF004700450052005FFF0041004300430045FF0053005300000000FB lock_version: 427804809968484360 key: 7480000000000005675F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F70100540041

0053004BFF005F003600350037FF0039003100320000FD010044004500460041FF0055004C00540000FD lock_ttl: 20262 txn_size: 1 lock_for_update_ts: 427804810020913158 min_commit_ts: 427804810020913159"]

[2021/09/18 14:17:19.558 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005585F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F

7010054005200490047FF004700450052005FFF0041004300430045FF0053005300000000FB lock_version: 427804810086711299 key: 7480000000000005675F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F70100540041

0053004BFF005F003600350037FF0039003100350000FD010044004500460041FF0055004C00540000FD lock_ttl: 20264 txn_size: 1 lock_for_update_ts: 427804810125770755 min_commit_ts: 427804810125770756"]

[2021/09/18 14:17:19.767 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005705F728000000000000029 lock_version: 427804804817616904 key: 7480000000000005705F728000000000000029 lock_ttl: 394

21 txn_size: 5 lock_for_update_ts: 427804810204676100 min_commit_ts: 427804810204676101"]

[2021/09/18 14:17:19.906 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005585F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F

7010054005200490047FF004700450052005FFF0041004300430045FF0053005300000000FB lock_version: 427804810178199556 key: 7480000000000005675F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F70100540041

0053004BFF005F003500390034FF0035003000310000FD010044004500460041FF0055004C00540000FD lock_ttl: 20246 txn_size: 1 lock_for_update_ts: 427804810217521193 min_commit_ts: 427804810217521194"]

[2021/09/18 14:17:20.610 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005585F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F

7010054005200490047FF004700450052005FFF0041004300430045FF0053005300000000FB lock_version: 427804810361700359 key: 7480000000000005675F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F70100540041

0053004BFF005F003500390034FF0035003000300000FD010044004500460041FF0055004C00540000FD lock_ttl: 20234 txn_size: 1 lock_for_update_ts: 427804810414129154 min_commit_ts: 427804810414129155"]

[2021/09/18 14:17:20.966 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005705F728000000000000029 lock_version: 427804804830461966 key: 7480000000000005705F728000000000000029 lock_ttl: 405

22 txn_size: 5 lock_for_update_ts: 427804810505879560 min_commit_ts: 427804810505879561"]

[2021/09/18 14:17:21.259 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005705F72800000000001D4DF lock_version: 427804810532093988 key: 7480000000000005705F72800000000001D4DF lock_ttl: 200

01 txn_size: 2 lock_for_update_ts: 427804810532093991 min_commit_ts: 427804810532093992"]

[2021/09/18 14:17:21.582 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005585F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F

7010054005200490047FF004700450052005FFF0041004300430045FF0053005300000000FB lock_version: 427804810610737161 key: 7480000000000005675F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F70100540041

0053004BFF005F003600350031FF0034003100340000FD010044004500460041FF0055004C00540000FD lock_ttl: 20280 txn_size: 1 lock_for_update_ts: 427804810663165954 min_commit_ts: 427804810663165955"]

[2021/09/18 14:17:23.332 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005705F728000000000000029 lock_version: 427804805197463557 key: 7480000000000005705F728000000000000029 lock_ttl: 415

37 txn_size: 5 lock_for_update_ts: 427804811135025160 min_commit_ts: 427804811135025161"]

[2021/09/18 14:17:23.470 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 74800000000000056A5F72800000000001AE97 lock_version: 427804810951786504 key: 74800000000000056D5F72800000000003718F lock_ttl: 205

31 txn_size: 1 lock_for_update_ts: 427804810951786504 min_commit_ts: 427804810951786505"]

[2021/09/18 14:17:23.538 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005585F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F

7010054005200490047FF004700450052005FFF0041004300430045FF0053005300000000FB lock_version: 427804811121917966 key: 7480000000000005675F72800000000009874A lock_ttl: 20276 txn_size: 1 lock_type: Del lock_for_update_ts: 427804811174608906 min_commit_ts: 4278

04811174608907"]

[2021/09/18 14:17:23.649 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005585F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F

7010054005200490047FF004700450052005FFF0041004300430045FF0053005300000000FB lock_version: 427804811135025158 key: 7480000000000005675F69800000000000000101004D004500530053FF0041004700450053FF0043004800450044FF0055004C00450052FF0000000000000000F70100540041

0053004BFF005F003600350033FF0034003200350000FD010044004500460041FF0055004C00540000FD lock_ttl: 20324 txn_size: 1 lock_for_update_ts: 427804811200823299 min_commit_ts: 427804811200823300"]

[2021/09/18 14:17:24.684 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005705F728000000000000029 lock_version: 427804805223677960 key: 7480000000000005705F728000000000000029 lock_ttl: 425

79 txn_size: 5 lock_for_update_ts: 427804811488919555 min_commit_ts: 427804811488919556"]

[2021/09/18 14:17:25.822 +08:00] [WARN] [endpoint.rs:632] [error-response] [err="Key is locked (will clean up) primary_lock: 7480000000000005705F728000000000000029 lock_version: 427804811082596361 key: 7480000000000005705F728000000000000029 lock_ttl: 216

06 txn_size: 5 lock_for_update_ts: 427804811790385155 min_commit_ts: 427804811790385156"]

在124上cpu使用情况：

image
image984×381 92.9 KB

日志：

image
image1872×378 366 KB

117节点正常：

image
image1499×177 132 KB

在grafana查看：

image
image1870×663 248 KB

dashboard：

image
image1603×733 194 KB

【TiDB 版本】
v5.1.1

【附件】 相关日志及配置信息

  • TiUP Cluster Display 信息
  • TiUP CLuster Edit config 信息

监控（https://metricstool.pingcap.com/)

  • TiDB-Overview Grafana监控
  • TiDB Grafana 监控
  • TiKV Grafana 监控
  • PD Grafana 监控
  • 对应模块日志（包含问题前后 1 小时日志）

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

2

1，这次的cpu高是否跟for update语句有关？如果是，那在tidb中会有哪些好的代替方案，如果能够发我们一些资料或是链接，我们会非常感谢

查阅一下锁信息，我看你给的日志里头有有很多锁，
最要好从业务的方向来解决锁的问题，
或者启用其他的中间件，比如队列处理，分布式锁等等。

2，三个tikv节点，有一个正常，是不是表中region分布不均匀，我们要如何查看数据库对象（特指表）的region分布情况呢？

论坛中有关于regions 优化的 SOP 的帖子，可以搜索参考下
另外官网上也有海量regions 调优的文档，也可以看看
3

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