mysql load, help with ideas related to local cache
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
> page that used a metarefresh every 5-10s. This pagehit caused the database
> 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
> 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
> 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
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