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