Issue Description
During the operation of a TDSQL Boundless instance, a persistently high or sudden spike in the number of slow queries may be observed, potentially accompanied by the following phenomena:
The metric for the number of slow queries on the instance monitoring dashboard continues to rise.
CPU utilization surges concurrently, and query response times increase.
Request timeouts or response delays occur on the service side.
The number of SQL failures increases, and some queries cannot be executed normally.
You can view metrics such as the number of slow queries, CPU utilization, and SQL execution time on the Instance Details > Monitoring and Alarms > Monitoring page in the TDSQL Boundless console.
Common Causes
A high number of slow queries is usually caused by inefficient SQL execution or instance load exceeding capacity. Potential causes are as follows:
|
1 | The SQL statement does not use an index or does not use an optimal index, leading to full-table scans or inefficient execution plans. |
2 | The QPS pressure exceeds the capacity limit of the current instance specification, causing a large number of requests to queue up and accumulate. |
3 | Improper parallelism configuration and PQ resource contention lead to excessively long execution time for a single SQL statement. |
4 | After data volume increases, the statistics become outdated, and the optimizer selects a suboptimal execution plan. |
Problem-solving Ideas
For different causes, the troubleshooting approaches are as follows:
|
SQL not using indexes or poor execution plan | Analyze slow SQL queries using DBbrain intelligent diagnostics, add appropriate indexes, and optimize SQL statements. |
QPS exceeding instance capacity | Optimize the business request pattern to reduce invalid queries; upgrade the instance specification when necessary. |
Improper parallelism configuration | Adjust the max_parallel_degree parameter via the console to properly control the concurrency of PQ. |
Outdated statistics | Execute ANALYZE TABLE on the relevant tables to update statistics, enabling the optimizer to select a better execution plan. |
Operation Steps
Step 1: Viewing Slow Query Monitoring
1. Log in to the TDSQL Boundless console. In the Instance List, click the target Instance ID to go to the Instance Details page. 2. Choose the Monitoring and Alarms > Monitoring tab to view the trend of the following metrics:
|
Number of slow queries | Number of slow queries per second | Whether persistently increasing or spiking |
CPU Utilization | Instance CPU utilization | Whether synchronously spiking with the number of slow queries |
Average SQL execution time | Average SQL execution time | Whether significantly lengthened |
P95 SQL execution time | 95th percentile execution time | Long-tail query latency status |
P99 SQL execution time | 99th percentile execution time | Extremely slow query latency status |
Queries per Second | Total number of SQL per second | Whether exceeding instance capacity |
Number of Failed SQL Queries | Number of failed SQL statements per second | Whether a large number of failures occur |
Focus on whether the time of a sudden spike in slow queries coincides with the time of business changes, such as releases or major promotions.
Review the SQL execution time distribution metrics to determine which time range most slow queries fall into, providing a reference for subsequent optimization.
Step 2: Analyzing Slow SQL Queries via DBbrain
Log in to the DBbrain console. In the left-side navigation, select Diagnosis & Optimization and then the Slow SQL Analysis tab. Check whether any Slow SQL diagnosis suggestions exist. For details, see Slow SQL Analysis. Step 3: Analyzing Slow SQL Queries via System Views
For more granular SQL performance analysis, you can query SQL execution statistics through system views.
1. Connect to the database instance and execute the following SQL to view SQL digest statistics.
SELECT
SCHEMA_NAME,
DIGEST_TEXT,
COUNT_STAR AS EXEC_COUNT,
ROUND(AVG_TIMER_WAIT / 1000000000, 2) AS AVG_MS,
ROUND(MAX_TIMER_WAIT / 1000000000, 2) AS MAX_MS,
SUM_ROWS_EXAMINED AS TOTAL_ROWS_EXAMINED,
SUM_ROWS_SENT AS TOTAL_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;
This query lists the top 20 SQL templates with the longest average execution time. Focus on the following information:
AVG_MS: The average execution time in milliseconds. A larger value indicates slower SQL execution.
TOTAL_ROWS_EXAMINED: The total number of rows scanned. If the number of rows scanned is far greater than the number of rows returned, it indicates inefficient scanning.
DIGEST_TEXT: The normalized SQL text, used to locate specific SQL queries.
2. For the identified Slow SQL queries, execute EXPLAIN to view the execution plan.
Focus on the following information:
When the type column shows ALL, it indicates a full-table scan, and an index needs to be added.
A large value in the rows column indicates that too many rows are being scanned.
When the key column shows NULL, it indicates that no index is used.
Step 4: Optimizing SQL Statements.
Based on the analysis results from Step 2 and Step 3, optimize the Slow SQL queries. 1. Add indexes: Add appropriate indexes to the columns involved in WHERE, JOIN, ORDER BY, and GROUP BY clauses.
2. Rewrite SQL:
Avoid SELECT * and query only the required columns.
Avoid using functions or operations on indexed columns in WHERE clauses.
Break down large queries into multiple smaller ones and use pagination to reduce the amount of data returned in a single request.
Avoid mixing indexed and non-indexed columns in OR conditions.
3. Update statistics: If slow queries increase after data volume growth, execute the following SQL to update statistics.
ANALYZE TABLE table_name;
After statistics are updated, the optimizer can select a better execution plan based on the latest data distribution.
Step 5: Adjusting Parallelism Parameters
If slow queries are related to HTAP PQ, you can adjust the parallelism parameter via the console. For details, see Set Instance Parameters. If overall performance degrades due to resource contention from PQ, appropriately reduce the max_parallel_degree parameter.
If a single large query executes too slowly and resources are sufficient, appropriately increase the max_parallel_degree parameter to improve parallelism.
Step 6: Adjusting the Slow Query Threshold
If you need to adjust the criteria for slow query identification, you can modify the long_query_time parameter via the console. For detailed operations, see Set Instance Parameters. Reducing the long_query_time parameter can capture more slow queries, facilitating comprehensive troubleshooting, but may increase log volume.
Increasing the long_query_time parameter allows you to focus only on extremely slow queries, reducing interference.
Step 7: Setting SQL Execution Timeout
To limit the maximum execution time of a single SQL statement and prevent slow queries from occupying resources for extended periods, modify the max_execution_time parameter via the console. For example, setting it to 5000 means a SELECT statement will be automatically terminated if its execution exceeds 5 seconds. For detailed operations, see Set Instance Parameters. Warning:
max_execution_time applies only to SELECT statements. Setting an excessively small timeout may cause normal long-running queries to be terminated unexpectedly. Configure this parameter appropriately based on the actual maximum query duration of your workload.
Step 8: Upgrading the Instance Specification
If the number of slow queries remains high after the preceding optimizations and the QPS has exceeded the capacity of the current instance specification, it is recommended to upgrade the instance specification. For detailed operations, see Adjust Instance Configuration. Precautions
To prevent the issue of a high number of slow queries from recurring, the following preventive measures are recommended:
Configure Alarm Policies: Configure alarms for the Slow Queries and CPU utilization in the console. It is recommended to set the slow query threshold based on your business baseline and set the CPU utilization threshold to 80%.
Regularly Inspect Slow SQL Queries: Periodically check for Slow SQL queries through DBbrain's intelligent diagnosis and promptly optimize inefficient queries.
Update Statistics Regularly: Periodically execute the ANALYZE TABLE command on tables with frequent data changes to ensure the optimizer selects the optimal execution plan.
Standardize SQL Development: New SQL statements to be deployed must be validated using the EXPLAIN command to verify their execution plans, ensuring index usage and a reasonable number of scanned rows.
Monitor QPS Trends: Monitor the growth trend of QPS and plan instance scaling in advance.