Here is a quick question that my local DBA just asked me: Can ndo2db take advantage of InnoDB tables?
Janet -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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@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@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
