On Thu, Apr 16, 2009 at 11:12 PM, Brian Moon <[email protected]> wrote:
> > On 4/16/09 8:14 AM, Clint Webb wrote: > >> I think a more important question is... what do you hope to gain by >> caching sql queries? mysql and postgresql already have query caches, >> which work about as good as using memcached as a query cache. However, >> people dont like it because it clears a table cache whenever the table >> is modified. >> > > That is a common misconception. At least for MySQL. In many cases, the > MySQL query cache can cause more overhead for an application than no caching > at all. The process of checking the cache, clearing the cache and so on can > be quite burdensome on a write heavy application. In those cases > controlling the cache ttl with memcached can give you much better > performance. > > How are you handling removing cache entries when data is changed? If >> the data doesn't change, then what benefit is it over mysql built in >> cache. If the data does change, how are you removing the queries that >> are invalidated by that change? >> > > Unnecessary connections to the database for one thing. In high load > applications, hitting MySQL for the same data 100 times per second, even if > from the query cache is not as efficient as memcached. It also becomes a > single point of failure. > > Brian. > What you say is true. I personally dont use mysql cache, as it really is unnecessary if you are using memcache to cache things for you. My point was that Paras is new to using Memcached and I hardly think he is trying to tackle those issues. Getting the perception while learning, that you can safely just cache all your queries can be quite misleading. There are many complications, especially if you have a write heavy application. To be fair, I actually do use memcache to cache a few queries that get hit frequently, but I do this with surge/flow in mind, to minimise peaks of requests flooding the database, and I only do so when it is not easy (or would result in an ungodly number of gets) to cache individual objects. And in this case I use a prefix in front of the query that increments whenever any of the data in that query is updated (which means I invalidate all cached items related to that query whenever a write is done to any of those tables)... which certainly helps when I get a massive hit on a particular page full of comments, but does mean that I lose the caches for all pages whenever anyone adds a new comment to any page (but I could probably improve the granularity of this). In summary, if you are new to utilizing memcached, then you need to realize that there are some gotcha's in using it as a query cache, but there are also some valid uses of it. (Not that there aren't some gotcha's in using memcached for objects as well... but thats a whole other email... ) -- "Be excellent to each other"
