Strategies to cache from MySQL (or any DB I suppose)

[Paul G]

----- Original Message ----- 
From: "mike" <mike503 at gmail.com>
To: <memcached at lists.danga.com>
Sent: Monday, November 07, 2005 12:46 PM
Subject: Re: Strategies to cache from MySQL (or any DB I suppose)


On 11/7/05, Jacob Coby <jcoby at listingbook.com> wrote:

> Yeah, I was thinking, external calls based on some trigger would have
> been a way to handle it. I don't think MySQL still supports that even
> in 5.x.

it should be possible with some hacking. it is arguable whether it would be
worth it. doing it from within mysql, where you get to use their query
parsing etc, is probably a wiser option.

On 11/7/05, Paul G <paul-lists at perforge.com> wrote:

>> mysql already does query caching, from 4.something and up i believe. the
>> catch there is that a write to said table invalidates the cache.
depending
>> on your usage pattern, this can be hacked around by sticking updates into
a
>> second table with the same schema, performing queries against both and
>> flushing to the real table at an interval. this is still inferior to
using
>> memcached of course, but comparable to pgmemcache with no mucking about
>> required.

>MySQL query cache is a great idea... but I'm not sure how useful. The
>problem is, the same problem I see I think - they most likely do-table
>level caching because of how hard it would be to cache the resultsets
>for different types of queries mixing and matching columns that may or
>may not be affected by a write query.

>Even Brad says on the memcache main page:

>"MySQL's query cache destroys the entire cache for a given table
>whenever that table is changed. On a high-traffic site with updates
>happening many times per second, this makes the the cache practically
>worthless. In fact, it's often harmful to have it on, since there's a
>overhead to maintain the cache."

i said exactly this in my previous mail, which you quoted ;) "the catch
there is that a write to said table invalidates the cache.", sez paul.

>"If the data you need to cache is small and you do infrequent updates,
>MySQL's query caching should work for you. If not, use memcached."

i also posted a workaround, which may or may not be appropriate for any
given user, namely to 'buffer' inserts in another table and flush them
periodically. a good example of a case where this wouldn't work is if you're
doing updates/deletes ;]

-p