How to safeguard SQL Server on Linux from OOM-Killer
On a Windows based server, when all the available memory including the page file is consumed, the server's performance becomes sluggish and out of memory errors are logged to the event logs. On Linux systems, the behavior is slightly different. When the server is running low on memory, the Linux kernel will choose a process to be killed to restore smooth operation of the system. This mechanism on Linux is called OOM-Killer. More information at https://linux-mm.org/OOM_Killer
On servers running SQL Server, the killed process could very likely be SQL Server process as it is expected to have a larger memory foot print compared to other processes. In this blog, we shall review a customer scenario that highlights the need to perform additional configuration adjustments once SQL Server is installed on Linux.
SQL Server support team had the following customer scenario recently.
- Customer was running SQL Server 2017 on Redhat enterprise Linux server.
- The server had 12GB of RAM.
- SQL Server was installed with the default configurations.
- Some databases were part of Alwayson Availability group. The clustering layer is based on Red Hat Enterprise Linux (RHEL) HA add-on built on top of Pacemaker.
When an index rebuild was kicked off on a large table (around 25GB), the reindex operation terminated, and the availability group had failed over to the other replica.
Upon further investigation, we discovered that the SQL Server process terminated at the time reindex operation was run and this resulted in the failover.
To determine the reason for the unexpected shutdown, we reviewed the Linux System Logs (/var/log/messages on RHEL) & pacemaker logs. From the pacemaker logs and system logs, we saw entries indicating that oom-killer was invoked, and as a result SQL Server process was terminated.
Here are some relevant entries from the pacemaker log. Similar information can also be seen in /var/log/messages file.
Sep 13 16:17:30 l99s0004 kernel: [9264025.516359] sqlservr invoked oom-killer: gfp_mask=0x280da, order=0, oom_score_adj=0
Sep 13 16:17:30 l99s0004 kernel: [9264025.516555] 184007 total pagecache pages
Sep 13 16:17:30 l99s0004 kernel: [9264025.516556] 0 pages in swap cache
Sep 13 16:17:30 l99s0004 kernel: [9264025.516558] Swap cache stats: add 0, delete 0, find 0/0
Sep 13 16:17:30 l99s0004 kernel: [9264025.516558] Free swap = 0kB
Sep 13 16:17:30 l99s0004 kernel: [9264025.516559] Total swap = 0kB
Sep 13 16:17:30 l99s0004 kernel: [9264025.516560] 3145598 pages RAM
Note: The highlighted entry indicates that no swap file was configured on the server.
The log also shows snapshot of memory consumption of all the processes on the system at the time oom-killer was invoked. The output is shown in a tabular format with only three processes below for better readability.
| Pid | Uid | Tgid | Total_vm | Rss | Nr_ptes | Swapents | Oom_score_adj | name |
| 46206 | 0 | 46206 | 26370 | 247 | 54 | 0 | -1000 | Sshd |
| 33492 | 992 | 33492 | 49412 | 3805 | 46 | 0 | 0 | Sqlservr |
| 33495 | 992 | 33495 | 3029788 | 2418852 | 5059 | 0 | 0 | Sqlservr |
Note: Highlighted numbers are in 4K pages. Rss column (memory usage) for SQL Server process calculates to approximately 9448MB ((2418852*4)/1024).
Sep 13 16:17:30 l99s0004 kernel: [9264025.516658] Out of memory: Kill process 33495 (sqlservr) score 799 or sacrifice child
Sep 13 16:17:30 l99s0004 kernel: [9264025.516709] Killed process 33495 (sqlservr) total-vm:12119152kB, anon-rss:9675408kB, file-rss:0kB, shmem-rss:0kB
Sep 13 16:17:30 l99s0004 kernel: sqlservr invoked oom-killer: gfp_mask=0x280da, order=0, oom_score_adj=0
From the highlighted line, we can see that SQL Server has anon-rss value of 9675408kB.RSS stands for "resident set size" which is the amount of memory that is currently allocated in RAM for the process. file-rss is the amount of memory in the swap file which is 0KB for all the processes on this system.
When SQL Server starts, the amount of physical memory available to SQL Server is controlled by memory.memorylimitmb configuration option which by default is 80% of the physical memory. Based on this setting, the value 9675408kB (80% of 12GB) for SQL Server's anon-rss makes sense because the server has a total of 12GB of RAM.
This information can also be seen in SQL Server errorlog during the startup.
2017-09-13 16:23:17.62 Server Detected 9478 MB of RAM. This is an informational message; no user action is required.
Ref: /en-us/sql/linux/sql-server-linux-configure-mssql-conf#memorylimit
Due to the default configuration settings, SQL Server could use around 9.5GB of 12GB RAM installed on the server. This leaves around 2.5GB of memory on the server to be used by Linux kernel and other processes running on the server. To begin with, SQL Server was only using a portion of 9.5GB leaving plenty of memory available on the system. However, when the index rebuild was executed, SQL Server used up all the 9.5GB of memory it can use. This condition had caused very little memory to be left on the Linux server, causing the oom-killer to be invoked. SQL Server was chosen as a victim as it has the highest memory usage(oom-score). This behavior is expected.
To make SQL Server less susceptible for termination by oom-killer, we recommend one or both of the following suggestions.
- Adjust memory.memorylimitmb configuration option carefully to leave enough memory on the system even if SQL Server were to use all of the memory configured through this setting.
- Ensure that swap file exists and sized properly.
In this customer scenario, we were able to avoid oom-killer by setting memory.memorylimitmb to 6GB OR by creating a swap file and setting it to appropriate size.
For other best practices and configuration guidelines for SQL Server 2017 on Linux, please refer to documentation at /en-us/sql/linux/sql-server-linux-performance-best-practices .
Venu Cherukupalli
Senior Escalation Engineer
Microsoft SQL Server support.
Reviewed by: Suresh Kandoth, Denzil Rebeiro, Pradeep MM
Comments
- Anonymous
October 25, 2017
Great explanation of sqlservr invoked OOM-Killer in RHEL Venu!