[rrd-developers] PATCH: rrdtool with SQL DS using libdbi

Martin Sperl rrdtool at martin.sperl.org
Sun Mar 26 15:10:33 MEST 2006

Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

I have created a patch to rrdtool 1.2 (SVN) which allows to define a DS 
from a SQL database for graphing using the libdbi library for DB 
Tobi told me to post the patch to this list, as he thinks that several 
people might be interrested in it immediately.
He is also thinking about adding it to rrdtool 1.3 when this patch has 
been given some more testing...

List of features:
* db-abstration via libdbi with DB support for the following Databases 
out of the box:
  * mysql
  * postgress
* calculation of the "derivative" of a database values (so that we get 
behaviour simmilar to COUNTER/DERIVE type datasources)
* Prediction logic, which gives from DB data values for:
  * prediction
  * sigma
* SQL-DS can also be used for further transformations with CDEFs

This patch is only tested on mysql but should (hopefully) also support 
postgress out of the box.
I also believe that there is an oracle driver for libdbi in the pipeline.

Here the some sample graphs from a database source generated from a 
mysql database including prediction+sigma calculation (average 
ping-time) using the command from the documentation:

And the same as a derived datasource (adding "/derive"):

Here the manual page of the rrdgraph_libdbi:
RRDGRAPH_LIBDBI(1)                  rrdtool                 

       rrdgraph_libdbi - fetching data for graphing in rrdtool graph via 

       <rrdfile> = sql//<libdbi driver>/<driver-option-name>=<driver-option-
missing n
       value column>[/derive]/<where clause 1>/.../<where clause n>[/predic-

       This pseudo-rrd-filename defines a sql datasource:

                 magic cookie-prefix for a libdbi type datasource

       <libdbi driver>
                 which libdbi driver to use

                 defines the parameters that are required to connect to 
the database with the given libdbi driver
                 (These drivers are libdbi dependent - for details 
please look at the driver documentation of libdbi!)

       /rrdminstepsize=<minimum step size>
                 defines the minimum number of the step-length used for 
graphing (default: 300 seconds)

       /rrdfillmissing=<fill missing steps>
                 defines the number of steps to fill with the last value 
to avoid NaN boxes due to data-insertation jitter (default: 0 steps)

       /rrdperiodlength=<length of one prediction period>
                 defines the length of one period used for prediction 
(default: 1 day or 86400 seconds)

       /rrdperiods=<number of periods used for predictions>
                 defines the number of periods to talke into account for 
calculations of the prediction (default: 7)

       /rrdsmoothingE=<smoothing of prediction>
                 defines the smoothing factor for the prediction. The 
number of steps that are used for calculation is 1+2*B<E<lt>smoothing of 
predictionE<gt>> (default: 2)

                 defines the table from which to fetch the resultset

       <unixtimestamp column>
                 defines the column of E<lt>tableE<gt> which contains 
the unix timestamp

       <data value column>
                 defines the column of E<lt>tableE<gt> which contains 
the value column, which should be graphed

                 defines that the data value used should be the delta of 
the 2 consecutive values (to simulate COUNTER or DERIVE type datasources)

       /<where clause(s)>
                 defines one (ore more) where clauses that are joined 
with AND to filter the entries in the <lt>table<gt>

                 calculate the prediction and sigma instead of just 
returning the value

       the returned value column-names are:

                 is returned if "/prediction" is given, use "prediction" 
as ds-name in your DS definition.

                 is returned if "/sigma" is given, use "sigma" as 
ds-name in your DS definition.

                 is returned otherwise, use "value" as "ds-name" as 
ds-name in your DS definition.

         using the prediction requires more work on the database side 
and uses temporary tables heavily!
         The highest value and lowest value for each predicion values 
are removed,
         so that one time events are not included in calculations!

       Here an example of a table in a mysql database:

         DB connect information

         here the table:
           CREATE TABLE RRDValue (
             RRDKeyID      bigint(20) NOT NULL,
             UnixTimeStamp int(11) NOT NULL,
             value         double default NOT NULL,
             PRIMARY KEY  (RRDKeyID,UnixTimeStamp)

       and the RRDKeyID we want to graph for is: 1141942900757789274

       The pseudo rrd-filename to access this is:

       To illustrate this here a command to create a graph that contains the
       actual values plus the prediction and some margins.

         rrdtool graph test.png --imgformat=PNG --start=-1day 
--end=+3hours --width=1000 --height=600 \
           "DEF:value=$DS_BASE:value:AVERAGE" \
           "LINE1:value#FF0000:value" \
           "DEF:predict=$DS_BASE/predict:prediction:AVERAGE" \
           "LINE1:predict#00FF00:prediction" \
           "DEF:sigma=$DS_BASE/predict:sigma:AVERAGE" \
           "CDEF:upper=predict,4,sigma,*,+" \
           "LINE1:upper#0000FF:+4 sigma" \
           "CDEF:lower=predict,4,sigma,*,-" \
           "LINE1:lower#0000FF:-4 sigma"

       * Naturally you can also use any other kind of driver that libdbi 
       ports - e.g postgress,...

       * From the way the datasource is joined, it should also be 
possible to
       do joins over different tables
         (separate tables with "," in table and add in the WHERE Clauses the
       table equal joins.
         This has not been tested!!!)

       * It should also be relatively simple to add to the database 
using the
       same datasource string, but this has not been implemented...

       * The only supported aggregation functions are: MIN, AVERAGE and MAX

       * at least on Linux please make sure that the libdbi driver is 
       itly linked against libdbi.so.0
         check via ldd /usr/lib/dbd/libmysql.so, that there is a line with
         otherwise at least the perl module RRDs will fail because the 
       linker can not find some symbols from libdbi.so.
         (this only happens when the libdbi driver is actually used the 
         This is KNOWN to be the case with RHEL4 and FC4 and FC5! (But actu-
       ally this is a bug with libdbi make files!)

       Martin Sperl <rrdtool at martin.sperl.org>

1.2.12                            2006-03-26                

And attached the patch to svn - I  believe it should also apply to 
1.2.12, but you migth have to generate some files (configure,...)!

Please tell me your comments, ideas, or if you find bugs!
In the hope that it gets included quickly into rrdtool itself...


P.s: It might also be possible to add functionality, so that we could 
add data to the database via "rrdtool update". The question is if this 
is really a needed feature? Might be nice for mrtg!

-- Attached file removed by Ecartis and put at URL below --
-- Type: text/plain
-- Size: 28k (28743 bytes)
-- URL : http://lists.ee.ethz.ch/p/rrdtool+libdbi.patch

Unsubscribe mailto:rrd-developers-request at list.ee.ethz.ch?subject=unsubscribe
Help        mailto:rrd-developers-request at list.ee.ethz.ch?subject=help
Archive     http://lists.ee.ethz.ch/rrd-developers
WebAdmin    http://lists.ee.ethz.ch/lsg2.cgi

More information about the rrd-developers mailing list