Database landscape changed quite rapidly in the last couple of years. In the past the typical setup that we have seen was the database cluster. It could have been a standard, asynchronous replication-based cluster. It could have been a more cluster-aware solution like Galera. We are mentioning here solutions related to MySQL but this trend is visible in all data stores (think, for example, streaming replication and BDR in Postgresql). Both approaches have their pros and cons. Regular replication is somewhat faster and less dependent on the network. On the other hand cluster-aware software can handle network partitioning better, it will also be significantly less immune to the replication lag. What both solutions have in common is that every node in such clusters contains a whole data set. This allows you to scale out reads by creating more replicas or nodes, but when it comes to dealing with write scalability, there is no easy option to accomplish that.
Obviously, writes are typically less common in typical OLTP applications. Most of the time you are concerned about SELECT queries and their performance rather than how many INSERTs or UPDATEs your database performs. Still, at some point, inability to deal with increasing volume of writes will become a significant issue.
The only way to scale out writes is to split the data set – what it means is to shard your database. There are several methods of sharding the data. You can use range over a column, you can use range over a hash calculated from a column. You can use a mathematical function that, applied to a value in a column, will tell you in which shard it. You can split the data over functionality or you can build a metadata where you will store information about which row is located on which shard. No matter how you are going to approach it, it is not easy. Splitting the data is one thing. Finding it is another, more complex. Where are you going to implement the logic to find the data? Inside the application? It can be done, sure, but it will also increase the overhead of maintaining the application code. Reusing the code for another project may also become tricky. Are you going to decouple the logic and develop some sort of a middleman? A proxy that will perform the routing for your application? It will definitely increase flexibility in terms of repurposing this solution to new applications but you have just ended up with a new, very critical part of the infrastructure. Something that you have to maintain in terms of code but also something you have to take care of regarding high availability.
Automated sharding solutions
Recently we have observed a rise of database systems intended to reduce the pain of sharding. They come with a promise of letting you scale out your database while making it as transparent as possible. Instead of building your own solution, you can just use pre-made, ready-to-use software that will build a database system for you. Something you can just connect your application to and that’s it. One may argue that such a solution is available since, well, always, we are talking here about MySQL NDB Cluster, which, technically, shards the data across multiple data nodes. The main difference is that the NDB cluster is hardly a drop-in replacement for MySQL. It is quite specialized and while efforts are made to make it better for OLTP workload, it cannot really be compared to the “normal” MySQL.
Besides NDB Cluster, a solution we would like to focus a bit on is Vitess. Of course, no sharded environment will come with the same performance as a standalone database, where all data is stored locally. If you are going to query data that is located in multiple shards, things will slow down. Still, Vitess, underneath, has a “normal” MySQL/InnoDB datastore, with its pros and cons that we know, like and tolerate.
As for PostgreSQL, we have CockroachDB and YugabyteDB, both of which allow you to deploy a sharded environment. We are going to get there at some point and see how those are working. For now, though, we would like to focus on Vitess and its MySQL backends.
Vitess
Vitess is an open source software that is intended to build a database infrastructure on a very large scale, including support for sharding. It comes with a couple of software pieces that work together.
VTTablet
For starters, we have VTTablet – a service that is the backbone of Vitess. Each VTTablet is coupled with a MySQL database. You can think of them, essentially as databases. Tablets may be primary, replica, read only, backup and so on. Queries are routed to Tablets by VTGate.
VTGate
VTGate is a proxy that directs queries to correct tablets and then it processes the resultsets, aggregates them if they come from multiple VTTablets and sends them back to the client application. If you happen to use sharding, VTGate is what makes the magic happen for you – it will be able to identify where which row is located and direct traffic to the location that makes the most sense for a query.
Metadata (Topology service)
Finally, we have a metadata storage, typically Etcd, Consul or similar store, that keeps all the data related to the topology, shard configuration and so on.
How to start with Vitess?
There are several ways in which you can start your journey with Vitess. We found Kubernetes and Vitess operator the easiest way to emulate setups that we were interested in. It is not perfect and it does require some Docker/Kubernetes knowledge, but most of the time it should work just fine. Vitess comes with some guides where, step by step, you can get familiar with the most important aspects of the platform. Honestly, we found it not complex enough but hey, it’s great there’s something available.
If you prefer not to use containers, you can try to get yourself a locally-installed Vitess deployment. There are scripts and steps available to get it done. All required information can be found in “Get Started” section of Vitess’ documentation.
My environment
Couple of words about the environment that we are using to run tests. As we have decided to proceed with the Kubernetes and operator, we have prepared a “poor’s man Kubernetes cluster” spawning across four VirtualBox VM’s managed by Vagrant. All persistent volumes were based on NFS share mounted to every node. Not ideal, cleanup of released PV’s typically required manual intervention (rm -rf) but it was good enough. Such setup should be doable on a local laptop, given there’s enough memory to get it going.
This is it for today, in the next blogs we’ll start looking into some basic operations. Scaling out the keyspace, taking backups and implementing sharding.
