RDS for MySQL Performance Tuning – Resolving High Memory Usage Issues

Viewing the Memory Usage

• View the memory usage of instances using DBA Assistant. For details, see Performance Metrics.

  

• You can also use performance_schema to configure memory instruments. This allows you to aggregate memory usage statistics into memory summary tables. For more information, see official MySQL documentation.
  

  MySQL 5.6 does not support performance_schema for memory usage monitoring.

1. Set performance_schema to ON by referring to Modifying RDS for MySQL Instance Parameters.

   

2. Reboot your DB instance for the modification to take effect.
3. Query the sys.memory_global_total view for the total memory usage.

   select * from sys.memory_global_total;

4. Query the sys.session view.
   1. Check the current_memory field for memory usage of each session.

      select thd_id,conn_id, current_memory from sys.session;

   2. Check the memory usage details of the session thread with high memory usage in performance_schema.

      select * from memory_summary_by_thread_by_event_name where thread_id= "ID of the thread with high memory usage" order by CURRENT_NUMBER_OF_BYTES_USED;

5. Query the sys.memory_by_thread_by_current_bytes view.
   1. Check the current_allocated field for memory usage of each background thread.

      select thread_id, user, current_allocated from memory_by_thread_by_current_bytes;

   2. Check the memory usage details of the background thread with high memory usage in performance_schema.

      select * from memory_summary_by_thread_by_event_name where thread_id= "ID of the thread with high memory usage" order by CURRENT_NUMBER_OF_BYTES_USED;

6. Query the memory_global_by_current_bytes view for memory usage statistics by allocation type.

   select event_name,current_alloc from sys.memory_global_by_current_bytes where event_name not like 'memory/performance_schema%' ;

7. Analyze the cause of high memory usage based on the query results.

Common Causes for Excessively High Memory Usage

Generally, the InnoDB buffer pool consumes the most memory. The maximum memory that can be consumed by a buffer pool depends on the parameter settings of the pool. In addition, most of the memory is allocated dynamically and then adjusted as the requests are processed. The memory usage includes the memory that is used by in-memory temporary tables, prefetch caches, table caches, hash indexes, and row lock objects. For more information about the memory usage and parameter limits, see the official MySQL documentation.

• Multiple Statements in One Query

  You can include multiple SQL statements in a single query and separate them using semicolons (;). When receiving the multi-statement query, RDS for MySQL processes the SQL statements one by one. However, some memory can be released only after all these SQL statements are executed.

  If too many SQL statements are sent in a single batch, the memory that is used by various objects to batch execute these SQL statements increases by up to a few hundred MB. This can exhaust the available memory for the MySQL process.

  A multi-statement query causes a sharp increase of network traffic. You can detect a sudden increase through network traffic monitoring or SQL Explorer. Therefore, multi-statement queries are not recommended.

• Buffer pool issues

  The data pages of all tables are stored in the buffer pool. If the requested data pages are hit in the buffer pool, RDS does not perform physical I/O operations. In this case, RDS executes SQL statements very quickly. In addition, the buffer pool uses the least recently used (LRU) caching algorithm to manage the data pages. This algorithm allows the buffer pool to store all of the dirty pages in the flush list.

  The InnoDB buffer pool accounts for the largest proportion of the memory that is provided by your DB instance.

  The following issues are related to the buffer pool:

  • If data pages are not sufficiently pre-warmed, query latency increases. This can be an issue if you restart your DB instance, read cold data, or if there is a low buffer pool hit ratio. Before you upgrade instance specifications or launch a sales promotion, we recommend that you sufficiently pre-warm data pages.
  • There may be too many dirty pages. If a dirty page is not updated for a long period of time, for instance, if the difference between the earliest and current log sequence numbers (LSNs) of the dirty page exceeds 76%, a user thread is triggered to synchronously update the page. This significantly decreases the performance of your DB instance. To fix this issue, you can balance the write loads, prevent excessively high throughput for write operations, reconfigure the parameters that specify how to update dirty pages, or upgrade instance specifications.
• Temporary table issues

  The in-memory temporary table size is limited by the tmp_table_size and max_heap_table_size parameters. If the size of an in-memory temporary table exceeds the configured limit, the in-memory temporary table is converted into an on-disk temporary table. If too many temporary tables are created over a number of connections, the memory usage of your DB instance suddenly increases. MySQL 8.0 provides a new TempTable engine. With this engine, the total size of the in-memory temporary tables that are created by all threads must be smaller than the value of temptable_max_ram. The default value of this parameter is 1 GB. If the total size exceeds the value of this parameter, earlier in-memory temporary tables are converted into on-disk temporary tables.

• Other issues

  If too many tables are created on your DB instance or if the QPS is high, the table cache may consume a certain amount of memory. We recommend that you do not create too many tables or set table_open_cache to a too large value.

  The default memory usage for adaptive hash indexes is 1/64 of the buffer pool size. If you query or write large fields of the BLOB data type, memory is dynamically allocated to these large fields. This also increases the memory usage of your DB instance.

  If the memory usage increases abnormally or the memory runs out altogether, you can locate the cause by referring to official MySQL documentation.