Is Mysql Cluster Production ready

Is Mysql Cluster Production ready?

Setting up a mysql cluster is kind of easy on non-cloud environment by following But when you try to install on cloud (Amazon ec2) for a production environment with auto scale or at least auto heal it is not as straight as it looks, mainly because of few reasons,

  • Cluster nodes (ec2 instances) go down and new instances replace them which have new ip addresses.
  • The process of these NDBDs (re)joining the cluster is real slow.
  • Start sequence of mgmt node, api nodes being such an important issue for the cluster to be stable.
  • During add or restart of cluster nodes the overall health of the cluster was never predictable.

If you need a bare minimum HA & scalability solution for your database layer using cloud and clustering technologies, you will have to

  • Set up the mysql cluster and place it behind a load balancer of some sort for HA.
  • Try to configure auto heal / scale in amazon for HA and Scaling.

This is just a log of all the pain points we saw during install of such a setup using Mysql cluster GPL 7.1.3 on Ubuntu (x86_64) with Amazon EC2, AMI, ELB, S3 and Auto Scale.

We have a auto scaled web server (tomcat) layer connect to a 2 node mysql cluster through amazon’s elastic load balancer.

In this setup, we have 2 sql nodes,2 data nodes and 2 management nodes. Further, both data node and sql nodes run on the same ec2 instance, because if data node or sql node goes down, we treat the whole instance is down and fire another instance through amazon auto scale.

Mysql cluster setup needs the host name of all cluster members mentioned in the config.ini file and each of the my.ini file needs to have host name of the management server. This has issues in virtualized environment like AWS where we cannot depend on fixed set of host names or ip addresses. You can get around this issue by using your own DNS server or dynDNS or by playing around with the /etc/hosts file.

Trying out the hosts approach :

Once the mysql cluster is up and running, update the host names of all nodes in the cluster into the /etc/hosts file , sync it among all the nodes and place it in a easily accessible location, like an Amazon S3 location.

Then write a start up script on your ec2 instance to

  1. on initial boot , get the hosts file from s3(assuming you have the latest there) , update its own ip address into it , upload it back to s3 and call some kind of wake up script on other mysql nodes to update their own hosts file.
  2. on the mysql data+api node, Once the ip address work is done , update the script to start the ndbd with the –initial option so that the latest data is synched into it , wait till the ndbd is started and then start mysql api node.
  3. On reboot , do similar things except updating the hosts file.
  4. On mysql mgmt node , update the script to start the ndb_mgmd .

Create AMIs separately for mgmt node and data+api node.Once you have those AMIs you can start playing with the auto scale feature at Amazon.To use auto scaling, you need to create a load balancer, create a launch config on the AMI, finally create a auto scaling group on the launch config.

1. Create a elastic load balancer

elb-create-lb clusterdb-lb –availability-zones us-east-1b –listener “protocol=tcp,lb-port=3306,instance-port=3306”

2. Create the auto scale launch configuration based on the earlier created AMIs

as-create-launch-config clusterdb-lc –image-id ami-id –group <security group name> –instance-type m1.xlarge

3. Create the auto scaling group based on the earlier created launch config and fix it to the load balancer created before in step 1. since min-size and max-size is kept the same , it is not really auto scale but auto heal or auto restore. The following config makes sure we have 2 nodes all the time.

as-create-auto-scaling-group clusterdb-asg –launch-configuration clusterdb-lc –availability-zones us-east-1b –min-size 2 –max-size 2 –load-balancers clusterdb-lb

So that completes our mysql cluster setup for auto heal/restore, at least in theory. But there are many issues here related to.

1. IP Address / Host name changes

SSH clients start throwing out error messages because we changed the hosts file.

Change in single mysql node host change needs a complete cluster rolling restart.

We have not handled synchronizing hosts file access when more than one ec2 instance comes up at the same time.

Solution for these issues is to may be use a separate DNS server, which we still need to try.

2. ndbd, ndb_mgm, mysqld lifecycles.

Initial start of a new ndbd node sometimes took more than 30 minutes and was not always consistent and our DB had just 150 tables, total size of the dump being 10 MB.

Adding a new ndbd node did not bring back the cluster health and required manual restarts of the whole cluster.

Adding a new node requires restart of ndb_mgms, ndbds and finally mysqlds and in that order only.

Mysql cluster being such a delicate tool, it doesn’t give enough confidence for using it in production.

Other alternatives for HA and Scalability needs

1. master-master replication, one acting as a hot standby

2. Use dynDNS or a dns server itself

3. Try oracle’s commercial product, mysql cluster manager.


Post a Comment

Required fields are marked *


%d bloggers like this: