BigData: time-based word count

Question

What we are trying to do:

We're trying to build a system that will count the number of unique entries for a certain timeframe. It's working ok until the data grows up or the timeframe increase, then we get some OOM Exception or the response it's just too slow.

To simplify it's more or less the classic word-count but with timeframes.

# timestamp - userId - count
111111 - 12345 - 1
222222 - 67890 - 1
333333 - 67890 - 1
444444 - 12345 - 1
555555 - 12345 - 1

If the user asks for the "top" beetween (100000,400000) the answer should be "67890 - 2", otherwise if he asks for the "top" between (100000,600000) the answer should be "12345 - 3".

We are in the order of millions of entries (from 1 to 10, more or less).

What we did:

Right now we are storing the data on MongoDB, divided by hours. Getting the documents (that are quite big) it's pretty slow, about a minute to get 168 documents for a week. We tried Redis to speed up the things a bit but we're still out of our target.

What I tried:

To experiment a bit I wrote a text file with a couple of millions of entries (around 50mb) and I've tried simply to read and count from it, filtering by the timestamp. It's seems to be "incredibly" faster, around 2s to go through all the entries.
The drawback is that, also for small timeframes we should analyze all the file. To solve this we can think to split the files daily.

I've also tried Spark but for such small things seems overkilling and slower.

Anyway, it seems strange to me that the fastest way to do this is to read a text file! Is there any design,framework that I am missing?

Disclaimer: I'm not sure if I've exposed the problem clearly enough or if it suited for this site, but I'm losing sleep since 3 weeks struggling with this and this seems to be one of my last shots.

Answer 1

"Is there any design,framework that I am missing?"

Use a relational database.

Document and object databases (MongoDB among them) are great when your data is "chunky". That is, they excel if you intend to fetch data in a particular shape -- grab an entire order with its line items, grab a user with their preferences, grab a class with its students (or a student with their classes).

Key/value stores are screaming fast when you always fetch by key.

Trouble is, you're not using either of these strategies. The shape of your data depends completely on what a user asks for. There's no shortcut to the data; it's simply not available. Given that, you should crush your text file performance with a relational database. If you don't, you're doing something wrong. Make sure you understand indexes and partitioning. (That's where a text file falls apart. Don't use a text file as a database, please.) If you don't, hire someone who does.

---

As I'm typing this, I realize I may be leading you astray. Specifically, it's entirely possible to be successful with MongoDB. You mention that you're storing word counts by hour. That must not be an efficient assumption given the amount of time and memory it takes to complete a query. With the right expertise, you could create an effective MongoDB model. If you don't have the expertise, get it.

Until then...

Use a relational database.