“Relational databases don’t scale!”. This is the claim that database experts have heard over and over again, and yet we spend our careers scaling databases. I immediately translate this in my head to either “I threw hardware at the problem and it didn’t work!” or “I don’t know how to design applications for performance and I’m not going to learn!”
Perhaps the problem is that relational databases don’t scale in the same way that applications do. Where I work, we have a petabyte-scale e-commerce database that handled 14 million transactions per second last cyber Monday. My experience is with MySQL and Db2, so I thought I’d share some observations on scaling relational databases.
The most important factors in database scaling
The most important factor in scaling a database system is using it properly. There are many data models and query workloads that work just fine at a small scale, but when you get bigger or have higher volume, they hit a wall with database performance. At some point, throwing hardware at it cannot get over that wall.
Carefully making choices for the data model, proper indexing, and knowing/tuning your query workload all lead to a database that can grow quite large. I’ve seen something as simple as the addition of 4 indexes take a single-server database from absolutely overloaded to bored stiff. It is usually not quite that easy. Sometimes the answer is removing indexes, sometimes the answer is moving part of the workload from the app servers to the database server or vice versa.
The point is that an application that is really written with database performance in mind, a data model that is well thought out and well tested, and constant vigilance over changes to these is the single largest factor in scaling. I can partition a database across a thousand servers or put it on a machine with hundreds of cores, and if the work being done is not efficient the database won’t scale.
Scaling Up
Scaling up is the traditional RDBMS answer to scaling. Scaling up basically means making your main database server bigger. Once you’ve addressed data model and workload issues, often improving the hardware you’re running on by getting faster disks, more cores, and more memory have been the first way we’ve scaled databases. Yes, application can scale by running on dozens or hundreds of servers, but investing in a decent database server is often a way to go.
This often means dedicated hardware instead of VMs and containers. Most of the time, the overhead of VMs and containers is worth it for the ease of management, but if we’re scaling up our most important database or two, it may be worth going to bare metal for it to get that extra bit of performance for it. These days, in my opinion, the vast majority of databases do not need dedicated hardware, but there are a few that do.
For Db2, this might mean a power server running AIX, though larger linux servers can also work well. Db2 can take advantage of parallelism at many levels to really make use of the larger system to handle the workload. At the highest end, this may even involve a mainframe. I’ve never worked on mainframes, but I’ve heard that the rate of work you can get done on a single system or cluster is the gold standard. These systems are often expensive, and are often only available from certain hosting providers or on-site. Not every cloud provider even has decent offerings for dedicated hardware, let alone mainframe options.
Scaling Out
Scaling out can also be done, but often requires application changes to work.
With MySQL the first line of defense is often having replicas in the same region and zone as the writer, and spreading read activity across those replicas using something like proxySQL. If you’re not familiar with database workloads, most of them are very heavily reads. 20% writes is an insanely high write workload when compared to most other database workloads. Often spreading reads out is enough, at least for a while.
If there is a clear sharding key available, using many database servers as if they were one is a reasonable option. In the MySQL space, there are tools like Vitess that can make this easier than rolling your own. The effort here is either in sharding or in somehow getting the same data synchronized in a reasonable way across many servers.
Db2 offers a shared-nothing partitioned database through the DPF feature that works well for analytics or warehousing workloads that allows you to spread the same database across many servers. This is actually transparent to the application, but involves some more complicated work around query tuning and administration. Db2 also offers PureScale, which is a shared-disk solution for more transactional workloads.
Summary
Do relational databases scale? Yes, absolutely, when you use them properly. Do we fail to use them properly? All the time. One thing that still continues to amaze me is how well relational databases perform on the small end. You can do really unreasonable things to that database, and it still works quickly. The database scales – an Unreasonable workload doesn’t scale.
boy, do we have some customers who could do with reading this. We are still having discussions with one of our larger clients who are hoping to solve performance issues that we have been reporting to them for years (!!), and which have been ignored and is now biting them as the data and transaction growth has reached tipping point. Management solution; “let’s get more CPU”.
The old fashioned expression in the UK is “you can’t make a silk purse out of a sows’ ear”. There are some cruder versions too, but I’ll keep those to myself.