I also meant to point out that you have to be careful with very wide partitions, like those where the partition key is the year, with all usages for that year. Thousands of rows in a partition is probably okay, but millions could become problematic. 100MB for a single partition is a reasonable limit – beyond that you need to start using “buckets” to break up ultra-large partitions.
Also, you need to look carefully at how you want to query each table. -- Jack Krupansky From: Chamila Wijayarathna Sent: Sunday, December 14, 2014 11:36 PM To: user@cassandra.apache.org Subject: Re: Cassandra Database using too much space Hi Jack , Thanks for replying. Here what I meant by 1.5M words is not 1.5 Distincts words, it is the count of all words we added to the corpus (total word instances). Then in word_frequency and word_ordered_frequency CFs, we have a row for each distinct word with its frequency (two CFs have same data with different indexing). Also we keep frequencies year wise ,category wise (newspaper, magazine, fiction, etc.) and position where word occur in a sentence. So the distinct word count will be probably about 0.2M. We don't keep any details in frequency table where frequency is 0. So word 'abc' may only have rows for year 2014 and 2010 if it only used in those years. In bigram and trigram ables, we do not store all possible combinations of words, we only store bigrams/trigrams that occur in resources we have considered. In word_usage table we have a entry for each word, that means 1.5M rows with the context details where the word has been used. Same happens in bigrams and trigrams as well. Here we used separate column families word_usage, word_year_usage, word_Category_usage with same details, since we have to search in 4 scenarios, using 1.. year, 2.. category, 3.. year&category, 4.. none inside WHERE clause and also order them by date. They contain same data but different indexing. Same goes with bigram and trigram CFs. We update frequencies while entering words to database. So for every word instances we add, we either insert a new row or update a existing row. In some cases where we use frequency as clustering index, since we can't update frequency, we delete entire row and add new row with updated frequency. [1] is the client we used for inserting data. I am very new to Cassandra and I may have done lot of bad things in modeling and implementing this database. Please let me know if there is anything wrong here. Thank You! 1. https://github.com/DImuthuUpe/DBFeederMvn/blob/master/src/main/java/com/sinmin/corpus/cassandra/CassandraClient.java On Mon, Dec 15, 2014 at 1:46 AM, Jack Krupansky <j...@basetechnology.com> wrote: It looks like you will have quite a few “combinatoric explosions” to cope with. In addition to 1.5M words, you have bigrams – combinations of two and three words. You need to get a handle on the cardinality of each of your tables. Bigrams and trigrams could give you who knows how many millions more rows than the 1.5M word frequency rows. And then you have word, bigram, and trigram frequencies by year as well, meaning take the counts from above and multiply by the number of years in your corpus! And then you have word, bigram, and triagram “usage” - and by year as well. Is that every unique sentence from the corpus? Either way, this is an incredible combinatoric explosion. And then there is category and position, which I didn’t look at since you didn’t specify what exactly they are. Once again, start with a focus on cardinality of the data. In short, just as a thought experiment, say that your 1.5M words expanded into 15M rows, divide that into 15Gbytes and that would give you 1000 bytes per row, which may be a bit more than desired, but not totally unreasonable. And maybe the explosion is more like 30 to 1, which would give like 333 bytes per row, which seems quite reasonable. Also, are you doing heavy updates, for each word (and bigram and trigram) as each occurrence is encountered in the corpus or are you counting things in memory and then only writing each row once after the full corpus has been read? Also, what is the corpus size – total word instances, both for the full corpus and for the subset containing your 1.5 million words? -- Jack Krupansky From: Chamila Wijayarathna Sent: Sunday, December 14, 2014 7:01 AM To: user@cassandra.apache.org Subject: Cassandra Database using too much space Hello all, We are trying to develop a language corpus by using Cassandra as its storage medium. https://gist.github.com/cdwijayarathna/7550176443ad2229fae0 shows the types of information we need to extract from corpus interface. So we designed schema at https://gist.github.com/cdwijayarathna/6491122063152669839f to use as the database. Out target is to develop corpus with 100+ million words. By now we have inserted about 1.5 million words and database has used about 14GB space. Is this a normal scenario or are we doing anything wrong? Is there any issue in our data model? Thank You! -- Chamila Dilshan Wijayarathna, SMIEEE, SMIESL, Undergraduate, Department of Computer Science and Engineering, University of Moratuwa. -- Chamila Dilshan Wijayarathna, SMIEEE, SMIESL, Undergraduate, Department of Computer Science and Engineering, University of Moratuwa.
