Page 2 of 4
Re: XI 2011R3.2 Mysql Crash
Posted: Fri Oct 19, 2012 4:50 pm
by arnab.roy
Ok something within notifications.php...I have narrowed it down...everthing else looks ok...sorry if I have caused confussion...but I am in a little bit of panick moment now...!!!
Re: XI 2011R3.2 Mysql Crash
Posted: Fri Oct 19, 2012 6:07 pm
by scottwilkerson
Ok, so without the max_connections set in /etc/my.cnf the default is 50 connections without blocking.
I counted the process list you displayed and it was right below 50
I would recommend adding the following to your /etc/my.cnf right below [mysqld]
and then restart mysqld
Re: XI 2011R3.2 Mysql Crash
Posted: Fri Oct 19, 2012 6:36 pm
by arnab.roy
Sorry scott, that made no difference to the system....any more ideas ? I am temporarily moving the notifications.php to prevent someone from clicking on it and killing the server......
Re: XI 2011R3.2 Mysql Crash
Posted: Sat Oct 20, 2012 2:16 pm
by arnab.roy
Hi Guys,
I have been comparing the performance between this XI server and another of our production system regarding this issue.
1. Is an XI built on centos 5.8 running mysql v 5.0.18 - And ran a notification report for the whole year that executed within about 1 min and fetched approx 33k results, this server has 4 cores and 8GB RAM
2. Is an XI built on Centos 6.3 running mysql v 5.1.61 -Ran a report for a week with 148 records takes almost 1 minute execute , this server has 8 cores and 12GB RAM
So everything points to the version of mysql.................................?
To further troubleshoot this can you provide me some details on the backend code
1. DB querries ,exact query being passed back...
2. Can I try upgrading Mysql to 5.5 are their any known compatibility issues?
Many Thanks
Arnab
Re: XI 2011R3.2 Mysql Crash
Posted: Mon Oct 22, 2012 9:59 am
by mguthrie
Going to follow up here so others can continue to chime in.
I'll be doing some testing on this today to see what I can figure out. We've had a few other reports of problems with this page, so it may not be specific to just your environment.
Re: XI 2011R3.2 Mysql Crash
Posted: Mon Oct 22, 2012 11:13 am
by arnab.roy
Thanks Mike,
If you need sql backups from this box to replicate the challenge than I might be able to provide you with that let me know.
Many Thanks
Arnab
Re: XI 2011R3.2 Mysql Crash
Posted: Mon Oct 22, 2012 1:02 pm
by mguthrie
I think I got it. Comment out the code on line 687 of the following file and see if things work more smoothly.
/usr/local/nagiosxi/html/includes/utils-xmlreports.inc.php
Line 686-687:
Code: Select all
//add group by clause to prevent duplicate entries
//"groupby" => " GROUP BY `notification_id`,`contact_object_id`,`contactnotification_id`,`contactnotificationmethod_id` ",
Re: XI 2011R3.2 Mysql Crash
Posted: Mon Oct 22, 2012 2:39 pm
by arnab.roy
No luck my code now looks like this :
Code: Select all
$default_order="start_time DESC, notification_id DESC";
$args=array(
"sql" => $sqlquery['GetNotificationsWithContacts'].$sqlmods,
"fieldmap" => $fieldmap,
"objectauthfields" => $objectauthfields,
"instanceauthfields" => $instanceauthfields,
"instanceauthperms" => P_LIST,
"default_order" => $default_order,
"useropts" => $request_args, // ADDED 12/22/09 FOR NEW NON-BACKEND CALLS
//add group by clause to prevent duplicate entries
//"groupby" => " GROUP BY notification_id,contact_object_id,contactnotification_id,contactnotificationmethod_id ",
);
$sql=generate_sql_query(DB_NDOUTILS,$args);
I am not even get details for 24 hours.....
Re: XI 2011R3.2 Mysql Crash
Posted: Mon Oct 22, 2012 2:41 pm
by arnab.roy
Just to add their is about 1642 records in that table...
Re: XI 2011R3.2 Mysql Crash
Posted: Mon Oct 22, 2012 2:52 pm
by arnab.roy
Ok , I think I might have found the issue I may be wrong :
I dumped out the query that is passed on by printing the debug you guys left on the code to me it looks like the same query is being executed twice 1. with 100000 as the limit and 2 with 25 as the limit???? Is that what you want to do ?
1. SQL: SELECT obj1.objecttype_id as objecttype_id, obj1.name1 AS host_name, obj1.name2 AS service_description, obj2.name1 AS contact_name, obj3.name1 AS notification_command, nagios_contactnotifications.contactnotification_id, nagios_contactnotifications.contact_object_id, nagios_contactnotificationmethods.command_object_id, nagios_contactnotificationmethods.command_args, nagios_contactnotificationmethods.contactnotificationmethod_id, nagios_notifications.* FROM nagios_notifications LEFT JOIN nagios_objects as obj1 ON nagios_notifications.object_id=obj1.object_id LEFT JOIN nagios_contactnotifications ON nagios_notifications.notification_id=nagios_contactnotifications.notification_id LEFT JOIN nagios_objects as obj2 ON nagios_contactnotifications.contact_object_id=obj2.object_id LEFT JOIN nagios_contactnotificationmethods ON nagios_contactnotifications.contactnotification_id=nagios_contactnotificationmethods.contactnotification_id LEFT JOIN nagios_objects as obj3 ON nagios_contactnotificationmethods.command_object_id=obj3.object_id WHERE TRUE AND nagios_notifications.contacts_notified > '0' AND nagios_notifications.start_time >= '2012-10-21 20:40:33' AND nagios_notifications.start_time <= '2012-10-22 20:40:33' AND nagios_notifications.instance_id = '1' ORDER BY start_time DESC, notification_id DESC LIMIT 100000
SQL: SELECT obj1.objecttype_id as objecttype_id, obj1.name1 AS host_name, obj1.name2 AS service_description, obj2.name1 AS contact_name, obj3.name1 AS notification_command, nagios_contactnotifications.contactnotification_id, nagios_contactnotifications.contact_object_id, nagios_contactnotificationmethods.command_object_id, nagios_contactnotificationmethods.command_args, nagios_contactnotificationmethods.contactnotificationmethod_id, nagios_notifications.* FROM nagios_notifications LEFT JOIN nagios_objects as obj1 ON nagios_notifications.object_id=obj1.object_id LEFT JOIN nagios_contactnotifications ON nagios_notifications.notification_id=nagios_contactnotifications.notification_id LEFT JOIN nagios_objects as obj2 ON nagios_contactnotifications.contact_object_id=obj2.object_id LEFT JOIN nagios_contactnotificationmethods ON nagios_contactnotifications.contactnotification_id=nagios_contactnotificationmethods.contactnotification_id LEFT JOIN nagios_objects as obj3 ON nagios_contactnotificationmethods.command_object_id=obj3.object_id WHERE TRUE AND nagios_notifications.contacts_notified > '0' AND nagios_notifications.start_time >= '2012-10-21 20:40:33' AND nagios_notifications.start_time <= '2012-10-22 20:40:33' AND nagios_notifications.instance_id = '1' ORDER BY start_time DESC, notification_id DESC LIMIT 25