High Read Latency in TiDB

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

Original topic: tidb 读取延迟高

| username: leoones

[Overview] Scenario + Problem Overview
The TiDB query latency is very high, with a high volume of writes and mostly concurrent transaction operations on the business side.

[TiDB Version]
TiDB v5.4.1

[Attachments] Relevant logs and monitoring

| username: xfworld | Original post link

Provide more information to take a look.

Currently, where do you suspect the issue might be?

image

Is the performance of PD meeting your expectations?

| username: leoones | Original post link

High latency in Apply Wait

| username: h5n1 | Original post link

Has it always been like this or did it suddenly increase? Check the CPU utilization of the TiDB server and the disk IO situation of TiKV. If the SQL execution plans are all normal, it might be time to scale out the cluster.

| username: Raymond | Original post link

Are there many slow queries? Let’s start by investigating the slow queries first.

| username: 裤衩儿飞上天 | Original post link

Are you using SSDs?

| username: zhouzeru | Original post link

As the amount of data increases, the memory required for a single write becomes larger. Modify the configuration and increase the default value of the underlying replication protocol.

| username: leoones | Original post link

It has always been like this, the CPU utilization of TiDB is not high, with a configuration of 16 cores and 32GB.
image

| username: leoones | Original post link

NVMe SSD drive

| username: 裤衩儿飞上天 | Original post link

Focus on the TiKV node where this green line is located. Compared to other nodes, I personally feel there is a clear issue.

| username: leoones | Original post link

The IO busy rate of this machine is higher than the others, and the CPU usage is also very high.
image

The commit latency is extremely high.
image

| username: xfworld | Original post link

Hot issues…

It’s very easy to find the problem points through the dashboard… Exactly which table in which database…

| username: leoones | Original post link

Write Bytes

| username: xfworld | Original post link

Hover over it to see the details…

Check it out, if it’s not a hotspot issue, there might be a hardware malfunction…

| username: leoones | Original post link

TiKV nodes with high CPU load are frantically logging the following message: commit_ts is too large, fallback to normal 2PC

The logs of the other 2 TiKV nodes are as follows:

| username: xfworld | Original post link

Congratulations on finding the issue :sunflower: :sunflower:

| username: leoones | Original post link

How to optimize this commit_ts is too large?

| username: xfworld | Original post link

You can refer to this post, it’s basically due to insufficient configuration…

| username: system | Original post link

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