[wp-hackers] MySQL MEMORY worth using for an object cache?

[David Chait]

And just to chime in, I agree that for high-volume sites, with complete 
dedi server control, you can make great use of APC/memcached on the 
'front end', BUT that can be tuned/balanced with effective use of the 
MySQL query cache -- ESPECIALLY if you have enough $$ to have a separate 
MySQL box (or four).  Then, in some cases, I can see the query cache 
being a big impact.  It depends on the ram in the two boxes, what's 
allocated to what.  Pulling over the lan out of ram should usually be 
faster than storing to disk, so if you are thrashing memcached ram 
storage for query/object caching on the front-end, you might be better 
off letting MySQL do it.  At least, that's my understanding -- I've 
never had a dedi box to try out on! ;)

Some quick notes for other readers.

1) memcached is a general persistant caching mechanism, that can store 
any serialized data in local ram for quick retrieval.  It's a great 
drop-in replacement for the default to-disk caching of the wp object 
cache.  there are a few other caching daemons (xcache for one).  
memcached actually is designed to allow the memory storage be remoted 
(to another box) which basically gives you ram-over-lan-based caching -- 
can be powerful if you want a big-ram box in back, with a lot of fast 
apache (or lighttpd, thttpd, etc.) servers in front, and mysql on the 
side. ;)

2) APC is a php 'opcode cache'.  it makes your PHP 'dynamic' code load 
and run quickly.  you need to tune its memory use properly from what I 
understand.  and, I've also read about people making APC network 
distributed too, though I'm not sure in what cases you'd win remoting an 
opcode cache's results vs regenerating locally.  There are other popular 
opcode caches, including I though Zend had one themselves (but I think 
Zend's is $$$, the others are usually free...).

So, two different parts of the equation, plus mysql query caching, gives 
you a 'hierarchy of caching mechanisms'.  Lots of places to tune.

However, just as a reminder, these solutions are only for dedi platforms 
-- they have zero impact on shared servers (well, you have no control, 
the host could use them if desired...).  And in some cases, some of them 
only make big impacts when you are talking multiple boxes.

Back to the original question. ;)  I wouldn't think MySQL object cache 
would be all that useful as a generalized cache, better to use 
memcached.  Again, zero experience, just from reading.  Then again, if 
you have a beefy-but-underused MySQL box, and a heavily-overused 
webserver, then yeah, I could see shifting data over to the db box being 
useful.  But at that point, why not just let the mysql query cache do 
its job, and get the object cache out of the way?

-d

Robert Deaton wrote:
> As far as I know, there isn't anything particularly wrong with using
> MySQL's query caching afaik. It would almost certainly be faster than
> file based caching, however there are advantages and disadvantages to
> each.
>
> The advantage of using MySQL is indeed using RAM, which is many
> magnitudes faster than disk based files. The disadvantage, however, is
> that you still have MySQL connection overhead, a limited number of
> connections to the MySQL server, etc. On a server expecting burst
> traffic or just flat out heavy traffic, its worthwhile to use the
> diskbased cache instead to relieve some of the stress from the MySQL
> server.
>
> It is because of this that I'd choose APC or memcached as a persistent
> object cache backend. Probably APC because there is no daemon there
> either, it can be stored straight to RAM and retrieved straight from
> RAM.
>
> Might want to get someone else's opinion on this one though.
>