MySQL server on openstack

Question

I want to set up a MySQL database that is easy to scale. Traditional, off-cloud scaling means (1) upgrading the hardware, (2) sharding, or (3) replicating.

Option 1 is easy but is limited. Option 2 (I hear) is very complicated, and I don't want to waste limited manpower on maintenance/administration. Option 3 appears suitable only for high-read, low-write cases, which won't fit my use case, and there are issues with consistency as well.

I am contemplating setting up a private OpenStack cloud and deploying a VM that hosts the database. It would be easy to scale vertically (just assign more vCPU, vRAM, HDD), and I can add more physical nodes to the cloud as needed. Then, I would just have a single, huge MySQL instance on the cloud.

My questions:
1) Does this make practical sense? I don't mind a small overhead hit (I'd rather spend more on hardware than on maintenance/administration), but if the overhead is too large it may not make sense.

2) How scalable would this solution be? Can I just keep adding physical nodes to the cloud and scaling up the vCPUs, vRAM to arbitrarily high amounts?

The application is not very demanding on latency. I just need an easy-to-scale solution.

Answer 1

No, a "cloud" can't magically add more physical nodes to a single VM.

Virtualization is a technology to partition a single real hardware machine into several virtual machines. A "cloud" setup is just an administration architecture that lets you manage several VMs on a bunch of real machines without too much work. But in the end, each VM runs in a single real machine.

A totally different setup, which is (slightly) closer to what you want is a cluster. That is a set of machines that run some software designed to run on the whole set of machines, using resources (CPU, RAM, storage) from all of them. In a sense, the cluster becomes a single machine. But this "big machine" is not the same as a normal server, just bigger; it's totally different, and any application must be designed from the start to run on a cluster.

There's a "MySQL Cluster" setup, but it's not what you would imagine, as it stores everything in RAM. It's really fast and scalable, but very expensive and complex to manage.

Since you say that "The application is not very demanding on latency", I guess when you say "easy to scale" you mean lots of storage. Nowadays, it's not hard to add a couple hundred terabytes to a single machine, so just scaling up can in fact be an easy and (relatively) cheap way.

The real challenge is not plugging all the hardware together, but really using it. MySQL can certainly be configured to do this, but whole-table locks on MyISAM tables would be a disaster. Even InnoDB gets into real trouble and long process times. On that space PostgreSQL is getting more and more use cases, so if you're not stuck on MySQL, it's a real option to consider.

bigger than that, there's a whole different scenario, either using a cell architecture (which can be seen as an extreme case of sharding), or some NoSQL solutions like Hadoop or Riak

Answer 2

Depending on your security and connectivity requirements, you could opt for a managed solution or DBaaS in the cloud.

Amazon, of course, offer their RDS http://aws.amazon.com/rds/. This is really point and click stuff and they'll manage the clustering, hardware and maintenance for you. It's a few cents per hour and if you can opt for the high resilience Multi-AZ option for twice the price.

Rackspace do it too (this is a link to the UK cloud) and many others.

Sorry if this isn't for you but hosting on your own tin, or worse, creating a private cloud from scratch may not be worth your money or your effort.