Making Logical Volumes on EC2

The MySQL backups using LVM Snapshots post is now by far the most popular post.


Here is shell script which helps get the Logical Volumes (LV) setup in the first place.

### START OF SCRIPT ###

#!/bin/sh
# Name: make_mnt_LV.sh
# Script to make EC2 /mnt into a LVM volume

modprobe dm-snapshot

umount /mnt
pvcreate /dev/sda2
vgcreate vg /dev/sda2
lvcreate -L30720M -n myvmdisk1 vg
mkfs -t ext3 /dev/vg/myvmdisk1
mkdir -p /data1/mysql/data
mount /dev/vg/myvmdisk1 /data1/mysql

lvcreate -L30720M -n myvmdisk2 vg
mkfs -t ext3 /dev/vg/myvmdisk2
mkdir -p /data2/mysql/data
mount /dev/vg/myvmdisk2 /data2/mysql

lvcreate -L30720M -n myvmdisk3 vg
mkfs -t ext3 /dev/vg/myvmdisk3
mkdir -p /backup/mysql/data
mount /dev/vg/myvmdisk3 /backup/mysql

lvdisplay

mkdir -p /data1/mysql/data
mkdir -p /data2/mysql/data

chown -R mysql:mysql /data1/mysql
chown -R mysql:mysql /data2/mysql
chown -R mysql:mysql /backup/mysql

### END OF SCRIPT ###


Sample Output

chmod 750 make_mnt_LV.sh

df -h

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             4.0G  1.2G  2.7G  31% /
/dev/sda2             147G  189M  140G   1% /mnt

lvdisplay

 No volume groups found

./make_mnt_LV.sh


 Physical volume "/dev/sda2" successfully created
 Volume group "vg" successfully created
 Logical volume "myvmdisk1" created
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3932160 inodes, 7864320 blocks
393216 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
240 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
       32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
       4096000

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 32 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
 Logical volume "myvmdisk2" created
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3932160 inodes, 7864320 blocks
393216 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
240 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
       32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
       4096000

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 28 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
 Logical volume "myvmdisk3" created
mke2fs 1.35 (28-Feb-2004)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
3932160 inodes, 7864320 blocks
393216 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=8388608
240 block groups
32768 blocks per group, 32768 fragments per group
16384 inodes per group
Superblock backups stored on blocks:
       32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
       4096000

Writing inode tables: done
Creating journal (8192 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 29 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
 --- Logical volume ---
 LV Name                /dev/vg/myvmdisk1
 VG Name                vg
 LV UUID                h10Ev4-EVYY-CoGl-2AHS-sjqe-BNHE-297EyT
 LV Write Access        read/write
 LV Status              available
 # open                 1
 LV Size                30.00 GB
 Current LE             7680
 Segments               1
 Allocation             inherit
 Read ahead sectors     0
 Block device           253:0

 --- Logical volume ---
 LV Name                /dev/vg/myvmdisk2
 VG Name                vg
 LV UUID                UM6GmA-s0vr-Mt6u-kh8m-wvMr-TMtD-rb34kk
 LV Write Access        read/write
 LV Status              available
 # open                 1
 LV Size                30.00 GB
 Current LE             7680
 Segments               1
 Allocation             inherit
 Read ahead sectors     0
 Block device           253:1

 --- Logical volume ---
 LV Name                /dev/vg/myvmdisk3
 VG Name                vg
 LV UUID                nCKCDh-MgIf-92il-FQHu-XduC-yJsO-koIGgM
 LV Write Access        read/write
 LV Status              available
 # open                 1
 LV Size                30.00 GB
 Current LE             7680
 Segments               1
 Allocation             inherit
 Read ahead sectors     0
 Block device           253:2

df -h

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             4.0G  1.2G  2.7G  31% /
/dev/mapper/vg-myvmdisk1
                      30G   77M   28G   1% /data1/mysql
/dev/mapper/vg-myvmdisk2
                      30G   77M   28G   1% /data2/mysql
/dev/mapper/vg-myvmdisk3
                      30G   77M   28G   1% /backup/mysql

Modifying your MySQL my.cnf to use the new data directories available.

http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html


What do we have already?

grep "data" /etc/my.cnf

# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
#innodb_data_home_dir = /usr/local/mysql/data/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /usr/local/mysql/data/
#innodb_log_arch_dir = /usr/local/mysql/data/

Make a backup and edit using your favourite text editor:

cp /etc/my.cnf /etc/my.cnf.old
vi /etc/my.cnf

Check again:

This setup will place one INNODB datafile on each of the newly created Logical Volumes.
An alternative would be to place the logfiles on one of them instead.

grep "data" /etc/my.cnf

# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/data) or
datadir=/data1/mysql/data
innodb_data_home_dir = /data1/mysql/data
innodb_data_file_path = /data1/mysql/data/ibdata1:10M:autoextend;/data2/mysql/data/ibdata2:10M:autoextend
innodb_log_group_home_dir = /data1/mysql/data
innodb_log_arch_dir = /data1/mysql/data

Sysbench vs MySQL on EC2

I was reading how Morgan was slightly disappointed at the results of his sysbench test. He ran sysbench on his laptop and then on EC2 and got a large difference in the results. Thorsten from RightScale also ran some sysbench tests.

I was keen to either replicate or disprove their results. Given their parameters I replicated their results.
Rather than stop there, I decided to see what was main determining factor.

I discovered that the number of threads made little difference to the final result. The size of the table made the largest difference. However when I continued to decrease the size, the results became reverted to the same as the original.

How to install sysbench on EC2:

1. yum -y install sysstat gcc
2. cd /mnt
   
3. wget http://optusnet.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.8.tar.gz
4. tar -xzvf sysbench-0.4.8.tar.gz
5. cd sysbench-0.4.8
6. ./configure
7. make
8. make install

Running sysbench against MySQL on EC2:

1. install MySQL 5.1
2. cp /usr/local/mysql-5.1.20-beta-linux-i686-glibc23/support-files/my-large.cnf /etc/my.cnf
3. service mysql.server start
4. export LD_LIBRARY_PATH=/usr/local/mysql-5.1.20-beta-linux-i686-glibc23/lib/
5. sysbench help
6. mysqladmin -u root -pyourpasswordhere create database sbtest
   
7. sysbench --test=oltp --mysql-table-engine=myisam --oltp-table-size=1000000 --mysql-user=root --mysql-password=yourpasswordhere prepare
8. sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --mysql-user=root --mysql-password=yourpassword --oltp-read-only run
9. sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=1000000 --mysql-user=root --mysql-password=yourpassword cleanup
   

Results:

1. Table-size=1000000, Threads= {2,4,6,8,10,12,16}: Avg transactions: 100000 (126.06 per sec.)
2. Table-size=100000, Threads= {2,4,8,16}: Avg transactions: 100000 (411.74 per sec.)
3. Table-size=50000, Threads= {4,8,16}: Avg transactions: 100000 (125.59 per sec.)

Comments:

1. Given that the table size seems to have a big impact on the transaction performance, it would suggest that tuning the size of cache (key and sort) should make a difference. The jury is still out on that.
2. Some of the sql running appeared in the slow-query.log at the default settings. The main culprit is as always, a range scan on a index followed by a filesort.
   

Coming soon:

1. Repeat runs using difference storage engines.
2. Further investigation on changing various memory and sort system variables.
3. Repeating the tests for various table sizes.

The whole aim of this series of articles on benchmarks is the determine the optimal design for databases in general on EC2. Once we have a reports from varied sources of benchmarking tools, we can get closer to knowing the appropriate size and design of databases using EC2.

Have Fun

Paul