nagios 2.8 + NDO 1.4b2 + NDO2DB = high servicelatency
Janet Post
Janet.Post at excapsa.ca
Fri May 4 17:54:56 CEST 2007
Here is a quick question that my local DBA just asked me:
Can ndo2db take advantage of InnoDB tables?
Janet
-----Original Message-----
From: nagios-users-bounces at lists.sourceforge.net
[mailto:nagios-users-bounces at lists.sourceforge.net] On Behalf Of Robin
Ericsson
Sent: May 4, 2007 6:39 AM
To: Ton Voon
Cc: Nagios Users mailinglist
Subject: Re: [Nagios-users] nagios 2.8 + NDO 1.4b2 + NDO2DB = high
servicelatency
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
-------------------------------------------------------------------------
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
More information about the Users
mailing list