Date: Fri, 19 Nov 2004 17:42:21 +0300 (MSK) From: Seva Gluschenko <gvs@rinet.ru> To: FreeBSD-gnats-submit@FreeBSD.org Cc: blaz@si.FreeBSD.org Subject: ports/74117: net-mgmt/nagios performance patches Message-ID: <200411191442.iAJEgL7V002238@y1.yandex.ru> Resent-Message-ID: <200411191450.iAJEoTfl086217@freefall.freebsd.org>
next in thread | raw e-mail | index | archive | help
>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:
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?200411191442.iAJEgL7V002238>