IO Scheduler tuning

What is an I/O scheduler? The I/O scheduler is a kernel level tunable whose purpose is to optimize disk access requests. Traditionally this is critical for spinning disks as I/O requests can be grouped together to avoid “seeking”.

Different I/O schedulers have their pro’s and con’s, so choosing which one to use depends on the type of environment and workload. There is no one right I/O scheduler to use, it all simply ‘depends’. Benchmarking your application before and after the I/O scheduler change is usually your best indicator. The good news is, the I/O scheduler can be changed at run time and can be configured to persist after reboots.

The three common I/O schedulers are:
– noop
– deadline
– cfq

noop

The noop I/O scheduler is optimized for systems that don’t need an I/O scheduler such as VMware, AWS EC2, Google Cloud, Rackspace public cloud, etc. Since the hypervisor already controls the I/O scheduling, it doesn’t make sense for the VM to waste CPU cycles on it. The noop I/O scheduler simply works as a FIFO (First In First Out) queue.

You can update the I/O scheduler to noop by:

## CentOS 6

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq] 
[[email protected] ~]# echo 'noop' > /sys/block/sda/queue/scheduler
[ro[email protected] ~]# cat /sys/block/sda/queue/scheduler
[noop] anticipatory deadline cfq

# Change at boot time by appending 'elevator=noop' to end of kernel line:
[[email protected] ~]# vim /boot/grub/grub.conf
kernel /vmlinuz-2.6.9-67.EL ro root=/dev/vg0/lv0 elevator=noop


## CentOS 7

# Change at run time
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq] 
[[email protected] ~]# echo 'noop' > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
[noop] anticipatory deadline cfq

# Change at boot time by appending 'elevator=noop' end of the following line, then rebuild the grub config:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=rhel00/root rd.lvm.lv=rhel00/swap elevator=noop"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg


## Ubuntu 14.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
[[email protected] ~]# echo noop > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
[noop] deadline cfq

# Change at boot time by appending 'elevator=noop' end of the following line, then rebuild the grub config:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=noop"
...
[[email protected] ~]# grub-mkconfig -o /boot/grub/grub.cfg


## Ubuntu 16.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
[[email protected] ~]# echo noop > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
[noop] deadline cfq

# Change at boot time by appending 'elevator=noop' end of the following line, then rebuild the grub config:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=noop"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg

deadline

The deadline I/O scheduler is optimized by default for read heavy workloads like MySQL. It attempts to optimize I/O request by putting it in a read queue or write queue and assigning a timestamp to the request. For requests in the read queue, they have 500ms (by default) to execute before they are given the highest priority to run. Requests entering the write queue have 5000ms to execute before they are given the highest priority to run.

This deadline assigned to each I/O request is what makes deadline I/O scheduler optimal for read heavy workloads like MySQL.

You can update the I/O scheduler to deadline by:

## CentOS 6

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq] 
[[email protected] ~]# echo 'deadline' > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory [deadline] cfq

# Change at boot time by appending 'elevator=deadline' to end of kernel line apply the changes to grub:
[[email protected] ~]# vim /boot/grub/grub.conf
kernel /vmlinuz-2.6.9-67.EL ro root=/dev/vg0/lv0 elevator=deadline


## CentOS 7

# Change at run time
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq] 
[[email protected] ~]# echo 'deadline' > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory [deadline] cfq

# Change at boot time by appending 'elevator=deadline' end of the following line and apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=rhel00/root rd.lvm.lv=rhel00/swap elevator=deadline"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg


# Ubuntu 14.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop deadline [cfq]
[[email protected] ~]# echo deadline > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq

# Change at boot time by appending 'elevator=deadline' end of the following line apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=deadline"
...
[[email protected] ~]# grub-mkconfig -o /boot/grub/grub.cfg


# Ubuntu 16.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop deadline [cfq]
[[email protected] ~]# echo deadline > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq

# Change at boot time by appending 'elevator=deadline' end of the following line apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=deadline"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg

cfg

The cfg I/O scheduler is probably best geared towards things running GUIs (like a desktop) where each process needs a fast response. The goal of the cfq I/O scheduler (Complete Fairness Queueing) is to give a fair allocation of disk I/O bandwidth for all the processes which requests an I/O operation.

You can update the I/O scheduler to cfq by:

## CentOS 6

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory [deadline] cfq 
[[email protected] ~]# echo 'cfq' > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq]

# Change at boot time by appending 'elevator=cfq' to end of kernel line apply the changes to grub:
[[email protected] ~]# vim /boot/grub/grub.conf
kernel /vmlinuz-2.6.9-67.EL ro root=/dev/vg0/lv0 elevator=cfq


## CentOS 7

# Change at run time
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory [deadline] cfq 
[[email protected] ~]# echo 'cfg' > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq]

# Change at boot time by appending 'elevator=cfq' end of the following line and apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=rhel00/root rd.lvm.lv=rhel00/swap elevator=cfq"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg


# Ubuntu 14.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
[[email protected] ~]# echo cfq > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop deadline [cfq]

# Change at boot time by appending 'elevator=cfq' end of the following line apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=cfq"
...
[[email protected] ~]# grub-mkconfig -o /boot/grub/grub.cfg


# Ubuntu 16.04

# Change at runtime
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop [deadline] cfq
[[email protected] ~]# echo cfq > /sys/block/sda/queue/scheduler
[[email protected] ~]# cat /sys/block/sda/queue/scheduler
noop deadline [cfq]

# Change at boot time by appending 'elevator=cfq' end of the following line apply the changes to grub:
[[email protected] ~]# vim /etc/default/grub
...
GRUB_CMDLINE_LINUX="elevator=cfq"
...
[[email protected] ~]# grub2-mkconfig -o /boot/grub2/grub.cfg

As with any performance tuning recommendations, there is never a one size fits all solution! Always benchmark your application to establish a baseline before you make the change. After the performance changes have been made, run the same benchmark and compare the results to ensure that they had the desired outcomes.

Disabling Transparent Huge Pages in Linux

Transparent Huge Pages (THP) is a Linux memory management system that reduces the overhead of Translation Lookaside Buffer (TLB) lookups on machines with large amounts of memory by using larger memory pages.

However, database workloads often perform poorly with THP, because they tend to have sparse rather than contiguous memory access patterns. The overall recommendation for MySQL, MongoDB, Oracle, etc is to disable THP on Linux machines to ensure best performance.

You can check to see if THP is enabled or not by running:

[[email protected] ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never
[[email protected] ~]# cat /sys/kernel/mm/transparent_hugepage/defrag
[always] madvise never

If the result shows [never], then THP is disabled. However if the result shows [always], then THP is enabled.

You can disable THP at runtime on CentOS 6/7 and Ubuntu 14.04/16.04 by running:

[[email protected] ~]# echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled
[[email protected] ~]# echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag

However once the system reboots, it will go back to its default value again. To make the setting persistent on CentOS 7 and Ubuntu 16.04, you can disable THP on system startup by making a systemd unit file:

# CentOS 7 / Ubuntu 16.04:
[[email protected] ~]# vim /etc/systemd/system/disable-thp.service
[Unit]
Description=Disable Transparent Huge Pages (THP)

[Service]
Type=simple
ExecStart=/bin/sh -c "echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled && echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag"

[Install]
WantedBy=multi-user.target

[[email protected] ~]# systemctl daemon-reload
[[email protected] ~]# systemctl start disable-thp
[[email protected] ~]# systemctl enable disable-thp

On CentOS 6 and Ubuntu 14.04, you can disable THP on system startup by adding the following to /etc/rc.local. If this is on Ubuntu 14.04, make sure its added before the ‘exit 0’:

# CentOS 6 / Ubuntu 14.04
[[email protected] ~]# vim /etc/rc.local
...
if test -f /sys/kernel/mm/transparent_hugepage/enabled; then
   echo never > /sys/kernel/mm/transparent_hugepage/enabled
fi
if test -f /sys/kernel/mm/transparent_hugepage/defrag; then
   echo never > /sys/kernel/mm/transparent_hugepage/defrag
fi
...

MySQL 5.1 to MySQL 5.7 upgrade on CentOS

Upgrading MySQL is pretty straight forward, however there are some catches. Per MySQL’s official documentation, you must upgrade from 5.1 to 5.5, then upgrade from 5.5 to 5.6, and finally again from 5.6 to 5.7. You cannot upgrade from 5.1 directly to 5.7.

This guide will outline how to upgrade MySQL 5.1 to MySQL 5.7 in sequence and will assume you are using the IUS repositories for MySQL. If the IUS repositories are not already setup, you can install them by:

# CentOS 6
[[email protected] ~]# yum install epel-release
[[email protected] ~]# rpm -ivh https://dl.iuscommunity.org/pub/ius/stable/CentOS/6/x86_64/ius-release-1.0-15.ius.centos6.noarch.rpm
# CentOS 7
[[email protected] ~]# yum install epel-release
[[email protected] ~]# rpm -ivh https://dl.iuscommunity.org/pub/ius/stable/CentOS/7/x86_64/ius-release-1.0-15.ius.centos7.noarch.rpm

Upgrade from MySQL 5.1 to MySQL 5.5

Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:

[[email protected] ~]# mysql -S mysqldump
[[email protected] ~]# mkdir -p /root/mysqlupgrade/mysql51
[[email protected] ~]# mysql -e 'show databases;' > /root/mysqlupgrade/mysql51/mysql-5.1.databases
[[email protected] ~]# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql51/mysql-5.1.grants
[[email protected] ~]# cp /etc/my.cnf /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig
[[email protected] ~]# yum install xz
[[email protected] ~]# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql51/mysql-5.1.dump.sql.xz

Now stop MySQL and upgrade it to MySQL 5.5:

[[email protected] ~]# service mysqld stop
[[email protected] ~]# yum --disableexcludes=all shell
remove mysql mysql-server mysql-libs
install mysql55 mysql55-server mysql55-libs mysqlclient16
ts solve
ts run
exit

Generate a version of the my.cnf that is valid for MySQL 5.5. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:

[[email protected] ~]# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake
[[email protected] ~]# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig --target 5.5 > /root/mysqlupgrade/mysql-5.5.cnf
[[email protected] ~]# mv /etc/my.cnf /etc/mysql-5.5.cnf.orig
[[email protected] ~]# mv -f /root/mysqlupgrade/mysql-5.5.cnf /etc/my.cnf

Start MySQL without the grant tables to verify it is running MySQL 5.5 and all the databases loaded:

[[email protected] ~]# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf
[[email protected] ~]# service mysqld start
[[email protected] ~]# mysql -sse "select @@version"
[[email protected] ~]# mysql -e "show databases;" > /root/mysqlupgrade/mysql51/mysql-5.5.databases
[[email protected] ~]# diff -U0 /root/mysqlupgrade/mysql51/mysql-5.1.databases /root/mysqlupgrade/mysql51/mysql-5.5.databases

Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:

[[email protected] ~]# mysql_upgrade
[[email protected] ~]# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf
[[email protected] ~]# service mysqld restart

Finally, confirm MySQL is running version 5.5:

[[email protected] ~]# mysqladmin version

Upgrade from MySQL 5.5 to MySQL 5.6

Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:

[[email protected] ~]# mysql -S mysqldump
[[email protected] ~]# mkdir -p /root/mysqlupgrade/mysql55
[[email protected] ~]# mysql -e 'show databases;' > /root/mysqlupgrade/mysql55/mysql-5.5.databases
[[email protected] ~]# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql55/mysql-5.5.grants
[[email protected] ~]# cp /etc/my.cnf /root/mysqlupgrade/mysql55/mysql-5.5.cnf.orig
[[email protected] ~]# yum install xz
[[email protected] ~]# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql55/mysql-5.5.dump.sql.xz

Now stop MySQL and upgrade it to MySQL 5.6:

[[email protected] ~]# service mysqld stop
[[email protected] ~]# yum --disableexcludes=all shell
remove mysql55 mysql55-server mysql55-libs
install mysql56u mysql56u-server mysql56u-libs mysqlclient16
ts solve
ts run
exit

Generate a version of the my.cnf that is valid for MySQL 5.6. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:

[[email protected] ~]# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake
[[email protected] ~]# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql55/mysql-5.5.cnf.orig --target 5.6 > /root/mysqlupgrade/mysql-5.6.cnf
[[email protected] ~]# mv -f /root/mysqlupgrade/mysql-5.6.cnf /etc/my.cnf

Start MySQL without the grant tables to verify it is running MySQL 5.6 and all the databases loaded:

[[email protected] ~]# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf
[[email protected] ~]# service mysqld start
[[email protected] ~]# mysql -sse "select @@version"
[[email protected] ~]# mysql -e "show databases;" > /root/mysqlupgrade/mysql55/mysql-5.6.databases
[[email protected] ~]# diff -U0 /root/mysqlupgrade/mysql55/mysql-5.5.databases /root/mysqlupgrade/mysql55/mysql-5.6.databases

Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:

[[email protected] ~]# mysql_upgrade
[[email protected] ~]# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf
[[email protected] ~]# service mysqld restart

Finally, confirm MySQL is running version 5.6:

[[email protected] ~]# mysqladmin version

Upgrade from MySQL 5.6 to MySQL 5.7

Create a restore point. This will be backing up the databases, the my.cnf, as well as a user and database listing. This can be done by:

[[email protected] ~]# mysql -S mysqldump
[[email protected] ~]# mkdir -p /root/mysqlupgrade/mysql56
[[email protected] ~]# mysql -e 'show databases;' > /root/mysqlupgrade/mysql56/mysql-5.6.databases
[[email protected] ~]# mysql --silent --skip-column-names --execute "select concat('\'',User,'\'@\'',Host,'\'') as User from mysql.user;" | sort | while read u; do echo "-- $u"; mysql --silent --skip-column-names --execute "show grants for $u" | sed 's/$/;/'; done > /root/mysqlupgrade/mysql56/mysql-5.6.grants
[[email protected] ~]# cp /etc/my.cnf /root/mysqlupgrade/mysql56/mysql-5.6.cnf.orig
[[email protected] ~]# yum install xz
[[email protected] ~]# mysqldump --routines --all-databases | xz > /root/mysqlupgrade/mysql56/mysql-5.6.dump.sql.xz

Now stop MySQL and upgrade to MySQL 5.7:

[[email protected] ~]# service mysqld stop
[[email protected] ~]# yum --disableexcludes=all shell
remove mysql56u mysql56u-server mysql56u-libs mysql56u-common
install mysql57u mysql57u-server mysql57u-libs mysqlclient16
ts solve
ts run
exit

Generate a version of the my.cnf that is valid for MySQL 5.7. Some options get deprecated or changed between versions, so it is important that this is not skipped. To help with this, we’ll be using dbsake as shown below:

[[email protected] ~]# wget -O /root/mysqlupgrade/dbsake http://get.dbsake.net; chmod u+x /root/mysqlupgrade/dbsake
[[email protected] ~]# /root/mysqlupgrade/dbsake upgrade-mycnf --config /root/mysqlupgrade/mysql56/mysql-5.6.cnf.orig --target 5.7 > /root/mysqlupgrade/mysql-5.7.cnf
[[email protected] ~]# mv -f /root/mysqlupgrade/mysql-5.7.cnf /etc/my.cnf

Start MySQL without the grant tables to verify it is running MySQL 5.6 and all the databases loaded:

[[email protected] ~]# sed -i 's/\[mysqld\]/[mysqld]\nskip-grant-tables\nskip-networking/' /etc/my.cnf
[[email protected] ~]# service mysqld start
[[email protected] ~]# mysql -sse "select @@version"
[[email protected] ~]# mysql -e "show databases;" > /root/mysqlupgrade/mysql56/mysql-5.7.databases
[[email protected] ~]# diff -U0 /root/mysqlupgrade/mysql56/mysql-5.6.databases /root/mysqlupgrade/mysql56/mysql-5.7.databases

If MySQL fails to start, check the logs as it may be due to MySQL looking for a /var/lib/mysqltmp directory. You can verify and correct it by:

[[email protected] ~]# cat /var/log/mysqld.log |grep ERROR
[ERROR] InnoDB: Unable to create temporary file; errno: 2

[[email protected] ~]# cat /etc/my.cnf |grep tmpdir
tmpdir                          = /var/lib/mysqltmp

[[email protected] ~]# mkdir /var/lib/mysqltmp
[[email protected] ~]# chown mysql:mysql /var/lib/mysqltmp
[[email protected] ~]# service mysqld start

Now update the database schema. If mysql_upgrade says that a table needs to repaired, repair it with mysqlcheck -A before moving on:

[[email protected] ~]# mysql_upgrade
[[email protected] ~]# sed -i '/\(skip-grant-tables\|skip-networking\)/d' /etc/my.cnf
[[email protected] ~]# service mysqld restart

Finally, confirm MySQL is running version 5.7:

[[email protected] ~]# mysqladmin version

Rollback plan

What happens if you need to roll back? If you followed the instructions in this article to create the backups, restoration is simple. Just keep in mind that you also need to restore the databases themselves from the original backups. So anything that changed in the database since the upgrade will be lost. If this is not acceptable, do not use these rollback instructions!

It is going to be assumed that you are going to roll all the way back from MySQL 5.7 to MySQL 5.1. Simply adjust the instructions below accordingly if you are going to roll back to a different version.

Stop MySQL and rollback to MySQL 5.1 by:

[[email protected] ~]# service mysqld stop
[[email protected] ~]# yum --disableexcludes=all shell
remove mysql*
install mysql mysql-server mysql-libs mysqlclient16
ts solve
ts run
exit

Restore the original /etc/my.cnf by:

[[email protected] ~]# cp /root/mysqlupgrade/mysql51/mysql-5.1.cnf.orig /etc/my.cnf

Startup MySQL and restore the 5.1-formatted databases:

[[email protected] ~]# mv /var/lib/mysql /var/lib/mysql.bak
[[email protected] ~]# mkdir /var/lib/mysql
[[email protected] ~]# chown mysql:mysql /var/lib/mysql
[[email protected] ~]# service mysqld start
[[email protected] ~]# mysql_secure_installation
[[email protected] ~]# unxz -c /root/mysqlupgrade/mysql51/mysql-5.1.dump.sql.xz | mysql
[[email protected] ~]# service mysqld restart
[[email protected] ~]# mysqladmin version