SQL Databases Don't Scale

SQL Databases Don't Scale

databases

Mon Jul 06 12:51:22 -0700 2009

A question I’m often asked about Heroku is: “How do you scale theSQL database?” There’s a lot of things I can say about using caching,sharding, and other techniques to take load off the database. But theactual answer is: we don’t. SQL databases are fundamentallynon-scalable, and there is no magical pixie dust that we, or anyone, cansprinkle on them to suddenly make them scale.

What Is Scaling?

To qualify as true scaling, I believe a technique must fit the following criteria:

1. Horizontal scale: more servers creates more capacity.
2. Transparent to the application: the business logic of the app should be separated from concerns of scaling server resources.
3. No single point of failure: there should be no one server which, if lost, causes downtime of the application.

As an example from the hardware world, a RAID5 disk array offers true scaling:

1. Horizontal scale: you can run a RAID5 with 4 disks, or 12, or 20; more disks gives you more drive space and (generally) better performance.
2. Transparent to the application: applications using the RAID as a single device. My text editor doesn’t care that the file it is saving or loading is split across many disks.
3. No single point of failure: you can pop out a drive and the array will continue to function (albeit with a performance hit, known as “degraded mode”). Replace the drive and it will rebuild itself. All of this happens without the applications using the RAID being aware any interruption of the functioning of the disk storage.

So let’s take a look at some of the techniques used to “scale” SQLdatabases, and why they all fail to achieve the criteria above.

Vertical Scaling

One way to scale a SQL database is to buy a bigger box. This isusually called “vertical scaling.” I sometimes call it: Moore’s lawscaling. What are the problems with this?

• It’s a complicated transaction that usually requires manual labor from ops people and substantial downtime.
• The old machine is useless afterward. This wastes resources and encourages you to overprovision, buying a big server before you know you’re really going to need it.
• There’s a cap on how big you can go.

I know plenty of folks who have bumped their head on the last point(usually somewhere around a 256-core Sun server) and now they arepainted into a corner. They find themselves crossing their fingers andhoping that they’ll stop growing - not cool at all.

So yes, you can put a SQL database on a bigger box to buy yourselfmore headroom. But this fails point #1 on my checklist for true scaling.

Partitioning, aka Sharding

Sharding divides your data along some kind of application-specificboundary. For example, you might store users whose names start with A-Mon one database, and N-Z on another. Or use a modulo of the user id bythe number of databases.

This requires deep integration into the application and carefulplanning of the partitioning scheme relative to the database schema andthe kinds of queries you want to do. Summary: big pain in the ass.

So while sharding is a form of horizontal scaling, it fails point #2:it is not transparent to the business logic of the application.

The deeper problem with sharding is that SQL databases are relationaldatabases, and most of the value in a relational database is that itstores relationships. Once you split records across multipleservers, you’re servering many of those relations; they now have to bereconstructed on the client side. Sharding kills most of the value of arelational database.

Read Slaves

MySQL’s killer feature is easy configuration of master-slavereplication, where you have a read-only slave database that replicateseverything coming to the master database in realtime. You can thencreate a routing proxy between the clients and your database (or buildsmart routing into the client library) which sends any reads (SELECT) toone of the read slaves, while only sending writes (INSERT, UPDATE,DELETE) to the master.

Postgres has replication via Slony,though it’s much more cumbersome to set up than MySQL’s replication.Years ago I even did basic master-slave replication with a 50-line Perlscript that read from the query log and mirrored all write queries overto the slave. So replication is possible just about anywhere, withdiffering degrees of setup and maintenance headaches.

The read slave technique is the best option for scaling SQLdatabases. It qualifies as horizontal scaling on the read side, and istransparent to the application. This is the technique that many of thelargest MySQL installs use.

And yet, this is still ultimately a limited technique. The masterserver is a bottleneck, particularly on write-heavy applications. And itfails point #3 on the true scaling definition: there is a single pointof failure. This is a problem not only when the database fails, but whenyou want to perform maintenance on the server. Promoting one of theread slaves to master allows you to recover relatively quickly, but thisswitcharoo requires hands-on attention from the sysadmins.

And while it qualifies as horizontal scaling for read performance, itdoes not qualify for writes and capacity. Horizontal scaling shouldspread out both the load of executing queries and the data storageitself. This is how you can grow the total storage space of a RAID: addenough disks, and you can get a RAID which has a much greater capacitythan any single hard drive on the market. Read slaves require completecopies of the database, so you still have a ceiling on how much data youcan store.

A Long-Term Solution

So where do we go from here? Some might reply “keep trying to makeSQL databases scale.” I disagree with that notion. When hundreds ofcompanies and thousands of the brightest programmers and sysadmins havebeen trying to solve a problem for twenty years and still haven’tmanaged to come up with an obvious solution that everyone adopts, thatsays to me the problem is unsolvable. Like Kirk facing the KobayashiMaru, we can only solve this problem by redefining the question.