MySQL horizontal scaling

ScaleBase is a distributed database built on MySQL and optimized for the cloud. It is a relational database cluster that dynamically optimizes workloads and availability by logically distributing data. ScaleBase automates the data lifecycle, including analysis, data migration and node rebalancing.  ScaleBase provides an easy to manage horizontally scalable database cluster built on MySQL that dynamically optimizes workloads across multiple instances.  is  based in Newton, MA. The AWS Marketplace offering can be found here:  https://aws.amazon.com/marketplace/pp/B00K8B5BOG.  ScaleBase provides the scalability and availability benefits of NoSQL databases while using a relational database.

Auto Scaling Script

Auto Scaling with VPC 

This example uses the CLI version 1.0.61.2 (API 2011-01-01).  There is now a newer version of the CLI.

Overall notes:

1. Create an image (aka: gold image) that has a health check on it that consists of a simple static HTML page such as a ping.html or an index.html. 

2. The ELB (need an ELB before creating Auto Scaling group) hat has the instances running from your gold image AMI created in step 1. Could potentially (nothing is preventing you from doing) create a auto scale launch configuration (step 6 in process below) with an AMI that is different, with a different instance type as instance type is another parameter of your launch configuration, than the instance that will be part of your auto scaling group.  Both are possible but most will have the same AMI (gold image, so you know you have a health check) and the same instance type.

3. Before you create the ELB you should have instances running of the  gold image to make sure the ELB is working properly.  To provide true HA, make sure to put the instances in two AZs. (for example: us-west-2a, us-west-2b)

4. When creating elb that is using subnets, limitation on ELB is that only one subnet per AZ is allowed.  

5. When creating autoscaling group (as-create-auto-scaling-group)

 need to make sure to specific the VPC subnets and AZs.  The VPC subnets need to be in the AZs specified in the AZ parameter for command.

Steps:

Prework:

1. Identify the VPC : For example, VPC: vpc-9b120cf2

2. Add subnets : In this case, using two public subnets. I had to create the second one (subnet) which was private by default and I had to add an igw to make it public.

3. Subnets -

            A. Subnet: subnet-9f120cf6

            CIDR: 10.0.0.0/24   VPC: vpc-9b120cf2   Availability Zone: us-west-2a

            B. Subnet: subnet-8c130de5

            CIDR: 10.0.5.0/24   VPC: vpc-9b120cf2   Availability Zone: us-west-2b

4. Luanch two instances from AMI that has Apache installed with ping.html (or some other file) as a health check. One instance in subnet A and the other in subnet B. 

5. Create an ELB with the two instances across subnets.  ELB is healthy with two instances running in two AZs in two different public subnets. Limitation on ELB is that one subnet per AZ. 

6. Auto Scaling:

A. as-create-launch-config vpcautoscaling-as-lc --image-id ami-dcd344ec --instance-type t1.micro --key AutoScalingKey

B. as-create-auto-scaling-group vpcautoscaling-as-grp --launch-configuration vpcautoscaling-as-lc  --min-size 4 --max-size 12 --load-balancers VpcautoscalingAutoScalingELB --vpc-zone-identifier subnet-9f120cf6,subnet-8c130de5 --availability-zones us-west-2a,us-west-2b

C. as-describe-auto-scaling-groups --headers

output of command:

INSTANCE  INSTANCE-ID  AVAILABILITY-ZONE  STATE      STATUS   LAUNCH-CONFIG

INSTANCE  i-3af6e708   us-west-2b         InService  Healthy  vpcautoscaling-as-lc

INSTANCE  i-3cf6e70e   us-west-2a         InService  Healthy  vpcautoscaling-as-lc

INSTANCE  i-3ef6e70c   us-west-2a         InService  Healthy  vpcautoscaling-as-lc

INSTANCE  i-38f6e70a   us-west-2b         InService  Healthy  vpcautoscaling-as-lc

D. as-put-scaling-policy vpcautoscaling-scale-out-policy --auto-scaling-group vpcautoscaling-as-grp --adjustment=30 --type PercentChangeInCapacity

ARN: arn:aws:autoscaling:us-west-2:649163059618:scalingPolicy:69270ce4-3350-48f4-9d6f-71bc64225554:autoScalingGroupName/vpcautoscaling-as-grp:policyName/vpcautoscaling-scale-out-policy

E.  as-put-scaling-policy vpcautoscaling-scale-in-policy --auto-scaling-group vpcautoscaling-as-grp --adjustment=1 --type PercentChangeInCapacity

ARM - arn:aws:autoscaling:us-west-2:649163059618:scalingPolicy:d9a6780b-3319-4b00-98f1-e98dcfc68d82:autoScalingGroupName/vpcautoscaling-as-grp:policyName/vpcautoscaling-scale-in-policy

F. mon-put-metric-alarm --alarm-name AddCapacity --metric-name CPUUtilization --namespace "AWS/EC2" --statistic "Average" --evaluation-periods 6 --period 120 --threshold 80 --comparison-operator GreaterThanOrEqualToThreshold --dimensions "AutoScalingGroupName=vpcautoscaling-as-grp"  --alarm-actions arn:aws:autoscaling:us-west-2:649163059618:scalingPolicy:69270ce4-3350-48f4-9d6f-71bc64225554:autoScalingGroupName/vpcautoscaling-as-grp:policyName/vpcautoscaling-scale-out-policy

G. mon-put-metric-alarm --alarm-name RemoveCapacity --metric-name CPUUtilization --namespace "AWS/EC2" --statistic "Average" --evaluation-periods 2 --period 120 --threshold 40 --comparison-operator LessThanOrEqualToThreshold --dimensions "AutoScalingGroupName=vpcautoscaling-as-grp"  --alarm-actions arn:aws:autoscaling:us-west-2:649163059618:scalingPolicy:d9a6780b-3319-4b00-98f1-e98dcfc68d82:autoScalingGroupName/vpcautoscaling-as-grp:policyName/vpcautoscaling-scale-in-policy

H. as-describe-policies --auto-scaling-group vpcautoscaling-as-grp —headers

Horizontal Scaling of Oracle on AWS

Just came across this solution.
Parelastic: http://www.parelastic.com (MySQL only at this time)

I really enjoyed the 'sharding sucks' mantra.  Being an 'old school' Oracle guy, the new world answer to  database scaling is sharding...I think that is very naive.

I know of these two companies in this space as well:

Scalearc (MySQL and SQL Server)

Scalebase : http://www.scalebase.com (MySQL only at this time)