Dashboard Monitoring Query Slow SQL, Encountering Concurrency Issues

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

Original topic: Dashboard监控查询慢sql,遇到并发问题

| username: TiDBer_w9g3Bozx

When a certain SQL query is executed concurrently, it is found that its execution time is relatively long (about 20 seconds). If executed individually (without concurrency), it only takes about 0.7 seconds. How can this be investigated?

| username: TiDBer_jYQINSnf | Original post link

Check if the execution plan is the same as the execution plan when you run it separately.

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

Please execute explain analyze on this SQL, and then post the result.

| username: 连连看db | Original post link

Check the SQL performance in the slow log and explain analyze to locate where the query time is being consumed.

| username: zhaokede | Original post link

Could it be that the entire database was slow at that time, and all the SQL executed were slow SQL;
I’ve encountered this when using other databases.

| username: Miracle | Original post link

By extracting the execution plan of the SQL that took 20 seconds from the slow log and comparing it with the execution plan of the individually executed SQL, you can see where the specific time consumption occurs.

| username: TiDBer_w9g3Bozx | Original post link

The two SQLs in the screenshot are the same SQL.

| username: redgame | Original post link

When concurrency increases, it will slow down.

| username: DBAER | Original post link

Check the actual CPU and memory usage to see if resource constraints are affecting SQL response time.

| username: Miracle | Original post link

I know it’s the same SQL, but just looking at the SQL itself doesn’t reveal much. You need to look at their execution plans to understand where the time differences between the two SQLs are, and then you can analyze further.

| username: Kongdom | Original post link

Check the resource usage during concurrency.

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

Is the execution plan the same? Is there a lot of data being scanned? Could it be that the read thread pool is full?

| username: dba远航 | Original post link

This depends on whether the load conditions at the time are consistent and whether the query filters are the same.

| username: lemonade010 | Original post link

Is the concurrency high? Are the resources fully utilized? I have encountered situations before where a single SQL query executes very quickly, but when concurrency increases, the resources get fully utilized, leading to very long execution times.