rrdgraph_libdbi (1) Linux Manual Page
NAME
rrdgraph_libdbi – fetching data for graphing in rrdtool graph via libdbi
SYNOPSIS
<rrdfile> = sql//<libdbi driver>/<driver-option-name>=<driver-option-value>/…[/rrdminstepsize=<stepsize>][/rrdfillmissing=<fill missing n seconds>]//<table>/<unixtimestamp column>/<data value column>[/derive]/<where clause 1>/…/<where clause n>
DESCRIPTION
This pseudo-rrd-filename defines a sql datasource:
sql//-
magic cookie-prefix for a libdbi type datasource
<libdbi driver>-
which libdbi driver to use (e.g: mysql)
<driver-option-name>=<driver-option-value>-
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 seconds>-
defines the number of seconds to fill with the last value to avoid NaN boxes due to data-insertation jitter (default: 0 seconds)
<table>-
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 %
<[*]unixtimestamp column>-
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 %
<data value column>-
defines the column of E<lt>tableE<gt> which contains the value column, which should be graphed hex-type-encoding via %xx are translated to the actual value, use %% to use %
/derive-
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> 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:
min,avg,max,countandsigma-
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
EXAMPLES
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/dbname=rrd/username=rrd/password=secret//RRDValue/UnixTimeStamp/value/RRDKeyID=1141464142203608274”
To illustrate this here a command to create a graph that contains the actual 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"
NOTES
* 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
* It should also be relatively simple to add to the database using the same data source string.
* The aggregation functions are ignored and several data columns are used instead
* for DB efficiency you should think of having 2 tables, one containing historic values and the other containing the latest data.
* To debug the SQL statements set the environment variable RRDDEBUGSQL and the actual SQL statements and the timing is printed to stderr.
Performance issues with MySQL backend
LibDBI has a big performance issue when you retrieve 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 on latest version of LibDBI (not release yet). At that time, you would need to compile libdbi and libdbi-drivers from CVS repository to fix it. You can find more informations on this libdbi-users mailing list thread : http://sourceforge.net/mailarchive/message.php?msg_id=30320894
BUGS
* at least on Linux please make sure that the libdbi driver is explicitly linked against libdbi.so.0
* at least version 0.8.1 of libdbi exhibits a bug with BINARY fields
AUTHOR
Martin Sperl <rrdtool [at] martin.sperl.org>