XI 2011R3.2 Mysql Crash

[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...!!!

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

Code: Select all

set-variable=max_connections=500

and then restart mysqld

Code: Select all

service mysqld restart

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

[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

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

[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

[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` ", 

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

[arnab.roy]

Just to add their is about 1642 records in that table...

[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