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
[[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:
[root[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

Allowing multiple developers to read/write to website via SFTP or FTP

This article simply exists to serve as a visual reference when I’m explaining permissions to others. If you are looking to apply the concepts in this article on a live site, make sure you create a backup of the permissions and ownerships before proceeding as this article could break a pre-existing site!

This is one of those things where there is more than one way to go about it. The goal here is to allow multiple users the ability to work with the site via FTP/SFTP using basic permissions and groups.

First, create the shared group. In this case, as my domain is going to be example.com, I will call it exampleadmins:

[[email protected] ~]# groupadd exampleadmins

Now add the preexisting users to the group

[[email protected] ~]# usermod -aG exampleadmins user01
[[email protected] ~]# usermod -aG exampleadmins user02

Now change the group ownership recursively on the website directory:

[[email protected] ~]# chgrp -R exampleadmins /var/www/vhosts/example.com

Since we want users in the exampleadmins group to have write access, set the group write permissions on the website directory by:

[[email protected] ~]# chmod -R g+w /var/www/vhosts/example.com

To ensure that any new files or directory inherit the group ownership, use the SetGID bit on the directory recursively:

[[email protected] ~]# find /var/www/vhosts/example.com -type d -exec chmod g+s "{}" \;

To ensure that files or directories the user creates or uploads are group writable by default, you need to adjust the default umask for the FTP and SFTP server. For vsftpd which is generally the default FTP server, change the default umask from 022 to 002 by:

[[email protected] ~]# vim /etc/vsftpd.conf
...
local_umask = 002
...
[[email protected] ~]# service vsftpd restart

When using SFTP, update the sftp subsystem within /etc/ssh/sshd_config to set a umask of 0002 by:

[[email protected] ~]# vim /etc/ssh/sshd_config
...
Subsystem       sftp    /usr/libexec/openssh/sftp-server -u 0002
...
# Append to bottom of file:
Match Group exampleadmins
   ForceCommand internal-sftp -u 0002
[[email protected] ~]# service sshd restart

Now whenever you need to add additional users, simply create the user with a membership to exampleadmins

[[email protected] ~]# useradd -s /sbin/nologin -d /var/www/vhosts/example.com -G exampleadmins user03

And if the user already exists, simply run:

[[email protected] ~]# usermod -aG exampleadmins user03

Convert MyISAM tables to InnoDB in MySQL

Converting MyISAM tables over to InnoDB comes with several benefits such as row level locking, being able to commit and roll back transactions and using tractional logs for autorecovery.

While converting MyISAM over to InnoDB is usually straight forward, you must use caution:

- Always create a backup of the database before making any changes!
- Confirm you are running MySQL 5.6.4 or above as that was when full-text indexing was introduced to InnoDB.

You can check to see what tables are using MyISAM in your database by running the following:

[[email protected] ~]# mysql
mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS FROM information_schema.TABLES WHERE  ENGINE = 'MyISAM' and TABLE_ROWS
+--------------+-----------------------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME            | ENGINE | TABLE_ROWS |
+--------------+-----------------------+--------+------------+
| mysql        | db                    | MyISAM |          3 |
| mysql        | help_category         | MyISAM |         39 |
| mysql        | help_keyword          | MyISAM |        464 |
| mysql        | help_relation         | MyISAM |       1028 |
| mysql        | help_topic            | MyISAM |        508 |
| mysql        | proxies_priv          | MyISAM |          2 |
| mysql        | user                  | MyISAM |          5 |
| example01    | wp_commentmeta        | MyISAM |         15 |
| example01    | wp_comments           | MyISAM |         72 |
| example01    | wp_links              | MyISAM |         11 |
| example01    | wp_options            | MyISAM |        224 |
| example01    | wp_postmeta           | MyISAM |        260 |
| example01    | wp_posts              | MyISAM |        277 |
| example01    | wp_term_relationships | MyISAM |        178 |
| example01    | wp_term_taxonomy      | MyISAM |         62 |
| example01    | wp_terms              | MyISAM |         62 |
| example01    | wp_usermeta           | MyISAM |         22 |
| example01    | wp_users              | MyISAM |          1 |
+--------------+-----------------------+--------+------------+

Alternatively, if you wanted to see MyISAM tables that have more than 5,000 rows, run:

mysql> SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS FROM information_schema.TABLES WHERE  ENGINE = 'myISAM' and TABLE_ROWS > 5000;
+--------------+---------------------+--------+------------+
| TABLE_SCHEMA | TABLE_NAME          | ENGINE | TABLE_ROWS |
+--------------+---------------------+--------+------------+
| example02    | employees           | MyISAM |      80257 |
| example02    | templates           | MyISAM |     155341 |
| example02    | zipcodes            | MyISAM |      33178 |
| example02    | providers           | MyISAM |      34833 |
| example02    | portal              | MyISAM |      25184 |
+--------------+---------------------+--------+------------+

Converting the tables is pretty simple, but keep in mind that this will lock the table while it is running. Make sure you backup your database BEFORE converting any tables! Things can go wrong at the worst times, so be sure to have a solid roll back plan before proceeding!

Convert the desired table from MyISAM to InnoDB by running:

[[email protected] ~]# mysql
mysql> use example02;
mysql> ALTER TABLE providers ENGINE=InnoDB;