I couldn't find a similar enough thread, so i'm making a new one. My question is about reducing database writes. Context is that I have an application which increments a number in a database every time a user sends a message (which is more than hundreds of messages every second, peaking thousands at times) and i'm concerned that the database may start having troubles coping and will reach bottlenecks. Saving reads is fairly easy, check caching solution (redis/program cache), if does not exist, hit up database and cache that data with a TTL. But my question is about saving writes.
It's quite a tricky task as I want to keep the cache updated as well as the database. The database would not need to be updated 100% of the time as every cluster uses a shared Redis database. Even if the program crashes, a little bit of data loss from not saving to the database would not be a huge issue.
I was thinking of something like this (assuming the data that needs to be processed is already cached and needs to be updated):
- Request comes in
- Cached copy of data is found in Redis and returned to cluster
- Cached Redis copy of data is updated with new data
- Updated data gets put in a "queue" which runs in an interval, creating a transaction/pipeline, updating the database with every data updated in the created transaction.
I'm unsure if this would be the best strategy, if anyone has anything better, i'd like to see it and I would also like an opinion on this strategy.
Running on MySQL 8 (thinking of switching to MongoDB) Running on Redis (always kept updated) Running on NodeJS (this is not the main scope of the question) Running on Ubuntu 18.04
The counter is used to count the users messages for the purpose of showing it to the other users, as well as checking if the user has sent a specific amount of messages for some internal processing.