I am building real time achievements web service.
My current idea is to have an achievements collection in MongoDB as well as a player collection. I would store the list of achievements in the achievements collection (that list can be modified to add new achievements and would serve as the achievements definitions) and it would contains a list of stats and thresholds (goals to complete the achievement), while the player collection would have objects that are composed of the playerID as well as a dict of each achievements as keys and many stats (progress) as values, as well as informations (completed or not).
When a client would post new stats, I would get the list of achievements and find those that use those stats in their progression by fetching the achievements collection. Then I would need to fetch the players collection to find which achievements are already completed and remove those from my current list of achievements to process. Then I would fetch the players collection again to get the other stats and compute the new progress. I would need to update the progress of the achievement on the players collection. If an achievement is complete, I would send a callback to the client, so it can see it "live".
My problem is that I need the service to work under high pressure (hundreds of thousands of players sending new stats a lot (like number of kills, maybe thousands of stats with thousands of achievements)) and my current idea seems to do WAY TOO MANY CALLS to the database.
I thought of changing to an MySQL database instead but I am not very good with them, so I am not sure if things would be better that way (could views speed things up?). Redis seems to be too costly for a big database.
Is there a better flow / Design pattern I should use instead?
Is there a way to make schemas so it will still be quick on heavy load?
Should I use MySQL instead? And if yes, what is the key element that would help me speed up things? (So I can read on it and design something better)
I never used NoSQL but used SQL a lot. So my thought may be biased or too much SQL centric.
Having said that, here is my idea. Overall, I think two db call is needed per new stat.
When a client would post new stats, I would get the list of achievements and find those that use those stats in their progression by fetching the achievements collection.
If the achievements collection is small enough, you could cache into the memory when your service is initialized. If not, I think you should go "MySQL" approach and not do this step alone but join to the next step. In conclusion, we could reduce one trip to DB
Then I would need to fetch the players collection to find which achievements are already completed
This could be the first trip to DB
remove those from my current list of achievements to process
I believe this is not DB related but logic inside your program. But please correct me if I am wrong.
Then I would fetch the players collection again to get the other stats and compute the new progress.
I think you could get this information from your first DB trip and save somewhere in the memory. So no further DB trip is needed
I would need to update the progress of the achievement on the players collection.
This will be your second DB trip to update.
If an achievement is complete, I would send a callback to the client, so it can see it "live".
And this is not related with DB
If this is still too much DB call and you would like to make this only one trip, my only idea is to switch MySQL and create a procedure that deal with the logic.
In this way, you will make only one DB contact per stat, which is inevitable and push all your load to DB layer so that it scales over there.