How can I troubleshoot low freeable memory in an Amazon RDS for MySQL database?

Last updated: 2021-08-17

I'm running an Amazon Relational Database Service (Amazon RDS) for MySQL instance. I see that my available memory is low, my database is out of memory, or low memory is causing latency issues in my application. How do I identify the source of the memory utilization, and how can I troubleshoot low freeable memory?

Short description

In Amazon RDS for MySQL, you can monitor four memory statuses:

  • Active: The memory that's actively being consumed by database processes or threads.
  • Buffer: A buffer is a temporary space in memory that's used to hold a block of data.
  • Free Memory: The memory that's available for use.
  • Cache: Caching is a technique where data is temporarily stored in memory, enabling fast retrieval of data.

By default, when you create an Amazon RDS for MySQL instance, buffers and caches are allocated to improve database operations. Amazon RDS for MySQL also has an internal memory component (such as key_buffers_size or query_cache_size) that creates internal temporary tables to perform certain operations.

When you're using Amazon RDS for MySQL, make sure to understand how MySQL uses and allocates memory. After you identify the components that are using memory, you can look for bottlenecks at the instance and database level. Then, monitor those specific metrics and configure your sessions for optimal performance.

Resolution

How MySQL uses memory

In Amazon RDS for MySQL, 80% to 90% of the available memory on an instance is allocated with the default parameters. This allocation is optimal for performance, but if you set parameters that use more memory, then modify other parameters to use less memory to compensate.

You can calculate the approximate memory usage for your RDS for MySQL DB instance like this:

Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)

Buffer pools

Global buffers and caches include components like Innodb_buffer_pool_size, Innodb_log_buffer_size, key_buffer_size, and query_cache_size. The innodb_buffer_pool_size parameter is the memory area for RAM where innodb caches the database tables and index-related data. A larger buffer pool requires less I/O operation diverted back to the disk. By default, the innodb_buffer_pool_size uses a maximum of 75% of available memory allocated to the Amazon RDS DB instance:

innodb_buffer_pool_size = {DBInstanceClassMemory*3/4}

Make sure to review this parameter first to identify the source of memory usage. Then, consider reducing the value for innodb_buffer_pool_size by modifying the parameter value in your custom parameter group.

For example, the default DBInstanceClassMemory*3/4 can be reduced to *5/8 or *1/2. Make sure that the instance's BufferCacheHitRatio value isn't too low. If the BufferCacheHitRatio value is low, you might need to increase the instance size for more RAM. For more information, see Best practices for configuring parameters for Amazon RDS for MySQL, part 1: Parameters related to performance.

MySQL threads

Memory is also allocated for each MySQL thread that's connected to a MySQL DB instance. The following threads require allocated memory:

  • thread_stack
  • net_buffer_length
  • read_buffer_size
  • sort_buffer_size
  • join_buffer_size
  • max_heap_table_size
  • tmp_table_size

Additionally, MySQL creates internal temporary tables to perform some operations. These tables are created initially as memory-based tables. When the tables reach the size specified by tmp_table_size or max_heap_table_size (whichever has the lowest value), then the table is converted to a disk-based table. When multiple sessions create internal temporary tables, you might see increases in memory utilization. To reduce memory utilization, avoid using temporary tables in your queries.

Note: When you increase the limits tmp_table_size and max_heap_table_size, larger temporary tables are able to live in-memory. To confirm whether an implicit temporary table has been created, use the created_tmp_tables variable. For more information about this variable, see created_tmp_tables on the MySQL website.

JOIN and SORT operations

Memory usage will increase if multiple buffers of the same type, such as join_buffer_size or sort_buffer_size, are allocated during a JOIN or SORT operation. For example, MySQL allocates one JOIN buffer to perform JOIN between two tables. If a query involves multi-table JOINs and all the queries require a JOIN buffer, then MySQL allocates one fewer JOIN buffer than the total number of tables. Configuring your session variables with a value that is too high can cause issues if the queries aren't optimized. You can allocate the minimum memory to session-level variables such as join_buffer_size and join_buffer_size andsort_buffer_size. For more information, see Working with DB parameter groups.

If you perform bulk inserts to MYISAM tables, then bulk_insert_buffer_size bytes of memory are used. For more information, see Best practices for working with MySQL storage engines.

The Performance Schema

Memory can be consumed by the Performance Schema if you enabled the Performance Schema for Performance Insights on Amazon RDS for MySQL. When the Performance Schema is enabled, then MySQL allocates internal buffers when the instance is started and during server operations. For more information about how the Performance Schema uses memory, see the MySQL Documentation for The Performance Schema memory-allocation model.

Along with the Performance Schema tables, you can also use MySQL sys schema. For example, you can use the performance_schema event to show how much memory is allocated for internal buffers that are used by Performance Schema. Or, you can run a query like this to see how much memory is allocated:

SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

Memory instruments are listed in the setup_instruments table, following a "memory/code_area/instrument_name" format. To enable memory instrumentation, update the ENABLED column of the relevant instruments in the setup_instruments table:

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

Monitoring memory usage on your instance

Amazon CloudWatch metrics

Monitor the Amazon CloudWatch metrics for DatabaseConnections, CPUUtilization, ReadIOPS, and WriteIOPS when available memory is low.

For DatabaseConnections, it's important to note that each connection made to the database needs some amount of memory allocated to it. Therefore, a spike in database connections can cause a drop in freeable memory. In Amazon RDS, the soft limit for max_connections is calculated like this:

{DBInstanceClassMemory/12582880}

Monitor whether you're exceeding this soft limit by checking the DatabaseConnections metric in Amazon CloudWatch.

Additionally, check for memory pressure by monitoring the CloudWatch metrics for SwapUsage in addition to FreeableMemory. If you see that a large amount of swap is used and you have low FreeableMemory, then your instance might be under high JVM memory pressure. High JVM memory pressure affects database performance. It's a best practice to keep JVM memory pressure levels below 95%. For more information, see Why is my Amazon RDS instance using swap memory when I have sufficient memory?

Enhanced Monitoring

To monitor the resource utilization on a DB instance, enable Enhanced Monitoring. Then, set a granularity of one or five seconds (the default is 60 seconds). With Enhanced Monitoring, you can monitor the freeable and active memory in real time.

You can also monitor the threads that are consuming maximum CPU and memory by listing the threads for your DB instance:

mysql> select THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID from performance_schema.threads;

Then, map the thread_OS_ID to the thread_ID:

select p.* from information_schema.processlist p, performance_schema.threads t 
where p.id=t.processlist_id and t.thread_os_id=<Thread ID from EM processlist>;

Troubleshooting low freeable memory

If you're experiencing low freeable memory issues, consider the following troubleshooting tips:

  • Make sure that you have enough resources allocated to your database to run your queries. With Amazon RDS, the amount of resources allocated depends on the instance type. Also, certain queries, such as stored procedures, can take an unlimited amount of memory while being run.
  • Avoid any long-running transactions by breaking up large queries into smaller queries.
  • To view all active connections and queries in your database, use the SHOW FULL PROCESSLIST command. If you observe a long-running query with JOIN or SORTS operations, then you must enough RAM for the optimizer to calculate the execution plan. Also, if you identify a query that needs a temporary table, you must have additional memory to allocate to the table.
  • To view long-running transactions, memory utilization statistics, and locks, use the SHOW ENGINE INNODB STATUS command. Review the output and check the BUFFER POOL AND MEMORY entries. The BUFFER POOL AND MEMORY entry provides information about memory allocation for InnoDB, such as “Total Memory Allocated”, “Internal Hash Tables”, and “Buffer Pool Size”. The InnoDB Status also helps to provide additional information regarding latches, locks, and deadlocks.
  • If your workload often encounters deadlocks, then modify the innodb_lock_wait_timeout parameter in your custom parameter group. InnoDB relies on the innodb_lock_wait_timeout setting to roll back transactions when a deadlock occurs.
  • To optimize database performance, make sure that your queries are properly tuned. Otherwise, you might experience performance issues and extended wait times.
  • Use Amazon RDS Performance Insights to help you monitor DB instances and identify any problematic queries.
  • Monitor Amazon CloudWatch metrics such as CPU utilization, IOPS, memory and swap usage so that the instance doesn't throttle.
  • Set a CloudWatch alarm on the FreeableMemory metric so that you receive a notification when available memory reaches 95%. It's a best practice to keep at least 5% of the instance memory free.