It's time to start thinking about databases as multi-tier applications

Relational databases have historically been thought of as single entities, black boxes to developers where SQL goes in and results come out. The dark arts of wizardly, bearded DBAs capable of maintaining this illusion even in large, clustered environments commanded large salaries, armies of budget-busting consultants and the begrudging respect of mere mortal developers.

This remains largely the case today, but the emergence of the cloud in the enterprise is starting to change this picture. Databases will still remain an insoluble, difficult to hack entity of any application stack in the cloud until we are able to easily think about databases not as single, hostile units, but multi-tier applications in and of themselves. Then we can use cloud tools ready at our disposal like the higher tiers of typical cloud-based applications.

Multi-tiered DBs

These tiers already exist in databases, it's just that most people other than DBAs aren't aware of them. Indeed, understanding the various tiers of a typical relational database is fundamental to knowing how to tune such systems. Take MySQL as an example:

This is an oversimplification, but MySQL already has several tiers, and it does a good job of hiding this complexity from most developers. It could be argued that MySQL owes its success largely to its ability to perform well with a minimal of tinkering, a testament to a well-architected system. There are various levels of cache, including a MySQL query cache, storage-engine specific caches and buffers such as InnoDB's bufferpools, and then there is the interaction each storage engine does with the OS for storage and logging purposes. Typically this involves a write to disk, but there is no reason that this cannot involve writing data to memory (like the Memory “storage” engine) or out to the network (as MySQL Cluster does), thanks to MySQL's flexible storage engine interface.


Scaling out MySQL traditionally means creating a new instance of this multi-tiered database process on another server and replicating out data. This is architecturally unpleasant, introducing all sorts of pain in terms of maintenance headaches and potential inconsistencies between systems that leak up into the higher tiers of applications depending on just how imperfectly this replication is done. For a typical scale out of MySQL where reads are offloaded to asynchronously replicated slaves, why should an application server have to know and/or care if one pool of servers is read-only and another can take writes but should avoid reads? Systems like Galera take an important step in the right direction, but still remain monolithic, multi-tiered systems made to work together awkwardly.


What we really need is the ability to invert this picture: to be able to scale up and down various tiers of the database depending on our needs, and most importantly, maintain the abstraction of a single database to the application. For highly cyclical, read-mostly workloads, it should be possible to maintain only a core set of servers for logging and storage, and have servers that can be added and removed from the cache tier with ease to handle workload spikes.

The future of databases in the cloud

Ideally, all of this should be presented to an application as a single, strongly consistent entry point – if an application developer is given login credentials and an IP address, she should have no clue that this is any different from a single-instance database server. Indeed, it is remarkable that we are still so far from this ideal in the database world.

A system that does just that for MySQL is (shameless plug) the MoSQL storage engine, the system I helped develop as part of my research at the University of Lugano in Switzerland:

The benefit of a system like MoSQL is that the caching, storage and logging tiers have been decoupled, allowing an application architect to scale the tiers independently dependent on application needs, all while providing the illusion of a single, strongly consistent database no matter which node is connected to. Not only is this architecturally cleaner, leading to easier development, it allows easy integration into cloud orchestration tools such as Openstack Heat or Amazon's CloudFormation to save potentially significant costs due to overprovisioning, a topic I discussed at length in a previous post.

(A version of this article originally appeared on LinkedIn Pulse)

Database Elasticity: It's more important than you think

Databases are notoriously hard to do right: to design, to administer, to make highly available and above all, scalable.

But this discussion will not be about SQL vs. NoSQL, Big Data vs. Not-So-Big Data. This is about elastic vs. inelastic.

Workloads are not only continuing their perpetual climb to the sky, they are becoming less predictable. This means ever-larger worst-case peaks to prepare for. There is an old adage among IT managers: No manager ever got fired for buying IBM. Similarly, no DBA was ever fired for over-provisioning a database server. It's not hard to understand why: paying $10000 for an un-sexy, infrastructure-like service like a relational database when $5000 would have been enough to cover any peaks of demand is often a trivial $5000 cost in comparison to the potentially disastrous costs due to high latency, instability or unavailability that could be brought on by using an under-provisioned $2500 service [1]. Systems tend to be at their most unpredictable when approaching their peak capacity, and so over-provisioning is a deeply ingrained practice.

Consider the demand and capacity curves for a typical inelastic database service here:

The workload graph above is admittedly contrived and simplified for the purposes of this discussion, but fairly well represents the peaks and valleys that characterize most workloads, say, on a daily basis. The black line is the capacity of the server/VM you paid for. It could be a physical server in your own data center or a virtual machine deployed through a database-as-a-service like Amazon RDS or Openstack Trove. The entirety of the green area of the curve below the capacity line represents wasted computing power, and thus, wasted money given a constant price per time, which is the typical case in most public clouds. Put another way, the cost per operation is highest in the valleys of the workload and lowest at peak. Similarly, the blue area of the curve is the dangerous situation of having a service that is under-provisioned.

The holy grail of elastic services, database or otherwise, is to make the flat capacity curve fit the demand curve as closely as possible, thereby approaching a constant cost per operation and not time. The state of the art in relational database systems is short of this ideal: services like Amazon RDS with Provisioned IOPS indirectly take a step in this direction through the use of cost-per-IO in the underlying block storage. Research systems, like the MoSQL storage engine, bring elasticity a layer higher, enabling a flexible allotment of servers in a cluster in order to match workload.

An interesting twist is that public cloud providers will not have much interest in changing this status quo. As long as customers are able and willing to pay to over-provision services in the cloud, whether databases or anything else, public clouds will be able to over-commit their underlying hardware resources and sell more VMs with less hardware.

It is not clear how long this situation will last. As services such as databases become more elastic and capacity begins to more closely match workload, users will be attracted to the cost savings and place downward pressure on the over-commitment ratios for underlying hardware. Public clouds may be forced to either raise prices or move away from an instance-hour pricing model.

(A version of this article originally appeared on LinkedIn Pulse)

[1] The numbers are of course arbitrary; it could be $10,000 for an actual server housed in your data center or a $10/hour VM on a public cloud