The TUNED_ADM command line tool enables switching between different tuning profiles in Linux. In this best practice, we used the TUNED_ADM tool to switch to an optimized Linux profile for SQL Server. 

Overview

The TUNED_ADM tool enables the Linux administrator to switch between profiles for the operating system. Predefined profiles enable the application of Linux configuration settings in a downloadable package. In this best practice the TUNED-MSSQL was downloaded and installed in the Linux OS. This resulted in the following changes in the tuned SQL Server profile:

• FORCE_LATENCY = 5

Certain configuration parameters that adjust the PM QoS CPU DMA latency according to the CPU load. For example, the force latency parameter enables the user or in this case the SQL Server profile to force a specific value. For more information on this CPU configuration parameter and similar parameters, see the Red Hat Performance Tuning Guide.

• VM.TRANSPARENT_HUGEPAGES set to ALWAYS

Transparent HugePage Support (THP) automates the promotion and demotion of memory pages. Promotion is the automation from small memory pages to larger memory page sizes and demotion is the automation from larger memory pages to smaller memory pages. SQL Server uses transparent HugePages to optimize performance thus, this parameter is set to ALWAYS.   

• VM.SWAPPINESS was set to 1

Swap space is used by the Linux operating as virtual memory. This works is by the Linux kernel writing data from RAM to the swap space which allows for greater memory use on the system. By changing VM.SWAPPINESS to 1 the kernel is instructed to more aggressively reclaim unmapped memory rather than using swap space. 

• VM.DIRTY_BACKGROUND_RATIO was set to 3

The DIRTY_BACKGROUND_RATIO is the percentage of memory that contains free pages and reclaimable pages, that can be filled with dirty pages before the pages are written to disk. Databases can aggressively use the kernel page cache which lowers the background ratio forcing the kernel to flush the dirty pages to disk more frequently. 

• VM.DIRTY_RATIO set to 80

The DIRTY_RATIO is the percentage of total system memory that contain dirty pages before the pages are written to disk. In setting dirty_pages to 80 the kernel is instructed to use 80 percent of total system memory for dirty pages before having to flush the data to disk. 

• VM.DIRTY_EXPIRE_CENTISECS is set to 500

The DIRTY_EXPIRE_CENTISECS define in centisecs (one hundredth of a second) when data is old enough to be written to disk. By setting DIRTY_EXPIRE_CENTISECS to 500 a dirty page can stay in memory for 500 centisecs before it is written to disk. 

• VM.DIRTY_WRITEBACK_CENTISECS is set to 100

The DIRTY_WRITEBACK_CENTISECS parameter defines the frequency the kernel will check to write dirty pages to disk. In this case a value of 100 centisecs instructs the kernel to check every 100 centisecs to write dirty pages to disk. 

• VM.MAX_MAP_COUNT is set to 1600000

The MAX_MAP_COUNT defines the number of memory map areas a process may include. For example, databases use memory extensively which means increasing this value results in more memory map areas available for the database to use.

 In addition to the VM memory settings, this best practice also included network and kernel settings updated by the MSSQL tuned profile. The following list describes the network and kernel settings that were updated by the MSSQL tuned profile: 

• Net.core.rmem_default = 262144
• Net.core.rmem_max = 4194304
• Net.core.wmem_default = 262144
• Net.core.wmem_max = 1048576
• Kernel.numa_balancing = 0
• Kernel.sched_latency_ns = 60000000
• Kernel.sched_migration_cost_ns = 500000
• Kernel.sched_min_granularity_ns =15000000
• Kernel.sched_wakeup_granularity_ns = 2000000

Note: To maintain concise, no descriptions for these updates are included in this best practice. 

Recommendation

Using TUNED_ADM tool to apply the TUNED-MSSQL package automates the application of many best practices in the Linux operating system. After applying the tuned mssql settings, we load tested the new configuration and compared it to the prior performance test. The load OLTP load test showed moderate performance improvements in the following metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• PowerMax IOPS
• Server CPU utilization
• Batch Requests Per Second (BRPS)

The TCP-C NOPM and TPM metrics showed a moderate increase, indicating the changes to the Linux operating system improved performance. PowerMax IOPS, overall CPU utilization, and BRPS showed slight improvements. 

Overall, the configuration changes made to the Linux operating system fine tuned memory, network, and kernel configurations. These changes resulted in more efficient usage of system resources, providing moderate performance improvements. 

Implementation Steps

In this test case, we are going to create and enable Microsoft and Red Hat tuned-profiles-mssql profiles, which is recommended and offers finer Linux performance-related tunings for SQL Server workloads. 

To install tuned-profiles-mssql profile, use yum install command.

Use the following steps to create the profile: 

Save the following definitions in a tuned.conf file under a /usr/lib/tuned/mssql folder

#
# A Tuned configuration for SQL Server on Linux
#
 
[main]
summary=Optimize for Microsoft SQL Server
include=throughput-performance
 
[cpu]
force_latency=5
 
[sysctl]
vm.swappiness = 1
vm.dirty_background_ratio = 3
vm.dirty_ratio = 80
vm.dirty_expire_centisecs = 500
vm.dirty_writeback_centisecs = 100
vm.transparent_hugepages=always
# For multi-instance SQL deployments, use
# vm.transparent_hugepages=madvise
vm.max_map_count=1600000
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
kernel.numa_balancing=0

Note: If you are using Linux distributions with kernel versions greater than 4.18, please comment the following options as shown. Otherwise, uncomment the following options if you are using distributions with kernel versions less than 4.18.

# kernel.sched_latency_ns = 60000000
# kernel.sched_migration_cost_ns = 500000
# kernel.sched_min_granularity_ns = 15000000
# kernel.sched_wakeup_granularity_ns = 2000000
 

Use the following command to enable the profile run:

chmod +x /usr/lib/tuned/mssql/tuned.conf
tuned-adm profile mssql

Use the following command to check the active profile run:

# tuned-adm active

Additional Resources

Performance best practices and configuration guidelines for SQL Server on Linux

In this best practice we changed the default Linux disk scheduler per Red Hat guidelines. 

Overview

The Linux operating system tracks the last time a file was accessed, a specification referred to as atime. For example, when the Linux operating system reads data from a data file the access time is updated. Database frequently access data files and updating the access time with each access represents an overhead on the operating system possibly impacting performance. 

It is possible to specify no access time (noatime), which means the Linux operating system will not update the access time for files. The goal of this best practice was to reduce associated overhead and increase efficiency by setting the operating system to noatime.

In testing noatime results showed no performance improvements. 

Recommendation

Changing the operating system to noatime on database files did not yield performance improvements for any of the following metrics tracked:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• PowerMax IOPS
• Server CPU utilization
• Batch Requests per Second

The last accessed time recommendation has been a long-standing best practice that might have more value in the past. However, this best practice might have a greater impact on other database systems than our OLTP tests. For this reason, we a recommend the read-ahead best practice as a Day 3, Fine Tuning consideration. 

Implementation Steps

To set noatime:

1. Navigate to the /etc/fstab file and open it
2. Add noatime to the filesystem after the defaults entry

Note: Do these steps and reboot.

References

Red Hat Enterprise Linux 8 Managing Storage Devices

In this best practice the default Linux disk scheduler is changed per Red Hat guidelines. 

Overview

When the Linux operating system performs a read from disk, it reads additional data, and this is referred to as read ahead. A benefit to reading additional data is the operating system improves performance if the data has relevance to what the application has requested. However, if the data has no relevance to what the application requested then reading the additional data could impact performance. Operating system administers can adjust the value of the read-ahead setting to optimize performance. For example, Microsoft recommends a read-ahead value of 4096 for SQL Server. Changing the read-ahead value to 4096 did not result in performance gains. 

Recommendation

Changing the operating system to use a read-ahead value of 4096 had no impact on these performance metrics:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• PowerMax IOPS
• Server CPU utilization
• Batch Requests per Second

Although OLTP workload tests showed no performance improvements, this read-ahead best practice might be valuable for other databases. For this reason, we are including the read-ahead recommendation as a Day 3, Fine Tuning recommendation. 

Implementation Steps

Use the following command to view the current read-ahead value for device:

1. blockdev --getra device

Use the following command to modify the read-ahead value:

1. blockdev --setra desired_value device

Note: The value set with this command will not persist between boots.

References

Performance best practices and configuration guidelines for SQL Server on Linux