nagios 2.8 + NDO 1.4b2 + NDO2DB = high service latency

[Ton Voon]

On 4 May 2007, at 11:39, Robin Ericsson wrote:

> On 5/4/07, Ton Voon <ton.voon at altinity.com> wrote:
>> Just to let you know that we've found DB contention issues with
>> Nagios 2.8 with NDO 1.4b2. Mysql takes a very long time (around 30
>> seconds) to run the (from memory) "DELETE FROM nagios_servicechecks
>> WHERE instance_id = 1 AND entry_time < ....." query. This holds up
>> other things, though I haven't fully understood where or why.
>>
>> The problem is with the indexes for nagios_servicechecks. We dropped
>> the unique key index, which was (instance_id, service_object,
>> entry_time, uentrysec), and created a new index based on entry_time
>> only. This is much quicker because (a) the index is smaller, (b)
>> mysql can locate the correct rows quicker because it finds the rows
>> based on time, rather than trying to find rows based on instance_id
>> (which is every row in the table - effectively doing a full table  
>> scan).
>>
>> (As an aside, mysql will not enforce the uniqueness on that index -
>> it only works for tables of type innodb, not myisam.)
>
> Seems like a very bad I idea to drop the unique key index, just create
> another index that works better with the query.

Wise words. I've just discovered that for each servicecheck result,  
it is inserted into nagios_servicechecks table twice.

The first time has results from the previous servicecheck result, but  
with the current start time. The 2nd time actually has the result  
from the servicecheck.

The insert is done with a "INSERT ... ON DUPLICATE KEY UPDATE ...",  
so with the unique key, it overwrites it. But with the index dropped,  
there are two entries.

(My previous statement about mysql not enforcing the uniqueness of a  
row is now clearly wrong, so I retract that :) )

Obviously we need to restore the unique index, but the better fix  
would be to work out why there are two inserts....


> If the query contains "WHERE instance_id = 1 and entry_time <", create
> an index on instance_id, entry_time which should make mysql fly a
> little faster.

I think maybe if the order of the index is arranged, it would work  
much better. For instance, set the unique index to be (start_time,  
instance_id, service_object_id, start_time_usec). Then for the query,  
the db will use the start_time first (which will reduce the number of  
rows to the smallest number found) and then check the instance_id.

The trouble was that the previous index returned back all rows (if  
you only have one instance_id), so a full table scan was required to  
find the start_time.

Ton

http://www.altinity.com
T: +44 (0)870 787 9243
F: +44 (0)845 280 1725
Skype: tonvoon



-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
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