Can you recommend a database that scales horizontally?

Question

Generally the database server is the biggest, most expensive box we have to buy as scaling vertically is the only option. Are there any databases that scale well horizontally (i.e. across multiple commodity machines) and what are the limitations in this approach?

Answer 1

There are storage techniques such as JavaSpaces (or a commercial implementation such as Gigaspaces) which provide highly scalable, fast & secure access to objects.

There are also distributed cacheing systems such as memcached, which offer a similar approach.

Of course, neither of these are true databases, but they are things that can work in conjunction with databases to offer a large amount of horizontal scalability, given a suitable architecture. The real problem is that if you want all of the ACID goodness that comes with a database, there are certain unavoidable performance penalties. The only way out is to figure out the bits where you don't need ACID, and use other technologies to service those bits.

Answer 2

Netezza and other datawarehouse appliances scale this way, but they are not good for OLTP and web app workloads.

Answer 3

The Oracle route for scaling across multiple machines is called Real Application Clusters (Oracle RAC). There's no end of documentation on this elsewhere; you might try starting at http://www.oracle.com/database/rac_home.html.

Answer 4

Oracle RAC -- Real Application Cluster

This works nicely, you just add boxes to your cluster. You can fail over from one box to the other. It's not replication, all the boxes are part of the same logical unit.

It's pretty spendy, of course.

Answer 5

Oracle RAC is the Rolls Royce of databases allowing extra hardware nodes to be added relatively easily and hardware failover.

However, your commodity hardware costs will be dwarfed by the licence costs.

Why dod you feel you need horizontal scaling. A multi CPU core server with 40GB RAM and SAN storage can support very sizeable DB installation.

Can you provide any sizing and expected activity information to allow better understanding of your problem?

Answer 6

Don't worry, good solutions are coming!

Couchdb and Hypertable are open source and still in alpha, but they are clearly designed to make scaling on commodity software simple. They work pretty well, and may change how you think about databases.

Also, if it's okay to let someone else do the distributing for you, Google AppEngine and Amazon SimpleDB are extremely cheap distributed database services, though they're both in beta right now so strict limitations are imposed.

Answer 7

Oracle Real Application Clusters. If you want the best then take a look at it.

Answer 8

If you seriously think you will out scale a decent multicore "Big Iron" box, then you think about partitioning your data. This is a good, database agnostic way to scale out.

All databases which horizontally will come at a serious cost.

Unless you have mega $$'s to throw at the problem, forget about RAC. While its very good, its VERY expensive once you scale beyond 2 nodes.

Answer 9

If you do go down the RAC route it's worth remembering that it doesnt scale horizontally forever. Even the sales guys admit 90% of rac customers are 4 nodes or less. Once you go more than that you get diminishing returns. So rac may work for you, but it's not guaranteed to be the answer.

Answer 10

MySQL: http://www.mysql.com/why-mysql/scaleout.html

Limitations are that it works best with read-mostly workloads. You typically have one 'master' that receives all the writes, and many 'slaves' that replicate the writes. Then you distribute the reads over all the databases.

MySQL replication is asynchronous, so you will probably have to deal with time lag problems (you write to the master, and then read from a slave before the write has been replicated).

Answer 11

Oracle RAC is not horizontally scalable at all, because all Oracle instances share the same data storage. Yes, with SAN stuff u can get a large size DB, but it's just not scalable at all. In other words, Oracle RAC is still a scale-up approach. So for scaling-out or horizontally scaling, you have to partition your data by function that means put different groups of tables in different databases; or partition your data per table that means partition one table into multiple subtables with the same schema but store in different databases. In this way, you get a scaling-out solution. There are many resources on that. Sharding has been a buzz word for a while in web 2.0 website architecture blog sphere. Because Sharding is not directly supported by database itself, you have to build your own solution. But as I said, there are many lessons already. For oracle, partitioning table is possible. For mysql, check this question