[Nagios-devel] NDOutils MySQL performance issues

[Thorsten T. <tue...@rz...>]

Hi,

since I had some issues with slow queries on my MySQL-DB, I digged a bit
deeper into the NDO database scheme. I would recommend to add some indices:

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

ALTER TABLE `nagios_configfilevariables` DROP INDEX `instance_id` ,
ADD INDEX `instance_id` ( `instance_id` , `configfile_id` )

should do the trick.

ciao,
Thorsten