Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Let's assume a successful web Spring application running on a MySQL or PostgreSQL database. The traffic is becoming so high and the amount of data is becoming so big that a distributed database solution needs to be implemented to address scalability issue. Let's also assume this application is using Hibernate and the data access layer is cleanly separated with DAOs.

Ideally, one should be able to add or remove databases easily. A failback solution is welcome too.

What would be the best strategy to scale this database? Is it possible to minimize sharding code (Shard) in the application?

share|improve this question
1  
Why is your application slow? Write contention? Lots of data -> slow queries? Simply loads of queries? Indexing, read-only slaves, caches can be better answers in some circumstances- sharding/replication is not the only answer. –  alex Sep 30 '12 at 19:43
    
Write contention, loads of queries mostly. –  JVerstry Sep 30 '12 at 21:23
    
If you can, shard –  alex Oct 1 '12 at 21:58
1  
Intelligent routing by services with cache to hold routes comes to mind. Hopefully your data is shardable. If not and you know no functional languages it's time to start learning Haskell. Pure functional design will teach you a lot about how to pass data around without having it persistently related which is what sharding causes you to lose if you can't segregate data without cutting relations. –  Jimmy Hoffa Oct 21 '12 at 19:04
add comment

2 Answers

up vote 3 down vote accepted

Firstly, I'd look at whether there was write-intensive data that could be moved out of the relational database altogether. In a system I currently work on, we have identified quite a lot of pointless write load from writes to tables which record user activity; we use that data for analytics and user support, so it doesn't really need to be in the main OLTPish database; it could be in a flat file, a NoSQL store, etc. I suspect that this is true of quite a lot of user-centric data in many applications - user preferences, profile, history, etc. If it's a structured dataset which is only ever stored and retrieved by key, rather than being searched by value, then it could go in some simpler, cheaper, more write-scalable, store, like Riak or something.

With that done, I'd look at what fraction of the load was reads. If it's at all significant (even 20%), move that to a read-only slave. MySQL and PostgreSQL both make it fairly straightforward to have one or more read-only slaves following a read-write master, and to use them as hot standbys for the master too.

However, splitting reads like this might be tricky; if you're doing things properly, then you will wrap every unit of work in a transaction, even if it's read-only, to guarantee a consistent view of data. Since you can't (AFAIK!) have a single transaction efficiently use two different databases (XA is not efficient), that means you have to be able to identify read-only transactions when they start, so you can send their queries to the read-only database. That will very likely require logic in the application code that isn't currently there, and might not be simple to retrofit. In our case, it was relatively simple: we send transactions from interactive user sessions to the read-write master, and transactions from our read-only REST API to the read-only slave. We could probably do a better job, by identifying read-only user requests, and sending those to the slave as well, but we haven't had to do that yet.

If you're not doing things properly, and are using no transactions, or implicit or explicit transactions around each query, then this is much simpler. A load-balancer between the app and the database can route the queries.

If segregating reads doesn't do the job, then it's time to look at having several machines handling writes. I have never done this, but we are talking about this a lot at my place of work, so I can give you my impressions. There are two ways to split writes: sharding, and multi-master replication of a shared database (I don't really consider sharding to be multi-master, because any item of data belongs to a single master). Neither of these are easy, which is why this is a step of last resort.

Multi-master is simpler from the application's point of view, because it doesn't care which master it talks to, but is less likely to help performance, because any write to one master still has to be applied by the others. It's only useful if the write traffic involves a lot of read work (eg inserts from selects, or you are using transactions and have units of work which do a lot of reading and a small amount of writing - which is not uncommon), and your multi-mastering operates at the row rather than the statement level.

Sharding is more complex. You will inevitably have to change your application code; you might even have to change it a lot. For example, if you split user profile data across multiple shards, you flat out lose the ability to do queries like "find me all the users with a Z in their name", because that crosses multiple shards. You have to do either some sort of map-reduce across the shards, or have a separate index for that field off to one side. You should be able to encapsulate this in your DAOs; where you might not is if some kinds of queries become so slow that you just can't afford to do them any more, and have to find different ways to solve the problems they solve. If your ORM has support for sharding (apparently Hibernate does), then that might help a lot, but it won't fundamentally change the problem.

Just how much of a problem this is will depend very sensitively on your access patterns. For a classic e-commerce system, for example, users make read-only access to catalogue data, which could therefore go in a single-master, many-slave database, and read-write access to their own order data, with no online access across multiple orders, which should shard very nicely. For a crowdsourced e-commerce system (like, famously, Etsy, or eBay), you have some users (shopkeepers) writing to the catalogue while others are reading from it. That makes sharding harder. For a social network application, where users are sharing content with others in a random meshwork, you will end up needing to do huge amounts of cross-shard work. Sadly, where I work, although our application is a financial system, it has many of the data characteristics of a social network; I'm not sure there is a productive way to shard it.

The good news about sharding is that if you can make it work, then it does offer a good shot at a linear speedup. To be honest, though, I suspect that the workloads that allow linear sharding are also the ones that would fit a NoSQL store well, because of that defining characteristic of not depending on cross-shard queries, so if you're going to do the work to do sharding, you might as well go the whole hog and put everything in whatever the hot NoSQL store today is.

Plus, I suspect that for an application of any complexity, the cost of the programmer and sysadmin time to introduce sharding may add up to more than the cost of simply buying some bigger iron.

So, yeah, if you can, don't shard. Stretch your existing model to see if you can get some more performance out of it first.

share|improve this answer
2  
Don't forget the longer you put it off with better SAN and more ram for db to live in, the harder it will be to demesh the system when it outgrows reasonable single master system. If your current usage growth rate patterns suggest you will eventually be incapable of servicing requests reasonably then sooner to start spreading the data the better. Though if you don't suspect to hit such a singularity, buy the hardware, expensive sure but cheaper than man hours. –  Jimmy Hoffa Oct 22 '12 at 1:06
1  
@JimmyHoffa: It's also worth thinking about whether you will ever actually get to the point where you need to shard. StackExchange serves 95 million page views a month off a non-sharded system; it's either a single master DB server, or one for StackOverflow and one for everything else, i forget. Some apps do get to the scale where they absolutely need sharding, but there probably aren't more than a few thousand such apps on the entire planet. It is overwhelmingly likely that neither mine, yours, or the OP's are among them! –  Tom Anderson Oct 22 '12 at 8:22
    
Or, as the saying goes, I'm Going To Scale My Foot Up Your Ass. –  Tom Anderson Oct 22 '12 at 8:24
    
That's what my last sentence was saying, don't bother unless you know you'll need it. –  Jimmy Hoffa Oct 22 '12 at 13:03
    
@JimmyHoffa: True dat. –  Tom Anderson Oct 22 '12 at 14:16
add comment

The best strategy might be to use an existing database sharding solution such as dbShards. This will allow you to move to a sharded solution without having to make any significant code changes to your application (dbShards will make the shards look like a single database as far as your application is concerned). While there is a cost to the software it could well be cheaper than building out your own sharding solution.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.