Linux Administration for SQL Server DBA’s: Changing the I/O Scheduler

By:   |   Updated: 2017-06-09   |   Comments   |   Related: > SQL Server on Linux


Problem

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.

Solution

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: 

/sys/block/[device]/queue/scheduler

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: 

cat /sys/block/sda/queue/scheduler

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.

Query I/O Scheduler - Description: This is the output of querying the I/O scheduler on the first drive.

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.

noop scheduler in 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. 

GRUB_CMDLINE_LINUX_DEFAULT="quiet elevator=noop"

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.

nano_capture - Description: This image shows how to modify the /etc/default/grub file to add the elevator clause.

But there is an additional step; we need to run the next command as root to make the changes effective. 

update-grub2

In order to show you the process I took the following screen capture and added some annotations.

GRUB2 - Description: This screen capture shows the whole process of changing the I/O scheduler on the GRUB2 bootloader.

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.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2017-06-09

Comments For This Article

















get free sql tips
agree to terms