When reading TiDB, there is an unbalanced load on one TiKV node

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 读取tidb时有一个tikv节点负载不均衡

| username: wfxxh

[TiDB Usage Environment] Production Environment
[TiDB Version] v5.4.2
[Encountered Problem] During queries, one TiKV node has extremely high CPU usage

image
image1380×486 76.8 KB

image
image1380×438 107 KB

2
| username: 数据小黑 | Original post link

Using JDBC or a database tool, run the same SQL query and check if the CPU usage on the same TiKV nodes is high. It is suspected that there might be a data read hotspot.

3
| username: wfxxh | Original post link

Yes, Boss Hei, querying directly at the TiDB layer also shows the same phenomenon.
I referred to this post:

The BlockCache hit rate is relatively stable.

4
| username: h5n1 | Original post link

First, analyze the SQL execution plan that is not performing well, especially for small data ranges with a large number of queries. Then, check the hot regions using pd-ctl or Information-schema.tikv_hot_region.

5
| username: wfxxh | Original post link

The SQL statement is very simple, just a filter and then an aggregation.

Query hot region:

image
image1280×554 35.8 KB

The hot region found is not large, and there are not many keys.

6
| username: h5n1 | Original post link

Please share the SQL and table structure. If the data is concentrated in a small range, it might be necessary to manually split or modify the table structure.

7
| username: wfxxh | Original post link

select yw_distinct as article_id, if(yw_Origin='NSTL_QK','WF_QK',yw_Origin) as source_db, f_year, count(1) from yw.cite_literature where yw_Origin is not null and yw_Origin !='' group by yw_distinct, yw_Origin, f_year limit 20

Table structure:

CREATE TABLE `cite_literature` (
  `f_id` int(11) NOT NULL,
  `f_pk` varchar(255) DEFAULT NULL,
  `f_qcode` varchar(255) DEFAULT NULL,
  `f_serialnum` int(11) DEFAULT NULL,
  `yw_distinct` varchar(255) DEFAULT NULL,
  `yw_CitingQCode` varchar(255) DEFAULT NULL,
  `yw_Origin` varchar(20) DEFAULT NULL,
  `yw_doi` varchar(255) DEFAULT NULL,
  `yw_SerialNum` int(11) DEFAULT NULL,
  `yw_title` varchar(255) DEFAULT NULL,
  `yw_author` varchar(255) DEFAULT NULL,
  `yw_author1` varchar(255) DEFAULT NULL,
  `yw_LiterType` varchar(10) DEFAULT NULL,
  `yw_Publisher` varchar(255) DEFAULT NULL,
  `yw_PeriCode` varchar(50) DEFAULT NULL,
  `yw_year` varchar(10) DEFAULT NULL,
  `yw_issue` varchar(50) DEFAULT NULL,
  `yw_Volumn` varchar(50) DEFAULT NULL,
  `yw_page` varchar(255) DEFAULT NULL,
  `yw_url` varchar(255) DEFAULT NULL,
  `yw_FileDir` varchar(255) DEFAULT NULL,
  `yw_degree` varchar(50) DEFAULT NULL,
  `yw_tutor` varchar(255) DEFAULT NULL,
  `yw_ServiceMode` int(11) DEFAULT NULL,
  `yw_WFPublishDate` datetime DEFAULT NULL,
  `yw_WFFulltextAvailableDate` datetime DEFAULT NULL,
  `yw_HasOriginalDoc` int(11) DEFAULT NULL,
  `f_adddate` date DEFAULT NULL,
  `f_fulltitle` varchar(255) DEFAULT NULL,
  `f_author` varchar(255) DEFAULT NULL,
  `F_PeriCode` varchar(40) DEFAULT NULL,
  `f_periname` varchar(255) DEFAULT NULL,
  `f_year` varchar(10) DEFAULT NULL,
  `f_issue` varchar(20) DEFAULT NULL,
  `f_url` varchar(255) DEFAULT NULL,
  `F_FileDir` varchar(255) DEFAULT NULL,
  `lw_ServiceMode` int(11) DEFAULT NULL,
  `lw_WFPublishDate` datetime DEFAULT NULL,
  `lw_WFFulltextAvailableDate` datetime DEFAULT NULL,
  `lw_HasOriginalDoc` int(11) NOT NULL,
  `lw_doi` varchar(255) DEFAULT NULL,
  `yw_datasource` varchar(50) DEFAULT NULL,
  `yw_etc` varchar(10) DEFAULT NULL,
  `IsSelfCitedByJournal` int(11) NOT NULL,
  `IsSelfCitedByAuthor` int(11) DEFAULT NULL,
  `BatchID` int(11) DEFAULT NULL,
  `f_tutor` varchar(255) DEFAULT NULL,
  `f_degree` varchar(10) DEFAULT NULL,
  `f_Volumn` varchar(40) DEFAULT NULL,
  `F_origin` varchar(10) DEFAULT NULL,
  `yw_Version` varchar(50) DEFAULT NULL,
  `yw_PubPlace` varchar(255) DEFAULT NULL,
  `yw_PubDate` varchar(50) DEFAULT NULL,
  `yw_StandardState` varchar(10) DEFAULT NULL,
  `yw_NoticeNumber` varchar(255) DEFAULT NULL,
  `yw_NoticeDate` varchar(20) DEFAULT NULL,
  `yw_PatentOrga` varchar(255) DEFAULT NULL,
  `yw_PatentType` varchar(20) DEFAULT NULL,
  `primary_partition` int(4) GENERATED ALWAYS AS ((crc32(`f_pk`)) % 9999) STORED NOT NULL,
  `last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `spark_update_time` datetime DEFAULT NULL,
  UNIQUE KEY `idx_f_pk` (`f_pk`),
  KEY `idx_f_qcode` (`f_qcode`),
  KEY `idx_yw_distinct` (`yw_distinct`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
8
| username: h5n1 | Original post link

Full table scan, you can try:

  1. Add scatter scheduling to this table using pd-ctl operation, allowing the system to automatically distribute regions evenly across all TiKV nodes.
  2. Manually transfer the leader of this table to another TiKV using pd-ctl operator add…
9
| username: wfxxh | Original post link

For more hot topics, you can refer to: 专栏 - 【SOP 系列 19】region 分布不均问题排查及解决不完全指南 | TiDB 社区

10
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.