A SQL query takes longer to execute concurrently than when executed individually

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

Original topic: 一个SQL在并发执行查询的时候耗时比单独执行时间长

| username: TiDBer_mVc0OtQv

[TiDB Usage Environment] Production Environment
[TiDB Version] 7.1
[Reproduction Path] Trigger page A, which calls many interfaces, many of which will trigger B-SQL with different where conditions.
[Encountered Problem: Phenomenon and Impact]
In the slow SQL monitoring on the dashboard panel, B-SQL was monitored, and it was found that the execution time was around 20 seconds. However, when B-SQL was copied and executed separately, it only took about 4 seconds.
Checked the system load, and the highest CPU and memory usage were within 80%.
[Resource Configuration] Enter TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]

| username: Billmay表妹 | Original post link

Post the complete SQL.

| username: forever | Original post link

Concurrent SQL is indeed slower than single SQL execution. When you executed the single SQL, was it tested during peak business hours?

| username: xfworld | Original post link

Compare the execution plans to see if the execution plan paths are completely consistent. If there are changes, there is still room for optimization.

Then check if there is a significant change in the query data corresponding to B-SQL. If the health update has not been performed, the collected statistical sample data can also cause changes in the execution plan path, leading to inaccuracies…

It is recommended to investigate step by step from the above perspectives.

| username: zhanggame1 | Original post link

The CPU load is indeed very high at 80%.

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

Is there any bottleneck with the resources, such as CPU, network card, or thread pool?

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

Are there execution plans for 20s and 4s?

| username: dba远航 | Original post link

One is to check if there is data skew, and the other is to see if the load is the same at two test time points?

| username: zhaokede | Original post link

Is there a potential performance bottleneck?

| username: tidb菜鸟一只 | Original post link

Post the execution plan of the SQL’s explain analyze for us to review. Guessing without it doesn’t make much sense…

| username: onlyacat | Original post link

Please provide the execution plan, the SQL statement, and screenshots of Grafana and the dashboard.

| username: redgame | Original post link

It is common for it to be slower when concurrent. The opposite is rare…

| username: TiDBer_aaO4sU46 | Original post link

It is normal for an SQL query to take longer when executed concurrently compared to when executed individually.

| username: 不想干活 | Original post link

The CPU cannot reach 80%, that’s too high.

| username: Soysauce520 | Original post link

There is no specific information, it’s too broad.

| username: TiDBer_小阿飞 | Original post link

Can you provide the execution plan for EXPLAIN ANALYZE? Moreover, the execution conditions and environments for 20s and 4s are also different.

| username: 小于同学 | Original post link

Take a look at the execution plan of the SQL.

| username: Raymond | Original post link

I’ve encountered this issue as well. I actually suspect that the internal concurrency or queuing mechanism of TiKV needs improvement.

| username: 连连看db | Original post link

If the where conditions in B-SQL are different, then it is necessary to analyze whether these different where condition SQL statements all execute quickly.

| username: YuchongXU | Original post link

Please share the performance screenshot.