mysql load, help with ideas related to local cache

Nicholas Leippe nick at leippe.com
Wed Feb 25 07:51:08 MST 2009


On Tue Feb 24 2009 17:14:38 Wade Preston Shearer wrote:
> On 24 Feb 2009, at 16:35, Nicholas Leippe wrote:
> > Just add a timestamp and a lock. If a page request finds the
> > timestamp is too  old, attempt to grab the lock and then do the
> > refresh queries and update the timestamp. If it fails at the lock,
> > another page got it and did it, so poll until the timestamp is
> > changed and then go. (If you really need speed you could implement
> > the timestamp test to block until ready.)
>
> While I do not have much experience with locking database records, how
> will that eliminate the hits to the database? Is a hit that encounters
> a lock and then stops significantly less overhead than a hit that
> retrieves a record?

I inferred from your problem description that you had a browser open to a web 
page that used a metarefresh every 5-10s. This pagehit caused the database to 
run a bunch of queries to update some tables. Once additional people were 
watching this page as well, this caused more of the same update queries to be 
run and it became problematic.

This is a polling model.

The solution I proposed turns it closer to an interrupt driven model, and 
makes it so that updates are never ran more frequently than the desired 
interval.

If I got the problem wrong, please elaborate.








More information about the PLUG mailing list