Ndoutils MySQL performance problem?

[Ciro Iriarte]

2009/3/3 Livio Zanol Puppim <livio.zanol.puppim at gmail.com>:
> Hello everyone!
>
> I'm having huge troubles with Mysql using 100% of CPU at peculiar times. I
> don't know when this problem starts and I don't know the reason, but when it
> happends I kill nagios, ndoutils and Mysql, and try to restart Mysql,
> ndoutils and Nagios at this order. When I start Nagios, the CPU usage of
> Mysql goes to 100% and everything just freezes with the initials
> INSERT/UPDATES made by ndoutils.
>
> Has anyone had the same problem? I've changed my.cnf to hold slow-queries
> logging, an have seen at intervals of 1 minute a query runned by ndo that
> tries to delete service_checks results. It takes about 4 seconds too run. Is
> this expected? Can anyone help me?
>
> Running Nagios 3.0.3, Debian etch, NDO2DB 1.4b7 and mysql  Ver 14.12 Distrib
> 5.0.45.
>
>
> Slow-Queries log:
> # Time: 090303 14:29:25
> # User at Host: ndoutils[ndoutils] @ localhost []
> # Query_time: 4  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
> DELETE FROM nagios_servicechecks WHERE instance_id='1' AND
> start_time<FROM_UNIXTIME(1235496561);
> # Time: 090303 14:30:26
> # User at Host: ndoutils[ndoutils] @ localhost []
> # Query_time: 4  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
> DELETE FROM nagios_servicechecks WHERE instance_id='1' AND
> start_time<FROM_UNIXTIME(1235496622);
>
> Thank you.
> --
> []'s
>
> Lívio Zanol Puppim
>

Try creating a index for that table

-----
create index   nagios_servicechecks_idx1 on nagios_servicechecks
(start_time, instance_id)
-----

I'm not sure if the order would change the result though

(start_time, instance_id) OR (instance_id, start_time)

Regards,

-- 
Ciro Iriarte
http://cyruspy.wordpress.com
--

------------------------------------------------------------------------------
Open Source Business Conference (OSBC), March 24-25, 2009, San Francisco, CA
-OSBC tackles the biggest issue in open source: Open Sourcing the Enterprise
-Strategies to boost innovation and cut costs with open source participation
-Receive a $600 discount off the registration fee with the source code: SFAD
http://p.sf.net/sfu/XcvMzF8H
_______________________________________________
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