mysql load, help with ideas related to local cache

Matt Nelson matt at frozenatom.com
Wed Feb 25 12:40:10 MST 2009


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

> On Wed Feb 25 2009 09:00:39 Matthew Walker wrote:
> > On Wed, February 25, 2009 8:43 am, Kirk Cerny wrote:
> > > 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.
> >
> > I don't think this fits his situation, because the data is being updated
> by
> > other tools. It's just /this/ tool is only running selects.
> >
> > I'm curious as to why the selects should be causing a problem. How many
> > queries per second are we talking about, and have you doublechecked that
> > indexes are being used?
>
> Agreed.
>
> Also, my original suggestion may still help. If the problem is caused
> because
> these selects are complicated, then what you want to do is compute the
> resulting report once and cache it for multiple readers, thus minimizing
> the
> slower queries to the update interval and not scaling the update queries
> with
> the number of readers.
>
>
Looking into this it looks like it might be locking the npc_servicestatus
table which causes the others using this dashboard to backup and eventually
tips over the default connection limit.  My mysql is not the greatest.  When
I run "show processlist;" it gives me:

...
| 5780040 | nogwebuser  | nocnag02:60597 | nocnag2_cacti | Query   |       2
| Locked                                                                |
select npc_hostgroups.alias,SUM(npc_servicestatus.state_type),
SUM(npc_servicestatus.problem_has_bee |
| 5780041 | nogwebuser  | nocnag02:60598 | nocnag2_cacti | Query   |       2
| Copying to tmp table                                                  |
select DISTINCT npc_hosts.alias from npc_hosts LEFT JOIN npc_services on
npc_hosts.host_object_id=np |
| 5780042 | nogwebuser  | nocnag02:60599 | nocnag2_cacti | Query   |       2
| Locked                                                                |
select last_check from npc_servicestatus ORDER BY last_check DESC LIMIT
1                            |
| 5780043 | nogwebuser  | nocnag02:60600 | nocnag2_cacti | Query   |       2
| Locked                                                                |
select last_check from npc_servicestatus ORDER BY last_check DESC LIMIT
1                            |
...


Am I reading this "Locked" state correctly? And does this look like a lock
contention problem.


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