SQL/memcache tradeoff

[Brian Moon]

Marcus Bointon wrote:
> So, if I lose the DB optimisation and go back to doing a single look-up 
> for each object (playing to memcache's strengths, but giving terrible 
> performance without it), I'd be doing <= 100 DB requests, <= 100 cache 
> reads, <= 100 cache writes, 100 instatiations (or __wakeup). At some 
> point there must be a tradeoff between doing a more efficient DB lookup 
> and doing more cache reads. How many items is that likely to be for? For 
> 100 items, 100 memcache lookups would have to be faster than one DB 
> lookup for 100 items. Is that the case? Is it typically higher? If so, 
> how much?
> 
> Is there a middle way? Is it worthwhile culling the requests for 
> instances that are in the cache already before submitting the request to 
> the DB? Would the overhead of doing that pay off?

IMO, yes, there is.  You are thinking too low level IMO.  The main piece 
of data for us is an article.  dealnews has 150ish new articles per day 
on the site.  Plus our archives are always getting hit.  We do cache 
each article individually as multiple sources may need a single article. 
However, there are operations that need lists of articles.  So, we layer 
memcache data.

For the front page, a function (get_pub_articles) is called that 
retrieves all articles needed for that day.  That function runs a query, 
determines what articles are need, and calls a function that either gets 
them from memcached or the db.  We don't stop there however, the 
get_pub_articles function then caches the full array of articles it 
needs for the front page.  So, another call to the front page does not 
have to make 150 memcache calls or a multi get even.  It makes one 
memcache call to get the full array.  When new articles are published, 
we simply refresh that cache as part of our publication process.  In 
reality, the front page itself never creates the cache.  cron jobs take 
care of that for us.  But it could if it needed to.  We do the same 
thing on category pages, vendor pages, etc.  We don't currently cache 
search results as they are highly dynamic and mysql full text search 
makes them quite fast.

I hope that makes sense.

-- 

Brian Moon
-------------
http://dealnews.com/
It's good to be cheap =)