Issue Description
When the CPU utilization of a TDSQL Boundless instance is excessively high, the following phenomena typically occur:
In instance monitoring, the CPU Utilization or Max CPU Utilization remains consistently high or exhibits noticeable spikes during specific periods.
The Slow Queries increases, and the execution time for some SQL statements rises significantly.
The average SQL execution time, P95 SQL execution time, and P99 SQL execution time increase.
The number of currently open connections, aggregated number of active threads, or node-level number of active threads increases.
In a multi-node instance, the CPU utilization of only a few individual nodes may be significantly higher than that of other nodes.
In severe cases, service access may experience response jitter, request backlog, or timeouts.
If CPU utilization remains excessively high over an extended period, it may affect instance performance and business stability. It is recommended to investigate promptly.
Common Causes
Excessively high CPU utilization in a TDSQL Boundless instance is commonly caused by the following reasons:
Slow or Inefficient SQL Queries
Slow SQL queries are a common cause of high CPU utilization. They are typically characterized by queries missing appropriate indexes or by SQL statements involving high-overhead operations such as large-scale scans, sorting operations, aggregation, or table lookups. In such scenarios, you will typically observe concurrent increases in the number of slow queries, average SQL execution time, P95 SQL execution time, and P99 SQL execution time.
Sudden Surge in Service Requests
When business peak traffic surges abruptly, or when batch tasks, data extraction tasks, or migration tasks are executed intensively within a short period, the instance CPU utilization may rise rapidly. This scenario is often accompanied by an increase in the total QPS, read QPS, write QPS, and transactions processed per second.
Hotspot Access or Uneven Load Distribution
TDSQL Boundless is a distributed database. If requests are consistently concentrated on a small amount of hot data, hot shards, or a few nodes, it may cause the load on individual nodes to become significantly higher, resulting in a single node's CPU reaching full utilization. In this scenario, you will typically observe that node-level metrics such as CPU utilization, number of active threads, total QPS, and data disk IO latency are significantly higher than those of other nodes.
Frequent Metadata Access
Frequently executing metadata operations such as SHOW TABLES, SHOW TABLES LIKE, or extensively querying table structures and system object information can also incur high CPU overhead. This impact becomes more pronounced, especially when the number of tables in a single database is large.
Memory or I/O Pressure Amplifying CPU Issues
In some scenarios, high CPU utilization is not the primary cause but is indirectly triggered by resource bottlenecks. For example, conditions such as high memory utilization, persistently high read IOPS or write IOPS, or elevated maximum data disk IO latency can all lead to slower SQL execution, thereby amplifying CPU pressure.
Troubleshooting
Step 1: Viewing Instance Monitoring
Log in to the TDSQL Boundless console, go to the TDSQL Boundless instance monitoring page, and first confirm the scope and duration of the CPU anomaly. It is recommended to focus on the following types of monitoring metrics:
|
Resource metrics | Used to determine whether there is a resource bottleneck or abnormal node load. It is recommended to focus on the following: CPU Utilization Maximum CPU Utilization Memory Utilization Maximum Memory Utilization Maximum Data Disk Utilization Disk utilization Read IOPS Write IOPS Total IOPS Maximum Data Disk IO Latency |
Connection metrics | Used to determine whether there is connection buildup or thread pressure. It is recommended to focus on the following: Number of open connections Maximum Number of Connections Aggregated number of active threads Connection utilization |
Access metrics | Used to determine whether there is a traffic surge, an increase in slow SQL queries, or a decline in SQL execution efficiency. It is recommended to focus on the following: Number of slow queries Total QPS Read QPS Write QPS Transactions Per Second (TPS) Number of SQL failures Average SQL execution time P95 SQL execution time P99 SQL execution time |
If the instance is deployed across multiple nodes, it is recommended to also check the node-level monitoring to confirm whether only a few nodes are abnormal. If only a single node's CPU usage is significantly high, you typically need to focus on investigating potential hotspot access or load imbalance issues.
Step 2: Viewing Abnormal Diagnosis via DBbrain
TDSQL Boundless supports anomaly diagnosis and Slow SQL query analysis through DBbrain.
2. Select Performance Optimization from the left navigation pane.
3. At the top of the page, select TDSQL Boundless as the database type and then select the target instance.
4. Click the Exception Diagnosis tab to view diagnostic events triggered within the last 3 hours.
5. Click on a specific event to view its Event Details, Description, Intelligent Analysis, and Optimization Suggestions.
If slow SQL queries exist on the instance, it is recommended to also check the Slow SQL Analysis page, focusing on confirming whether there are any SQL queries with high execution time, high frequency, or a large number of scanned rows during the abnormal time period. For detailed operations, see Exception Diagnosis. Step 3: Troubleshooting Slow and Inefficient SQL Queries
If an increase in CPU utilization is accompanied by a rise in the number of slow queries or an increase in average SQL execution time, it is recommended to prioritize investigating SQL execution efficiency.
You can execute the following statement to view the current session:
For suspicious SQL queries, it is recommended to examine the execution plan:
Focus on the following scenarios:
Check whether the appropriate index is used.
Check whether a full-table scan occurs.
Check whether a large number of back-to-table operations occur.
Check whether there are high-cost sorting, aggregation, or temporary table operations.
Check whether the execution time of the abnormal SQL queries aligns with the abnormal time period in the monitoring data.
If it is confirmed that the issue is caused by poor index design or inefficient SQL writing, it is recommended to optimize the indexes and SQL based on the actual query conditions.
Step 4: Troubleshooting Hotspot Access
If the instance uses a multi-node deployment and only a few nodes exhibit significantly high CPU usage, it is recommended to further investigate whether a hotspot access issue exists.
It is recommended to focus on verifying the following information:
Check whether a certain type of SQL query consistently accesses the same business key.
Check whether a specific node consistently exhibits high CPU usage during peak hours.
Check whether the total QPS, running threads, read IOPS, write IOPS, and disk IO max latency of the abnormal node(s) are rising simultaneously.
Check whether the connection and thread metrics of the abnormal node(s) are significantly higher than those of other nodes.
If hotspot access is confirmed, it is recommended to address it from the following directions:
Optimize the relevant SQL to reduce the cost of individual queries.
Optimize the business model to prevent requests from being concentrated on a single hotspot for extended periods.
Distribute the load by scaling out.
Step 5: Troubleshooting Service Traffic and Background Tasks
If abnormal CPU usage coincides with business peaks, scheduled tasks, batch processing, migration, or inspection times, it is recommended to further investigate whether there is a traffic surge or a concentration of task execution.
Focus on the following:
Queries per Second
Read Queries
Write Queries
Transactions Per Second (TPS)
Failed Queries
If the aforementioned metrics show a significant increase during the abnormal period, it may be caused by a sudden surge in business requests, concentrated task execution, or application retry amplification. It is recommended to mitigate the issue by staggering task execution, controlling concurrency, or implementing application-side rate limiting.
Step 6: Checking for Memory or I/O Pressure
If a CPU increase is accompanied by the following phenomena, it indicates that the issue may be related to a resource bottleneck.
Memory Utilization remains consistently high.
Read IOPS, Write IOPS, and Total IOPS remain consistently high.
Disk IO Max Latency or Node-Level Disk IO Max Latency shows a significant increase.
Avg Query Latency, P95 Query Latency, and P99 Query Latency all increase.
At the node level, whether an OOM has occurred is indicated.
At this point, it is recommended to comprehensively evaluate whether the current instance specification meets the business load requirements and consider upgrading or scaling out based on the actual situation.
Step 7: Scaling Out Instances When Necessary
If investigation confirms that the high CPU usage is caused by insufficient current specifications, it is recommended to scale out based on the actual business situation.
If only a few nodes show significantly high loads, consider scaling out first to distribute the hotspot load.
If both memory and I/O pressure exist, it is recommended to perform a unified scale-out after a comprehensive evaluation.
Note:
An increase in CPU utilization is not necessarily caused solely by the CPU itself. It is recommended to make a comprehensive judgment by combining metrics such as Slow Queries, Queries per Second, Avg Query Latency, P95 Query Latency, P99 Query Latency, Read IOPS, Write IOPS, and Memory Utilization.
For multi-node instances, it is recommended to simultaneously monitor at both the instance and node levels to avoid missing single-node hotspot issues.
Before the cause is confirmed, avoid directly making high-risk parameter adjustments. Prioritize completing investigations on SQL, traffic, and resources.
If the business involves extensive metadata access, it is recommended to minimize the call frequency and avoid execution during peak business hours.
If whether an OOM has occurred is indicated at the node level, it is recommended to prioritize investigating memory pressure, abnormal SQL, and cache hit rates.
If the cause cannot be identified after investigation, it is recommended to further collect monitoring data, slow SQL queries, execution plans, and business change information from the abnormal time period before an in-depth analysis is conducted.