Horizontal Scaling of SQL Databases?

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?"

Comments

XML

[Anonymous Coward]

Just store everything in a big XML file.

Re:XML

[Anonymous Coward]

XXXML

What limitations are you running into?

[Anonymous Coward]

It would be a lot easier to talk about solutions if you said which limitations you run into.

Is your dataset to large (large tables), are you having to much joins, too many transactions per second? In short, what is the problem we're trying to solve here?

Re:What limitations are you running into?

[Anonymous Coward]

It would be a lot easier to talk about solutions if you said which limitations you run into.

Is your dataset to large (large tables), are you having to much joins, too many transactions per second? In short, what is the problem we're trying to solve here?

My money is on "No one here likes SQL" and "There aren't any exports on RDBMs to help us get things set up properly".

Re:What limitations are you running into?

[DarkOx]

I would have to agree, its really hard to imagine a "start up" can't make anything work on traditional SQL RDBMS(es). If you put the right hardware underneath it even SQL Server 2000 (64bit anyway) will scale just fine to terabyte size databases at thousands of transactions per second. That is not on impossible hardware for a successful start to buy either, we are talking a dedicated storage controller with gigabyte or so cache and few dozen SAS drives. I know I have worked on such projects.

You need the schema right, and if its more reads than writes you might even de-normalize a little and you will need to partition the data appropriately, but it can be done. This is why realDBAs still make the big bucks. There is a lot to know in that domain. You probably should hire someone who is an expert on whatever stuff you are using now to consult before you go down the path of NOSQL. All you told us is you are a growing start up with is not much to go on but without know what you are doing its hard for me to believe you are doing anything on a scale that can't be done well with a relational database; but maybe I am wrong and maybe you are doing something huge. Remember as soon as you go down the NOSQL path you are going to have to be doing a great deal of heavy lifting because the quantity of libraries and off the shelf stuff out there is not great.

Relational stuff scales

[Anonymous Coward]

Learn partitioning principles, get a database product that does partitioning properly, learn normalization, never worry again about not being able to scale with relational databases. It just requires some real skills but relational databases really do scale all the way up.

Re:Relational stuff scales

[ani23]

Partitioning does complicate backups and HA/DR scenarios as the entire system is dependent on all machines being up and running. Also in most commercial db's (I know about db2) this feature takes you to the enterprise tier of software which is usually very expensive.

Re:Relational stuff scales

[h4rr4r]

Postgres seems to not charge extra for that.

Re:Relational stuff scales

[atomic777]

Right. "We are hitting limitations in what we can do with X" means they cannot solve an underlying difficult problem Z, and are hoping that by swapping X with magic fairy dust Y, that somehow Z will go away. Sales people owe their BMWs to this simple fallacy.

Consider scaling via other layers?

[mlts]

Another idea is to scale using other layers, if there are problems at the SQL server level.

At the lower areas, one can go with a mainframe (parallel sysplex) and have geographically separate pieces of hardware acting coherently.

At the higher layers, have the app use multiple SQL servers and handle the redundancy in this layer.

Call me skeptical

[Kjella]

Call me skeptical but there are companies out there with massive amounts of data in relational databases, if you as a setup are "constantly hitting limitations" you're either a very odd startup or using it very wrong. As long as the volume is small you can make almost anything happen on SQL. Hell, most small business I've known run mostly on Excel. Somehow I don't see a startup needing NoSQL unless they specialized in processing huge amounts of data, in which case trying to make slashdot work on your core business seems stupid. But maybe I missed something...

Re:Call me skeptical

[Squeebee]

Agreed, we have massive sites serving millions of requests a day using Open Source relational databases and yet it seems everyone wants to use NoSQL because it's the hip new thing.

Naturally I start thinking of this: http://xtranormal.com/watch/6995033

Re:Call me skeptical

[craftycoder]

My thoughts exactly. I have a couple 100 GB in a MsSQL database with extensive normalization and it is lightning fast. It's all about indexes and appropriate design.

Re:Call me skeptical

[Ruke]

I think the real problem is that people are seeing inconsistencies in their growing systems, and looking to grow to a system that doesn't have inconsistencies. Which is basically impossible. It's not that the big players don't ever have inconsistent data - Amazon's Dynamo relies on reaching a quorum, rather than a totally consistent state. Rather, the big players have a much better idea of exactly how inconsistent their data can be, while still giving their system good performance.

Re:Call me skeptical

[RobertM1968]

Agreed... the biggest limitation I see with SQL (My, DB2, Postgres anyway... found plenty in MS) are people who don't know how to lay out a database, people who don't know how to install and configure the server daemon(s), people who have no idea how to properly select appropriate hardware, and people who don't know how the heck to do a query (as a for instance, I worked on some code done by someone else, where on massive records, they were always selecting "*" instead of the needed or anticipated values. Big waste when one needs (by ID#) last and first name and selects a whole row instead - then wonders why it's not scaling upwards).

Re:Call me skeptical

[PRMan]

MySpace is also slower than maple syrup in January.

Re:Call me skeptical

[Cylix]

I just select * from * and then sort it out with grep and cut.

Re:Call me skeptical

[nschubach]

It's rather fast now that nobody uses it anymore.

(sorry, I couldn't resist.)

Re:Call me skeptical

[Anonymous Coward]

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...

Call me skeptical but there are companies out there with massive amounts of data in relational databases, if you as a setup are "constantly hitting limitations" you're either a very odd startup or using it very wrong.

Agreed. My knee-jerk response once I saw the sentence in the article's summary was "No, you're not [hitting the limititations in what we can do with relational databases]. You're hitting the limits of what you know about performance tuning and scalability with the relational databases you have.

NoSQL, BigTable, and Cassandra are designed for extremely fast key-value pair lookups over enormous datasets (as one poster puts it, > exabyte-sized.) With these solutions alone, you lose:

a) ACID
b) FK relations/semantic modeling

which is huge. (If you don't know why losing ACID and FK relations is such a bad thing, you might as well stop here, hit the library for a good database textbook, read and understand it, then come back in 3-6 months and rephrase your question.)

If you *really* have > exabyte-sized data in a table or two and you really are hitting the limits of what current RDBMS engines can provide (and if you haven't looked at DB2 or Oracle, maybe you should - their optimizers are better than Postgres or (laugh) MySQL), you'd probably want to work around (a) and (b) by using some sort of enterprise transaction management system (e.g. JTA if you're using Java EE), then incorporate the tables you need into NoSQL, Cassandra, or BigTable by providing middleware to interface with these hash stores that provides support for two-phase distributed commit and fakes the FK relationship to cross datastore boundaries.

And if you think that doesn't sound too bad, think again: what I just described is a HUGE undertaking. Are you really sure you haven't exhausted all other options to stick with proven database technology that performs well up to exceptionally large-sized datasets? Maybe it's time to hire, you know, a real DBA - this type of analysis is what they get paid the big bucks for.

Re:Call me skeptical

[vadim_t]

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:Call me skeptical

[Squeebee]

Would you have preferred I have said bazillions?

Re:Call me skeptical

[Natural Join]

The small startups are using NoSQL because there is, more and more, a push in the web app market to store data which does not fit into any schema.

There is no such thing as "data which does not fit into any schema", just like there is no such thing as data which cannot be encoded into binary. All data necessarily has a schema. However much or little of the schema you may choose to model in your (SQL or other type of) schema is, like the rest of software engineering, a design tradeoff.

The various NoSQL approaches do not solve the full generality of data management problems the way SQL databases do. They are narrower in scope, and as is generally the case, they can achieve better performance by virtue of doing less. They can be much faster with certain data access paths, but at a cost of the fact that other data access paths become prohibitive.

The frustrating thing for many of us is that the NoSQL spin on data management is about where mainstream data management was in the 1960s. As the field matured, it learned many important lessons, all of which are now being tossed out the window by people saying "oh we don't need that" but of course, they just haven't needed it yet. As these problems become apparent to them, they will spend the next decades of their lives reinventing what the data management field figured out in the 80s and 90s. Until then, they'll be making beginner mistakes, like thinking that their data somehow doesn't fit into any schema.

Re:Call me skeptical

[Skal Tura]

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:Call me skeptical

[Klinky]

NoSQL is not just key-value lookups. Take a look at Redis or MongoDB, there are novel ideas in both of them & yes they do bring new things to the table. They are NOT memcache. I am also not sure people are "sacrificing a lot of what is present in an RDBMS" by choosing NoSQL over an RDBMS. I think your gripe is with people who don't know what the hell they're doing, but you project that griping on to NoSQL in general. There are some things that RDBMSs are really good at, there are some things RDBMSs aren't so great at. The huge majority of people in the NoSQL communities and the users of these solutions know that loading a million objects client-side and discarding all but 5 is stupid & no one would suggest that is a failing of either RDBMS or NoSQL solutions, but squarely on the user.

I would have to say that NoSQL is more relatable to how people think about objects and their relation to each other. People don't easily boil their objects down into relational tables and how each of those tables should interact with each other. This takes skill & talent & can be a bit of a pain to dive into which is why we have a bunch of ORM solutions which add another layer of cruft on top of RDBMSs. NoSQL is basically getting rid of the ORM & the tables(though some still use table-like structures). For apps that use that would normally use ORM(a lot of web apps) extensively that's great. For newbies who don't have years and years under their belt designing, tweaking normalization, sharding/partitioning it can be easier to pickup. Some of the NoSQL solutions have clustering/horizontal scaling and/or replication built-in, with no or very little schema/query changes required.

So for some NoSQL will be a better solution and for others a RDBMS will be a better solution. I wouldn't knock either. Just because you can do something in an RDBMS doesn't mean it's better than a NoSQL solution & visa versa.

Is it a technical or a budget problem?

[ducomputergeek]

Given my past 12 years between working at consultancies and start ups, I've seen this a few times. It's usually not a technical hurdle, it's a "We can't solve this problem within our budget" problem. Either by going out and hiring someone who is an expert at performance tuning with their DB of choice or moving from certain db's to real databases that could handle the work like MSSQL, DB2, Oracle, or in some cases Teradata if dealing with Data warehousing.

Because I've worked around some very large database installs in my day. Every time the scaling question/problem came up, it was solvable with RDBMS's, but the solution wasn't cheap.

Re:Is it a technical or a budget problem?

[PRMan]

My experience is that there is a lot you can do that is very cheap.

One time, I walked into a mortgage company (I'm a developer, not a DBA) and they were complaining that they couldn't run a required government report breaking down their fee codes because it would time out after 2 minutes. The table had millions of records. I looked at the table and immediately noticed that they didn't have an index on fee code, which the report was trying to sort and total by. I told the manager that I would add an index on the fee code column after hours and run the report. He wasn't sure it would work so he said, "Go ahead and add it now."

I added the index (which took about 30 seconds) and ran the report again. It finished in 45 seconds.

I looked at the report. Whoever wrote it for them was concatenating strings all over the place. Millions of them. I switched the app to StringBuilder using a search-and-replace.

I ran the report again. 8 seconds. In less than an hour I took a report that wasn't finishing in 2 minutes down to 8 seconds. That wasn't expensive for them and it wasn't hard to do.

At another client, they were complaining about database slowness and the DBA wasn't having much luck fixing it. They fired him and asked me to look at it. I simply recorded a profiler log (a little slower for that day, but it's already dog slow so who would notice), found the longest duration and most common queries and then searched the source code repository and rewrote them. Many of these queries were cross-joins, missing indexes on the joined field or other really obvious problems. One was doing a data conversion on every record instead of data converting the passed in input once. It took me about 2-3 days to solve massive slowness problems. At the end, the employees were saying, "I'm glad they finally bought a new database server." This was at one of the country's largest mortgage companies with tens of millions of records in the database. And the fixes should have been brain-dead obvious to anyone with a few years of SQL experience.

Re:Is it a technical or a budget problem?

[Hoi Polloi]

I wish most tuning efforts only required fixing glaring index issues. You eventually find yourself dealing with large dbs with all the basic tuning done and now they want to get app X to return in 8 secs instead of 10. Then you go down the rabbit hole of initialization params, hints, etc. Sadly design considerations are almost always off the plate at this point.

Re:Is it a technical or a budget problem?

[doofusclam]

There are a few other players in the field next to teradata, but when you move to that format there is nothing that would be associated with the word cheap.

However, generally when it gets to that level of field the amount of data in storage usually makes it very obvious.

In some scenarios, we have avoided going to those rather massive solutions by really digging down and seeing if we really needed to store everything.

In a previous job at the start of my career, my company bought a Teradata system which came with the requisite sharp suited consultant, who told us how to lay out the DB schema.

Being Teradata all the hashed indexes were in vogue, so it was lightning fast.

Until the day they realised the users mainly did substring searches, which don't really work on a hashed index. Table scans a plenty = unhappy users.

It doesn't mean a RDBMS is bad, it means that technology misapplied always sucks.

Re:Is it a technical or a budget problem?

[ducomputergeek]

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)

you're doing something wrong

[Surt]

"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. "

Relational databases scale to pretty amazing heights. The notion that you are hitting some limit of relational databases at a startup stretches the imagination. I mean, really, you've already hit exabyte data sizes? That's typically where relational starts to struggle.

You really need to define your problem with much greater specificity to get a valuable answer.

Re:you're doing something wrong

[Stradenko]

Relational databases scale to pretty amazing heights

Horizontally?

Re:you're doing something wrong

[camperdave]

Relational databases scale to pretty amazing heights. The notion that you are hitting some limit of relational databases at a startup stretches the imagination. I mean, really, you've already hit exabyte data sizes? That's typically where relational starts to struggle.

You really need to define your problem with much greater specificity to get a valuable answer.

Given that the title of the story is "Horizontal Scaling of SQL Databases?" the notion that that relational databases are able to scale to pretty amazing heights is irrelevant.

You really need to define your problem with much greater specificity to get a valuable answer.

That's definitely true. It may be, in fact, that an RDBMS is not what is needed at all.

Re:you're doing something wrong

[mlyle]

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.

Re:you're doing something wrong

[Surt]

I meant heights of performance and size, but admittedly, that was a poorly chosen phrase. But yes, you scan scale sql very wide.

Wow

[mlyle]

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.

Re:Wow

[Cylix]

He posted to slashdot.... do you really think he can afford you?

Re:Wow

[Squeebee]

Congratulations, you just won Slashdot's buzzword bingo, please collect your prize at the cashier window in the back of the hall.

Re:Wow

[mlyle]

The short answer is, CA/CP/AP on a transaction-by-transaction basis depending on application requirements. Also of note: network delay is effectively a special "partition", requiring an engine that can have massive workloads in flight and reconcile/order non-commutative changesets in a distributed fashion.

Justification for new toys?

[StuartHankins]

The post is so vaguely worded, I imagine the author is merely trying to find some justification to purchase some new toys. "See, Slashdot people think this is a good idea!"

I agree with most of the posts so far -- if you're truly hitting a limit, you are most likely doing something wrong. Hire an outside DBA to make recommendations if you don't have the resources in-house. I strongly suspect this is the real issue.

hbase is an option to NoSQL and Cassandra.

[ooglek]

I recently read that someone moved their large operation from Cassandra to Hbase, a hadoop file system. http://hbase.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

What you should really be doing...

[ADRA]

Is to write better queries, I mean how hard can it be:

select * from (select * from A,B,C,D,E,F,G WHERE A.ID=B.AID(+) AND B.ID=C.BID(+) AND C.ID=D.CID(+) AND D.ID=E.DID(+) AND E.ID=F.EID(+) AND F.ID=G.FID(+) order by F.name ASC) where F.name='zzzzz'
Everything will work out, I swear.

Re:What you should really be doing...

[Nerdfest]

I think I've seen SQL written by you before. I realize your post is a joke, but I see people aliasing bad table names down to even less readable single letters. It's a maintenance nightmare. Treat SQL like a language and write it so it's readable and maintainable. It even frequently helps when you're trying to resolve performance problems ... they're much easier to spot in well written SQL.

Re:What you should really be doing...

[ErikZ]

I could do that, but your tears are delicious.

MySQL scales just fine.

[poptix_work]

I work with some very high traffic sites, storing large data sets (100GB+).

  Depending on the application (if it allows for different write-only/read-only database configurations) we'll have a master-master replication setup, then a number of slaves hanging off each MySQL master. In front of all of this is haproxy* which performs TCP load balancing between all slaves, and all masters. Slaves that fall behind the master are automatically removed from the pool to ensure that clients receive current data.

  This provides:
  * Redundancy
  * Scaling
  * Automatic failover

  The whole NoSQL movement is as bad as the XML movement. I'm sure it's a great idea in some cases, but otherwise it's a solution looking for a problem.

(*) http://haproxy.1wt.eu/

Re:What company?

[jlusk4]

Geez, you guys. There's a real person behind the question. Do you HAVE to be an asshole?

Rick Cattell's work on scalable datastores

[MoxFulder]

I recently came across Rick Cattell's site which addresses just the questions you're asking.

Rick Cattell has written an excellent comparison guide of horizontally scalable datastores 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.

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.

Re:Relational stuff scales - not around the world!

[mikehoskins]

Can you shard the same SQL data store in Chicago, London, and Tokyo? Not with standard SQL databases, unless you write your own complicated replication techniques or pay through the nose. (See CAP Theorem).

Yes, the company I work for has expressed the world-wide SQL database need, so this is not just a thought experiment.

Have you heard of GemFire/GemStone, VoltDB, or Xeround?

If you can get rid of the SQL requirement, try
    XML (or other format) on Amazon's S3
    or try one of the NoSQL databases, such as MongoDB, Riak, or CouchDB.

All of the above scale horizontally, most even scale in a geographically diverse environment.