rrdgraph_libdbi (1) - Linux Manuals
rrdgraph_libdbi: fetching data for graphing in rrdtool graph via libdbi
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>
DESCRIPTIONThis pseudo-rrd-filename defines a sql datasource:
magic cookie-prefix for a libdbi type datasource
- <libdbi driver>
which libdbi driver to use (e.g: mysql)
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)
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 %
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, count and sigma
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
EXAMPLESHere 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 backendLibDBI 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
AUTHORMartin Sperl <rrdtool [at] martin.sperl.org>