TiDB Performance Issues

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

Original topic: TiDB 慢速问题

| username: Leonadle

[TiDB Usage Environment]

  • TiKV: 5 nodes
  • TiDB: 2 nodes
  • PD: 3 nodes

[Encountered Issues]

Recently, we have noticed many slow SQL queries in TiDB. Inserts into large tables generally take more than 1 second. Below is the time consumption during the execution phase:

This is for select queries, and we found that the time taken to fetch transactions from PD is also quite high, generally reaching over 100ms.

[Questions]

Could you please advise whether these slow queries are mainly caused by TiKV being slow or PD being slow? Is there any way to troubleshoot this?

| username: WalterWj | Original post link

You are using an insert select SQL, right? Otherwise, where does the coprocessor come from?

| username: Leonadle | Original post link

The above screenshot is the insert statement, and the one below is the select statement.

| username: forever | Original post link

Have you checked the monitoring? How are the server’s IO and CPU resource usage?

| username: WalterWj | Original post link

The slow performance of pri write is generally due to disk issues. Slow tso retrieval is usually caused by high pressure on PD or poor network connection between TiDB server and PD.

| username: zhaokede | Original post link

You should post all the SQLs of this transaction.
It looks like the two-phase commit took 1.2 seconds.

| username: zhanggame1 | Original post link

You can also check if there are any locks.

| username: YuchongXU | Original post link

How about checking the CPU and network status?

| username: TiDBer_rvITcue9 | Original post link

Take a look at the memory and CPU situation.

| username: No_Fear | Original post link

Check Grafana for memory, CPU, network, and I/O metrics to first analyze whether resources are sufficient.

| username: Soysauce520 | Original post link

How many CPUs are configured for the TiDB server, and how many CPUs are being used? If the TiDB server is busy, such a situation may occur.

| username: Leonadle | Original post link

TiDB Node Monitoring

| username: 饭光小团 | Original post link

Is the disk an HDD or an SSD?

| username: buddyyuan | Original post link

For the issue with PD TSO, you can check the following images. Please take a screenshot and have a look.

| username: TiDBer_ok0VXN1s | Original post link

Disk吧

| username: Leonadle | Original post link

All are SSDs.

| username: Leonadle | Original post link

Please help take a look.

| username: Jolyne | Original post link

The latency of your PD getting TSO is a bit high. Check the network status on the monitoring system.

| username: buddyyuan | Original post link

It seems there are some issues with the network. You can check the blackbox monitoring, mainly the tidb->pd and pd->tidb links.

| username: Leonadle | Original post link

Network situation