RDS instance memory is divided into two categories: shared memory and session-specific private memory. Shared memory is allocated once at instance startup and shared across all connections. Session-specific private memory is allocated per connection on demand — each active query that requires sorting, joining, or reading data claims its full buffer size immediately. Under high concurrency, this per-connection allocation is the primary cause of out-of-memory (OOM) errors and primary/secondary switchovers.
How memory is calculated
The following formula shows how total memory usage is estimated:
Total memory ≈ Shared memory
+ max_connections × per-session memory per connection| Component | Parameters |
|---|---|
| Shared memory | innodb_buffer_pool_size + innodb_log_buffer_size + innodb_additional_mem_pool_size + key_buffer_size + query_cache_size |
| Per-session memory | sort_buffer_size + read_buffer_size + read_rnd_buffer_size + join_buffer_size + binlog_cache_size + tmp_table_size |
Shared memory
Run the following query to check shared memory allocation:
SHOW VARIABLES WHERE Variable_name IN (
'innodb_buffer_pool_size',
'innodb_log_buffer_size',
'innodb_additional_mem_pool_size',
'key_buffer_size',
'query_cache_size'
);The following output is from a 240 MB instance:
+---------------------------------+-----------------+
| Variable_name | Value |
+---------------------------------+-----------------+
| innodb_additional_mem_pool_size | 2097152 |
| innodb_buffer_pool_size | 67108864 |
| innodb_log_buffer_size | 1048576 |
| key_buffer_size | 16777216 |
| query_cache_size | 0 |
+---------------------------------+-----------------+Parameters
| Parameter | Default on RDS | Description |
|---|---|---|
innodb_buffer_pool_size | 64 MB (240 MB instance) | The InnoDB buffer pool. Stores data pages, index pages, undo pages, insert buffers, adaptive hash indexes, locks, and data dictionaries. All SQL reads and writes operate on the buffer pool rather than physical data files — data is written to disk later via checkpoints. A larger buffer pool improves read/write performance but extends fault recovery time. |
innodb_log_buffer_size | 1 MB | The InnoDB log buffer. Stores redo logs before they are flushed to disk. Flushing is triggered every second by the master thread, at each transaction commit, and whenever the available buffer drops below half its total size. 1 MB is sufficient for most workloads. |
innodb_additional_mem_pool_size | 2 MB | Stores internal InnoDB data structures. Increase this value if your instance has a large number of tables. |
key_buffer_size | 16 MB | Caches MyISAM table indexes. For MyISAM tables, index caches are stored in key_buffer, while data caches are stored in operating system memory. Set to 16 MB for all RDS instances. |
query_cache_size | 0 (disabled) | Caches query results by SQL statement hash. Disabled on RDS instances. The cache is invalidated whenever a table is modified, making it unsuitable for write-heavy workloads. |
Session-specific private memory
Unlike shared memory, session buffers are not pre-allocated at startup. Each parameter value is allocated in full the moment a query needs it. With many concurrent connections, the cumulative allocation can exceed available memory, triggering OOM errors.
Run the following query to check session memory allocation:
SHOW VARIABLES WHERE Variable_name IN (
'read_buffer_size',
'read_rnd_buffer_size',
'sort_buffer_size',
'join_buffer_size',
'binlog_cache_size',
'tmp_table_size'
);Example output:
+-------------------------+-----------------+
| Variable_name | Value |
+-------------------------+-----------------+
| binlog_cache_size | 262144 |
| join_buffer_size | 262144 |
| read_buffer_size | 262144 |
| read_rnd_buffer_size | 262144 |
| sort_buffer_size | 262144 |
| tmp_table_size | 262144 |
+-------------------------+-----------------+Parameters
| Parameter | Default on RDS | Modifiable in console | Description |
|---|---|---|---|
read_buffer_size | 256 KB | No | Sequential scan buffer. Scanned before physical reads to reduce I/O. |
read_rnd_buffer_size | 256 KB | No | Random scan buffer. Used when rows are read in sorted order after an index scan. |
sort_buffer_size | 256 KB | No | Sorting buffer for ORDER BY and GROUP BY operations. If the data to sort exceeds this size, MySQL writes a temporary table to disk. On Linux, setting this value above 2 MB switches memory allocation from malloc() to mmap(), which reduces efficiency. |
join_buffer_size | 256 KB | No | Join operation buffer. ApsaraDB RDS for MySQL supports only the nested loop join algorithm. The non-driving table is stored in this buffer to avoid contention on the buffer pool. |
binlog_cache_size | 256 KB | No | Binary log cache. Stores transaction logs before commit; flushes to the binary log file on disk after commit. |
tmp_table_size | 256 KB | Yes | Maximum size of an in-memory temporary table. If a query creates a temporary table that exceeds this limit, MySQL converts it to a MyISAM table on disk. If you see the following error, increase this value: [Err] 1114 - The table '/home/mysql/data3081/tmp/#sql_6197_2' is full |
Note: Only tmp_table_size can be modified in the RDS console. The other session buffer parameters cannot be changed through the console.