Essential Caching Considerations

Question

We have a fantasy football application that uses memcached and classic memcached-object-read-with-sql-server-fallback. This works pretty well, but lately I've been thinking about overhead and whether this was the best approach.

A random point - we need to create a drop-down list of user commands, so we follow this pattern:

• Get list of user commands from memcached
• If unavailable, get the list from the SQL server and save it in memcached.
• Make a multi-gate to get team objects.
• Return to loading objects from sql repository.

All this is very good - each cached piece of data is relatively easy to cache and invalid, but there are two main disadvantages:

1) Since we work on objects, we face quite a lot of overhead - one command takes about a hundred bytes in memcached, and what we really need for this case is a list of command names and identifiers - not all the others in command objects.

2) Due to the return to loading of individual objects, the number of SQL queries generated in an empty cache or when items expire can be massive: 1 x Memcached multiget (which skips, which causes) 1 x SELECT ... FROM Command WHERE Id IN (...) 20 x Store in memcached So 21 network requests for this single request only, as well as an IN request, are slower than a specific connection.

Obviously, we could just make simple

SELECT Id, Name FROM Teams WHERE UserId = XYZ 

And a cache that will lead, but this will mean that this data must be specially invalid whenever the user creates a new command. In this case, this may seem relatively simple, but we have many of these types of queries, and many of them work with axes that are not easy to perform (for example, a list of identifiers and team names created by your friends in a certain game).

Soooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo well over there!

Answer 1

First cache what you need, maybe two fields, not a full record.

Secondly, cache what you need again, split the result set into records and cache them separately

Answer 2

about caching:

Usually you use caching to offload slow disk storage, in this case mysql. The memory cache scales quite easily, mysql scales less easily.

Given that even if you double the use of cache / netowork / cache memory and reconnect everything together, it will still unload db. Adding another instance of nodejs or another memcached server is easy.

back to your question

• You say that this is a user command, you can go and extract it when the user logs in and store it in the cache until the user changes it throughout the session.

• I assume that the names of the team members do not change, so you can load all team members by id, name and cache or even locally on nodejs, use the same return strategy as now. Then only steps 1 and 2 and 4 will be left.

personally, I usually try to split the sql results into smaller ready-made parts and cache them, and the cache is updated as long as possible without trying to use mysql only as storage and never read from it

usually you will run some logic in the returned mysql string form anyway, no need to repeat this.