mysql load, help with ideas related to local cache

Kirk Cerny 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.
http://dev.mysql.com/doc/refman/5.0/en/myisampack.html

I have tried doing this on read only data with awesome success.

Kirk Cerny

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
>> 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.
>> */
>>
>
> /*
> 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