From owner-freebsd-ports-bugs@FreeBSD.ORG Fri Nov 19 14:50:29 2004 Return-Path: Delivered-To: freebsd-ports-bugs@hub.freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id C2B9916A4CF for ; Fri, 19 Nov 2004 14:50:29 +0000 (GMT) Received: from freefall.freebsd.org (freefall.freebsd.org [216.136.204.21]) by mx1.FreeBSD.org (Postfix) with ESMTP id 82AF943D1F for ; Fri, 19 Nov 2004 14:50:29 +0000 (GMT) (envelope-from gnats@FreeBSD.org) Received: from freefall.freebsd.org (gnats@localhost [127.0.0.1]) iAJEoTOi086238 for ; Fri, 19 Nov 2004 14:50:29 GMT (envelope-from gnats@freefall.freebsd.org) Received: (from gnats@localhost) by freefall.freebsd.org (8.12.11/8.12.11/Submit) id iAJEoTfl086217; Fri, 19 Nov 2004 14:50:29 GMT (envelope-from gnats) Resent-Date: Fri, 19 Nov 2004 14:50:29 GMT Resent-Message-Id: <200411191450.iAJEoTfl086217@freefall.freebsd.org> Resent-From: FreeBSD-gnats-submit@FreeBSD.org (GNATS Filer) Resent-To: freebsd-ports-bugs@FreeBSD.org Resent-Reply-To: FreeBSD-gnats-submit@FreeBSD.org, Seva Gluschenko Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 8E0D016A4CE for ; Fri, 19 Nov 2004 14:42:25 +0000 (GMT) Received: from y1.yandex.ru (y1.yandex.ru [213.180.193.141]) by mx1.FreeBSD.org (Postfix) with ESMTP id 9F73C43D39 for ; Fri, 19 Nov 2004 14:42:24 +0000 (GMT) (envelope-from gvs@y1.yandex.ru) Received: from y1.yandex.ru (localhost [127.0.0.1]) by y1.yandex.ru (8.12.11/8.12.11) with ESMTP id iAJEgMSX002290; Fri, 19 Nov 2004 17:42:22 +0300 (MSK) (envelope-from gvs@y1.yandex.ru) Received: (from gvs@localhost) by y1.yandex.ru (8.12.11/8.12.11/Submit) id iAJEgL7V002238; Fri, 19 Nov 2004 17:42:21 +0300 (MSK) (envelope-from gvs) Message-Id: <200411191442.iAJEgL7V002238@y1.yandex.ru> Date: Fri, 19 Nov 2004 17:42:21 +0300 (MSK) From: Seva Gluschenko To: FreeBSD-gnats-submit@FreeBSD.org X-Send-Pr-Version: 3.113 cc: blaz@si.FreeBSD.org Subject: ports/74117: net-mgmt/nagios performance patches X-BeenThere: freebsd-ports-bugs@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Ports bug reports List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 19 Nov 2004 14:50:29 -0000 >Number: 74117 >Category: ports >Synopsis: net-mgmt/nagios performance patches >Confidential: no >Severity: non-critical >Priority: medium >Responsible: freebsd-ports-bugs >State: open >Quarter: >Keywords: >Date-Required: >Class: update >Submitter-Id: current-users >Arrival-Date: Fri Nov 19 14:50:28 GMT 2004 >Closed-Date: >Last-Modified: >Originator: Seva Gluschenko >Release: FreeBSD 4.10-RELEASE-p3 i386 >Organization: Yandex LLC >Environment: System: FreeBSD y1.yandex.ru 4.10-RELEASE-p3 FreeBSD 4.10-RELEASE-p3 #0: Wed Nov 17 11:41:01 MSK 2004 root@y1.yandex.ru:/usr/local/smb-shares/obj/usr/src/sys/Y1 i386 >Description: Nagios interacts with MySQL the very expensive way using DELETE FROM for erasing tables and SELECT w/o WHERE for getting host and service status. This makes database interaction performance significantly low. The proposed patch addresses those problems. >How-To-Repeat: Place the following patches to files/ directory, rebuild and reinstall nagios. Create indexes in Nagios table per host_status and service_status fields. >Fix: File: patch-cgi-cgiutils.c --- cgi/cgiutils.c.orig Thu Nov 18 17:32:31 2004 +++ cgi/cgiutils.c Thu Nov 18 18:47:25 2004 @@ -613,11 +613,11 @@ /* don't duplicate things we've already read in */ if(program_status_has_been_read==TRUE && (options & READ_PROGRAM_STATUS)) - options-=READ_PROGRAM_STATUS; + options &= ~READ_PROGRAM_STATUS; if(host_status_has_been_read==TRUE && (options & READ_HOST_STATUS)) - options-=READ_HOST_STATUS; + options &= ~READ_HOST_STATUS; if(service_status_has_been_read==TRUE && (options & READ_SERVICE_STATUS)) - options-=READ_SERVICE_STATUS; + options &= ~READ_SERVICE_STATUS; /* bail out if we've already read what we need */ if(options<=0) File: patch-cgi-status.c --- cgi/status.c.orig Thu Nov 18 17:34:22 2004 +++ cgi/status.c Thu Nov 18 17:58:20 2004 @@ -203,7 +203,8 @@ } /* read all status data */ - result=read_all_status_data(DEFAULT_CGI_CONFIG_FILE,READ_ALL_STATUS_DATA); + result=read_all_status_data(DEFAULT_CGI_CONFIG_FILE,READ_ALL_STATUS_DATA | + (host_status_types << 4) | (service_status_types << 8)); if(result==ERROR){ document_header(FALSE); status_data_error(); File: patch-xrddb.c-extra --- xdata/xrddb.c.orig Thu May 16 06:46:11 2002 +++ xdata/xrddb.c Fri Nov 19 15:58:25 2004 @@ -534,7 +534,7 @@ xrddb_begin_transaction(); /* delete old program retention information */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s",XRDDB_PROGRAMRETENTION_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s",XRDDB_PROGRAMRETENTION_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; result=xrddb_query(sql_statement); xrddb_free_query_memory(); @@ -605,7 +605,7 @@ xrddb_begin_transaction(); /* delete old host retention information */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s",XRDDB_HOSTRETENTION_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s",XRDDB_HOSTRETENTION_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; result=xrddb_query(sql_statement); xrddb_free_query_memory(); @@ -698,7 +698,7 @@ xrddb_begin_transaction(); /* delete old service retention information */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s",XRDDB_SERVICERETENTION_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s",XRDDB_SERVICERETENTION_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; result=xrddb_query(sql_statement); xrddb_free_query_memory(); File: patch-xsddb.c-extra --- xdata/xsddb.c.orig Fri Nov 19 15:14:17 2004 +++ xdata/xsddb.c Fri Nov 19 17:21:18 2004 @@ -653,7 +653,7 @@ xsddb_begin_transaction(); /* delete all entries from the program status table */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s;",XSDDB_PROGRAMSTATUS_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s;",XSDDB_PROGRAMSTATUS_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; result=xsddb_query(sql_statement); xsddb_free_query_memory(); @@ -663,7 +663,7 @@ } /* delete all entries from the host status table */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s;",XSDDB_HOSTSTATUS_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s;",XSDDB_HOSTSTATUS_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; result=xsddb_query(sql_statement); xsddb_free_query_memory(); @@ -673,7 +673,7 @@ } /* delete all entries from the service status table */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s;",XSDDB_SERVICESTATUS_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s;",XSDDB_SERVICESTATUS_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; result=xsddb_query(sql_statement); xsddb_free_query_memory(); @@ -716,19 +716,19 @@ #endif /* delete all entries from the program status table */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s",XSDDB_PROGRAMSTATUS_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s",XSDDB_PROGRAMSTATUS_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; xsddb_query(sql_statement); xsddb_free_query_memory(); /* delete all entries from the host status table */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s",XSDDB_HOSTSTATUS_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s",XSDDB_HOSTSTATUS_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; xsddb_query(sql_statement); xsddb_free_query_memory(); /* delete all entries from the service status table */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s",XSDDB_SERVICESTATUS_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s",XSDDB_SERVICESTATUS_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; xsddb_query(sql_statement); xsddb_free_query_memory(); @@ -786,7 +786,7 @@ #endif /* delete all entries from the program status table */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s;",XSDDB_PROGRAMSTATUS_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s;",XSDDB_PROGRAMSTATUS_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; result=xsddb_query(sql_statement); xsddb_free_query_memory(); @@ -802,7 +802,7 @@ } /* delete all entries from the host status table */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s;",XSDDB_HOSTSTATUS_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s;",XSDDB_HOSTSTATUS_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; result=xsddb_query(sql_statement); xsddb_free_query_memory(); @@ -818,7 +818,7 @@ } /* delete all entries from the service status table */ - snprintf(sql_statement,sizeof(sql_statement)-1,"DELETE FROM %s;",XSDDB_SERVICESTATUS_TABLE); + snprintf(sql_statement,sizeof(sql_statement)-1,"TRUNCATE TABLE %s;",XSDDB_SERVICESTATUS_TABLE); sql_statement[sizeof(sql_statement)-1]='\x0'; result=xsddb_query(sql_statement); xsddb_free_query_memory(); @@ -1267,14 +1267,14 @@ /* read in all host status information */ if(options & READ_HOST_STATUS){ - result=xsddb_add_host_status(); + result=xsddb_add_host_status(options >> 4); if(result==ERROR) return ERROR; } /* read in all service status information */ if(options & READ_SERVICE_STATUS){ - result=xsddb_add_service_status(); + result=xsddb_add_service_status(options >> 8); if(result==ERROR) return ERROR; } @@ -1450,7 +1450,7 @@ /* adds host status information */ -int xsddb_add_host_status(void){ +int xsddb_add_host_status(int options){ char sql_statement[XSDDB_SQL_LENGTH]; int result; int x; @@ -1489,6 +1489,34 @@ #ifdef USE_XSDPGSQL snprintf(sql_statement,sizeof(sql_statement)-1,"SELECT host_name, host_status, date_part('epoch',last_update) AS last_update, date_part('epoch',last_check) AS last_check, date_part('epoch',last_state_change) AS last_state_change, problem_acknowledged, time_up, time_down, time_unreachable, date_part('epoch',last_notification) AS last_notification, current_notification, notifications_enabled, event_handler_enabled, checks_enabled, flap_detection_enabled, is_flapping, percent_state_change, scheduled_downtime_depth, failure_prediction_enabled, process_performance_data, plugin_output FROM %s",XSDDB_HOSTSTATUS_TABLE); #endif + /* mask extra options */ + options &= HOST_ALL; + /* should we use WHERE here? -gvs */ + if (options != HOST_ALL) { + /* + * this is quick & dirty but it should cover nearly 90% of + * status requests, so we can allow expensive queries for + * the rest 10% -gvs + */ + strncat(sql_statement, + (options & HOST_PENDING == options) ? + " WHERE host_status=\"PENDING\";" : + (options & HOST_DOWN == options) ? + " WHERE host_status=\"DOWN\";" : + (options & HOST_UNREACHABLE == options) ? + " WHERE host_status=\"UNREACHABLE\";" : + (options & HOST_UP == options) ? + " WHERE host_status=\"UP\";" : + (options & ~HOST_PENDING == options) ? + " WHERE !(host_status=\"PENDING\");" : + (options & ~HOST_DOWN == options) ? + " WHERE !(host_status=\"DOWN\");" : + (options & ~HOST_UNREACHABLE == options) ? + " WHERE !(host_status=\"UNREACHABLE\");" : + (options & ~HOST_UP) ? + " WHERE !(host_status=\"UP\");" : "", + sizeof(sql_statement) - strlen(sql_statement) - 1); + } sql_statement[sizeof(sql_statement)-1]='\x0'; @@ -1654,7 +1682,7 @@ /* adds service status information */ -int xsddb_add_service_status(void){ +int xsddb_add_service_status(int options){ char sql_statement[XSDDB_SQL_LENGTH]; int result; int x; @@ -1703,6 +1731,51 @@ #ifdef USE_XSDPGSQL snprintf(sql_statement,sizeof(sql_statement)-1,"SELECT host_name, service_description, service_status, date_part('epoch',last_update) AS last_update, current_attempt, max_attempts, state_type, date_part('epoch',last_check) AS last_check, date_part('epoch',next_check) AS next_check, should_be_scheduled, check_type, checks_enabled, accept_passive_checks, event_handler_enabled, date_part('epoch',last_state_change) AS last_state_change, problem_acknowledged, last_hard_state, time_ok, time_warning, time_unknown, time_critical, date_part('epoch',last_notification) AS last_notification, current_notification, notifications_enabled, latency, execution_time, flap_detection_enabled, is_flapping, percent_state_change, scheduled_downtime_depth, failure_prediction_enabled, process_performance_data, obsess_over_service, plugin_output FROM %s",XSDDB_SERVICESTATUS_TABLE); #endif + /* mask extra options */ + options &= SERVICE_ALL; + /* should we use WHERE here? -gvs */ + if (options != SERVICE_ALL) { + /* + * this is quick & dirty but it should cover nearly 90% of + * status requests, so we can allow expensive queries for + * the rest 10% -gvs + */ + strncat(sql_statement, + (options & SERVICE_PENDING == options) ? + " WHERE service_status=\"PENDING\";" : + (options & SERVICE_OK == options) ? + " WHERE service_status=\"OK\";" : + (options & SERVICE_RECOVERY == options) ? + " WHERE service_status=\"RECOVERY\";" : + (options & SERVICE_WARNING == options) ? + " WHERE service_status=\"WARNING\";" : + (options & SERVICE_UNKNOWN == options) ? + " WHERE service_status=\"UNKNOWN\";" : + (options & SERVICE_CRITICAL == options) ? + " WHERE service_status=\"CRITICAL\";" : + (options & SERVICE_HOST_DOWN == options) ? + " WHERE service_status=\"HOST_DOWN\";" : + (options & SERVICE_UNREACHABLE == options) ? + " WHERE service_status=\"UNREACHABLE\";" : + (options & ~SERVICE_PENDING == options) ? + " WHERE !(service_status=\"PENDING\");" : + (options & ~SERVICE_RECOVERY == options) ? + " WHERE !(service_status=\"RECOVERY\");" : + (options & ~SERVICE_WARNING == options) ? + " WHERE !(service_status=\"WARNING\");" : + (options & ~SERVICE_UNKNOWN == options) ? + " WHERE !(service_status=\"UNKNOWN\");" : + (options & ~SERVICE_CRITICAL == options) ? + " WHERE !(service_status=\"CRITICAL\");" : + (options & ~SERVICE_HOST_DOWN == options) ? + " WHERE !(service_status=\"HOST_DOWN\");" : + (options & ~SERVICE_UNREACHABLE == options) ? + " WHERE !(service_status=\"UNREACHABLE\");" : + (options & ~SERVICE_OK) ? + " WHERE !(service_status=\"OK\");" : "", + sizeof(sql_statement) - strlen(sql_statement) - 1); + } + sql_statement[sizeof(sql_statement)-1]='\x0'; File: patch-statusdata.h --- common/statusdata.h.orig Fri Nov 19 14:20:57 2004 +++ common/statusdata.h Fri Nov 19 14:21:36 2004 @@ -119,6 +119,7 @@ #define SERVICE_CRITICAL 32 #define SERVICE_HOST_DOWN 64 #define SERVICE_UNREACHABLE 128 +#define SERVICE_ALL 255 /**************************** HOST STATES ****************************/ @@ -127,6 +128,7 @@ #define HOST_UP 2 #define HOST_DOWN 4 #define HOST_UNREACHABLE 8 +#define HOST_ALL 15 File: patch-xsddb.h --- xdata/xsddb.h.orig Tue Feb 26 07:04:11 2002 +++ xdata/xsddb.h Fri Nov 19 14:41:14 2004 @@ -51,8 +51,8 @@ #ifdef NSCGI int xsddb_read_status_data(char *,int); int xsddb_add_program_status(void); -int xsddb_add_host_status(void); -int xsddb_add_service_status(void); +int xsddb_add_host_status(int options); +int xsddb_add_service_status(int options); #endif int xsddb_grab_config_info(char *); >Release-Note: >Audit-Trail: >Unformatted: