Memory capacity is an important metric to measure the performance of an ApsaraDB RDS for MySQL instance. If abnormal SQL requests are detected on an RDS instance and some databases of the RDS instance need to be optimized, the memory usage of the RDS instance increases. In extreme cases, a primary/secondary switchover is triggered due to out-of-memory (OOM) errors. The memory of an RDS instance consists of the shared memory and session-specific private memory. This topic describes the shared memory and session-specific private memory.
Shared memory
Execute the following statement to query the allocation of the shared memory of an RDS instance:
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 code provides an example on the allocation of the shared memory of an RDS instance that provides a memory capacity of 240 MB:
+---------------------------------+-----------------+
| 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 |
+---------------------------------+-----------------+
A total of five data records are returned in 342.74 milliseconds.
Parameters:
innodb_buffer_pool
The InnoDB buffer pool, which is an area in which InnoDB stores data. The InnoDB buffer pool uses memory resources in an efficient manner and accelerates data read and modification operations. This helps improve the performance of RDS instances in a comprehensive manner. The InnoDB buffer pool stores information about data pages, index pages, undo pages, insert buffers, adaptive hash indexes, locks, and data dictionaries. When you execute SQL statements to read and write data, the operations are performed on the InnoDB buffer pool instead of physical data files. Then, the generated data is written to physical data files by using mechanisms such as checkpoints. The InnoDB buffer pool helps improve the performance of your RDS instance and accelerate the execution of SQL statements. However, if the InnoDB buffer pool is used, a longer period of time is required for RDS instances to recover from faults.
innodb_log_buffer
The InnoDB log buffer, which is used to store redo logs. The InnoDB storage engine writes redo logs to the InnoDB log buffer and then flushes the redo logs to redo log files at a specific frequency. The size of the InnoDB log buffer is set to 1 MB for each RDS instance. You do not need to set the size of the InnoDB log buffer to a large value. In most cases, specific data stored in the InnoDB log buffer is flushed to redo log files at a high frequency. The master thread is flushed every second, transactions are flushed upon commitment, and the stored data is flushed if the available size of the InnoDB log buffer is less than half of the total size.
innodb_additional_mem_pool
The additional memory buffer pool, which is used to store specific InnoDB data structures. You need to set this parameter to 2 MB for your RDS instance. In most cases, you apply for 2 MB of memory to store InnoDB data structures and apply for additional memory to store information about the data structures. The size of the additional memory buffer pool varies based on the number of tables. If a large number of tables are created for your RDS instance, you need to set this parameter to a large value.
key_buffer
The important area, which is used to cache MyISAM tables. You need to set this parameter to 16 MB for all RDS instances. The buffer stores the keys of MyISAM tables. MyISAM tables are different from InnoDB tables. The index caches of MyISAM tables are stored in key_buffer, and the data caches are stored in the memory of the operating system. If an RDS instance uses the MyISAM engine, a specific amount of memory must be reserved for MyISAM tables.
query_cache
The query cache, which is used to store query results to reduce the overhead of the parsing and execution of SQL statements. This parameter is disabled for RDS instances. This parameter is suitable for scenarios in which a large number of read requests but a small number of write requests need to be processed. The query cache stores query results based on the hash values of SQL statements. If you modify a table on your RDS instance, all stored query results of the table become invalid.
Session-specific private memory
The shared memory is the memory allocated when your RDS instance is created and shared by all connections to the RDS instance. If an OOM error occurs on your RDS instance, the OOM error is caused by the private memory of each connection.
Execute the following statement to query the session-specific private 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'
);
The following information describes the query result for the test RDS instance:
+-------------------------+-----------------+
| 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 |
+-------------------------+-----------------+
A total of six data records are returned in 356.54 milliseconds.
Parameters:
read_buffer&read_rnd_buffer
The buffers, which are used to separately store the caches of sequential scans and random scans. You need to set these parameters to 256 KB for your RDS instance. When a thread scans data in a sequential or random manner, the buffers are preferentially scanned to reduce physical reads.
sort_buffer
The memory, which is used to store the intermediate sorting result, which is allocated when the ORDER BY and GROUP BY clauses are used to sort data. You need to set this parameter to 256 KB for your RDS instance. During the sorting process, if the amount of stored data is greater than the value of the sort_buffer_size parameter, a temporary table is generated on the disk for the sorting to complete. In a Linux operating system, if the value of this parameter is greater than 2 MB, the memory is allocated by using
mmap()instead ofmalloc(). This reduces the efficiency.join_buffer
The memory, which is used during a join operation. ApsaraDB RDS for MySQL supports only the nested loop join algorithm. When you perform a join operation between a row in a driving table and a non-driving table, you can store the non-driving table in the join buffer. In this case, you do not need to access the buffer pool that has the concurrency protection mechanism. Set this parameter to 256 KB for your RDS instance.
binlog_cache
The binary log cache, which is used to store the binary log of the thread. You need to set this parameter to 256 KB for your RDS instance. Before a transaction is committed, the system stores the log of the transaction to the binary log cache. After the transaction is committed, the system flushes the log back to the binary log file on the disk to persistently store the log.
tmp_table
The size of the memory temporary table. You can modify this parameter in the console. However, the preceding parameters cannot be modified in the console. If the size of the memory temporary table created by using the required thread exceeds the value of this parameter, the temporary table is converted into a MyISAM temporary table on the disk. If an error similar to the following information occurs when you execute a transaction, you can increase the value of the tmp_table parameter.
[Err] 1114 - The table '/home/mysql/data3081/tmp/#sql_6197_2' is full