By: Daniel Farina | Last Updated: 2017-06-09 | Comments | SQL Server on Linux
One of the factors that determines the performance of a database is disk latency. On Linux we have the possibility to select the kernel scheduling algorithm for disk I/O and in this tip I will explain the basics of the available I/O scheduling algorithms, so you can select which one to use.
When an application requires disk access, the application requests the access to the operating system. Computers now run many tasks simultaneously, so it is intuitive that something must manage the processes when they request I/O access because the system cannot read and/or write different disk sections at the same time. This is the I/O scheduler, which is part of the operating system kernel, but the I/O scheduler must follow some rules in order to decide which process can read or write to the hard disk at a certain time. Those rules are the I/O scheduler’s algorithm.
The Linux I/O Scheduler
Most of us as SQL Server DBA’s are new to Linux and UNIX like operating systems. With those operating systems and especially Linux, you can fine tune each and every configuration, in fact on Linux you can modify the whole kernel and recompile it. Linux has a community of developers across the globe that create, develop and test new software based on the community needs and that includes different I/O scheduling mechanisms.
Linux gives you the possibility to select amongst different scheduling algorithms which includes the noop, deadline and Completely Fair Queuing (CFQ) algorithms which I will introduce.
Something to note is that you should only change the I/O scheduler if you are having a disk bottleneck, otherwise it will be pointless. But if you need to change the I/O scheduler you should test which one is the best for your environment.
Linux noop I/O Scheduler
This scheduler works as a FIFO (First In First Out) queue. Basically it assumes that the underlying system cannot effectively decide which request needs more priority. The question is how an I/O scheduler can determine disk access priorities. If we think about it, the two things needed are: knowing the workload priority which in Linux is called the niceness, and the disk layout. So if a scheduler is unaware of the disk layout it cannot queue the I/O requests.
That is the case when working on a virtual environment, with an external storage device like a NAS or SAN, with RAID controllers and with flash drives. In those cases underlying storage device has its own algorithm to queue the I/O requests.
Linux Deadline I/O Scheduler
As its name suggest, this algorithm imposes a deadline on all I/O operations to prevent starvation of requests. This scheduler creates two queues, a read queue and a write queue. The scheduler has a predefined timeout and assigns a timestamp to each request that acts as a trigger when the timeout is expired.
This scheduler prioritizes reads because usually processes are stalled when waiting on read operations and writes can be queued and then flushed to disk without penalties. In other words, this algorithm aims to put a hard limit on latency.
Linux Complete Fairness Queuing (CFQ) Scheduler
This I/O scheduler has a queue for each process. It tries to balance each process I/O in a Round Robin fashion. Usually this is the preferred scheduler for desktop systems on which there are multiple GUI processes who needs a fast response each. To avoid confusion, I must add that this scheduler is meant to be fair, neither excellent nor poor amongst processes.
Changing the I/O Scheduler
On Linux almost every setting is handled by a text file and this is not the exception, you can see and change which scheduler is in use on your storage device by querying or changing the following file:
For example, if you want to see the scheduler in use on the first hard drive which usually is recognized with the device name hda, you can execute the following command to see the contents of the file in question:
On the next screen capture you will see the output of the previous command on my system. Notice that the output shows the three schedulers name but only one is between brackets. That is the scheduler in use.
Now let’s change the scheduler to noop. To do so, we just need to write the word noop into that file.
echo noop > /sys/block/sda/queue/scheduler
As you can see on the next image, after writing noop to that file, when we look at its content we see the same schedulers list as before with the difference that the noop scheduler is now between brackets.
But there is a drawback, if we change the I/O scheduler by modifying this file, when the server restart we will back to the previous scheduler because changes made to the /sys folder are not persistent. One way to make the changes persistent is by passing the parameter with the bootloader.
The most widely used bootloader on Linux is GRUB2. In order to modify the boot parameters of GRUB2 we have to edit the file /etc/default/grub and modify the parameter GRUB_CMDLINE_LINUX_DEFAULT by adding the following sentence.
elevator=[The Chosen Scheduler]
For example, if you want to select the noop scheduler, then you will have to change the GRUB_CMDLINE_LINUX_DEFAULT parameter as follows.
In this case the GRUB_CMDLINE_LINUX_DEFAULT parameter has “quiet” as its value and I added the elevator=noop clause.
The following is a screen capture of the nano text editor that I used to modify the /etc/default/grub file.
But there is an additional step; we need to run the next command as root to make the changes effective.
In order to show you the process I took the following screen capture and added some annotations.
On the first green box on the previous image I am querying the I/O scheduler that is in use by the disk sda (the first hard drive) which is the deadline scheduler. The blue box shows the commands executed to change the GRUB2 bootloader. The red box marks the sentence used to restart the system, and the last green box shows the result of querying the I/O scheduler that is in use by the disk sda after restarting the system which is the noop scheduler.
- In this tip you will get an insight about SQL Server on Linux: Getting Started with SQL Server on Linux.
- If you need help to find out CPU consumption on Linux, take a look at my previous tip on this series of articles about Linux administration for SQL Server DBA's: Linux Administration for SQL Server DBA’s: Checking CPU Usage.
- Take a look at my previous tip about Linux administration for SQL Server DBA’s: Linux Administration for SQL Server DBA’s: Checking Disk I/O .
- Check out my tip of the Linux Administration for SQL Server DBA’s tip series about Checking Network I/O.
- Read my previous tip about checking disk space on Linux: Linux Administration for SQL Server DBA’s: Checking Disk Space.
- Check out more SQL Server 2016 tips.
- Stay tuned for more SQL Server on Linux Tips.
Last Updated: 2017-06-09
About the author
View all my tips