ndoutils Query optimisation

[Alan Cooper]

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?

The database server is a dual  3.00GHz Xeon with 4GB RAM

My MySQL config is as follows:


[client]

port            = 3306
socket          = /var/lib/mysql/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 512M
max_allowed_packet = 2M
table_cache = 1024
sort_buffer_size = 4M
read_buffer_size = 16M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 32M
thread_cache_size = 16

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

skip-name-resolve
skip_name_resolve

server-id       = 1

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout


Any help and suggestions gratefully received.





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