In the next following posts we’ll deploy a multi-master synchronous MySQL Galera Cluster with Amazon’s VPC service. We’re going to create a public facing subnet for app/web servers and a private subnet for our database cluster.
The deployment will look similar to the below diagram.
Amazon’s VPC provides a secure environment where you can chose to isolate parts of your servers by having complete control of how to deploy your virtual networking infrastructure much like your own datacenter.
The steps that we’ll go through are as following:
- Create a VPC with Public and Private subnets
- Define Security Groups (add rules later)
- Launch one instance for ClusterControl
- Launch three EBS optimized instances for the Galera/database nodes
- Format and mount an EBS volume (or RAID set) for each Galera nodes
- Create a Galera Configuration with Severalnines Galera Configurator
- Deploy and bootstrap Galera Cluster
- Add an internal load balancer
- Add a MySQL user for the internal load balancer
- Add web server instances
- Add an external load balancer
- Test the VPC database cluster setup
At the end we have the following instances available on the public subnet (Note your IP addresses would be different):
- 1 Elastic External Load Balancer, Elastic IP 220.127.116.11
- 2 Web servers: IP 10.0.0.28, Elastic IP 18.104.22.168 and IP 10.0.0.38, Elastic IP 22.214.171.124
- 1 ClusterControl server: IP 10.0.0.53, Elastic IP 126.96.36.199
and on the private subnet:
- 1 Elastic Internal Load Balancer, IP 10.0.1.17
- Galera Node 1, IP 10.0.1.13
- Galera Node 2, IP 10.0.1.16
- Galera Node 3, IP 10.0.1.26
In this example going forward we only deploy one private subnet however if you require a more fault tolerant setup you can for example create two private subnets for the database cluster one in each Availability Zone (AZ) which can protect you from single location failures within a single Amazon region.
There are a number issues that needs to be handled properly with a Galera cluster across two regions and/or AZs (which practically is two data centers). This will be addressed in a future post.
Create a VPC with Public and Private subnets
We’ll use Amazon’s VPC wizard to create our VPC that has a public and private subnet. Go into the Amazon VPC console dashboard, verify the region that your want your VPC to be created in and click on the ‘Get started creating a VPC’ button.
You will be prompted to select from a list of VPC templates and for this exercise we’ll chose ‘VPC with Public and Private Subnets'.
The final confirmation dialog shows you the VPC configuration that will be deployed. Using defaults your VPC will allow up to 65,531 IPs with the public and private subnet allocating up to 251 slots each. You can create the VPC with defaults or configure ranges and the subnets to your preference.
There will be also one NAT instance created which allows your EC2 instances on the private subnet to access the internet by routing traffic through that instance. Instances created on the public subnet will route traffic through the internet gateway.
Click on ‘Create VPC’ and shortly after you should have 1 VPC, 2 subnets, 1 network ACL, 1 internet gateway and route tables all setup.
Create Security Groups
Before continuing lets define some security groups to be used for our different EC2 instances.
This group is for the Galera database cluster which reside on the private subnet.
TCP: 4567 (Group comm), 4568 (IST), 4444 (rsync), 3306 (MySQL)
TCP: 9200 (HTTP health check ping port) xinetd invoked shell script
TCP: 22 (ssh) ClusterControl passwordless ssh
ICMP: Echo Request/Reply. Being able to ping the host is a requirement for the deployment/bootstrap scripts
The ClusterControl instance is our monitoring and administration access point to the database cluster and resides on the public subnet. It is also serving as our staging server from where we deploy and bootstrap the MySQL Galera cluster.
TCP port 22 (ssh) SSH access to our VPC
TCP port 80 (HTTP) ClusterControl web application
TCP port 3306 (MySQL) The ClusterControl’s MySQL database and below we’ve also only allowed the source to be other instances with the GaleraCluster security group. This is important since the ClusterControl agent that is installed on the Galera nodes need access to this port.
This are public facing instances and in our example we'll create a couple of web servers.
TCP port 22 (ssh) Source only allows ssh connections from the ClusterControl.
Create EC2 instances
We are later going to use Severalnines’s Galera configurator to quickly deploy our MySQL Galera Cluster. The number of instances needed are 1+3, one instance will be dedicated for the ClusterControl package and the rest for the Galera database nodes.
Use Amazon’s Quick Launch wizard and select one of the supported OSs for Galera (http://support.severalnines.com/entries/21589522-verified-and-supported-operating-systems).
I’m going to use a Ubuntu Server 12.04 LTS image and create 1 small instance for the ClusterControl and 3 large EBS optimized instances for the Galera Nodes.
In order to launch the instance in a VPC subnet you need to edit the details and make sure to enable ‘Launch into a VPC’. Select the private subnet for the Galera instances and the public subnet for the ClusterControl instance.
Select the public subnet.
Select the ‘ClusterControl’ security group.
Save the changed details.
Naming your instances makes is easier to identify it later on.
Next allocate an elastic IP. Instances in the public subnet that do not have an elastic IP are not able to access the internet.
Allocate an elastic IP and associate it to the ClusterControl instance
Go to the ‘Elastic IPs’ in the EC2 dashboard and click on the ‘Allocate New Address’.
Make sure that the selected ‘EIP used in’ says VPC and not EC2.
Associate the IP with the ClusterControl instance.
You should now be able to logon to your ClusterControl instance using ssh.
$ ssh -i <your aws pem> email@example.com
Next launch 3 large instances for the Galera nodes and also make sure to launch it as an “EBS optimized instance” (500 Mbps bandwidth for large instance types) and create an EBS volume to store the database files in order to survive instance reboots. A separate EBS volume is great for taking backups/snapshots etc.
Using an EBS optimized instance should give give you an increase in throughput and a more consistent level of IOPS and latency between the EC2 instance and the EBS volume.
AWS quote: “Provisioned IOPS volumes are designed to deliver within 10% of your provisioned IOPS 99.9% of the time. So for a volume provisioned with 500 IOPS, it volume should deliver at least 450 IOPS 99.9% of the time.”
Select the GaleraCluster security group.
Then add a provisioned IOPS EBS volume with the number of IOPS that you want.
Only a maximum ratio of 10:1 between the IOPS and volume is allowed so for example 10GB volume <=100 IOPS or 200GB <= 200 IOPS
If your database workload is very write intensive and/or if your “hot” data does not fit entirely into your InnoDB buffer pool then you can opt to create a RAID array with a bunch of EBS volumes to increase the throughput for disk-bound workloads.
Since AWS charge per GB used and the number of provisioned IOPS not volumes you could easily create for example 6 EBS volumes and setup a RAID 1+0 stripe.
Save and launch the instance. Repeat for the next two Galera instances.
“Pre-flight check” ClusterControl and Galera instances
Before deploying and bootstrapping the Galera Cluster there are a few pre-requisites that we need to do.
- Copy your AWS key to the ClusterControl instance
$ scp <your aws pem file> firstname.lastname@example.org:~/.ssh/id_rsa $ ssh -i <your aws pem file> email@example.com chmod 400 ~/.ssh/id_rsa $ ssh -i <your aws pem file> 188.8.131.52
The location of the aws key will be needed later in the Galera Configurator section.
Verify that you can use ssh to connect to your Galera instances
If you don’t feel comfortable using the AWS key you easily generate your own passwordless ssh key instead to be used for the database cluster.
$ ssh-keygen -t rsa -N "" -f ~/.ssh/id_rsa
- Ping Galera hosts from the ClusterControl instance
This should work if you have setup the GaleraCluster security group properly allowing ICMP echo request/reply. The deployment script requires that you can ping the Galera nodes.
From your ClusterControl instance ping your Galera instances.
$ ping 10.0.1.13 $ ping 10.0.1.16 $ ping 10.0.1.26
- 1) hostname -i needs to resolve properly on all instances/hosts
If you get “hostname: Name or service not known” then the most painless way to fix this issue is to add the hostname to the /etc/hosts file (another way is to edit the Galera deployment scripts).
Currently the deployment script does not use ‘hostname --all-ip-addresses’ as default.
- 2) Doing ‘ssh 10.0.1.26 sudo ls’ should not give you “sudo: unable to resolve host ip-10-0-1-26”
Once again the most painless way to resolve this is to add the hostname to the /etc/hosts file on each instance.
$ echo "10.0.1.26 ip-10-0-1-26" | sudo tee -a /etc/hosts
Make sure that 1) and 2) passes on all Galera instances.
Format and mount EBS volume(s) on the Galera instances
On each Galera instance format a new ext4 (or use xfs) volume that we are going to use for our database files. If you created volumes to be used for a RAID setup then follow instructions further down.
Look for your EBS volume
$ sudo fdisk -l (or cat /proc/partitions) ... Disk /dev/xvdf: 10.7 GB, 10737418240 bytes 255 heads, 63 sectors/track, 1305 cylinders, total 20971520 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: 0x00000000 ...
Format the volume with an ext4 filesystem
$ sudo mkfs -t ext4 /dev/xvdf
Create the mount point, this is where we’ll store the MySQL data files
$ sudo mkdir /data
Add the volume to the /etc/fstab so that it survives instance reboots.
$ echo “/dev/xvdf /data auto defaults,nobootwait,noatime,data=writeback,barrier=0,nobh 0 0” | sudo tee -a /etc/fstab
Mount the new volume
$ sudo mount -a $ df -h Filesystem Size Used Avail Use% Mounted on /dev/xvda1 8.0G 867M 6.8G 12% / udev 3.7G 12K 3.7G 1% /dev tmpfs 1.5G 168K 1.5G 1% /run none 5.0M 0 5.0M 0% /run/lock none 3.7G 0 3.7G 0% /run/shm /dev/xvdb 414G 199M 393G 1% /mnt /dev/xvdf 10G 280M 9.2G 3% /data
Recommended optimized mount options for ext4:
If you for example created a few EBS volumes then you can create a RAID 1+0 setup in a few steps.
6 volumes striped as RAID 1+0
$ sudo apt-get install mdadm $ sudo mdadm --create md0 --level=10 --chunk=64 --raid-devices=6 /dev/xvdf /dev/xvdg /dev/xvdh /dev/xvdi /dev/xvdk /dev/xvdj $ sudo mkfs -t ext4 /dev/md/md0
Verify the new array
$ cat /proc/mdstat Personalities : [linear] [multipath] [raid0] [raid1] [raid10] [raid6] [raid5] [raid4] md127 : active raid10 xvdj xvdk xvdi xvdh xvdg xvdf 31432320 blocks super 1.2 64K chunks 2 near-copies [6/6] [UUUUUU] [>....................] resync = 1.0% (326636/31432320) finish=76.1min speed=6806K/sec unused devices: <none>
Add the array
$ sudo mdadm --detail --scan | sudo tee -a /etc/mdadm/mdadm.conf
(ARRAY /dev/md/ip-10-0-1-27:md0 metadata=1.2 name=ip-10-0-1-27:md0 UUID=6e339907:9d9ff219:044ae233:47b4a362)
$ echo “/dev/md/ip-10-0-1-27:md0 data auto defaults,nobootwait,noatime,data=writeback,barrier=0,nobh 0 0” | sudo tee -a /etc/fstab
Mount the new volume
$ sudo mount -a $ df -hT Filesystem Type Size Used Avail Use% Mounted on /dev/xvda1 ext4 8.0G 906M 6.7G 12% / udev devtmpfs 3.7G 8.0K 3.7G 1% /dev tmpfs tmpfs 1.5G 204K 1.5G 1% /run none tmpfs 5.0M 0 5.0M 0% /run/lock none tmpfs 3.7G 0 3.7G 0% /run/shm /dev/xvdb ext3 414G 199M 393G 1% /mnt /dev/md127 ext4 30G 582M 28G 2% /data
Quick Disk IO Performance Test
Lets do a simple test with dd. Write 8GB to /data and perform a sync once before exit.
Standard EBS volume
$ time sudo dd bs=16K count=524288 if=/dev/zero of=test conv=fdatasync 524288+0 records in 524288+0 records out 8589934592 bytes (8.6 GB) copied, 1157.38 s, 7.4 MB/s real 19m17.440s user 0m0.504s sys 0m12.641s
$ time sudo dd bs=16K count=524288 if=/dev/zero of=test conv=fdatasync 524288+0 records in 524288+0 records out 8589934592 bytes (8.6 GB) copied, 628.86 s, 13.7 MB/s real 10m28.880s user 0m0.432s sys 0m11.697s
Read 8GB test file
Standard EBS volume
$ time sudo dd if=test of=/dev/null bs=16K 524288+0 records in 524288+0 records out 8589934592 bytes (8.6 GB) copied, 906.412 s, 9.5 MB/s real 15m6.439s user 0m0.428s sys 0m6.256s
RAID 1+0 on 6 provisioned IOP volumes, 10GBx6
$ time sudo dd if=test of=/dev/null bs=16K 524288+0 records in 524288+0 records out 8589934592 bytes (8.6 GB) copied, 133.016 s, 64.6 MB/s real 2m13.188s user 0m0.180s sys 0m5.080s