mysql load, help with ideas related to local cache

Matt Nelson matt at frozenatom.com
Wed Feb 25 08:34:53 MST 2009


On Wed, Feb 25, 2009 at 7:51 AM, Nicholas Leippe <nick at leippe.com> wrote:

> 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.
>
>
> Sorry just got in this morning, and was unable to get to a computer last
night to respond.  I appreciate everyone's comments.  Nicholas, what you
have described is correct.  I should have included a few details that I left
out.

Specifically I have a dashboard that is written in php, and runs via
apache.  It connects to two separate databases with each 5 second
meta-refresh.  One database is for nagios stats, nodb, and the other is on a
separate mysql instance where we get some other stats.  for both of these
databases we are only running selects.

Where it is a dashboard each user slapping that page will want the data back
so I don't believe a push model, or a lock file with a timestamp would
work.  <- Sorry I don't think I gave enough info earlier to clairify that
this would be the case.

As far as Barry's comment related to memcached, I refuse to use this because
Barry suggested it haha jk.  Barry and I used to work together, this
actually looks like it might be promising, and I plan to dig into this today
along with anything else that might come by that sounds promising.

Thanks for all the comments and suggestions, and keep em comming.


>
>
>
> /*
> PLUG: http://plug.org, #utah on irc.freenode.net
> Unsubscribe: http://plug.org/mailman/options/plug
> Don't fear the penguin.
> */
>



More information about the PLUG mailing list