Why does my Amazon RDS DB instance use swap memory when I have sufficient memory?

Short description

Amazon Elastic Compute Cloud (Amazon EC2) instances that run Linux use swap memory when a system requires more memory than is allocated. For more information, see Activate instance store swap volume for M1 and C1 EC2 instances. Because most RDS DB instances use Linux (except SQL Server), your database might use swap memory.

RDS DB instances only require pages in the RAM when the pages are currently accessed, such as when you run queries. Other pages that are brought into the RAM by previously run queries are flushed to swap space if they aren't in use. It's a best practice to let the operating system (OS) swap older pages instead of forcing the OS to keep pages in memory. This makes sure that there is enough free RAM available for upcoming queries.

Linux swap usage isn't cleared frequently because clearing the swap usage requires extra overhead to reallocate swap when it's needed and when reloading pages. As a result, if swap space is used on your RDS DB instance, even a single time, the SwapUsage metrics don't return to zero. Swap memory can also be used when you use HugePages that are supported by Amazon RDS for Oracle and HugePages on Amazon RDS for PostgreSQL. HugePages are larger than the Linux default size of two megabytes.

Resolution

To understand swap usage behavior for your RDS DB instance, first check the DB performance metrics based on the application workload. Review both the FreeableMemory and the SwapUsage Amazon CloudWatch metrics to see the overall memory usage pattern of your RDS DB instance. Check for a decrease in the FreeableMemory metric that occurs at the same time as an increase in the SwapUsage metric. This can show that there's pressure on the memory of the RDS DB instance. For more information, see How do I troubleshoot low freeable memory in an Amazon RDS for MySQL database?

If there is enough freeable memory available, then swap usage shouldn't affect the RDS DB instance's performance. If your freeable memory is consistently low, then change your RDS DB instance class to a larger instance class that has more memory.

To monitor swap memory, turn on Enhanced Monitoring to review metrics in intervals as little as one second. Enhanced Monitoring collects statistics at the host level, and CloudWatch collects data from the hypervisor level every 60 seconds. Enhanced Monitoring identifies increases or decreases that occur for only one second and shows the CPU and memory that are used by individual processes. For more information, see Viewing OS metrics using CloudWatch logs.

You can also turn on Performance Insights to identify SQL and wait events that consume excessive swap or memory on the RDS DB instance. Performance Insights collects the data at the database level and shows the data in the Performance Insights dashboard. Use Performance Insights to help you troubleshoot database performance-related issues. For more information, see Monitoring DB load with Performance Insights on Amazon RDS.

Amazon RDS for MySQL

If you have low freeable memory, then run SHOW FULL PROCESSLIST to view all the threads that run on your database. For more information, see SHOW PROCESSLIST statement on the MySQL website. The process ID from the output of SHOW FULL PROCESSLIST won't match with the process ID that Enhanced Monitoring shows. To view the correct process ID, modify the DB parameter group that's associated with the database to the Performance_Schema parameter. This is a static parameter, so you must reboot the RDS DB instance.

Note: To avoid downtime, modify the parameter and reboot the database outside peak traffic hours.

After the memory has reached the desired usage, complete the following steps:

1. Sort the process IDs on the Enhanced Monitoring page so that you see the IDs of the processes that consume the maximum CPU.
2. Run the following query as the master user:

   select * from performance_schema.threads where THREAD_OS_ID in (ID shown in the Enhanced Monitoring window)\G

   For example, if the max memory is consumed by Thread_OS_Id 10374 and 1432, then run the following query:

   select * from performance_schema.threads where THREAD_OS_ID in (10374, 1432)\G

   For more information, see The threads table on the MySQL website.
3. Get the PROCESSLIST_ID column from the output of this query. This column gives you the process ID that matches the value of the process ID from SHOW FULL PROCESSLIST.
4. After you have the correct process ID, map the process ID with the query. Use the process ID to identify the root cause of the high memory and CPU usage.

To view the OS process, use Enhanced Monitoring. For more information, see Viewing OS metrics in the RDS console.

Amazon RDS for PostgreSQL

To identify the process that consumes high amounts of memory, map the process ID in the Enhanced Monitoring process list to the exact query. To identify the process, run the following pg_stat_activity view:

select * from pg_stat_activity where pid=(the PID of your process);

Then, tune the queries to consume less compute resources.

Amazon RDS for SQL Server

Enhanced Monitoring might identify a specific thread ID that consumes high amounts of memory. The thread ID is what RDS for SQL Server refers to as the kernel process ID (KPID).

From RDS for SQL Server, run the following query to get the server process ID (SPID) that corresponds to the KPID:

select * from sys.sysprocesses where kpid = '<Value of Thread ID from Enhanced Monitoring>' ;

After you have the server process ID, for example 69, run the following command to review what is being done by SPID 69:

dbcc inputbuffer(69)

Amazon RDS for Oracle

To identify the process that consumes the most memory, use the OS process ID from Enhanced Monitoring. Then, run the following query to get the process address for the session:

select ADDR from v$process where SPID=OS_PID;

To identify the session inside the database, use the process address to run the following query:

select sid,serial#,username, status from v$session where PADDR='<ADDR from above query>';

When you have Enhanced Monitoring turned on, compare the FreeMemory and FreeableMemory metrics. If the metrics are different, then this can show that a large amount of memory is used in the cache or inactive memory. This memory usage can lead to high swap usage. You might need to clear the cache. For more information about how to clear the cache, see Flushing the buffer cache.

Note: Clearing the buffer cache might negatively affect your database performance.

Related information

Monitoring tools for Amazon RDS