Increased Query Latency

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

Original topic: 查询耗时增加

| username: TiDBer_1111

[TiDB Usage Environment] Production Environment
[TiDB Version] v4.0.10
[Encountered Problem: Phenomenon and Impact]
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]
Scheduler - batch_get in Scheduler scan details [lock]
It basically matches the increase in query time. How can this be located and optimized?

| username: TiDBer_1111 | Original post link

3kv+3pd+3tidb+3pump
3 machines with 48 cores and 258GB RAM

| username: Jasper | Original post link

It looks like the amount of scanned data has increased. In the dashboard slow query section, try sorting by process_key and total_keys in descending order.

| username: TiDBer_1111 | Original post link

Because the query time increased by about 20ms, it hasn’t reached the threshold for a slow query yet. We can only analyze it from the SQL statement:

The difference between the average value and the maximum value is quite large.

Are there any further verification methods available?

| username: Billmay表妹 | Original post link

It is possible that resource contention issues are caused by mixed deployment.

It is recommended to deploy 3 TiKV instances separately.

Install 1 TiDB + PD on one machine, and then add 3 more small machines to host PD + TiDB.

| username: Jasper | Original post link

You can check the slow query to see if the SQL with many scanned keys has followed the wrong execution plan. For example, an index scan might have turned into a full table scan, which would unexpectedly scan more data.

| username: 路在何chu | Original post link

Take a look at the growth of the region.

| username: TIDB-Learner | Original post link

For older environments that have been used for several years, first check the health status using SHOW STATS_HEALTHY. If the health status is low, regenerate it. If you have recently modified the program or executed scripts, check the execution plan and the index situation. Alternatively, you can delete and rebuild the index. Also, check if the region distribution across the TiKV instances is balanced and if there are any hotspot issues.

| username: TiDBer_1111 | Original post link

Currently, it appears that only two slow query execution plans have failed.

| username: 小龙虾爱大龙虾 | Original post link

Version 4 has a dashboard, right? Go check the statement analysis on the dashboard to see the execution status of the same SQL at different times.

| username: Jellybean | Original post link

Follow the ideas in these two articles to troubleshoot cluster issues.

| username: TiDBer_1111 | Original post link

Okay, thanks.

| username: TiDBer_1111 | Original post link

Yes, currently it seems that only a few queries have issues in the execution plan.

| username: TiDBer_1111 | Original post link

It doesn’t seem to have any issues :thinking:

| username: 小龙虾爱大龙虾 | Original post link

It’s not just the execution plan; there’s also execution time analysis there.

| username: dba远航 | Original post link

Query the DML statements at this point in time.

| username: 像风一样的男子 | Original post link

Did the latency of the entire database increase during this time?

| username: Sunward | Original post link

Are there any tables being executed without indexes?

| username: oceanzhang | Original post link

The increase in time consumption is not merely a technical issue; it should be considered in conjunction with the characteristics of your own business.

| username: oceanzhang | Original post link

First, is your business scenario experiencing high concurrency during that time period? If high concurrency leads to resource contention, it will naturally cause an increase in query latency since everyone is competing for the same pool of resources. Second, identify specific SQL queries and compare them with previous ones. Are there any SQL queries that inexplicably appear in the slow query log? Has the query plan undergone any sudden changes?