Common MySQL OOM Scenarios and TDSQL‑C Memory Optimization Strategies

Out‑of‑memory (OOM) errors occur when a MySQL instance exceeds its allocated memory, causing the process to be killed and resulting in brief unavailability. Because MySQL’s memory management is complex and performance schema is disabled by default, diagnosing OOM can be challenging.

Common OOM causes in production:

Large InnoDB data dictionary memory when many tables exist (e.g., >200k tables and 700k indexes), which is not counted in the buffer pool.

Memory spikes caused by large queries; performance schema can reveal which modules consume the most memory.

Prepared‑statement cache growth, especially with tools like sysbench that enable it by default.

Excessive connection count on small‑memory instances.

Oversized net buffers (e.g., 16 MB) due to large statements or long‑lived connections.

Kernel bugs leading to memory leaks, detectable with valgrind.

Typical diagnostic queries include:

# 1. Query memory usage per module
SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,
       sys.format_bytes(SUM(current_alloc)) AS current_alloc
FROM sys.x$memory_global_by_current_bytes
GROUP BY SUBSTRING_INDEX(event_name,'/',2)
ORDER BY SUM(current_alloc) DESC;

# 2. Identify top connections by memory
SELECT thread_id, event_name, SUM_NUMBER_OF_BYTES_ALLOC
FROM performance_schema.memory_summary_by_thread_by_event_name
ORDER BY SUM_NUMBER_OF_BYTES_ALLOC DESC
LIMIT 20;

# 3. Detail of a specific connection
SELECT *
FROM performance_schema.threads
WHERE THREAD_ID = xxx;

Additional queries can retrieve per‑connection memory components such as SERVER_MEMORY_USED, INNODB_MEMORY_USED, PFS_MEMORY_USED, and OS_MEMORY_USED.

TDSQL‑C overview: TDSQL‑C is a MySQL‑compatible distributed database that separates compute and storage, providing high performance, low cost, large storage, low replication lag, rapid scaling, and serverless features. This architecture introduces extra memory consumption for log‑send buffers, remote page‑IO queues, and communication management.

Optimization measures for TDSQL‑C:

Server‑side parameter tuning (e.g., reducing innodb_log_buffer_size , innodb_ncdb_log_buffer_size , key_buffer_size , innodb_ncdb_wait_queue_size , innodb_ncdb_log_flush_events ) can lower memory usage without noticeable performance loss.

Expose information_schema.detail_processlist to quickly query per‑connection memory usage and aggregate totals.

Introduce innodb_hot_page_time and show coldpage status to count cold pages, helping users size the buffer pool appropriately.

Limit buffer‑pool growth with innodb_max_lru_pages_pct to prevent OOM even when usage is below 100%.

Improve dynamic buffer‑pool resizing to reduce performance jitter, especially when shrinking the pool, by minimizing the time the buffer‑pool mutex is held.

Experimental results on a 2c4g instance showed that after applying these tunings, memory consumption dropped by roughly 200 MB with no significant performance degradation, and dynamic resizing caused only brief QPS fluctuations.

Conclusion: Memory‑related OOM issues remain a major challenge for database systems. TDSQL‑C addresses these through comprehensive server‑parameter optimization, richer monitoring, cold‑page detection, buffer‑pool usage caps, and refined resizing algorithms, thereby enhancing stability and user experience.