Sql-docs: Linux performance tuning - description of vm.swappiness parameter

[HumanEquivalentUnit]

This section of the Linux tuning guide says:

vm.swappiness: This parameter controls relative weight given to swapping out runtime memory by limiting the kernel to swap out SQL Server process memory pages.

I found this phrasing confusing and wanted to understand more about what the setting does and why 1 is the recommended value. Some references are below - would it be possible to use these to expand and clarify this part of the documentation?

I have a rough draft description of what might have helped me, though it can surely be improved:

vm.swappiness: When a process on Linux requests memory and none is available, there is a choice of what happens next: removing cached filesystem data (a quick task), or writing memory pages to the disk swapfile (a slower task). vm.swappiness adjusts this balance by setting one side of a ratio that totals 200, so the default setting of 60 prefers removing items from the filesystem cache at a ratio of 140:60. A setting of 1 is the strongest preference towards clearing down the filesystem cache and away from swapping process memory to disk. This setting is optimal because SQL Server has its own internal caching that can use information about the contents of the database files to make better caching decisions than the operating system, so the priority is to avoid the slowness of SQL Server memory being written to swap disk. (Setting vm.swappiness to 0 blocks the use of swap even in extreme situations where it would avoid a crash, and increases the risk of an Out Of Memory handler forcibly ending SQL Server instead; this is not recommended).

(I am not certain of this reasoning, it is my best current understanding)

• Linux kernel memory management concepts (in the Linux kernel source code) - "As the [memory] load increases, the amount of the free pages goes down and when it reaches a certain threshold (low watermark), an allocation request will awaken the kswapd daemon. It will asynchronously scan memory pages and either just free them if the data they contain is available elsewhere [drop cached filesystem data], or evict to the backing storage device [swap process memory out to disk]"
• Documentation for /proc/sys/vm/ at kernel.org - "Swappiness: This control is used to define the rough relative IO cost of swapping and filesystem paging, as a value between 0 and 200. At 100, the VM [Virtual Memory manager] assumes equal IO cost and will thus apply memory pressure to the [filesystem] page cache and swap-backed pages equally"
  
  
  
  • The Linux kernel source code comments where the swappiness measure is taken into account - "The amount of pressure we put on each LRU [Least Recently Used list, those being filesystem cached pages and process memory pages] is inversely proportional to the cost of reclaiming each list, as determined by the share of pages that are refaulting, times the relative IO cost of bringing back a swapped out anonymous page vs reloading a filesystem page (swappiness)." and the equation fp = (200 - swappiness) * (total_cost + 1) giving the ratio involving 200.
  
  

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: dc91cc25-7d13-a975-76ee-6d97bdcf9416
• Version Independent ID: ae8d2f7e-0c69-1269-a438-d6d36594dd27
• Content: Performance best practices for SQL Server on Linux - SQL Server
• Content Source: docs/linux/sql-server-linux-performance-best-practices.md
• Product: sql
• Technology: linux
• GitHub Login: @tejasaks
• Microsoft Alias: tejasaks

[WilliamAntonRohm]

@HumanEquivalentUnit -- thank you for your well-researched feedback. Excellent alias too.

@tejasaks -- please look into this issue.

[tejasaks]

@HumanEquivalentUnit , thank you for your research and feedback. We were trying to keep the individual description concise while trying to give some context into recommendation. I agree with you that our text could be improved and we will look into it. It may not be too elaborate though as we would like to keep it just descriptive without making it a tutorial on that one parameter.

@amvin87 to help review the comment and content.

[HumanEquivalentUnit]

Thank you :)

I understand it's not a place for a huge tutorial, it would be a very long page if that happened for every parameter. The parts I wanted to understand more about were:

• If the relative weight has "swapping out runtime memory" on one side, what is on the other side - what are we trading away?
• If the setting can have hundreds of possible values, why is 1 a value that can always be confidently recommended - and yet it's not the default value?

Trying to focus on these, I can rewrite to half the size, is that any better and is it clear?

vm.swappiness: adjusts how the Linux kernel reclaims used memory, either by writing some memory pages out to disk (swapping), or by removing data from its filesystem cache. The default is to compromise between them, weighted against the extra IO cost of swapping to disk. A database workload performs best when avoiding swapping, and setting 1 most strongly prioritises reclaim from filesystem cache instead. (Setting 0 increases the risk of reclaiming memory by stopping processes, and is not recommended)