Background

I am in charge to design viuGraph system that:

  1. easily scale to able serve from thousands of users to millions of users and
  2. cost efficient.

One component I wondering to face bottleneck hardly is Database Layer. On this development phase we are using MySQL to store data. Now I need to search how we will scale viuGraph’s database layer in the future.

Problem Definition

What strategy to scale viuGraph’s MySQL database layer in the future that easily scale to able serve from thousands of users to millions of users and cost efficient?

MySQL and forks scaling strategy

I have found MySQL and forks (MariaDB, Percona XtraDB) scaling strategies:

  1. MySQL Cluster and
  2. Galera Cluster.

MySQL Cluster

MySQL cluster is scaling strategy provided by MySQL AB (acquired by Oracle Inc). It can help scale-out (adding more nodes) and deliver high-availability. Nodes that exist in cluster:

  1. Cluster Manager node,
  2. SQL node and
  3. Storage node.

Our viuGraph’s Ruby on Rails instance must send write and read query to SQL node.

MySQL cluster High Availability illustration. Source: [MySQL Cluster](https://www.mysql.com/products/cluster/scalability.html)
MySQL cluster High Availability illustration. Source: MySQL Cluster

Galera Cluster

Galera Cluster is multi-master scaling strategy provided by Codership oy. It can help scale-out (adding more nodes) and deliver high-availability. Galera Cluster architecture’s is much simpler than MySQL Cluster. It’s only has one node type: master. If you have 10 MySQL server instance, they all is master node.

Galera cluster multi-master illustration. Source: [Galera Cluster](http://galeracluster.com/products/)
Galera cluster multi-master illustration. Source: Galera Cluster

Our viuGraph’s Ruby on Rails instance can call every instance from 10 server instances and can do write and read query to every node. I think it is more convenient for viuGraph.

Test

After do due dilligence on MySQL Cluster vs Galera Cluster, I choose Galera Cluster to test. I have try to setup Galera Cluster with Percona XtraDB and MySQL, but I have no luck with MariaDB. The Galera Cluster is seamless and smooth. I can do INSERT and UPDATE query on every single node.

But when I try to migrate Ruby on Rails database, there is a problem. I can’t apply the table structure changes! And there is a known limitation that Galera Cluster unsupport explicit locking such as LOCK TABLES… (reference: Galera Cluster known limitations)

Phew… viuGraph is a startup product that will have new feature and modification. The limitation is unsuitable for viuGraph.

Conclusion

I have found that Galera Cluster is much simpler than MySQL Cluster. These can help to scale-out (cost efficient) and offer high-availability. But I found that Galera Cluster does not support LOCK TABLES, that block Ruby on Rails database migration feature. The conclusion is Galera Cluster is not fit for viuGraph.