With the release of SQL Server database platform on Linux database, administrators have a lot more operating system performance tuning options compared to Windows implementations. One tunable is the Linux kernel IO scheduler, which – depending on the workload – can make a significant difference in transaction processing performance.
Redhat Enterprise Linux 7.6 schedulers are listed and we will focus on completely fair queue (cfq)and deadline for this comparison:
- cfq
- deadline
Setting the IO scheduler allows you to compare different algorithms relating to IO priority, timing, sorting, merging, etc.
The Pure Storage® solutions team uses realistic data and real-world workloads for SQL Server testing. For this testing, a 90% write OLTP (Online Transaction Processing) workload (SQL Server 2017 Linux) consisting of short highly concurrent transactions was used. The IO scheduler for the database data/log files device was changed using each default scheduler values during the same workload run between the two schedulers.
For this specific workload, deadline performs better with regard to the transactions per second (business transaction) metric.
Comparison of Linux deadline, and cfq schedulers (TPC-C and the numbers displayed cannot be compared to any published TPC-C result.):
Linux IO Scheduler Tunable Parameters:
As you can see there are many options to tune Linux with regard to IO processing in the kernel and your mileage may vary depending on the workload. In Redhat Enterprise Linux 7.6, deadline is the default IO scheduler; in prior versions, it was cfq.
Now with the new release of Redhat Enterprise Linux 8.0, mq-deadline (Multi-Queue Block I/O Queueing Mechanism) is the default IO scheduler, which I will write about in the future.
Bottom line: if workload performance is a concern, check the default IO scheduler settings and test different schedulers to ensure optimization for your specific workload.
