There are about a gazillion FAQs and HOWTOs out there that talk about XFS configuration, RAID IO alignment, and mount point options. I wanted to try to put some of that information together in a condensed and simplified format that will work for the majority of use cases. This is not meant to cover every single tuning option, but rather to cover the important bases in a simple and easy to understand way.
Let’s say you have a server with standard hardware RAID setup running conventional HDDs.
For the sake of simplicity you create one single RAID logical volume that covers all your available drives. This is the easiest setup to configure and maintain and is the best choice for operability in the majority of normal configurations. Are there ways to squeeze more performance out of a server by dividing the logical volumes: perhaps, but it requires a lot of fiddling and custom tuning to accomplish.
There are plenty of other posts out there that discuss RAID minutia. Make sure you cover the following:
You want to run only MySQL on this box, and you want to ensure your MySQL datadir is separated from the OS in case you ever want to upgrade the OS, but otherwise keep it simple. My suggestion? Plan on allocating partitions roughly as follows, based on your available drive space and keeping in mind future growth.
Are there alternatives? Yes. Can you have separate partitions for Innodb log volumes, etc.? Sure. Is it work doing much more than this most of the time? I’d argue not until you’re sure you are I/O bound and need to squeeze every last ounce of performance from the box. Fiddling with how to allocate drives and drive space from partition to partition is a lot of operational work which should be spent only when needed.
Disk /dev/sda: 438.5 GB, 438489317376 bytes
255 heads, 63 sectors/track, 53309 cylinders, total 856424448 sectors
Units = sectors of 1 * 512 = 512 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00051fe9
Device Boot Start End Blocks Id System
/dev/sda1 2048 7813119 3905536 82 Linux swap / Solaris
Partition 1 does not end on cylinder boundary.
/dev/sda2 * 7813120 27344895 9765888 83 Linux
/dev/sda3 27344896 856422399 414538752 83 Linux
XFS requires a little massaging (or a lot). For a standard server, it’s fairly simple. We need to know two things:
# mkfs.xfs -d su=64k,sw=4 /dev/sda3
meta-data=/dev/sda3 isize=256 agcount=4, agsize=25908656 blks
= sectsz=512 attr=2
data = bsize=4096 blocks=103634624, imaxpct=25
= sunit=16 swidth=64 blks
naming =version 2 bsize=4096 ascii-ci=0
log =internal log bsize=4096 blocks=50608, version=2
= sectsz=512 sunit=16 blks, lazy-count=1
realtime =none extsz=4096 blocks=0, rtextents=0
The XFS FAQ is a good place to check out for more details.
Again, there are many options to use here, but let’s use some simple ones:
/var/lib/mysql xfs nobarrier,noatime,nodiratime
This is a commonly missed step related to getting the IO setup properly. The best choices here are between ‘deadline’ and ‘noop’. Deadline is an active scheduler, and noop simply means IO will be handled without rescheduling. Which is best is workload dependent, but in the simple case you would be well-served by either. Two steps here:
echo noop > /sys/block/sda/queue/scheduler # update the scheduler in realtime
And to make it permanent, add ‘elevator=<your choice>’ in your grub.conf at the end of the kernel line:
kernel /boot/vmlinuz-2.6.18-53.el5 ro root=LABEL=/ noapic acpi=off rhgb quiet notsc <strong>elevator=noop</strong>
This is a complicated topic, and I’ve tried to temper the complexity with what will provide the most benefit. What has made most improvement for you that could be added without much complexity?