CPU Performance Bottleneck of TiKV

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

Original topic: tikv 的 cpu 性能瓶颈

| username: 是我的海

[TiDB Usage Environment] Production Environment
Problem Description: During peak business hours, the write QPS is around 6k, but the TiKV CPU is high, and SQL execution time is high (machine resources are sufficient).
TOP-SQL and some monitoring situations:


TiKV-detail monitoring panel



TiKV related configuration

tikv:
    gc.enable-compaction-filter: true
    raftstore.capacity: 1536G
    readpool.coprocessor.use-unified-pool: true
    readpool.storage.use-unified-pool: true
    readpool.unified.max-thread-count: 24
    storage.block-cache.capacity: 48G
    storage.scheduler-worker-pool-size: 8 

Table structure information

CREATE TABLE `tblRealtimeSidAIGCDetail` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary key auto-increment',
  `sid` varchar(100) NOT NULL DEFAULT '0' COMMENT 'Search ID',
  `query` text DEFAULT NULL COMMENT 'Question text',
  `subquestioninfo` text DEFAULT NULL COMMENT 'Sub-question information',
  `batch` bigint(40) NOT NULL DEFAULT '0' COMMENT 'Production batch',
  `qid` varchar(40) NOT NULL DEFAULT '0' COMMENT 'ID generated by delivery, 0 if none',
  `source` varchar(20) NOT NULL DEFAULT '0' COMMENT 'Source: 1-Single question shot, 2-Full page shot',
  `course` bigint(10) NOT NULL DEFAULT '0' COMMENT 'Subject',
  `grade` bigint(10) NOT NULL DEFAULT '0' COMMENT 'Grade 1,20,30: Primary, Middle, High',
  `questiontype` bigint(10) NOT NULL DEFAULT '0' COMMENT 'Question type',
  `weight` bigint(10) NOT NULL DEFAULT '0' COMMENT 'Heat value',
  `pval` decimal(10,2) NOT NULL DEFAULT '0' COMMENT 'Satisfaction',
  `gentime` bigint(40) NOT NULL DEFAULT '0' COMMENT 'Delivery time',
  `genway` bigint(10) NOT NULL DEFAULT '0' COMMENT 'Generation method 1-Real-time, 2-Hourly, 2-Daily',
  `status` bigint(10) NOT NULL DEFAULT '0' COMMENT 'Current status',
  `createtime` bigint(40) NOT NULL DEFAULT '0' COMMENT 'Storage time',
  `updatetime` bigint(40) NOT NULL DEFAULT '0' COMMENT 'Status update time',
  `top_pid` varchar(100) NOT NULL COMMENT 'SID image PID',
  `tid` varchar(255) NOT NULL DEFAULT '0' COMMENT 'TID after going online',
  `ori_tid` varchar(20) NOT NULL DEFAULT '0' COMMENT 'First TID of the search scene',
  `version` varchar(100) NOT NULL DEFAULT '0' COMMENT 'Model version',
  `deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1: Soft delete',
  `ext` text DEFAULT NULL COMMENT 'Reserved extension field',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Storage time, used for automatic deletion time format',
  PRIMARY KEY (`id`) /*T![clustered_index] NONCLUSTERED */,
  UNIQUE KEY `sid` (`sid`),
  KEY `tid` (`tid`),
  KEY `questiontype` (`questiontype`),
  KEY `pull` (`batch`,`course`,`status`),
  KEY `idx_createtime_status` (`createtime`,`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=56243485550 /*T! SHARD_ROW_ID_BITS=4 */ COMMENT='';

screencapture-172-29-238-86-8085-d-eDbRZpnWa-tidb-superstrategy-aigc-performance-overview-2024-04-29-11_56_33.pdf (5.0 MB)

| username: Jellybean | Original post link

OP, take a look at the heatmap on the Dashboard and post the heatmap of the cluster access.

Prewrite is the commit phase of 2PC. This phase mainly involves MVCC multi-version checks and lock conflict detection, indicating that this process is taking a considerable amount of time.

| username: 是我的海 | Original post link

| username: Jellybean | Original post link

From the heatmap, it appears that there is a relatively concentrated write situation.

You can go to the TiDB panel in Grafana to check the KV Error and DistSQL panels to see if there are many lock conflicts or backoffs.

| username: 是我的海 | Original post link

It looks okay. Could it be related to the fact that this SID is a unique key? Causing resource consumption during writing.

| username: Jellybean | Original post link

From the Lock Resolve OPS, it can be confirmed that there are many locks encountered during task execution, leading to a high number of internal backoff retries. This process is relatively resource-intensive, and the business side will also perceive the SQL to be slower.

Now, let’s focus on why the locks are held for so long. Is it because the data reading process is slow, or are there many MVCC versions to check?

The original poster should focus on searching and troubleshooting the TiKV panel and TiKV logs.

| username: 有猫万事足 | Original post link

In Grafana, look for Compaction pending bytes.

Pay special attention to when this value drops, which indicates a compaction is happening, and see if it matches the times when your insert prewrite time is excessively long.

In my experience, occasional slow insert queries with excessively long prewrite times are usually due to TiKV undergoing compaction.

| username: 有猫万事足 | Original post link

Also, pay attention to whether the obviously bright diagonal lines in this graph are indexes. There seem to be quite a few local bright diagonal lines.

If it is an index write hotspot, there is indeed no good solution at the moment.
If it is not an index but a table, then the bright diagonal line corresponds to a very clear write hotspot for the table. You need to modify it using SHARD_ROW_ID_BITS, just like you did for the tblRealtimeSidAIGCDetail table.