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.