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 have many text files, their total size is about 300GB ~ 400GB. They are all in this format

key1 value_a
key1 value_b
key1 value_c
key2 value_d
key3 value_e
....

each line is composed by a key and a value. I want to create a database which can let me query all value of a key. For example, when I query key1, value_a, value_b and value_c are returned.

First of all, inserting all these files into the database is a big problem. I try to insert a few GBs size chunk to MySQL MyISAM table with LOAD DATA INFILE syntax. But it appears MySQL can't utilize multicores for inserting data. It's as slow as hell. So, I think MySQL is not a good choice here for so many records.

Also, I need to update or recreate the database periodically, weekly, or even daily if possible, therefore, insertion speed is important for me.

It's not possible for a single node to do the computing and insertion efficiently, to be efficient, I think it's better to perform the insertion in different nodes parallely.

For example,

node1 -> compute and store 0-99999.txt
node2 -> compute and store 10000-199999.txt
node3 -> compute and store 20000-299999.txt
....

So, here comes the first criteria.

Criteria 1. Fast insertion speed in distributed batch manner.

Then, as you can see in the text file example, it's better to provide multiple same key to different values. Just like key1 maps to value_a/value_b/value_c in the example.

Criteria 2. Multiple keys are allowed

Then, I will need to query keys in the database. No relational or complex join query is required, all I need is simple key/value querying. The important part is that multiple key to same value

Criteria 3. Simple and fast key value querying.

I know there are HBase/Cassandra/MongoDB/Redis.... and so on, but I'm not familiar with all of them, not sure which one fits my needs. So, the question is - what database to use? If none of them fits my needs, I even plan to build my own, but it takes efforts :/

Thanks.

share|improve this question
3  
You're making some pretty big assumptions here. What on earth makes you believe that you are bottlenecked by CPU? It's much more likely you are IO bound, and running in parallel won't make the disks spin any faster. This is not an exception volume of data to be honest, but your requirement to rebuild the DB every day is a little disconcerting. –  JNK Apr 5 '12 at 13:02
1  
I think you need to take a look at the DB layout and server hardware. This volume should not be beyond the capabilities of a modern server of even modest specification. –  ConcernedOfTunbridgeWells Apr 5 '12 at 13:11
add comment

2 Answers

up vote 4 down vote accepted

I have been in a similar situation and highly recommend looking into MongoDB or HBase for your needs. The others (Cassandra/Redis) are good too, but HBase and MongoDB have been battle-tested in large environments, have a large community, good documentation (MongoDB's is better) and generally fulfill the three requirements you are looking for.

share|improve this answer
    
Thanks, I'm looking at HBase, it appears to be exactly what I want. –  Victor Lin Apr 6 '12 at 7:01
add comment
  • How big are the keys?
  • How big are the values?
  • Will the data arrive "continually", or is this a one-time load?
  • Let's see all the representative queries. You have mentioned only

SELECT val FROM tbl WHERE key = 'constant';

Are there others ?

In general key-value stores suck big time. But maybe we can make your special case work not-too-badly.

share|improve this answer
    
What problems have you seen with KVP databases? Or do you mean that storing raw KVP data in an SQL RDMS is a problem? –  Jon of All Trades Apr 6 '12 at 17:16
    
The JOINs to do a non-trivial WHERE clause become clumsy and inefficient. This disk footprint is extra large. –  Rick James Apr 9 '12 at 23:37
    
+1 for "Let's see all the representative queries." I think this is a great point to start understanding the limitations of KVP databases. –  Larsenal Aug 13 '13 at 18:54
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.