Issue Description
During the operation of a TDSQL Boundless instance, if its memory utilization remains consistently high or experiences a sudden surge, the following phenomena may accompany it:
Memory utilization on the instance monitoring panel consistently exceeds 80%.
Query response times increase, and intermittent stalling occurs.
New connection establishment fails or is rejected.
A node restart triggered by OOM (Out Of Memory) may cause a primary/secondary switchover.
You can view the memory utilization metrics for each node on the Instance Details > Monitoring and Alarms > Monitoring page in the TDSQL Boundless console.
Fault Risks
Excessively high memory utilization may lead to the following risks:
After triggering OOM, a node automatically restarts, and ongoing transactions are interrupted.
An OOM (Out Of Memory) event on a Data Node (DN) may trigger a primary/secondary switchover. During the switchover, business services may experience second-level interruptions.
An OOM (Out Of Memory) event on a compute node (CN) disconnects all sessions on that node, requiring reconnection.
The occurrence of OOM or a primary/secondary switchover during peak business hours can severely impact business stability and continuity.
Possible Causes
Memory consumption in TDSQL Boundless primarily originates from the following categories. OOM may be triggered by abnormal growth in any of these memory categories.
Session-Level Private Memory
Each database connection is independently allocated a private memory buffer when executing SQL. The higher the number of concurrent connections and the larger the buffer consumed by a single SQL statement, the greater the total private memory usage.
Inefficient SQL or Full-Table Scans: SQL statements that lack indexes or have poor execution plans trigger a large number of sorting operations and temporary table operations. This causes the private memory buffer of a single session to be repeatedly allocated, leading to a sharp increase in memory usage.
Excessive Connections: The number of concurrent connections far exceeds actual business requirements, and the cumulative private memory of each connection results in an excessively large total. You can control the maximum number of connections using the max_connections parameter. Excessive Table Cache Size: When the table_open_cache and table_definition_cache parameters are set too high, an excessive number of cached table objects and table definitions are retained, consuming a large amount of memory. Global Shared Memory
Global shared memory is pre-allocated when the instance starts and is shared for access by all connections.
Insufficient Block Cache: When the Block Cache size is insufficient to cache hot data, a large number of read requests penetrate to the disk, increasing I/O pressure. The Block Cache size is determined by the instance memory specification and cannot be adjusted independently.
Write Buffer Backlog: During peak write periods, the storage engine's MemTable accumulates a large amount of memory, which is typically associated with batch write operations.
Transactional Memory
Large transactions and batch imports hold a large amount of memory during their execution.
Oversized Transactions: A single transaction writes an excessive amount of data, consuming too much transaction memory. You can limit the maximum memory usage per transaction using the tdstore_max_txn_size parameter. Batch Import: Batch operations such as LOAD DATA and INSERT ... SELECT generate a large volume of writes within a short period, resulting in concentrated consumption of transaction memory. You can control the size of the merge buffer for batch imports using the tdstore_bulk_load_total_merge_buffer_size parameter. Instance Specification Mismatch
Insufficient Memory Specification: The memory specification configured for the instance cannot meet the actual workload requirements of the business.
Imbalanced Memory-to-CPU Ratio: After CPU scaling, memory is not adjusted accordingly, causing memory to become the bottleneck.
Solutions
TDSQL Boundless memory management centers on the instance memory specification. When an instance starts, it automatically calculates and sets parameters such as the maximum number of connections, Block Cache size, and table cache memory limit based on the memory specification. The overall approach to addressing high memory utilization is as follows:
1. Check Monitoring: Use console monitoring to confirm memory usage trends and abnormal time points.
2. Identify the Source of Memory Consumption: Use system views to examine the usage distribution of each memory component and determine whether the issue lies with shared memory, private memory, or transaction memory.
3. Optimize SQL and Connections: Optimize inefficient SQL, reduce invalid long-lived connections, and lower session-level private memory consumption.
4. Adjust Parameters: Adjust user-visible memory-related parameters on the console parameter settings page.
5. Scale Up or Upgrade: If requirements are still not met after optimization, upgrade the instance memory specification.
Troubleshooting
Step 1: Viewing Memory 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 memory utilization curves for each node.
3. Focus on the following information:
Check whether memory utilization consistently exceeds 80%.
Check whether there is a sudden surge (a sharp increase over a short period).
Check whether the time point of the surge coincides with business peaks or batch task execution times.
4. If you need more fine-grained memory distribution information, proceed to Step 2.
Step 2: Viewing Memory Usage Details
Use system views to examine the usage of each memory component and identify the primary source of memory consumption.
1. Connect to the database instance and execute the following SQL to view LogService memory statistics.
SELECT * FROM information_schema.LOGSERVICE_MEMORY_STAT;
This view displays the current usage and total capacity of each memory component, helping you quickly identify the primary consumers of memory.
2. Execute the following SQL to view memory summary information in performance_schema (applicable to instances where performance_schema has been enabled).
SELECT
EVENT_NAME,
CURRENT_NUMBER_OF_BYTES_USED / 1024 / 1024 AS CURRENT_MB,
HIGH_NUMBER_OF_BYTES_USED / 1024 / 1024 AS HIGH_MB
FROM performance_schema.memory_summary_global_by_event_name
WHERE CURRENT_NUMBER_OF_BYTES_USED > 0
ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC
LIMIT 20;
This query lists the top 20 memory events with the highest current memory usage, helping you pinpoint the specific source of memory consumption.
Step 3: Optimizing Slow SQL Queries
Slow SQL queries are a common cause of abnormal growth in session-level private memory. Optimize inefficient SQL by using DBbrain intelligent diagnostics or manual investigation.
1. 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. 2. Connect to the database instance and execute the following SQL to view currently executing high-consumption SQL queries.
SELECT
ID,
USER,
HOST,
DB,
COMMAND,
TIME,
STATE,
INFO
FROM information_schema.PROCESSLIST
WHERE COMMAND != 'Sleep'
ORDER BY TIME DESC
LIMIT 20;
Focus on sessions with a high TIME value and a STATE that includes statuses such as Sorting result, Creating sort index, or Sending data. These sessions are typically consuming a large amount of sorting or join buffer.
3. Optimize the identified Slow SQL queries:
Add appropriate indexes to avoid full-table scans.
Avoid sorting or grouping large result sets in SQL.
Break down complex, large queries into multiple smaller ones.
Step 4: Reducing Invalid Connections
An excessive number of connections leads to an excessively large total of session-level private memory.
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 Parameter Settings > Database Parameters tab to view the current value of max_connections (maximum connections). 3. Choose the Monitoring and Alarms > Monitoring tab. View the current connection quantity metric to confirm whether the actual concurrent connections are approaching the upper limit of max_connections.
4. Connect to the database instance and execute the following SQL to view the connection distribution by source and identify the sources with an excessive number of connections.
SELECT
SUBSTRING_INDEX(HOST, ':', 1) AS CLIENT_HOST,
COUNT(*) AS CONNECTION_COUNT
FROM information_schema.PROCESSLIST
GROUP BY CLIENT_HOST
ORDER BY CONNECTION_COUNT DESC;
5. Based on the query results, take the following measures:
Reduce the maximum connection pool size on the application side to align it appropriately with max_connections.
Check for connection leaks (connections not properly closed).
Apply rate limiting or schedule off-peak execution for non-core services.
Step 5: Viewing Block Cache Hit Count
Block Cache is the core cache of the TDStore storage engine. A consistently low hit rate indicates that the current Block Cache size is insufficient. This causes a large number of read requests to penetrate to the disk, increasing I/O pressure. We recommend evaluating whether to scale up the memory specification to increase the Block Cache size.
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, switch to the node dimension, and locate the DataDB Block Cache Hit metric curve.
3. Monitor the hit quantity trend:
A consistently low hit count indicates poor caching effectiveness of the Block Cache, causing a large number of read requests to penetrate to the disk.
You can comprehensively assess disk read pressure by combining metrics such as read IOPS and read I/O throughput.
4. If the number of hits remains consistently low, consider the following measures:
Optimize query patterns to reduce large-scale scans.
The Block Cache size is determined by the instance memory specification and cannot be adjusted independently. To increase the Block Cache size, see Upgrade Instance Memory Specification. Step 6: Viewing Transactional Memory Parameters
Large transactions and batch imports consume a large amount of memory during their execution.
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 Parameter Settings > Database Parameters tab and view the current values of the following transaction memory-related parameters:
|
| The memory usage limit for a single transaction, with a default of 1 GB. |
| The total size of the merge buffer for batch loading. |
3. If you observe a sudden increase in memory utilization during batch task execution, you can appropriately lower the relevant parameter limits.
Attention:
Lowering the transaction memory limit may cause large-scale write operations to be restricted or report errors. Adjust this setting cautiously based on your actual business conditions and verify the changes during off-peak hours.
Step 7: Adjusting Parameters via the Console
Based on the troubleshooting results, adjust the following user-visible memory-related parameters. For details, see Set Instance Parameters. |
| Maximum number of connections. An excessive number of connections will cause the total amount of session-level private memory to become too large. | Set based on actual business concurrency requirements to avoid setting it too high. |
| Number of open table caches. Setting it too high will occupy excessive memory. | Set appropriately based on the number of tables in the instance, and avoid setting it too high. |
| Number of table definition caches. Setting it too high will occupy excessive memory. | Set appropriately based on the number of tables in the instance, and avoid setting it too high. |
| Maximum memory usage per transaction. | If large transactions cause a sudden increase in memory usage, you can appropriately reduce it. |
| The total size of the merge buffer for batch loading. | If batch import causes a sudden increase in memory usage, you can appropriately reduce it. |
Step 8: Upgrading Instance Memory Specifications
If memory utilization still consistently exceeds 80% and affects normal business operations after the above optimizations, it is recommended to upgrade the instance memory specification. After the upgrade, the instance memory specification is automatically updated, and the system automatically adjusts related parameters such as the maximum number of connections, Block Cache size, and table cache memory limit based on the new memory specification. For detailed operations, see Adjusting Instance Configuration. Precautions
To prevent the issue of high memory utilization from recurring, the following preventive measures are recommended:
Configure Alarm Policies: Configure memory utilization alarms for the instance in the console. It is recommended to set the threshold to 80% to detect memory growth trends in advance.
Regularly Inspect Slow SQL Queries: Periodically check for Slow SQL queries through DBbrain's intelligent diagnosis and promptly optimize inefficient queries.
Properly Configure the Connection Pool: The connection pool size on the application side should be appropriately matched with the instance's max_connections to avoid an excessive number of connections.
Control the Scale of Batch Tasks: It is recommended to execute large-scale write operations in batches. Keep the data volume of each batch within a reasonable range.
Monitor Block Cache Hit: Regularly monitor the DataDB Block Cache Hit metric on the console monitoring panel. Consider scaling out when the hit quantity remains consistently low.