How can I troubleshoot low freeable memory in an Amazon RDS for MySQL or MariaDB database?
Last updated: 2019-04-30
I am running an Amazon Relational Database Service (Amazon RDS) MariaDB or 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 can I identify the source of the memory utilization, and how can I troubleshoot low freeable memory?
MySQL allocates buffers and cache to carry out database operations. For more information, see the MySQL Documentation for How MySQL Uses Memory. When using Amazon RDS, 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. After you identify which components are using memory, you can look for bottlenecks at the instance and database level. Then, you can configure your sessions for optimal performance.
Understanding how MySQL uses memory
Global buffers and caches include components like Innodb_buffer_pool_size, Innodb_log_buffer_size, key_buffer_size, and query_cache_size. By default, the innodb_buffer_pool_size uses 75% of the memory of an RDS DB instance. Review this parameter first to identify the source of memory usage. Then, consider reducing the value for innodb_buffer_pool_size. For example, the default DBInstanceClassMemory*3/4 can be reduced to *5/8 or *1/2. Be sure to check the instance's BufferCacheHitRatio to make sure that the ratio is not too low. If the BufferCacheHitRatio is low, you might need to increase the instance size so that you have more RAM. For more information, see Best practices for configuring parameters for Amazon RDS for MySQL, part 1: Parameters related to performance.
Memory is also allocated for each MySQL thread that is connected to a MySQL DB instance. The following threads require allocated memory:
Additionally, MySQL creates internal temporary tables to perform some operations. These tables are created initially as memory-based tables. When the size of these tables reaches the value 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.
You can also see increases in memory usage if multiple buffers of the same type, such as join_buffer_size or sort_buffer_size, are allocated when the session is performing JOIN or SORT operations. 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 JOIN buffer fewer 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 sort_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.
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, 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.
Monitoring and troubleshooting memory usage on your instance
Monitor the Amazon CloudWatch metrics for DatabaseConnections, CPUUtilization, ReadIOPS, and WriteIOPS when available memory is low. To check for memory pressure, monitor 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, your DB instance might be under memory pressure, which can degrade performance. For more information, see Why is my Amazon RDS instance using swap memory when I have sufficient memory?
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).
Run SHOW FULL PROCESSLIST; to see all the open connections. Then, run SHOW ENGINE INNODB STATUS; to see InnoDB information, such as long-running transactions, memory utilization statistics, and locks. Review the BUFFER POOL AND MEMORY section to see if there are any free pages.
Finally, enable Performance Insights so that you can identify queries that are consuming memory. Then, set a CloudWatch alarm on the FreeableMemory metric, so that you receive a notification when available memory decreases. It's a best practice to keep at least 5% of the instance memory free, so set a CloudWatch alarm that notifies you when you've reached at 95% utilization.
You can use Performance Schema memory tables to monitor memory utilization. You can also use MySQL sys schema for simplified views to use in combination with the Performance Schema tables. 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 similar to the following 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. These instruments have a name that is based on the memory/code_area/instrument_name, such as memory/innodb/buf_buf_pool. 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/%';