Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.Original topic: 并发读执行慢
|
username: jiangh
We have a relatively simple read SQL that can produce results in just over 1 second when executed individually. However, when this SQL is executed concurrently, it takes more than 20 seconds, but the data load does not significantly increase during concurrent execution. Is there any way to optimize the SQL execution time?
[Resource Allocation] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
Please post this.
Sorry, I can’t translate the content from the image. Please provide the text you need translated.
When the execution time of an SQL query significantly increases during concurrent execution, but the data load does not noticeably rise, you can try the following methods to optimize the SQL execution time:
Check Indexes: Ensure that the relevant fields in the table have appropriate indexes. Indexes can speed up queries and reduce execution time. You can use the EXPLAIN command to view the SQL execution plan and confirm whether the correct indexes are being used.
Adjust Concurrency: Try adjusting the number of threads for concurrent execution by modifying the database configuration parameters. Increasing concurrency appropriately can enhance the concurrent execution capability of queries and reduce execution time.
Optimize SQL Statements: Check if the SQL statements can be optimized, such as using more suitable query methods, avoiding unnecessary subqueries, and reducing the data scan range. You can find potential optimization points by analyzing the SQL execution plan and query logs.
Database Parameter Tuning: Adjust the database configuration parameters as needed, such as adjusting cache size and query optimizer parameters. Determine the parameters that need adjustment based on database performance metrics and monitoring data.
Database Version Upgrade: If you are using an older database version, consider upgrading to the latest version. New versions typically include performance optimizations and bug fixes that may improve query execution performance.
Please note that optimizing SQL execution time is a comprehensive task that requires adjustments based on specific business scenarios and database configurations. It is recommended to try these methods according to your actual situation and conduct performance testing and monitoring during the optimization process to ensure the effectiveness and stability of the optimizations.
Looking at the monitoring, this metric coincides with the period when the SQL slows down. Is there any way to optimize it?
Let’s compare the execution plans for concurrent execution of 20 seconds and 1 second to see where the differences lie.
Take a look at the EXPLAIN ANALYZE results to see where the 20 seconds of execution time is being spent.
The image you provided is not visible. Please provide the text you need translated.
Please provide the complete execution plan.
At first glance, the two 120K table lookups are putting a lot of pressure on the read pool. We need to consider the rationality of these two indexes.
Is there a way to execute the subquery first? Currently, the filter on table b is executed first, and then it joins with table c. The filter result of table b is 120,000, while the subquery result is only a few.
Check the dashboard to see if there are any slow SQL queries.
Well, the statistics are inaccurate, leading to an incorrect execution plan.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: