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 am working on a project that has some serious requirements regarding its database and having hard time choosing the best solution. The details are:

  • Database will easily reach tens of terabytes of data
  • Schema is simple in nature. In relational database terms it would consist of 3 tables, each with millions and billions of rows. Each table has a few columns only. Records are like 10K in size each
  • Allow for tens of thousands of writes per second. I don't have good estimate for the reads but they should be less. Possibly will move to bulk inserts (write clients will insert at average 10 new rows per second so I can combine those and have 5000 bulk inserts per second)
  • Obviously will need to be able to scale horizontally easily. These number are just for start and I expect they will multiply over the years. Hopefully :)
  • SQL solution is not required
  • Read queries performed will be over ranges of data. Geospatial support would be nice though not needed. Secondary keys also would be cool
  • Should be easy to access via a C/C++ application. I am still considering Java as our choice of platform to build the servers that will talk to the database, but probably will end up with C/C++ (for reasons I am not going to put up here)
  • CAP related - obviously we would like to have it all and it won't happen. Availability wise we will be fine with some read/write delays (in terms of seconds). Eventually consistent is OK as long as it doesn't take ages for the database to become consistent. Partition tolerance should be enough to cover the numbers listed earlier. So I can't really put some serious accent on which of those three is most important for us. Only data loss is not acceptable :)
  • Cross-data centre replication would be nice, although this is not planned in the scope of the project.
  • Updates/Deletes of the data are minimal. Just insert and read.
  • There may be some Map/Reduce queries to the data but most likely they will not be executed very often and their results will be cached. The very least the heavy queries can be performed on a replica of the database so writes can continue while the heavy analysis is performed.
  • Schema can be easily fixed, flexibility is not necessary although if we can have it without sacrificing something else - why not? In fact a key-value store with 3-4 buckets is an option.

Basically the design of the project resembles a one-way VoIP application with constant recording of the data. A set of clients constantly push data in the database (several thousands of clients) at 10Hz rate and a similar number of clients constantly read the data (at 1Hz rate). Applying Pub/Sub solution would take off some of the database load so we are considering that as part of the solution. Any suggestions on that are also welcome. This also means the working set is much smaller. Real-time data is being passed between the clients and the recorded data is accessed far less frequently.

I do not have experience with NoSQL solutions but probably it is the way to go in that case.

So far I have few names in mind:

  • MongoDB (so far seems to match my requirements)
  • CouchBase (though I have yet to find the documentation how to use it from C)
  • MySQL with NDB (Probably will fail in time - I haven't found someone using it at such scale but I am an old MySQL user and have quite happily lived with it for ages)
  • Cassandra (I have some hard time getting my head around their data models so far but I will figure it out)

Does anyone have any suggestions based on experience with data warehouses at that scale? Which database solution would be the best in the provided scenario?

Thanks in advance to anyone who posts an answer :)

Edit: I missed something and one of the answers reminded me to add it - we are running *nix boxes only (blade servers).

Edit 2: Just making sure it is clear - I am not looking for in-memory database. I think it will give us hard time protecting against data loss. And I don't really need to have all the data available all the time - about 99% of the data is archive which will be used on rare occasions. The working set may be as low as few dozens of gigabytes.

share|improve this question
    
Why not use Microsofts SQL Server? –  Mobstaa May 7 '13 at 13:53
    
Quite a interesting question that I can't answer. Also quite curious to WHY a certain solution is the best option. It does sound like a NOSQL solution, but I have no experience on this field. I will follow this question and hope for a good explanation from anyone who can answer this. –  NLwino May 7 '13 at 14:30
1  
Thanks to whoever migrated my question to this place - I wasn't aware of its existence :) –  Dimitar K May 8 '13 at 20:19
2  
You could just dump into files and use Hive to query it. –  Remus Rusanu May 9 '13 at 7:20
1  
Hive supports querying arbitrary formats over externally partitioned 'tables' (ie. ordinary files). So you can basically just stream the input into output files properly named (eg. %year/%month/%day/%hour/table) and Hive will stitch together a 'table'. See An Introduction to Hive’s Partitioning, Dynamic Partitions. –  Remus Rusanu May 9 '13 at 7:35
show 8 more comments

migrated from stackoverflow.com May 8 '13 at 17:53

This question came from our site for professional and enthusiast programmers.

3 Answers

Tens of terabytes of data in 3 tables sounds expensive in many ways.

Do you have budgetary constraints for this solution? Storing tens of terabytes of data in an in-memory NoSQL platform may be exactly what you're after, but it's likely to require dozens of servers.

You might get some good ideas from High Scalability. For example, it's interesting to see that while Twitter make extensive use of Cassandra and graphing databases, they were still able to record and display bajillions of tweets stored in MySQL as of 2011.

This is not an expert opinion: I have experience with multi-terabyte databases, and databases doing >10,000 transactions a second, but not both at once (yet). Nevertheless:

Are you using the same data to solve more than one problem?

There's no law against storing the same data twice - you could put something into an in-memory database for fast, short-term access for the applications that need it, while at the same time streaming it to a file for batch processing / analytics using something like Hadoop, a relational database, etc.

This also makes it really easy to discard data that's expired from your real time system - you don't need to worry about exporting or persisting it, because it's already been stored.

Are your primary data readers there to do tagging, decoding/processing, alerting or correlation?

If so, your Pub/Sub idea sounds perfect. If you're able to send the raw data cheaply (over message bus where the data isn't stored long term) to something else which then only stores some of that data, you've saved yourself a lot of infrastructure headaches.

Depending on what you're trying to do, Complex Event Processing systems may also be of interest.

Can your data be sharded?

(all customers with IDs ending with 1 go on this system...)

Depending on the data platform you end up choosing, ten systems with 1TB of data can be a lot easier to work with than one system with 10TB of data. On the other hand, it's ten times as many systems to maintain.

Sharding also brings some technologies that don't scale writes well (like traditional RDBMS systems) back into the realms of possibility.

Data Grids

There are some really fancy in-memory data solutions out that that act as a data ecosystem - you can subscribe to changes in data, perform map/reduce type queries, etc.

I've looked at Oracle Coherence on several occasions, and it looks brilliant, but I haven't had any problems yet that require that level of... well, funding.

I'm not so sure that document databases are the way to go for your problem - you might want to benchmark and see how well they hold up when inserting thousands of records per second.

share|improve this answer
    
Thanks for the response! A couple of things - I am NOT looking for in-memory database. In fact the working set kept in memory can be very small (like latest few thousands of records only). Budget is not fixed and if it takes dozens of servers - well, it does. About sharding - absolutely, in fact we did some brainstorming about how we could use Mongo and one of the points to discuss was what keys to shard on. About benchmarking - I am still waiting one of the sample blade chassis to come to the office to start messing with it (can't wait!). Thanks for the info though! –  Dimitar K May 9 '13 at 4:41
    
"Are your primary data readers there to do tagging, decoding/processing, alerting or correlation" - no, they display the written data (mostly the real-time data as it comes, once in a while - review older records). On rare occasions they may make small changes, but that would be so rare that I am not even sure if we are still planning to allow it :) –  Dimitar K May 9 '13 at 4:47
    
"Are you using the same data to solve more than one problem?" I have been thinking about that - applying a MemCached layer where I keep the working set cached for fast access and record the data on disks for permanent storage. The reason I am not keen on that approach is I expect the DB server itself to actually cache in memory the working set. This is a bit like doing the same work twice. And will only add complexity to the application. Will give it a second thought though! –  Dimitar K May 9 '13 at 4:50
    
Finally got the time to check the link you posted about how Twitter build their data warehouse. Great reading - very close to what I am looking for. Leads me to some interesting projects, like Snowflake and Gizzard that are definitely worth checking out! –  Dimitar K May 9 '13 at 11:28
add comment

If you are going the NoSQL way you will have sharding for "free" it is built in in most of the solution at least in Couchbase, Cassandra, and MongoDB.

I am not saying that you won't be able to achieve that into an RDBMS but you may end to implement many layer at the top to manage fast access, for example I start to see caching layer in the discussion with Coherence, or Memcached... this is great but you had complexity to your infrastructure.

This is one of the reason Couchbase has been built with Memcached "inside":

  • the data is automatically shared with replicas for failover
  • you can store JSON document or any type of value and you can change the schema easily from your application. Thanks to JSON
  • if you need more storage, more power, just add new node and rebalance the data. This is it! This is something often harder do to with a RDBMS since sharding is often complex.
  • the integration of Memcached into Couchbase allows you to have consistant and predictable performance independently of the number of nodes in it.

Developing with C is possible with the Libcouchbase see http://www.couchbase.com/develop/c/current

share|improve this answer
    
Yes, I have looked into Couchbase. It seems like the perfect solution indeed, however I have ruled it out because of the C API. It is asynchronous without obvious link between the request and the response. In our case a single thread will be sending thousands of queries and we need to be able to match request to a response. The Java API seems better in that way, so if we choose to develop in Java, instead of C - it will be back into consideration. –  Dimitar K May 17 '13 at 7:02
add comment

Maybe consider IBM DB2. It scales quite well for warehousing, and meets your requirements from what I can see.

You can download a free version (the Express-C edition) from IBM here. Some features are turned "off" (such as Database Partitioning Feature, memory/CPU limits....) but it can get you going. When you want to scale it up a notch all it takes is a call to IBM to purchase a license and apply the license file and you immediately go from Express-C edition to Enterprise Server Edition or Advanced Enterprise Server Edition. DB2 scales well both horizontally and vertically. It has many features for protection against data loss. It should keep up quite well with your performance concerns (and allow quite a bit of areas to tune in). The newest version of DB2 out at this time (10.1) and the newest version due out this summer (10.5) have a lot of enhancements specifically for warehouses and data marts. These enhancements are meant to help with both administration as well as performance.

share|improve this answer
    
Thanks! That is one idea I haven't considered. Will check it out –  Dimitar K May 17 '13 at 6:39
add comment

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.