ndoutils Query optimisation

[Thomas Guyot-Sionnest]

-----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 at 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