Analysis and Assistance on the Cause of Sudden Increase in TiKV Node CPU Usage, Prolonged SQL Execution Time Leading to Business Timeout, and Decrease in QPS

Translated
1

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

Original topic: tikv节点cpu使用率突然变高，sql耗时明显变长造成业务超时，同时qps下降此问题原因分析协助

| username: heloong

[TiDB Usage Environment] Production Environment / Testing / Poc
Production Environment
[TiDB Version]
v5.0.6
[Encountered Problem]
2022/10/08 15:56:00 ～ 16:28:00, a large number of timeout errors reported by online applications
[Reproduction Path] Operations performed that caused the problem
Not reproduced, self-recovered
[Problem Phenomenon and Impact]
During this time period, checking the TiDB dashboard, all SQL execution times were several times slower than usual, not just individual SQLs, and QPS dropped significantly. Further checking Grafana, the CPU usage of all TiKV nodes was close to 80%, with some exceeding 80% and triggering alerts.
[Attachment]
On Alibaba Cloud, the TiKV nodes are 8c64g local disks, deployment information as follows:

image
image1380×434 201 KB

All parameter configurations use default values without adjustments.

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: 张雨齐0720 | Original post link

Check the monitoring to find slow SQL queries and see if they are causing the issue.

3
| username: heloong | Original post link

Through analysis, the disk latency and network latency of the TiKV nodes are normal. Some other monitoring information is as follows:

image
image1380×306 107 KB

image
image1380×358 105 KB

image
image1380×302 78.1 KB

image
image1380×696 174 KB

image
image1380×718 185 KB

4
| username: heloong | Original post link

First of all, looking at this, all SQLs have slowed down, indicating that there is an overall system issue.

5
| username: 大鱼海棠 | Original post link

Judging by the monitoring, it should be a slow query issue, as the unified read pool CPU has significantly increased. Additionally, the overall machine load should also be checked.

6
| username: OnTheRoad | Original post link

Write Stall?

7
| username: heloong | Original post link

There is no write stall, and nothing can be found in the logs at that time, nor in the monitoring.

The write latency has indeed increased significantly, and there were also screenshots earlier showing a significant increase in read latency.

8
| username: OnTheRoad | Original post link
  1. Are there any clues at the system level?
  2. How is the health of the table?
  3. Are there any scheduled tasks running that are affecting TiDB?