Caching COUNT with PHP and Redis
forrst.com
Caching COUNT with PHP and Redis
1–10 of 13 posts
Re: Caching COUNT with PHP and Redis
#2MySQL already caches these. You've added a cache on top of a cache. Is a round trip to the database so slow?
Re: Caching COUNT with PHP and Redis
#3MySQL already caches these. You've added a cache on top of a cache. Is a round trip to the database so slow?
if you are doing 15-20 COUNTS per page (as he said he was) even if you are on a fast local network those few milliseconds of latency can probably add up, also he said he was directly managing updating/invalidating the count cache entries which is probably more effective (because the caching strategy is so specific) than whatever more generic strategy mysql is using.
Re: Caching COUNT with PHP and Redis
#4Assuming good indexing with non-enormous tables, he could use subqueries to get the COUNT() data at the same time that he pulls the records themselves. And, as he notes, he could have a count field in MySQL, caching at that level instead of in Redis.
Re: Caching COUNT with PHP and Redis
#5Doesn’t this seem too basic to have been voted up so much? That is, compared to the rest of the technical posts that are voted up.
And perhaps MemcacheDB (http://memcachedb.org/) is a better bet if you don’t need Redis’s list and set operations. But I don’t understand why the cache needs to be persistent.
Re: Caching COUNT with PHP and Redis
#6> if for some reason the memory cache diverges from the true count, we just wipe out the memory cache and the app takes care of regenerating it automatically
Warning, this is a myth: once you starte relying on caching to serve your amount of traffic, flushing the cache will have the result of taking the site down. This is why persistence is an absolute requirement of a serious caching server IMHO. With Redis when things may be in desync it's better to selectively remove entries with (RANDOMKEY+DEL) at a rate that the system is able to handle.
Also given that you are using Redis that has atomic increments, why not going the extra mile and issuing INCR/DECR operations when something is added/removed?
Re: Caching COUNT with PHP and Redis
#7> if for some reason the memory cache diverges from the true count, we just wipe out the memory cache and the app takes care of regenerating it automatically Warning, this is a myth: once you starte relying on caching to serve your amount of traffic, flushing the cache will have the result of taking the site down. This is why persistence is an absolute requirement of a serious caching server IMHO. With Redis when thi…
I should have been more specific--technically the cache would be wiped for a specific post, never for the entire db of posts. But you're totally right about the persistence --that's why something like redis made sense to me.
Useful tip about randomkey and del, thank you.
I def. could switch it over to an incr/decr setup. Maybe I'll do that tonight.
Re: Caching COUNT with PHP and Redis
#8MySQL already caches these. You've added a cache on top of a cache. Is a round trip to the database so slow?
I was under the perhaps false impression that count star queries with or without where conditions are not cached on innodb tables.
Re: Caching COUNT with PHP and Redis
#9MySQL already caches these. You've added a cache on top of a cache. Is a round trip to the database so slow?
Depending on your database engine and WHERE conditions it may not be cached as you assume it to be.
Re: Caching COUNT with PHP and Redis
#10MySQL already caches these. You've added a cache on top of a cache. Is a round trip to the database so slow?
I was under the perhaps false impression that count star queries with or without where conditions are not cached on innodb tables.
Not exactly. On isam table the count(*) queries can sometimes directly lookup the answer from the index, or the table statistics.
On innodb, because of transactions, this is not possible, and it needs to actually count the rows.
BUT, the query itself is still cached - at least until the underlying table gets updated, which invalidates the cache (I'm not sure of the exact invalidation strategy, but it's part of MySQL and doesn't depend on the engine).