Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've heard that performance of non-sharded relational database such as MySQL or PostgreSQL "breaks" beyond 10 TB.

I suspect that limits as such do exist as one would not come up with Netezza, Greenplum, or Vertica, etc, however I would like to ask if anyone here has a reference to any research paper or formal case studies where these limits are quantified.

share|improve this question
    
Why don't you ask the guys tell you it breaks beyond 10TB? They have the experience, that's why tell you it breaks. –  Frank Heikens Sep 11 '12 at 5:08
    
-1 since this is not a specific question about administering databases. This question is likely to solicit opinion and discussion, which is not what this Stack Exchange site is for. If you have a specific issue that are running into, or have a specific database target size in mind with specific characteristics, by all means update your quest. –  Max Vernon Sep 25 '12 at 17:52
    
I disagree about soliciting opinions. I am purposefully asking for references to research papers, formal case studies and other "hard facts" quantifications. –  Edmon Sep 27 '12 at 18:34
    
Edmon: As per my answer below the big problem is that these problems don't give rise to quantifiable limits. Rather you have a bunch of considerations that start kicking in around the 1-2TB level and escallating gradually. These require specialization to resolve, so what does break down is the jack of all trades database, i.e. one running analytical and transaction processing loads on the same system. How this happens or at what limits is application-specific. Sharding further helps only with some classes of such problems. –  Chris Travers Oct 1 '12 at 9:12

1 Answer 1

up vote 13 down vote accepted

There's no simple answer to your question, but here are a few things to think about.

First, scale isn't the only thing to worry about. What you do with your data is. If you have 500 tables 30 TB of data, and you are doing simple OLTP with very little reporting, I don't think you will have too many problems. There are 32TB databases on PostgreSQL out there. However, at the same time performance will degrade somewhat because it is having to hit disk on everything. Similarly if you have 50TB if data but have a commonly hit set of about 100GB, then you can build a server with enough RAM to keep that part of the db in memory and you are golden.

On the other hand if you are trying to take the mode (most common value) out of 1TB of data, it doesn't matter what system you are using, this is going to be painful with or without sharding. (Edit: Sharding may, in fact, make this problem worse.)

The major problems you will run into with huge db's on MySQL and PostgreSQL involve the fact that neither supports intraquery parallelism. In other words a query is run as a single block by a single thread, and it can't be broken down into pieces and run separately. This is most often an issue when running large analytical queries over large amounts of data. This is where Postgres-XC and Green Plum come to the rescue since they separate storage from execution, and can do this at the coordinator level. Note that Postgres-XC and Green Plum essentially use sharding internally but the coordinators enforce all consistency globally.

With intraquery parallelism you can break up the query, have different processors/disk I/O channels run parts of it, and report back pieces of the result set to be assembled and passed back to the application. Again, this is usually most helpful in analytic rather than transaction processing loads.

The second thing is that some systems, like Vertica or Greenplum store columns of information together. This makes the system harder to use from an OLTP perspective and decreases performance there, but it drastically increases performance for large analytic workloads. So this is a workload-specific tradeoff.

So the answer is that once you get above 1-2 TB in size you may find yourself faced with a number of tradeoffs between systems and workloads. Again this is specific to databases, size of working sets, etc. However at this point you really have to go with snowflake systems, i.e. ones unique and tailored to your workload.

This of course means that the limits are not generally quantifiable.

share|improve this answer

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.