The application I'm working on has a need to create a counter representing the number of times each piece of content has been viewed.
There are two goals here:
- Update the counter in real time
- Minimize load on MySQL
Currently what we do, is make a cache key in Redis for each piece of content that contains the view count. When a view event happens, we increment the view count. If there is no value yet at the key when a read or write happens, we calculate the all-time views count using a separate data source (Influxdb).
The problem is, our existing approach won't' be feasible anymore because of the way we're restructuring our InfluxDB data. It is no longer adequately performant to calculate the all time views count for a card using Influx data, for reasons I won't get into here (feel free to ask).
Essentially, we no longer have a way to calculate the all time views count 'from scratch'. We will need to rely on the existing counter values and only increment them (never completely re-calculate them).
I have the following idea to do this:
- Calculate all-time views on each content and store in MySQL (this can be done once to seed the data)
- When a read/write happens for the first time, look it up in MySQL and store it in Redis
- Whenever a write happens, increment the count on Redis
- In a background job, once per hour or so, update the views count in the MySQL database using the data in Redis.
This will produce at maximum one hour of data loss, if Redis drops all the keys one minute 59 right before the background job happens.
Does this approach make sense? If there a better way to do it?
Now that I think about it a little more, the concept of a background job that updates all the cards is a little problematic. If we have millions keys stored in the cache, how should the application know which had activity and should have their Redis counts copied to MySQL?
So, I'm thinking that there can be a special key on Redis which stores a queue of all the card ids that have had updates. Then the background job can see the unqiue card ids in this list, and request only that data for the update.