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.

Imagine your requirement is that you have 3 huge tables (structured data) with say 30 billion rows in each (total size of 4TB) and your many concurrent users (which are parallel os threads on remote LAN machines) will need to read a portion of the data through their SELELCT WHERE GROUPBY queries and highly concurrent, say 10,000 concurrent reads at the same time and also users need to insert (no update) data into these tables highly concurrent too like 2000 concurrent writers (all over the data center LAN network). The users would want to read and insert as fast as possible form this storage where each read and write will happen is ms to 1 second range.

What technologies do you recommend to satisfy such requirement? Is there any data storage or key value store that could do this? Cloud is NOT an option.

Some Clarifications:

The users do NOT have to see the data right away and eventual consistency is acceptable. The data is accesses through whatever driver the storage can provide and users are again just threads running on remote machines of the data center. The queries are mostly like SELECT WHERE GROUPBY.

The data is in tabular format and each row is about 60 bytes.

No cloud option where I can not use DynamoDB or similar solutions. I have to be able to host it internally in the data center.

All the data of the tables can be read all the time and usage pattern is unpredictable. There is no join or super long query. No DR required but a reasonable HA is required but it does not have to be fancy. Every reader is getting a batches of rows based on its where clause and rows are not really related. We probably can have fixed length for each row but I am hoping the storage layer will worry about it.

Also, my biggest concern are all those concurrent writes that are happening with concurrent reads.

Your insights into this is highly appreciated.

And more thing, I have three of those tables with each 30 billion rows holding different object types

share|improve this question

migrated from programmers.stackexchange.com Feb 9 '12 at 4:29

This question came from our site for professional programmers interested in conceptual questions about software development.

    
define cloud because what most people, say 99% of the general populace and 100% of marketing people call cloud is just a cluster that someone else maintains. –  Jarrod Roberson Feb 9 '12 at 4:01
    
I mean, I can not use DynamoDB or some technology that are only available in a public cloud like amazon or azure and so on. –  iCode Feb 9 '12 at 5:54
add comment

1 Answer 1

up vote 6 down vote accepted

If eventual consistency is acceptable and all your queries are aggregates then perhaps a low-latency OLAP system might work for you. Your requirement sounds a bit like an algorithmic trading platform. This type of architecture is often used in trading floor systems that have a requirement to carry out aggregate statistical analysis computations on up to date data.

If you can partition your data by date and old rows don't get updated then you can build a hybrid OLAP system using a conventional OLAP server such as Microsoft Analysis services backed by an ordinary RDBMS platform. It should be possible to make this cope with ~4TB of data and both SQL Server and SSAS will do shared-disk clusters. Similar OLAP systems (e.g. Oracle/Hyperion Essbase) are available from other vendors.

OLAP servers work by persisting data in a native store, along with aggregates. Most will support partitioned data. In addition, most will also work in a ROLAP mode, where they issue queries against the underlying database. The important thing to note is that the storage strategy can be managed on a per-partition basis, and you can switch a partition from one to the other programatically,

In this model, historical data is stored in MOLAP partitions with aggregates of the data also persisted. If a query can be satisfied from the aggregates then the server will use them. Aggregates can be tuned to suit the queries, and correct aggregates will dramatically reduce the amount of computation needed to resolve the query. Very responsive aggregate queries are possible with this type of system.

Realtime data can be implemented by maintaining a small leading partition - for the current month, day or even hour if necessary. The OLAP server will issue queries against the database; if this partition is small enough the DBMS will be able to respond quickly. A regular process creates new leading partitions and converts closed historical periods to MOLAP. Older partitions can be merged, allowing the historical data to be managed at any grain desired.

The clients writing to the database just write straight out the the underlying RDBMS. If historical data remains static they will only be writing to the leading partition. 4TB is a practical volume to use SSDs for if you need extra DBMS performance. Even mainstream vendors have SSD based offerings with faster SLC units as an option.

share|improve this answer
    
Thank you for your response. You are correct. My problem is similar to algorithmic trading platform but different too. we have tried the RDBMS route and it could not scale. I need a storage that can scale and does not have the complexity of OLAP systems as our size of data is just growing and once we get to more TB on three tables, RDBMS will just create lots of locking and similar issue. I am hoping that a nosql option could satisfy such requirements. Any thoughts on that? –  iCode Feb 10 '12 at 5:01
    
@MDotnet Your expectation/requirement for a simple solution to a 12k concurrent user, 4TB sized problem may be unrealistic. You mention that you looked at RDBMS approaches and it didn't scale; 1) can you add the details of this to your Q 2) This answer is advocating a hybrid ROLAP/MOLAP approach, not a pure relational database. –  Mark Storey-Smith Feb 10 '12 at 8:22
    
I'm not a DBA and I think "drive by upvotes" are bad for most of the specialized sites, but I don't care, this answer is too good for just one upvote. +1 –  psr Feb 10 '12 at 18: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.