Why is my Amazon RDS DB instance using swap memory when I have sufficient memory?
Last updated: 2020-05-13
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 Instance store swap volumes. Because most RDS DB instances use Linux (except SQL Server), your database can sometimes use swap memory.
RDS DB instances require pages in the RAM only when the pages are currently accessed, such as when executing queries. Other pages that are brought into the RAM by previously executed queries are flushed to swap space if they weren't used recently. 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 if swap space was used only one time, the SwapUsage metrics don't return to zero. Swap memory can also be used if you use HugePages that are supported by Amazon RDS Oracle and HugePages on Amazon RDS for PostgreSQL. HugePages are larger than the Linux default size of two megabytes.
To understand swap usage behavior for your RDS DB instance, first check the DB performance metrics based on the application workload. Check both the FreeableMemory and the SwapUsage Amazon CloudWatch metrics to understand the overall memory usage pattern of your RDS DB instance. Check these metrics for a decrease in the FreeableMemory metric that occurs at the same time as an increase in the SwapUsage metric. This can indicate that there is pressure on the memory of the RDS DB instance. For more information, see How can I troubleshoot low freeable memory in an Amazon RDS for MySQL database? If there is enough freeable memory available, swap usage shouldn't affect the RDS DB instance's performance. If your freeable memory remains consistently low, you can change your RDS DB instance size to a larger instance size that has more memory.
To monitor swap memory, turn on Enhanced Monitoring to review metrics in intervals with a granularity of as little as one second. Enhanced Monitoring collects statistics at the host level, and CloudWatch collects data from the hypervisor level every 60 seconds. You can use Enhanced Monitoring to identify increases or decreases that occur for only one second and to see 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 the SQL and wait events that are consuming excessive swap or memory on the RDS DB instance. Performance Insights collects the data at the database level and displays that data in the Performance Insights dashboard. Performance Insights can help you troubleshoot database performance-related issues. For more information, see Monitoring DB load with Performance Insights on Amazon RDS.
Amazon RDS MySQL
If you have low freeable memory, execute SHOW FULL PROCESSLIST; to review all the threads that are running on your database. The process ID from the output of SHOW FULL PROCESSLIST won't match with the process ID that is displayed by Enhanced Monitoring. To view the correct process ID, modify the DB parameter group that is associated with the database to enable the Performance_Schema parameter. This is a static parameter, so you must reboot the RDS DB instance. To avoid downtime, modify the parameter and reboot the database outside peak traffic hours. After the memory has reached the desired usage, follow these steps:
1. Sort the process IDs in the Enhanced Monitoring page so that you can see the ID of the processes that are consuming the maximum CPU.
2. Execute 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 has been consumed by Thread_OS_Id 10374 and 1432, then execute the following query:
select * from performance_schema.threads where THREAD_OS_ID in (10374, 1432)\G
3. Get the PROCESSLIST_ID column from the output of this query. This gives you the process ID that matches the value of the process ID from SHOW FULL PROCESSLIST.
After you have the correct process ID, you can map the process ID with the query. You can use the ID to identify the root cause of the high memory and CPU usage. You can view the OS process by using Enhanced Monitoring. For more information, see Viewing OS metrics in the RDS console.
Amazon RDS PostgreSQL
To identify the process that is consuming high amounts of memory, map the process ID in the Enhanced Monitoring process list to the exact query by running 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 SQL Server
Enhanced Monitoring could identify a specific thread ID that is consuming high amounts of memory. The thread ID is what SQL Server refers to as the Kernal process ID (KPID).
From Amazon 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:
Amazon RDS Oracle
By using the OS process ID from Enhanced Monitoring, you can see which process is consuming the most memory. Then, execute the following query to get the process address for the session:
select ADDR from v$process where SPID=OS_PID;
You can use the process address to identify the session inside the database by executing the following query:
select sid,serial#,username, status from v$session where PADDR='<ADDR from above query>';