[rrd-developers] Patch: rrdtool+libdbi-Database backend - patch for rrdtool 1.3

[Martin Sperl]

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
> rrd-developers at lists.oetiker.ch
> https://lists.oetiker.ch/cgi-bin/listinfo/rrd-developers

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: libdbi.patch
Url: http://lists.oetiker.ch/pipermail/rrd-developers/attachments/20081120/5ed0989d/attachment-0001.ksh