Delivering High Availability For MySQL Apps On The Cloud

One of Xeround’s key goals that we always keep in mind is the simplicity and ease of use of our service. From the provisioning stage and daily database maintenance to auto-scaling and automatic failover, we strive to make it as simple as possible for end users to take advantage of Xeround’s unique features.

This goal gives rise to endless dilemmas and design considerations that we face while trying to deliver the best user experience and the best service to our users.

One such “dilemma de jour” that I thought would be interesting to share: What’s the best way to serve customers our unique dish of high availability, with no single point of failure and with load balancing?

Unlimited scalability via automatic front-end additions

An inherent capability of our distributed storage engine is the existence of multiple front-end nodes, which connect to our backend (which is also distributed on multiple nodes.) This multiple front-ends allow us to grow in throughput and number of connections beyond the limit of any single machine. This growth is not only unlimited, but extremely granular- in the sense that we can add just the right amount of extra resources required by the database, with no need for over-provisioning or being limited to Instance sizes, such as in the case of Amazon RDS. The automatic scalability is done in a way that is transparent to the application and with no downtime.

The application can open connections against all front-ends for both load balancing and for high availability.

Neat capability, right? But now, let’s consider this: How do we best deliver this feature to our customers?

Delivering high availability while not compromising automatic scaling:

1. The easiest way from our perspective (but the hardest from the user’s perspective) is to just provide users with the multiple front-end addresses and ports and let them handle it on the application level. Users will have to manage connection failovers, load balancing, etc. Best case scenario? They would be able to take advantage of a MySQL driver that has some of this capability built-in (Java Driver, for example).

   The main disadvantage of this implementation (aside from the extra difficulty on the user side, which goes against our value of simplicity,) is that it limits the automatic handling of scale-in and scale-out events. One of the main advantages of Xeround is our granular, unlimited, and automatic elasticity – which also manifests itself in our ability to add and remove front-end nodes as needed. With the above implementation, we don’t have any control over the connections, and therefore we would not be able to handle scaling automatically. The best we could do in this situation is send an email informing the user of the addition of a new front-end (A subtraction of a front-end node is even more difficult.)

2. Another alternative, probably the easiest from the user’s perspective, is to embed high availability into the driver itself. In this scenario, the driver will be supplied with the initial front-end details and would be updated automatically with any additional front-ends. The driver will balance the connections between front-ends, handle failover automatically, and move connections seamlessly in scale-in and scale-out cases without closing the connection on the user side.

   The problem with this approach is the need to support a multitude of drivers (due to the variety of platforms, programming languages, frameworks, etc.) This would also require an intrusive change on the user side, which we try to avoid, as some users may be reluctant to embed new components into their application and prefer to use the wealth of well-known and community-backed drivers.

3. The third alternative – the one we currently employ – is a balancer component on our servers, which is installed with each database instance. This component takes care of balancing and availability between all the front-end nodes and seamlessly moves connections when we scale in or out (This is why we do not use a standard balancer or Amazon load balancer.)
   

   Today, we expose to our users three IP addresses for those balancers that are used to access the database. The application connects to any of the IP addresses, and the balancer routes the SQL requests to any of the database’s multiple front- ends.

   The three IPs are provided for high availability so that you can failover from one IP to another in case of failure in either one of them.  The failover mechanism can either be coded in the application or, in some cases, implemented by a driver or even a middleware.

   Xeround’s auto-healing mechanism employs a “stand-by” machine, which, in the case of failure, takes over the failed machine components and preserves the same IP as the failed machine. However, it could take a couple of minutes for the process to complete, in which time failed requests sent to the first IP would need to be re-sent to one of the other IPs. Although users do need to take care of (some) of the failover between the balancers themselves, they also get unlimited front-ends for automatic scaling and for load balancing between the nodes to improve performance.

   Another downside to the implementation of our balancers is that some MySQL features are lost because the client is not connected directly to the MySQL, like SSL connections and host-based security are. These are all features we are working to support in upcoming releases of our balancer component.

   In the future, we plan to support failover between the load balancers in a way that will not require tweaking your app; this would be supported with a DNS that routes to the active balancer.

Let us know your thoughts on high availability – and how would you like yours served! :)

Originally posted on: http://xeround.com/blog/2011/05/delivering-high-availability