This guest post was submitted by Vinay Joosery, CEO of Severalnines. The company develops automation and management software for database clusters, and has enabled over 7000 deployments to date via its online Cluster Configurator.
Databases can be complicated things, especially when you want to scale or make them resilient to failures. Is a database cluster something you cobble together by setting up replication and throwing in a good dose of monitoring scripts? Well, not if you ask us 🙂
Web Server vs Database Server Scalability
When your service or application is rapidly growing, clustering is a great way to achieve the following:
Increase capacity by adding more nodes,
Increase availability by providing failover,
Decrease capacity when it is not needed.
Below is a typical clustered setup with multiple app servers. Notice something wrong in the diagram?
In a modern software stack, web servers and application servers are usually stateless. They receive an HTTP request, they process it, and forget it. This is a very good design for horizontal scalability, because deploying new stateless nodes is usually quite simple.
Databases, on the other hand, store application data and are not stateless, and are therefore usually harder to scale. Adding a node means copying a snapshot over to a new server and having the new node catch up with any transactions that happened after the snapshot was taken. And then, other complications come up. Copying a large snapshot could take hours. Data on multiple servers need to maintain consistency (or perhaps eventual consistency). Distributed locking protocols need to broker client requests that want to access or modify the same set of data. Finally, any failures usually require reconfiguration of the remaining cluster members, something that should be seamless to an application.
In other words, managing state is hard. The main reason other components in the software stack are able to become stateless is because they have pushed this problem down to the database layer.
Unfortunately, all this complexity shows itself in many, if not most, of the database cluster solutions out there on the market. Database clusters are usually complex to understand and operate. It is a real problem, and is illustrated by the dozens of companies out there providing clustering/high availability/scalability solutions for MySQL alone.
This is probably why sysadmins tend to shy away from database clusters. If one big server can handle the load, it is easier to let the single database server fail and manually restart it. Or, if you lose the server entirely, you know how to build a new one and restore from backup. In the end, you might need to get up at 3am on a Sunday morning. And you might be called into a meeting with your management, or worse, your customers, to explain why the entire service was down for (type in the time taken to recover the DB) hours.
At Severalnines, we have a strong interest in database clustering. Our products are aimed at making it easy for users to deploy and manage different types of database clusters. However, note that there are no generic cluster solutions that would handle all types of database workloads — if there was one solution that could fit all sizes, then we would not need to support multiple clustering technologies.
Some Clustering options for MySQL
MySQL Replication – This is not a clustering solution in itself, but a simple way to scale read-only workloads or achieve redundancy. Most MySQL users are using or have used MySQL Replication. Failures are not handled automatically, so failover is usually performed by a skilled DBA or sysadmin. Applications also need to direct write traffic to the master only.
MySQL Cluster – This is the official clustering solution from MySQL/Oracle. It consists of SQL nodes (MySQL Servers, all masters) that provide access to data stored in Data Nodes. It works well with write-intensive applications. Note that the storage engine is called NDB, so you will have to migrate your existing MyISAM or InnoDB tables to NDB. For more information, you can view these MySQL Cluster training slides.
Galera Cluster for MySQL – Galera is a replication plugin to MySQL, developed by Codership. MariaDB Galera Cluster or Percona XtraDB Cluster are also based on Galera. Galera provides a multi-master solution for InnoDB data, so you can read or write from any MySQL server. A failed node has the ability to resync automatically when coming back online. To integrate it with InterWorx, you can check out this HowToForge page. A Galera Cluster tutorial is also available for more information.
Database scalability is one of the fundamental building blocks of all successful web services or applications. If you have clustered your application servers, then database clustering is probably the next thing to look at. You can use our Online Configurator or ClusterControl to automate the deployment. Or perhaps it would make sense to integrate this into the InterWorx cluster manager control panel — let us know what you think!