The Time Difference Between Concurrent Execution and Single Execution of the Same SQL is Too Large

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

Original topic: 同一条sql并发执行与单次执行耗时差距过大

| username: Miracle

[TiDB Usage Environment] Testing
[TiDB Version] V5.4.0
[Reproduction Path] Operations performed that led to the issue

Single SQL execution can be seen in the attachment.
Concurrent execution script:
for ((i=1; i<=20; i++))
do
time mysql -uxxx -pxxx -P4000 -hxxx -D xxx < select.sql >> output${i}.txt 2>&1 &
done

Data volume:
table1: 10k
table2: 20k
table3: 0

[Encountered Issue: Problem Phenomenon and Impact]

The same SQL, single execution takes about 77ms. When running 20 concurrent executions, it takes about 460ms. (Both times are taken from the slow log table data)
In this instance, only the test SQL was run, with no other load.
I looked at the execution plans on both sides, and they were different the first time I checked. After recollecting the statistics and retesting, the execution plans were consistent. I want to ask why there is such a significant difference in execution time.

[Resource Configuration]
The instance is deployed on K8S, with three physical machine nodes. Physical machine configuration: CPU 32 cores, memory 256G, 10Gbps bandwidth.
TiDB resource allocation: 8 cores 12G X 1
PD resource allocation: 4 cores 2G X 3
TiKV resource allocation: 8 cores 16G X 3
The resource load on the physical machines is also very low.

[Attachments: Screenshots/Logs/Monitoring]
SELECT.sql (3.1 KB)
create_table.sql (4.1 KB)
Single Execution Plan.xlsx (15.1 KB)
Concurrent Execution Plan.xlsx (15.3 KB)

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

The execution plan is missing columns, where did the operator info go?

| username: Miracle | Original post link

I deleted the operator info, desensitizing it is a bit complicated :joy:
I’ll desensitize it and re-upload it.

| username: dba远航 | Original post link

The statistical information error is too large.

| username: Miracle | Original post link

There shouldn’t be a significant discrepancy since statistical information was manually collected before testing.

| username: zhanggame1 | Original post link

Are all the SQL statements exactly the same, or are the values filtered by the WHERE condition slightly different?

| username: Miracle | Original post link

There is only one SQL in total, and the single execution SQL is the same as the concurrent SQL.

| username: zhanggame1 | Original post link

If there are multiple TiDB servers, will the execution plan be the same if only one is left?

| username: Miracle | Original post link

The execution plans for single execution and concurrent execution are now consistent.

| username: zhanggame1 | Original post link

Did you leave only one TiDB server? If consistent, analyze it again.

| username: Miracle | Original post link

Yes, there is only one TiDB server during testing.

| username: zhanggame1 | Original post link

Was the issue resolved?

| username: Miracle | Original post link

No…

| username: xfworld | Original post link

First, provide the cluster configuration,
then the data structure and scale,
next, provide the SQL script, and then look at the execution plan.

Without the first two and some scenario descriptions, it’s hard to identify the problem points.

| username: Miracle | Original post link

I have updated the information. Please check if any additional information is needed.

| username: xfworld | Original post link

TiDB resource allocation: 8 cores 12G, PD resource allocation: 4 cores 2G, TiKV resource allocation: 8 cores 16G.

Don’t be stingy, how many node instances are there? Data structure?

Why are you using version 5.4.0? Why not use 6.1.x or 6.5.x, or a higher version?

| username: Miracle | Original post link

The data structure is also maintained, with one TiDB instance and three each of PD and TiKV.
We are using version 5.4.0 because that’s what is used in the production environment. We have also tried versions 6.5.0 and 7.5.0, and the performance is the same.
I don’t think it has much to do with the version…

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

Still can’t see the operator info.

| username: Miracle | Original post link

Uh, try downloading it again. I’ve updated it.
I downloaded it and it’s there too.

| username: Miracle | Original post link

Thank you all for your replies. After increasing the CPU limit for TiDB, the differences have narrowed to a normal range.