telling code to pull from the database when the data in a table changes

[KevinImNotSpacey]

Hey Nathan,

I think the appropriate solution is largely dependent on your particular
situation.  For instance, we have one website that is media hosting, we
cache media requests for 10 minutes.  Now if a user updates their media
description or something we simply tell them that it will not show up on the
site right away.  Then after roughly ten minutes the media expires from
cache, upon the next request our php code gets null back from memcached,
gets the media from the database and disk, puts in memcache and then
returns.

So we don't use cron or any other batch processing, instead we figure that
stale for 10 minutes is ok and just let the content naturally expire from
cache.

Hope this helps,
Kevin

On 5/21/07, Nathan Nobbe <quickshiftin at gmail.com> wrote:
>
> All,
>
> I recently began investigating memcached, and im very excited about it.
> im planning on using it w/ php to implement variables whose lifetime can be
> the duration of the application.
> I understand that storing the results of queries to the database can
> increase efficiency if those variables are checked for the data first on
> consecutive requests for the data they store, but
> i still have a disconnect...
> what i dont understand is when the data profile of a particular table
> changes, and the variable in the memcache that represents that table becomes
> stale, how can the variable be updated efficiently?
> obviously w/ php and a  unix system, a simple cron could be used to poll
> the database periodically to see if there are changes to the data profile of
> a given table.  i was thinking to make this even more
> efficient; triggers could be employed (im using mysql 5+) to update a
> table, call it recent_tables_that_changed, dedicated to signifying
> particular tables in the database have changed.  then the cron'd php script
> could simply check recent_tables_that_changed  to determine which, if any,
> application variables had become stale (do to a change in data profile).
> the stale variable  could then be updated and the record removed from
> recent_tables_that_changed.
> though decent, i am personally not a fan of this approach, because there
> would still be a frequent query running against the database just to
> determine what data had changed so that application variables in the cache
> would not remain stale for long once theyve become stale.  additionally,
> the mechanisms' responsiveness would be limited to the time granularity of
> cron, which i believe is a minute.  im wondering if there is some way to
> signal the cache only when tables, represented by application variables,
> have changed.  if you dont feel like typing out a response, because this is
> probably a naive question, would anyone mind directing me to a resource or
> two where i could read up on the topic myself?
>
> (sorry for the lengthy posting)
> thanks,
>
> -Nathan
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.danga.com/pipermail/memcached/attachments/20070521/32de3ddf/attachment-0001.htm