NDOutils MySQL performance issues

[Thorsten Tüllmann]

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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 7491 bytes
Desc: S/MIME Cryptographic Signature
URL: <https://www.monitoring-lists.org/archive/developers/attachments/20080325/32e76b8b/attachment.bin>
-------------- next part --------------
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
-------------- next part --------------
_______________________________________________
Nagios-devel mailing list
Nagios-devel at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nagios-devel