In a recent post to his blog on high scalability, Todd Hoff explains the ideas behind database sharding, including the advantages and disadvantages of this approach to scaling the data tier.
Various techniques of scaling the data tier have received a lot of attention lately. While databases are more scalable than many marketers would have us believe, some enterprises clearly need to scale beyond the confines of a single, powerful database server. When deciding to scale the data tier "horizontally," typically across several commodity-style boxes, ongoing ease of maintenance and costs are important considerations.
In a recent blog post, An Unorthodox Approach to Database Design : The Coming of the Shard, Todd Hoff explains the deceptively simple concept of database sharding, which is a form of data partitioning across multiple database servers. Hoff bases his account on a pair of articles by Dathan Pattishall who, in turn, writes of his experiences of scaling the Friendster and Flickr architectures.
According to Hoff,
While working at Auction Watch, Dathan got the idea to solve their scaling problems by creating a database server for a group of users and running those servers on cheap Linux boxes. In this scheme the data for User A is stored on one server and the data for User B is stored on another server. It's a federated model. Groups of 500K users are stored together in what are called shards.
The notion of logically partitioning data across multiple database servers seems simple enough. However, this technique is not without controversy, and may not work for every architecture.
Among the advantages of this type of partitioning, according to Hoff, are higher availability (the failure of a single node will make inaccessible "only" some users' data, for example), faster queries, and more overall bandwidth for the data tier.
Among the negatives of this sort of sharding, however, is the difficulty of making joins across shards, and the whole notion of managing just what data should be allocated to what shard. Many developers and database administrators have learned, often from bitter experience, that manually performing such a balancing act may be a task suitable for companies with large development resources, and that writing such infrastructure code by hand may be overall more expensive for smaller organizations than purchasing database management software that can handle its own data partitioning.
In what situations do you think such application-level data placement is worth the effort? And when do you think an organization is better off trusting the database server with data placement?
I am not a database administrator but it seems to me that a good database should be able to use partitioning to do essentially the same thing without the drawback of not being able to do joins. Does anybody have experience in having the database partition data to different servers? Is there any good explanation as far as scalability of MySQL, PostgreSQL, Oracle, DB2 in a partitioned and replicated environment (i.e. tables partitioned over several servers + each server replicated a couple of times).
In case of Oracle one could use Real Application Clusters (RACs) and not have to worry about rewriting the application or hand-rolling the database tier scalability logic into application design. Of course, this will not be a low cost solution. Perhaps not all that unaffordable for FaceBooks of the world either ;)
There are many applications out there that can benefit from using database shards. There will be additional cost, in terms of time and resources, associated with hand-rolling this kind of unorthodox solution.
It will be great to hear stories (especially from application manageability/maintainability angle) from people who have successfully/unsuccessfully tried this approach.
> In case of Oracle one could use Real Application Clusters > (RACs) and not have to worry about rewriting the > application or hand-rolling the database tier scalability > logic into application design. Of course, this will not > be a low cost solution. Perhaps not all that unaffordable > for FaceBooks of the world either ;)
I wondered about Oracle's RAC solution when I read this post. Having no real world experience with RAC, does it require a shared disk configuration, e.g. multiple servers sharing the same disks through a SAN? The Oracle documentation left me unclear.