Horizontal Scaling of SQL Databases? 222
still_sick writes "I'm currently responsible for operations at a software-as-a-service startup, and we're increasingly hitting limitations in what we can do with relational databases. We've been looking at various NoSQL stores and I've been following Adrian Cockcroft's blog at Netflix which compares the various options. I was intrigued by the most recent entry, about Translattice, which purports to provide many of the same scaling advantages for SQL databases. Is this even possible given the CAP theorem? Is anyone using a system like this in production?"
Re:Relational stuff scales (Score:5, Informative)
Postgres seems to not charge extra for that.
Wow (Score:5, Informative)
I didn't expect we'd be on Slashdot just yet. I'm Michael Lyle, CTO and cofounder of Translattice.
With regards to the original submitter's question, we'd love to talk to him. How much we can help, of course, depends on the specific scenario he's hitting.
What we've built is an application platform constituted from identical nodes, each containing a geographically decentralized relational database, a distributed (J2EE compatible) application container, and distributed load balancing and management capabilities. Massive relational data is transparently sharded behind the scenes and assigned redundantly to the computing resources in the cluster, and a distributed consensus protocol keeps all of the transactions in flight coherent and provides ACID guarantees. In essence, we allow existing enterprise applications to scale out horizontally while keeping the benefits of the existing programming model for transactional applications, by letting computing resources from throughout an organization combine to run enterprise workloads.
Current stacks are really complicated, multi-vendor, and require extensive integration/custom engineering for each application install. We're striving to create a world where massively performing infrastructure can be built from identical pieces.
hbase is an option to NoSQL and Cassandra. (Score:4, Informative)
I recently read that someone moved their large operation from Cassandra to Hbase, a hadoop file system. http://hbase.apache.org/ [apache.org]
HBase is the Hadoop database. Use it when you need random, realtime read/write access to your Big Data. This project's goal is the hosting of very large tables -- billions of rows X millions of columns -- atop clusters of commodity hardware.
HBase is an open-source, distributed, versioned, column-oriented store modeled after Google' Bigtable: A Distributed Storage System for Structured Data by Chang et al. Just as Bigtable leverages the distributed data storage provided by the Google File System, HBase provides Bigtable-like capabilities on top of Hadoop. HBase includes:
Convenient base classes for backing Hadoop MapReduce jobs with HBase tables
Query predicate push down via server side scan and get filters
Optimizations for real time queries
A high performance Thrift gateway
A REST-ful Web service gateway that supports XML, Protobuf, and binary data encoding options
Cascading, hive, and pig source and sink modules
Extensible jruby-based (JIRB) shell
Support for exporting metrics via the Hadoop metrics subsystem to files or Ganglia; or via JMX
HBase 0.20 has greatly improved on its predecessors:
No HBase single point of failure
Rolling restart for configuration changes and minor upgrades
Random access performance on par with open source relational databases such as MySQL
Re:you're doing something wrong (Score:4, Informative)
And that's what Translattice does, actually: for the database part of the system, we transparently shard large tables behind the scenes, and figure out how to store it to the computing resources available taking into account historical usage patterns and administrators' policies on how data must be stored (for redundancy and compliance purposes). A different population of nodes is used to store each shard and the redundancy is effectively loosely coupled, so when a failure or partition occurs, the work involved in re-establishing redundancy is fairly shared over all nodes. This provides linear scalability for many workloads and better redundancy properties, and can also as a side benefit position data closer to where it's consumed.
When it comes time to access the data, the query planner in our database figures out how to efficiently dispatch the query to the minimal necessary population of nodes, introducing map and reduce steps to provide for data reduction and efficient execution.
All of the table storage is directly attached to the nodes, eliminating much of the need for a storage area network and scaling beyond where shared-disk database clusters can go.
Voltdb (Score:1, Informative)
Have you looked at voltdb ? http://www.voltdb.com .
My 2 cents.
Re:Call me skeptical (Score:5, Informative)
A lot of people don't understand how a database really works, so they do it horribly wrong. As a result, it's dreadfully slow. So they go and use some key/value lookup system because "they're fast". There you often get one of two things:
They still don't understand the problem, so they recreate it yet again. If you don't understand what's wrong with reading an entire table with a million records, and discarding all but 5 of them client-side, then replacing the SQL DB with a key/value system just isn't going to make things better.
Or, they improve performance, but since they don't understand what ACID is for, they eventually end up with weird inconsistencies. In some cases this might be acceptable, but you really don't want to see it happening in an order tracking system.
The sickening feeling people get is not because it's a competitor. In a large part it isn't a competitor, but a different class of system with different tradeoffs. The sickening feeling comes from seeing people not understand what they're doing, and then run towards the latest technology because it's what $BIG_COMPANY uses without understanding it any better, and generally making an even bigger mess.
The performance of specialized solutions like key/value systems doesn't come from magic. They're not really new, and don't use anything very groundbreaking. They simply use different tradeoffs at the cost of sacrificing quite a lot of what is present in a RDBMS. It's important to understand first whether you can really afford to discard those things, because if you can't, it's either not going to work right, or you'll have to graft all that you removed on top of it anyway.
Re:MySQL scales just fine. (Score:2, Informative)
Re:Is it a technical or a budget problem? (Score:3, Informative)
I like PostgreSQL a lot. We use it now as the database that runs all of our company's software and those we deploy to clients. It's overkill for our point of sale product, but it's fast and stable. But PostgreSQL has lacked some features that made deploying it for very large databases not that attractive. There were three features that kept it out of the running: Lack of built in clustering, lack of Hot-Standby, no vender that could support both hardware and software under one roof (and could be sued if shit hit the fan). PostgreSQL 9 just addressed two of these drawbacks.
That last criteria was probably the single biggest factor for these organizations. Where I went to college and got my first jobs out of school had a lot of AS/400's. Three major Fortune 1000 companies used DB/400, all the colleges used them, all the local hospitals used them, and IBM had an office in the town of 150k people staffed with about 50 AS/400 techs. Most of whom worked on site at the folks who had 200 - 500 AS/400's. (Estimate Total number of AS/400's in the area at the time was something like 1500)
Re:Call me skeptical (Score:3, Informative)
So you've not worked on anything like that, where actually someone knew how to make a relational database.
Ty very much, but our DBs are running fine with over 100million rows that's almost purely textual data being searched (relational full text searches) and 500+ q/s, and double that in hits per sec with a single modern server still having plenty of free resources.
Ok that doesn't change that much, but then we got this one thing which over 100x the size, runs even way heavier searches (exponentially more complex), and updates almost constantly and public uses it from just 2 nodes, and this has been designed to have over 100 pageviews per sec.
All of that runs on top of MySQL and standard hardware. (No SSD, no gigantic amounts of ram, no gigantic amounts of HDDs etc.)
And the most expensive server was 5500eur, the more complex one uses ~3½k eur blades.
Re:you're doing something wrong (Score:3, Informative)
I meant heights of performance and size, but admittedly, that was a poorly chosen phrase. But yes, you scan scale sql very wide.
Rick Cattell's work on scalable datastores (Score:5, Informative)
I recently came across Rick Cattell's site [cattell.net] which addresses just the questions you're asking.
Rick Cattell has written an excellent comparison guide of horizontally scalable datastores [cattell.net] of different types (RDBMS as well as a variety of NoSQL systems).
Cattell has also written an academic paper with database expert Mike Stonebraker, which weighs the system design factors required to make a datastore scalable [cattell.net].
Executive summary of Cattell's work: although NoSQL may be a huge fad, the things that make a datastore scalable can be implemented in SQL RDBMS systems as well. Also, implementing do-it-yourself ACID in NoSQL systems is extremely difficult and error-prone, and is a significant advantage of most RDBMS systems. Stonebraker is the author of VoltDB, which is an open-source RDBMS designed for horizontal scalability, but they give a very fair and thorough look at competing datastores as well.