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.
Edit 3: In the end after some testing we have chosen MongoDB as the way to go. As it turns out - each and every database has its upsides and downsides but Mongo won us with simplicity and good documentation. Thanks to everyone who helped out with comments here