-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 20/08/08 04:56 AM, Alan Cooper wrote: > I am having problem scaling up a system we use to gather status data > from the NDO db: > > The query I currently use is:- > > SELECT > obj1.name1 AS host_name, > nagios_hoststatus.problem_has_been_acknowledged, > nagios_hoststatus.scheduled_downtime_depth, > nagios_hosts.alias > FROM `nagios_hoststatus` > LEFT JOIN nagios_objects as obj1 ON > nagios_hoststatus.host_object_id=obj1.object_id > LEFT JOIN nagios_hosts ON > nagios_hoststatus.host_object_id=nagios_hosts.host_object_id > LEFT JOIN nagios_hostgroup_members ON > nagios_hoststatus.host_object_id=nagios_hostgroup_members.host_object_id > LEFT JOIN nagios_hostgroups ON > nagios_hostgroups.hostgroup_id=nagios_hostgroup_members.hostgroup_id > WHERE nagios_hosts.config_type='1' > AND nagios_hoststatus.state_type > 0 > AND nagios_hoststatus.current_state > 0 > AND (nagios_hostgroups.alias = "hostgroup1" > OR nagios_hostgroups.alias = "hostgroup3" > ORDER BY host_name ASC; > > This gives us list of hosts and their aliases as well as their > acknowledgement and downtime status which allows us to get different > views on a status screen for different purposes. > > The problem is, due to the number of joins and the fact that these > SELECTS are being called several times a minute, we are generating very > high load on the database as data is being copied into temporary tables > and each query is taking >20 seconds, and eventually it just all breaks > down and no queries succeed. > > Does anyone have any suggestions for improving performance of this query?
I'm not a SQL guru, but here's some thing I'd try... You may get better help from SQL/MySQL communities. * Make sure obj1.name1 is indexed (or is a PK) * Make sure every columns in the JOINs are indexed/PKs * Try removing the ORDER BY (i.e. sort it in the application running the query instead) You can also try importing the tables to a different engine (i.e. InnoDB vs MyISAM) in a new db or with different names, and benchmark it on them. Hope this helps... - -- Thomas -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFIrAk46dZ+Kt5BchYRAs2pAJ4+aNdcKX8mjO++F6U0VU2oL2J1mgCgxoR5 i8X1OZ97tsHkdbwDAYNvvuw= =DrYE -----END PGP SIGNATURE----- ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ Nagios-users mailing list Nagios-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/nagios-users ::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. ::: Messages without supporting info will risk being sent to /dev/null
