How to make a xfs 4 disk raid 0 partition for ec2 mysql for maximum disk io performance

Use this technique at your own risk. You need to be hot on snapshots, backups and restores to ensure you dont lose everything.

• Raid 0 offers no fault tolerance
• Disk reads and writes can be potentially 400% faster
• Loss of one disk will destroy the entire dataset

Although moving the binary log to its own ebs volume means at least you can recover the transactions from another redundant volume which could also be mirrored and should be snapshotted as part of the process.

Download and install amazon api tools http://developer.amazonwebservices.com/connect/entry.jspa?externalID=351

create 4 ebs volumes and mount them

install mdadm and xfsprogs

mysql performance blog says to set (innodb pdf) chunk size from 256k - 1m
Trying with 256 - each chunk dictates how much is read or written serially from each disk, so a 1 M file would be read fastest as it would read 256k in parralell from each disk.

Create raid array from new disks

create xfs filesystem on the new raid array

create new mount point and mount the raid array

you now have a working raid array in /mnt/mysql_raid

however, there is a first write penalty on EC2 EBS volumes that we need to deal with. (arguments against this are that you will pay for a whole snapshot on S3. compared to the disk speed differences i wouldnt worry)

This will fill up the new 40gb raid partition with 41 bursts of 1000M of nothing.

how to move your innodb files from an existing partition to a new md0 raid partition

On a test machine
Down your current mysql server

copy the files to the new location

Edit the apparmor profile for mysqld

add these two lines as indicated. where the first two lines are the default (and should be there when you open the file, lines 3 and 4 are the new location of the innodb file and have the same permissions as was set for the default location.

Caveats - when an ebs disk is upgraded/replaced, might need to rerun mdadm stuff because of changed disk label

Questions. how does this survive a reboot?
#cd /etc/mdadm
#mdadm --examine --scan --config=/etc/mdadm/mdadm.conf >> /etc/mdadm/mdadm.conf

and add an entry to /etc/fstab

how to test
#!/bin/bash
dd if=/dev/zero of=0.raw bs=1 count=1 seek=1G #create a 1G sparse file
dd if=/dev/zero of=1.raw bs=1 count=1 seek=1G #create a 1G sparse file
dd if=/dev/zero of=2.raw bs=1 count=1 seek=1G #create a 1G sparse file
dd if=/dev/zero of=3.raw bs=1 count=1 seek=1G #create a 1G sparse file

losetup /dev/loop0 0.raw
losetup /dev/loop1 1.raw
losetup /dev/loop2 2.raw
losetup /dev/loop3 3.raw

mdadm -C /dev/md0 --chunk=256 -n 4 -l 0 /dev/loop0 /dev/loop1 /dev/loop2 /dev/loop3
mkfs.xfs /dev/md0
mkdir /mnt/mysql_raid
mount /dev/md0 /mnt/mysql_raid/
echo '/dev/md0 /mnt/mdadm_raid xfs noatime,nodiratime,grpid,suid,dev,exec 0 0' >> /etc/fstab

mdadm --examine --scan --config=mdadm.conf >> /etc/mdadm/mdadm.conf

If you need to reassemble an array, try this