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

Resolution

Important: Performance Insights will reach its end of life on June 30, 2026. You can upgrade to the Advanced mode of Database insights before June 30, 2026. If you don't upgrade, then DB clusters that use Performance Insights will default to the Standard mode of Database Insights. Only the Advanced mode of Database Insights will support execution plans and on-demand analysis. If your clusters default to the Standard mode, then you might not be able to use these features on the console. To turn on the Advanced mode, see Turning on the Advanced mode of Database Insights for Amazon RDS and Turning on the Advanced mode of Database Insights for Amazon Aurora.

Improve database performance

To improve database performance, optimize and tune your queries. Use Amazon RDS Performance Insights to monitor DB instances and identify problematic queries. Then, set an Amazon CloudWatch alarm on the FreeableMemory metric to receive a notification when available memory reaches 95%. It's a best practice to keep at least 5% of the instance memory free.

Check your memory allocation in Amazon RDS for MySQL

Calculate your memory allocation

To calculate the approximate memory usage for your DB instance, use the following formula:

Total Memory Usage = (innodb_additional_mem_pool_size + innodb_buffer_pool_size + innodb_log_buffer_size + key_buffer_size + query_cache_size + tmp_table_size) + max_connections (binlog_cache_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack ) + (performance_schema max_connections * 429498)`

Make sure that you have enough resources allocated to your database to run your queries. Certain queries, such as stored procedures, can take an unlimited amount of memory when they run. To avoid transactions that run for a long time, break up your large queries into smaller queries. For more information about how Amazon RDS for MySQL uses memory, see How MySQL Uses Memory on the MySQL website.

It's a best practice to, regularly upgrade the minor version of MySQL of your instance. Earlier minor versions might contain memory leak-related bugs. For more information about MySQL versions, see MySQL 8.0 release notes on the MySQL website.

Check your buffer pool size

To view long running transactions, memory utilization statistics, or locks, use the SHOW ENGINE INNODB STATUS command. For more information about the SHOW ENGINE query, see SHOW ENGINE query on the MySQL website. Review the output and then check the BUFFER POOL AND MEMORY entry for information about memory allocation for InnoDB, such as Total Memory Allocated, Internal Hash Tables, and Buffer Pool Size. 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.

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. For more information about buffer pools, see Buffer Pool on the MySQL website.

To identify the source of memory usage, check the innodb_buffer_pool_size first. Then, if needed, modify the parameter value in your custom parameter group to reduce innodb_buffer_pool_size.

For more information, see Best practices for configuring parameters for Amazon RDS for MySQL, part 1: Parameters related to performance.

Check your MySQL threads

Memory is also allocated for each MySQL thread that's connected to a MySQL DB instance. For more information about MySQL threads that require allocated memory, see Diagnosing and resolving incompatible parameters status for a memory limit on MySQL and MariaDB issues page.

MySQL creates temporary internal tables to perform some operations. If the tables reach the lowest value of tmp_table_size or max_heap_table_size, then MySQL converts the tables from memory-based tables to disk-based tables. If multiple sessions create temporary internal tables, then you might see increases in memory use. To reduce memory use, only use maximum tables in your queries. For more information, see Server System Variables on the MySQL website and How MySQL uses memory on the MySQL website.

If you increase the tmp_table_size and max_heap_table_size values, then larger temporary tables live in-memory. To verify that MySQL created an implicit temporary table, use the created_tmp_tables variable. For more information about this variable, see Created_tmp_tables on the MySQL website.

View active JOIN and SORT operations

If you identify a query that needs a temporary table, then you must have additional memory to allocate to the table. To view active connections and queries in your database, use the SHOW FULL PROCESSLIST command. For more information about SHOW FULL PROCESSLIST and example queries, see SHOW PROCESSLIST query on the MySQL website.

If your MySQL allocates multiple buffers of the same type, such as join_buffer_size or sort_buffer_size, during a JOIN or SORT operation, then your memory usage increases. For example, MySQL allocates one JOIN buffer to perform JOIN between two tables. For queries that use multiple JOIN tables where all queries require a JOIN buffer, MySQL allocates one fewer JOIN buffer than the total number of tables.

If you configure your session variables with a value that's too high, then you might get errors. To resolve this error, allocate the minimum memory needed to session-level variables such as join_buffer_size and sort_buffer_size.

Note: If you perform bulk inserts to MYISAM tables, then MySQL uses bulk_insert_buffer_size bytes of memory. For more information, see Best practices for working with MySQL.

Activate Performance Schema

If you turn on Performance Insights, then MySQL allocates internal buffers for the Performance Schema when you start instance and during server operations. For more information about how the Performance Schema uses memory, see The Performance Schema memory-allocation model on the MySQL website.

Monitor memory usage on your instance

Check your CloudWatch metrics

To check for low memory, use the Monitoring tab on the Amazon RDS console to monitor the DatabaseConnections, CPUUtilization, ReadIOPS, and WriteIOPS CloudWatch metrics.

For DatabaseConnections, each connection made to the database requires allocated memory that might reduce freeable memory. Use the following formula to calculate the estimated maximum max_connections quota:DBInstanceClassMemory/12582880

To check if you exceeded the max_connections quota, check the DatabaseConnections CloudWatch metric.

To check for memory pressure, monitor the SwapUsage and FreeableMemory CloudWatch metrics. It's a best practice to keep memory pressure levels below 95% to improve database performance. For more information, see Why is my Amazon RDS DB instance using swap memory when I have sufficient memory?

Use MySQL sys schema to track memory use

Use the MySQL sys schema to track the connection, component, and query of your memory. For more information, see Chapter 30 MySQL sys schema on the MySQL website. Use MySQL sys schema and the Performance Schema tables to identify and track current memory usage.

Note: You must activate Performance Schema to use the sys schema.

To track memory use in the sys schema, log in to the database and take the following actions:

• Use memory_by_host_by_current_bytes to determine what host uses the most memory.
• Use memory_by_thread_by_current_bytes to determine which thread ID uses the most memory.
  Note: The thread ID in MySQL can be a client connection or a background thread. You can use the sys.processlist view or performance_schema.threads table and map thread IDs to MySQL connection IDs.
• Use memory_by_user_by_current_bytes to determine which user uses the most memory.
• Use memory_global_by_current_bytes to determine which engine component uses the most memory.
• Use memory_global_total to view the total tracked memory usage in the database engine.
• Use sys.memory_global_by_current_bytes to determine which component uses the most memory.
  Note: For memory events related to the Performance Schema use memory/performance_schema/%. For InnoDB use memory/innodb/%.

To identify which components of features consume the most memory at the global level, run the following query:

SELECT  SUBSTRING_INDEX(SUBSTRING_INDEX(event_name, '/', 2),    '/',    -1    )  AS event_type,    ROUND(SUM(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) AS MB_CURRENTLY_USED    FROM performance_schema.memory_summary_global_by_event_name    GROUP BY event_type    HAVING MB_CURRENTLY_USED>0;

If performance_schema uses the most memory, then run the following queries to determine the event_name that uses memory.

select * from sys.memory_global_by_current_bytes where event_name like '%performance_schema%' and current_count > 0;

select * from performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%'order by CURRENT_NUMBER_OF_BYTES_USED desc;

Then, run the following queries:

select * from sys.memory_global_by_current_bytes where event_name like 'memory/sql%' and current_count > 0;

select p.id,p.user,p.host,p.db,p.command,p.state,p.info,t.thread_id,t.type from information_schema.processlist p, performance_schema.threads t where p.id=t.processlist_id and t.thread_id=thread_id;

Note: Replace memory/sql% with your event type.

To view the details on memory allocation per event for a specific thread, run the following query:

select * from performance_schema.memory_summary_by_thread_by_event_name where thread_id=thread_id and CURRENT_COUNT_USED >0 order by CURRENT_NUMBER_OF_BYTES_USED desc;

You can also use the performance_schema event to show how much memory MySQL allocates for internal buffers that the Performance Schema uses. To see how much memory is allocated, run the following query:

select * from performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

You can find the memory instruments in the setup_instruments table in the memory/code_area/instrument_name format.

To activate memory instrumentation in Performance Schema, set the ENABLED column of the instrument to YES in the performance_schema.setup_instruments table.

Note: In MySQL 8.x, memory instrumentation is active by default when the Performance Schema is active.

Monitor resource use

To monitor the resource use on a DB instance, activate Enhanced Monitoring. Then, set a granularity between 1 and 5 seconds. The default granularity is 60 seconds. You can use Enhanced Monitoring to see the freeable and active memory in real time.

To monitor the threads that consume the most CPU and memory, run the following command to list the threads for your DB instance:

select THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID from performance_schema.threads;

Then, run the following command to 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;

Note: Replace thread-ID with the thread ID.

Related information

Monitoring tools for Amazon RDS

Troubleshooting memory usage issues for Aurora MySQL databases