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

[Robin Ericsson]

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.

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.

If you have a trouble query you can always use explain to see what
mysql is trying to do with it index wise and you can create a better
index, etc. When the indexes are where they should, you can tweak
various settings in mysql so that you get a better hitrate from
various caches.

-- 
        regards,
        Robin

-------------------------------------------------------------------------
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