_R_R_D_G_R_A_P_H___L_I_B_D_B_I(1)                   rrdtool                  _R_R_D_G_R_A_P_H___L_I_B_D_B_I(1)

NNAAMMEE
     rrdgraph_libdbi - fetching data for graphing in rrdtool graph via libdbi

SSYYNNOOPPSSIISS
     <rrdfile>     =    ssqqll////<<lliibbddbbii    ddrriivveerr>>//<<ddrriivveerr--ooppttiioonn--nnaammee>>==<<ddrriivveerr--oopp‐‐
     ttiioonn--vvaalluuee>>//......[[//rrrrddmmiinnsstteeppssiizzee==<<sstteeppssiizzee>>]][[//rrrrddffiillllmmiissssiinngg==<<ffiillll mmiissssiinngg nn
     sseeccoonnddss>>]]////<<ttaabbllee>>//<<uunniixxttiimmeessttaammpp    ccoolluummnn>>//<<ddaattaa    vvaalluuee    ccoolluummnn>>[[//ddee‐‐
     rriivvee]]//<<wwhheerree ccllaauussee 11>>//......//<<wwhheerree ccllaauussee nn>>

DDEESSCCRRIIPPTTIIOONN
     This pseudo-rrd-filename defines a sql datasource:

     ssqqll////
               magic cookie-prefix for a libdbi type datasource

     <<lliibbddbbii ddrriivveerr>>
               which libdbi driver to use (e.g.: mysql)

     <<ddrriivveerr--ooppttiioonn--nnaammee>>=<<ddrriivveerr--ooppttiioonn--vvaalluuee>>
               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!)

     //rrrrddmmiinnsstteeppssiizzee=<<mmiinniimmuumm sstteepp ssiizzee>>
               defines the minimum number of the step-length used for graphing (default: 300 seconds)

     //rrrrddffiillllmmiissssiinngg=<<ffiillll mmiissssiinngg sseeccoonnddss>>
               defines the number of seconds to fill with the last value to avoid NaN boxes due to data-insertation jitter (default: 0 seconds)

     <<ttaabbllee>>
               defines the table from which to fetch the resultset.

               If there is a need to fetch data from several tables, these tables can be defined by separating the tablenames with a "+"

               hex-type-encoding via %xx are translated to the actual value, use %% to use %

     <<[[**]]uunniixxttiimmeessttaammpp ccoolluummnn>>
               defines the column of <table> 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 %

     <<ddaattaa vvaalluuee ccoolluummnn>>
               defines the column of <table> which contains the value column, which should be graphed

               hex-type-encoding via %xx are translated to the actual value, use %% to use %

     //ddeerriivvee
               defines that the data value used should be the delta of the 2 consecutive values (to simulate COUNTER or DERIVE type datasources)

     //<<wwhheerree ccllaauussee((ss))>>
               defines one (ore more) where clauses that are joined with AND to filter the entries in the <table>

               hex-type-encoding via %xx are translated to the actual value, use %% to use %

     the returned value column-names, which can be used as ds-names, are:

     mmiinn, aavvgg, mmaaxx, ccoouunntt and ssiiggmmaa
               are returned to be used as ds-names in your DS definition.
               The reason for using this is that if the consolidation function is used for min/avg and max, then the engine is used several times.
               And this results in the same SQL Statements used several times

EEXXAAMMPPLLEESS
     Here an example of a table in a MySQL database:

       DB connect information
         dbhost=127.0.0.1
         user=rrd
         password=secret
         dbname=rrd

       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: "sql//mysql/host=127.0.0.1/db‐
     name=rrd/username=rrd/password=secret//RRDValue/UnixTimeStamp/value/RRD‐
     KeyID=1141464142203608274"

     To illustrate this here a command to create a graph that contains  the  ac‐
     tual values.

       DS_BASE="sql//mysql/host=127.0.0.1/dbname=rrd/username=rrd/password=passwd//RRDValue/UnixTimeStamp/value/RRDKeyID=1141942900757789274"
       rrdtool graph test.png --imgformat=PNG --start=-1day --end=+3hours --width=1000 --height=600 \
         "DEF:min=$DS_BASE:min:AVERAGE" \
         "LINE1:min#FF0000:value" \
         "DEF:avg=$DS_BASE:avg:AVERAGE" \
         "LINE1:avg#00FF00:average" \
         "DEF:max=$DS_BASE:max:AVERAGE" \
         "LINE1:max#FF0000:max" \
         "DEF:sigma=$DS_BASE:sigma:AVERAGE" \
         "CDEF:upper=avg,4,sigma,*,+" \
         "LINE1:upper#0000FF:+4 sigma" \
         "CDEF:lower=avg,4,sigma,*,-" \
         "LINE1:lower#0000FF:-4 sigma"

NNOOTTEESS
     *  Naturally you can also use any other kind of driver that libdbi supports
     - e.g. postgres, ...

     * From the way the data source 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
     data source string.
       This has not been implemented...

     *  The  aggregation functions are ignored and several data columns are used
     instead
       to avoid querying the same SQL several times when  minimum,  average  and
     maximum are needed for graphing...

     *  for  DB  efficiency  you should think of having 2 tables, one containing
     historic values and the other containing the latest data.
       This second table should be kept small to allow for the least  amount  of
     blocking SQL statements.
       With  mysql  you  can even use myisam table-type for the first and InnoDB
     for the second.
       This is especially interesting as with tables with +100M rows  myisam  is
     much smaller then InnoDB.

     *  To debug the SQL statements set the environment variable RRDDEBUGSQL and
     the actual SQL statements and the timing is printed to stderr.

PPeerrffoorrmmaannccee iissssuueess wwiitthh MMyySSQQLL bbaacckkeenndd
     Previous versions of LibDBI have a big performance  issue  when  retrieving
     data  from a MySQL server. Performance impact is exponentially based on the
     number of values you retrieve from the database.   For  example,  it  would
     take more than 2 seconds to graph 5DS on 150 hours of data with a precision
     of  5 minutes (against 100ms when data comes from a RRD file). This bug has
     been fixed in version 0.9.0 of LibDBI.  You can find  more  information  on
     this      libdbi-users      mailing     list     thread:     http://source‐
     forge.net/mailarchive/message.php?msg_id=30320894

BBUUGGSS
     * at least on Linux please make sure that the libdbi driver  is  explicitly
     linked against libdbi.so.0
       check  via  ldd  /usr/lib/dbd/libmysql.so, that there is a line with lib‐
     dbi.so.0.
       otherwise at least the perl module RRDs will  fail  because  the  dynamic
     linker cannot find some symbols from libdbi.so.
       (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 libdbi exhibits 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 occurs, 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.

AAUUTTHHOORR
     Martin Sperl <rrdtool@martin.sperl.org>

1.10.3                             2026-05-25                 _R_R_D_G_R_A_P_H___L_I_B_D_B_I(1)
