mysql load, help with ideas related to local cache
kirksemail at gmail.com
Wed Feb 25 08:43:57 MST 2009
If the data is slow comming back where you are only doing SELECTs you
could look at compressing the data in mysql using myisampack.
I have tried doing this on read only data with awesome success.
On Wed, Feb 25, 2009 at 8:34 AM, Matt Nelson <matt at frozenatom.com> wrote:
> 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.
> 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