On Wed, Feb 25, 2009 at 9:13 AM, Nicholas Leippe <[email protected]> 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. > */ > /* PLUG: http://plug.org, #utah on irc.freenode.net Unsubscribe: http://plug.org/mailman/options/plug Don't fear the penguin. */
