Hi!
Attached a patch for the rrd-tool LIBDBI integration with the following
improvements:
a) correct error handling in case of libdbi being unable to load the
driver - was producing segmentation faults.
b) better parsing of datasources
* until now timestamp fields had to be integer and had to contain a
unix timestamp - now you can now also use DateTime fields (you still
need to specify it, as the time-range needs to be defined correctly)
* data fields are now no longer limited to (var)char or DOUBLE
fields - FLOAT, INTEGER,... are now also supported.
c) there is a bug with at least LIBDBI 0.8.1 in conjunction with mysql
that can result in segmentation faults when BINARY/BLOB fields are
accessed - rrdtool will now tell you about this fact before dying ;)
d) also the value of rrdderivemaxstep only gets applied if derive has
been selected correctly.
e) "GROUP BY timestamp" has been removed from SQL statement.
f) "ORDER BY timestamp" will be added only in the case of fetching
"derived" data.
Please apply to trunk.
Ciao,
Martin
Martin Sperl wrote:
Hi!
As some of you may know that I have created a patch for rrdtool 1.2 a
few years ago, so that a database could be queried for values for
graphing.
I have created an updated patch for rrdtool version 1.3 (against SVN
version 1644).
The patch has been mostly rewritten and the following changes have
been made:
* high dependency on mysql has been reduced by avoiding the
temporary tables (which was bad for mysql replication)
* The number of executed SQL-Statements for one CDEF has been
reduced to 1 compared to 11 SQLs (including CREATE TEMPORARY
TABLE) - for patch against version 1.2
* All consolidation is done in rrdtool itself (MIN,MAX,AVERAGE)
* Additional consolidation functions are COUNT and SIGMA, which
give information on statistics on a per "time-bin" basis.
* All these consolidation values are always returned as separate
columns, that are returned by RRD and the consolidation function
given as Argument is ignored.
Main reason is that this way there is only one call to
rrd_fetcht and thus the database even if we need to fetch for
example min, avg and max. Compare this to 3 calls in case of
different consolidation functions - and if you want to get SIGMA
and COUNT as well it is still only one call to the backend and
the database.
* Some previous existing features have been taken out at the
moment to allow for this reduced set of SQL queries.
o prediction using the values from the last X days at the
same time
o the corresponding sigma calculation
* The idea is to create generic CDEF's that will do the same
thing, but that is also available when using RRD-files (similar
to TREND, but with another scope)
This will get posted as a separate patch.
* Overall performance should be much better and the patch as a
whole simpler.
* The patch also includes modifications to the configuration
infrastructure, to make libdbi support optional.
As requested by Tobi in a private email-thread, the patch should be
highly localized (and configurable via configure), to allow a possible
merge with minimal shared code touched.
Please review the code and comment...
Thanks,
Martin
P.s: I know that there is the "new" approach of rrdcached to improve
write-performance with massive updates on rrd. This patch was an
initial attempt to get a similar problem solved.
Here for comparison: our production system (running with the 1.2 patch
for graphing) is updating on average 150k different values every 300
seconds or approximately 500 values/s on a HP-DL360G3 with Dual Xenon
3.0GHz, 4GB RAM connected via ISCSI (over GBit network) to our storage
system. (data-gathering Application and the mysql DB is done on the
same host)
------------------------------------------------------------------------
_______________________________________________
rrd-developers mailing list
[email protected]
https://lists.oetiker.ch/cgi-bin/listinfo/rrd-developers
Index: src/rrd_fetch_libdbi.c
===================================================================
--- src/rrd_fetch_libdbi.c (revision 1677)
+++ src/rrd_fetch_libdbi.c (working copy)
@@ -17,8 +17,9 @@
};
/* the prototypes */
+void _sql_close(struct sql_table_helper* th);
int _sql_setparam(struct sql_table_helper* th,char* key, char* value);
-int _sql_fetchrow(struct sql_table_helper* th,time_t *timestamp, rrd_value_t
*value);
+int _sql_fetchrow(struct sql_table_helper* th,time_t *timestamp, rrd_value_t
*value,int ordered);
char* _find_next_separator(char* start,char separator);
char* _find_next_separator_twice(char*start,char separator);
char _hexcharhelper(char c);
@@ -31,42 +32,135 @@
/* helpers to get correctly converted values from DB*/
long rrd_fetch_dbi_long(dbi_result *result,int idx) {
char *ptmp="";
- switch (dbi_result_get_field_type_idx(result,idx)) {
- case DBI_TYPE_STRING:
- ptmp=(char*)dbi_result_get_string_idx(result,idx);
- return atoi(ptmp);
- default:
- return dbi_result_get_longlong_idx(result,idx);
+ long value=DNAN;
+ /* get the attributes for this filed */
+ unsigned int attr=dbi_result_get_field_attribs_idx(result,idx);
+ unsigned int type=dbi_result_get_field_type_idx(result,idx);
+ /* return NAN if NULL */
+ if(dbi_result_field_is_null_idx(result,idx)) { return DNAN; }
+ /* do some conversions */
+ switch (type) {
+ case DBI_TYPE_STRING:
+ ptmp=(char*)dbi_result_get_string_idx(result,idx);
+ value=atoi(ptmp);
+ break;
+ case DBI_TYPE_INTEGER:
+ if (attr & DBI_INTEGER_SIZE1) {
value=dbi_result_get_char_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE2) {
value=dbi_result_get_short_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE3) {
value=dbi_result_get_int_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE4) {
value=dbi_result_get_int_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE8) {
value=dbi_result_get_longlong_idx(result,idx);
+ } else { value=DNAN;
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column
%i unsupported attribute flags %i for type INTEGER\n",time(NULL),idx,attr ); }
+ }
+ break;
+ case DBI_TYPE_DECIMAL:
+ if (attr & DBI_DECIMAL_SIZE4) {
value=floor(dbi_result_get_float_idx(result,idx));
+ } else if (attr & DBI_DECIMAL_SIZE8) {
value=floor(dbi_result_get_double_idx(result,idx));
+ } else { value=DNAN;
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column
%i unsupported attribute flags %i for type DECIMAL\n",time(NULL),idx,attr ); }
+ }
+ break;
+ case DBI_TYPE_BINARY:
+ attr=dbi_result_get_field_length_idx(result,idx);
+ ptmp=(char*)dbi_result_get_binary_copy_idx(result,idx);
+ ptmp[attr-1]=0;
+ /* check for "known" libdbi error */
+ if (strncmp("ERROR",ptmp,5)==0) {
+ if (!getenv("RRD_NO_LIBDBI_BUG_WARNING")) {
+ fprintf(stderr,"rrdtool_fetch_libDBI: you have possibly triggered a
bug in libDBI by using a (TINY,MEDIUM,LONG) TEXT field with mysql\n this may
trigger a core dump in at least one version of libdbi\n if you are not touched
by this bug and you find this message annoying\n please set the
environment-variable RRD_NO_LIBDBI_BUG_WARNING to ignore this message\n");
+ }
+ }
+ /* convert to number */
+ value=atoi(ptmp);
+ /* free pointer */
+ free(ptmp);
+ break;
+ case DBI_TYPE_DATETIME:
+ value=dbi_result_get_datetime_idx(result,idx);
+ break;
+ default:
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column %i
unsupported type: %i with attribute %i\n",time(NULL),idx,type,attr ); }
+ value=DNAN;
+ break;
}
+ return value;
}
double rrd_fetch_dbi_double(dbi_result *result,int idx) {
char *ptmp="";
+ double value=DNAN;
+ /* get the attributes for this filed */
+ unsigned int attr=dbi_result_get_field_attribs_idx(result,idx);
+ unsigned int type=dbi_result_get_field_type_idx(result,idx);
/* return NAN if NULL */
if(dbi_result_field_is_null_idx(result,idx)) { return DNAN; }
/* do some conversions */
- switch (dbi_result_get_field_type_idx(result,idx)) {
- case DBI_TYPE_STRING:
- ptmp=(char*)dbi_result_get_string_idx(result,idx);
- return strtod(ptmp,NULL);
- default:
- return dbi_result_get_double_idx(result,idx);
+ switch (type) {
+ case DBI_TYPE_STRING:
+ ptmp=(char*)dbi_result_get_string_idx(result,idx);
+ value=strtod(ptmp,NULL);
+ break;
+ case DBI_TYPE_INTEGER:
+ if (attr & DBI_INTEGER_SIZE1) {
value=dbi_result_get_char_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE2) {
value=dbi_result_get_short_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE3) {
value=dbi_result_get_int_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE4) {
value=dbi_result_get_int_idx(result,idx);
+ } else if (attr & DBI_INTEGER_SIZE8) {
value=dbi_result_get_longlong_idx(result,idx);
+ } else { value=DNAN;
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column
%i unsupported attribute flags %i for type INTEGER\n",time(NULL),idx,attr ); }
+ }
+ break;
+ case DBI_TYPE_DECIMAL:
+ if (attr & DBI_DECIMAL_SIZE4) {
value=dbi_result_get_float_idx(result,idx);
+ } else if (attr & DBI_DECIMAL_SIZE8) {
value=dbi_result_get_double_idx(result,idx);
+ } else { value=DNAN;
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column
%i unsupported attribute flags %i for type DECIMAL\n",time(NULL),idx,attr ); }
+ }
+ break;
+ case DBI_TYPE_BINARY:
+ attr=dbi_result_get_field_length_idx(result,idx);
+ ptmp=(char*)dbi_result_get_binary_copy_idx(result,idx);
+ ptmp[attr-1]=0;
+ /* check for "known" libdbi error */
+ if (strncmp("ERROR",ptmp,5)==0) {
+ if (!getenv("RRD_NO_LIBDBI_BUG_WARNING")) {
+ fprintf(stderr,"rrdtool_fetch_libDBI: you have possibly triggered a
bug in libDBI by using a (TINY,MEDIUM,LONG) TEXT field with mysql\n this may
trigger a core dump in at least one version of libdbi\n if you are not touched
by this bug and you find this message annoying\n please set the
environment-variable RRD_NO_LIBDBI_BUG_WARNING to ignore this message\n");
+ }
+ }
+ /* convert to number */
+ value=strtod(ptmp,NULL);
+ /* free pointer */
+ free(ptmp);
+ break;
+ case DBI_TYPE_DATETIME:
+ value=dbi_result_get_datetime_idx(result,idx);
+ break;
+ default:
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: column %i
unsupported type: %i with attribute %i\n",time(NULL),idx,type,attr ); }
+ value=DNAN;
+ break;
}
+ return value;
}
-int _sql_close(struct sql_table_helper* th) {
+void _sql_close(struct sql_table_helper* th) {
/* close only if connected */
if (th->conn) {
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: close
connection\n",time(NULL) ); }
/* shutdown dbi */
dbi_conn_close(th->conn);
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: shutting
down libdbi\n",time(NULL) ); }
dbi_shutdown();
/* and assign empty */
th->conn=NULL;
+ th->connected=0;
}
}
int _sql_setparam(struct sql_table_helper* th,char* key, char* value) {
char* dbi_errstr=NULL;
+ dbi_driver driver;
/* if not connected */
if (! th->conn) {
/* initialize some stuff */
@@ -74,14 +168,21 @@
th->result=NULL;
th->connected=0;
/* initialize db */
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: initialize
libDBI\n",time(NULL) ); }
dbi_initialize(NULL);
- th->conn=dbi_conn_new(th->dbdriver);
+ /* load the driver */
+ driver=dbi_driver_open(th->dbdriver);
+ if (! driver) {
+ rrd_set_error( "libdbi - no such driver: %s (possibly a dynamic link
problem of the driver being linked without -ldbi)",th->dbdriver);
+ return -1;
+ }
+ /* and connect to driver */
+ th->conn=dbi_conn_open(driver);
/* and handle errors */
if (! th->conn) {
- dbi_conn_error(th->conn,(const char**)&dbi_errstr);
- rrd_set_error( "libdbi - no such driver: %s (possibly a dynamic link
problem of the driver being linked without -ldbi)",dbi_errstr);
+ rrd_set_error( "libdbi - could not open connection to driver
%s",th->dbdriver);
dbi_shutdown();
- return -1;
+ return -1;
}
}
if (th->connected) {
@@ -89,6 +190,7 @@
_sql_close(th);
return -1;
}
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: setting
option %s to %s\n",time(NULL),key,value ); }
if (dbi_conn_set_option(th->conn,key,value)) {
dbi_conn_error(th->conn,(const char**)&dbi_errstr);
rrd_set_error( "libdbi: problems setting %s to %s -
%s",key,value,dbi_errstr);
@@ -98,7 +200,7 @@
return 0;
}
-int _sql_fetchrow(struct sql_table_helper* th,time_t *timestamp, rrd_value_t
*value) {
+int _sql_fetchrow(struct sql_table_helper* th,time_t *timestamp, rrd_value_t
*value,int ordered) {
char* dbi_errstr=NULL;
char sql[10240];
time_t startt=0,endt=0;
@@ -109,6 +211,7 @@
}
if (! th->connected) {
/* and now connect */
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: connect to
DB\n",time(NULL) ); }
if (dbi_conn_connect(th->conn) <0) {
dbi_conn_error(th->conn,(const char**)&dbi_errstr);
rrd_set_error( "libdbi: problems connecting to db with connect string %s
- error: %s",th->filename,dbi_errstr);
@@ -121,6 +224,7 @@
if (! th->result) {
/* return if table_next is NULL */
if (th->table_next==NULL) {
+ if (getenv("RRDDEBUGSQL")) { fprintf(stderr,"RRDDEBUGSQL: %li: reached
last table to connect to\n",time(NULL) ); }
/* but first close connection */
_sql_close(th);
/* and return with end of data */
@@ -131,8 +235,13 @@
th->table_next=_find_next_separator(th->table_start,'+');
_inline_unescape(th->table_start);
/* and prepare FULL SQL Statement */
- snprintf(sql,sizeof(sql)-1,"SELECT %s as rrd_time, %s as rrd_value FROM %s
WHERE %s GROUP BY rrd_time",
- th->timestamp,th->value,th->table_start,th->where);
+ if (ordered) {
+ snprintf(sql,sizeof(sql)-1,"SELECT %s as rrd_time, %s as rrd_value FROM
%s WHERE %s ORDER BY %s",
+ th->timestamp,th->value,th->table_start,th->where,th->timestamp);
+ } else {
+ snprintf(sql,sizeof(sql)-1,"SELECT %s as rrd_time, %s as rrd_value FROM
%s WHERE %s",
+ th->timestamp,th->value,th->table_start,th->where);
+ }
/* and execute sql */
if (getenv("RRDDEBUGSQL")) { startt=time(NULL);
fprintf(stderr,"RRDDEBUGSQL: %li: executing %s\n",startt,sql); }
th->result=dbi_conn_query(th->conn,sql);
@@ -152,7 +261,7 @@
dbi_result_free(th->result);
th->result=NULL;
/* and call recursively - this will open the next table or close
connection as a whole*/
- return _sql_fetchrow(th,timestamp,value);
+ return _sql_fetchrow(th,timestamp,value,ordered);
}
/* and return with flag for one value */
*timestamp=rrd_fetch_dbi_long(th->result,1);
@@ -271,6 +380,8 @@
/* the settings for the "works" of rrd */
int fillmissing=0;
unsigned long minstepsize=300;
+ /* by default assume unixtimestamp */
+ int isunixtime=1;
/* the result-set */
long r_timestamp,l_timestamp,d_timestamp;
double r_value,l_value,d_value;
@@ -339,6 +450,8 @@
rrd_set_error( "formatstring wrong - %s",tmpptr);
return -1;
}
+ /* if we have leading '*', then we have a TIMEDATE Field*/
+ if (table_help.timestamp[0]=='*') { isunixtime=0; table_help.timestamp++; }
/* hex-unescape the value */
if(_inline_unescape(table_help.timestamp)) { return -1; }
@@ -365,10 +478,10 @@
if (strcmp(sqlargs,"derive")==0) { /* the derive option with the default
allowed max delta */
derive=600;
} else if (strcmp(sqlargs,"prediction")==0) {
- rrd_set_error("argument prediction is no longer supported in a VDEF -
use new generic CDEF-functions instead");
+ rrd_set_error("argument prediction is no longer supported in a DEF - use
new generic CDEF-functions instead");
return -1;
} else if (strcmp(sqlargs,"sigma")==0) {
- rrd_set_error("argument sigma is no longer supported in a VDEF - use new
generic CDEF-functions instead");
+ rrd_set_error("argument sigma is no longer supported in a DEF - use new
generic CDEF-functions instead");
return -1;
} else if (*sqlargs==0) { /* ignore empty */
} else { /* else add to where string */
@@ -399,13 +512,10 @@
i=atoi(tmpptr);if (i>0) { minstepsize=i; }
} else if (strcmp(libdbiargs,"rrdfillmissing")==0) { /* allow override for
minstepsize */
i=atoi(tmpptr);if (i>0) { fillmissing=i; }
- } else if (strcmp(libdbiargs,"rrdderivemaxstep")==0) { /* allow override
for minstepsize */
- i=atoi(tmpptr);if (i>0) { derive=i; }
+ } else if (strcmp(libdbiargs,"rrdderivemaxstep")==0) { /* allow override
for derived max delta */
+ i=atoi(tmpptr);if (i>0) { if (derive) { derive=i; }}
} else { /* store in libdbi, as these are parameters */
- if (_sql_setparam(&table_help,libdbiargs,tmpptr)) {
- _sql_close(&table_help);
- return -1;
- }
+ if (_sql_setparam(&table_help,libdbiargs,tmpptr)) { return -1; }
}
/* and continue loop with next pointer */
libdbiargs=nextptr;
@@ -419,7 +529,13 @@
/* and append the SQL WHERE Clause for the timeframe calculated above
(adding AND if required) */
if (where[0]) {strcat(where," AND ");}
i=strlen(where);
- snprintf(where+i,sizeof(where)-1-i,"%li < %s AND %s <
%li",*start,table_help.timestamp,table_help.timestamp,*end);
+ if (isunixtime) {
+ snprintf(where+i,sizeof(where)-1-i,"%li < %s AND %s <
%li",*start,table_help.timestamp,table_help.timestamp,*end);
+ } else {
+ char tsstart[64];strftime(tsstart,sizeof(tsstart),"%Y-%m-%d
%H:%M:%S",localtime(start));
+ char tsend[64];strftime(tsend,sizeof(tsend),"%Y-%m-%d
%H:%M:%S",localtime(end));
+ snprintf(where+i,sizeof(where)-1-i,"'%s' < %s AND %s <
'%s'",tsstart,table_help.timestamp,table_help.timestamp,tsend);
+ }
/* and now calculate the number of rows in the resultset... */
rows=((*end)-(*start))/(*step)+2;
@@ -458,7 +574,7 @@
/* and assign undefined values for last - in case of derived calculation */
l_value=DNAN;l_timestamp=0;
/* here goes the real work processing all data */
- while((r_status=_sql_fetchrow(&table_help,&r_timestamp,&r_value))>0) {
+ while((r_status=_sql_fetchrow(&table_help,&r_timestamp,&r_value,derive))>0) {
/* processing of value */
/* calculate index for the timestamp */
idx=(r_timestamp-(*start))/(*step);
Index: doc/rrdgraph_libdbi.pod
===================================================================
--- doc/rrdgraph_libdbi.pod (revision 1677)
+++ doc/rrdgraph_libdbi.pod (working copy)
@@ -41,9 +41,10 @@
hex-type-encoding via %xx are translated to the actual value, use %% to use %
-=item B<E<lt>unixtimestamp columnE<gt>>
+=item B<E<lt>[*]unixtimestamp columnE<gt>>
- defines the column of E<lt>tableE<gt> which contains the unix timestamp
+ defines the column of E<lt>tableE<gt> which contains the unix-timestamp
+ - if this is a DATETIME field in the database, then prefix with leading '*'
hex-type-encoding via %xx are translated to the actual value, use %% to use %
@@ -145,6 +146,13 @@
(this only happens when the libdbi driver is actually used the first time!)
This is KNOWN to be the case with RHEL4 and FC4 and FC5! (But actually this
is a bug with libdbi make files!)
+* at least version 0.8.1 of libdbiexhibits a bug with BINARY fields
+ (shorttext,text,mediumtext,longtext and possibly also BINARY and BLOB
fields),
+ that can result in coredumps of rrdtool.
+ The tool will tell you on stderr if this occures, so that you know what may
be the reason.
+ If you are not experiencing these coredumps, then set the environment
variable RRD_NO_LIBDBI_BUG_WARNING,
+ and then the message will not get shown.
+
=head1 AUTHOR
Martin Sperl <[EMAIL PROTECTED]>
_______________________________________________
rrd-developers mailing list
[email protected]
https://lists.oetiker.ch/cgi-bin/listinfo/rrd-developers
