Optimisation NDOUtils

CREATE INDEX start_time_idx ON nagios_hostchecks (start_time);
CREATE INDEX start_time_idx ON nagios_servicechecks (start_time);
CREATE INDEX scheduled_time_idx ON nagios_timedeventqueue (scheduled_time);

Some of the DELETE operations depend on these columns in their WHERE statement and the performance gain was quite impressive.

My DB has about 1.5GB size running locally on the nagios host. Load15 used to be around 1.7. Right now the numbers look quite different: load average: 0.18, 0.39, 0.34.

Furthermore I had some strange errors in my syslog looking like:

ndo2db: Error: mysql_query() failed for 'INSERT INTO
nagios_configfilevariables SET instance_id='1', configfile_id='101',
varname='cfg_file',
varvalue='/usr/local/nagios/etc/objects/contacts-extern\.cfg''

Obviously the unique key in nagios_configfilevariables is not that unique. Something like

CREATE INDEX start_time_idx ON nagios_hostchecks (start_time);
CREATE INDEX start_time_idx ON nagios_servicechecks (start_time);
CREATE INDEX scheduled_time_idx ON nagios_timedeventqueue (scheduled_time);
ALTER TABLE `nagios_configfilevariables` DROP INDEX `instance_id` ;
ADD INDEX `instance_id` ( `instance_id` , `configfile_id` );

Autres possibilité d’index à ajouter

servicechecks / service_object_id
servicechecks / state
hostcheck / host_object_id
hostcheck / state
objects / objecttype_id
services / host_object_id
statehistory / object_id
statehistory / state_change
statehistory / state
statehistory / state_time
statehistory / last_state
hoststatus / last_hard_state
servicestatus / last_hard_state

Also consider adding this to your mysql config (for example in a file under /etc/mysql/conf.d/ ) :

[mysqld]
innodb_flush_log_at_trx_commit = 2

It might mean you could lose a few seconds worth of data in the event of a crash. Whether this matters is for you to decide. I found this change made a dramatic difference to the startup time for Nagios.