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

To Nha Notes | Jan. 20, 2023, 10:17 a.m.

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.

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 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.
• Regularly upgrade your instance to a more recent minor version of MySQL. Older minor versions are more likely to contain memory leak-related bugs.

Performance guidelines

In general, acceptable values for performance metrics depend on what your application is doing relative to your baseline. Investigate consistent or trending variances from your baseline. The following metrics are often the source of performance issues:

• High CPU or RAM consumption – High values for CPU or RAM consumption might be appropriate, if they're in keeping with your goals for your application (like throughput or concurrency) and are expected.

• Disk space consumption – Investigate disk space consumption if space used is consistently at or above 85 percent of the total disk space. See if it is possible to delete data from the instance or archive data to a different system to free up space.

• Network traffic – For network traffic, talk with your system administrator to understand what expected throughput is for your domain network and internet connection. Investigate network traffic if throughput is consistently lower than expected.

• Database connections – If you see high numbers of user connections and also decreases in instance performance and response time, consider constraining database connections. The best number of user connections for your DB instance varies based on your instance class and the complexity of the operations being performed. To determine the number of database connections, associate your DB instance with a parameter group where the User Connections parameter is set to a value other than 0 (unlimited). You can either use an existing parameter group or create a new one. For more information, see Working with parameter groups.

• IOPS metrics – The expected values for IOPS metrics depend on disk specification and server configuration, so use your baseline to know what is typical. Investigate if values are consistently different than your baseline. For best IOPS performance, make sure that your typical working set fits into memory to minimize read and write operations.

When performance falls outside your established baseline, you might need to make changes to optimize your database availability for your workload. For example, you might need to change the instance class of your DB instance. Or you might need to change the number of DB instances and read replicas that are available for clients.

References

https://aws.amazon.com/premiumsupport/knowledge-center/low-freeable-memory-rds-mysql-mariadb/

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MonitoringOverview.html