1RRDGRAPH_LIBDBI(1)                  rrdtool                 RRDGRAPH_LIBDBI(1)
2
3
4

NAME

6       rrdgraph_libdbi - fetching data for graphing in rrdtool graph via
7       libdbi
8

SYNOPSIS

10       <rrdfile> = sql//<libdbi
11       driver>/<driver-option-name>=<driver-option-value>/...[/rrdminstepsize=<stepsize>][/rrdfillmissing=<fill
12       missing n seconds>]//<table>/<unixtimestamp column>/<data value
13       column>[/derive]/<where clause 1>/.../<where clause n>
14

DESCRIPTION

16       This pseudo-rrd-filename defines a sql datasource:
17
18       sql//
19                 magic cookie-prefix for a libdbi type datasource
20
21       <libdbi driver>
22                 which libdbi driver to use (e.g.: mysql)
23
24       <driver-option-name>=<driver-option-value>
25                 defines the parameters that are required to connect to the database with the given libdbi driver
26                 (These drivers are libdbi dependent - for details please look at the driver documentation of libdbi!)
27
28       /rrdminstepsize=<minimum step size>
29                 defines the minimum number of the step-length used for graphing (default: 300 seconds)
30
31       /rrdfillmissing=<fill missing seconds>
32                 defines the number of seconds to fill with the last value to avoid NaN boxes due to data-insertation jitter (default: 0 seconds)
33
34       <table>
35                 defines the table from which to fetch the resultset.
36
37                 If there is a need to fetch data from several tables, these tables can be defined by separating the tablenames with a "+"
38
39                 hex-type-encoding via %xx are translated to the actual value, use %% to use %
40
41       <[*]unixtimestamp column>
42                 defines the column of <table> which contains the unix-timestamp
43                 - if this is a DATETIME field in the database, then prefix with leading '*'
44
45                 hex-type-encoding via %xx are translated to the actual value, use %% to use %
46
47       <data value column>
48                 defines the column of <table> which contains the value column, which should be graphed
49
50                 hex-type-encoding via %xx are translated to the actual value, use %% to use %
51
52       /derive
53                 defines that the data value used should be the delta of the 2 consecutive values (to simulate COUNTER or DERIVE type datasources)
54
55       /<where clause(s)>
56                 defines one (ore more) where clauses that are joined with AND to filter the entries in the <table>
57
58                 hex-type-encoding via %xx are translated to the actual value, use %% to use %
59
60       the returned value column-names, which can be used as ds-names, are:
61
62       min, avg, max, count and sigma
63                 are returned to be used as ds-names in your DS definition.
64                 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.
65                 And this results in the same SQL Statements used several times
66

EXAMPLES

68       Here an example of a table in a MySQL database:
69
70         DB connect information
71           dbhost=127.0.0.1
72           user=rrd
73           password=secret
74           dbname=rrd
75
76         here the table:
77           CREATE TABLE RRDValue (
78             RRDKeyID      bigint(20) NOT NULL,
79             UnixTimeStamp int(11) NOT NULL,
80             value         double default NOT NULL,
81             PRIMARY KEY  (RRDKeyID,UnixTimeStamp)
82           );
83
84       and the RRDKeyID we want to graph for is: 1141942900757789274
85
86       The pseudo rrd-filename to access this is:
87       "sql//mysql/host=127.0.0.1/dbname=rrd/username=rrd/password=secret//RRDValue/UnixTimeStamp/value/RRDKeyID=1141464142203608274"
88
89       To illustrate this here a command to create a graph that contains the
90       actual values.
91
92         DS_BASE="sql//mysql/host=127.0.0.1/dbname=rrd/username=rrd/password=passwd//RRDValue/UnixTimeStamp/value/RRDKeyID=1141942900757789274"
93         rrdtool graph test.png --imgformat=PNG --start=-1day --end=+3hours --width=1000 --height=600 \
94           "DEF:min=$DS_BASE:min:AVERAGE" \
95           "LINE1:min#FF0000:value" \
96           "DEF:avg=$DS_BASE:avg:AVERAGE" \
97           "LINE1:avg#00FF00:average" \
98           "DEF:max=$DS_BASE:max:AVERAGE" \
99           "LINE1:max#FF0000:max" \
100           "DEF:sigma=$DS_BASE:sigma:AVERAGE" \
101           "CDEF:upper=avg,4,sigma,*,+" \
102           "LINE1:upper#0000FF:+4 sigma" \
103           "CDEF:lower=avg,4,sigma,*,-" \
104           "LINE1:lower#0000FF:-4 sigma"
105

NOTES

107       * Naturally you can also use any other kind of driver that libdbi
108       supports - e.g. postgres, ...
109
110       * From the way the data source is joined, it should also be possible to
111       do joins over different tables
112         (separate tables with "," in table and add in the WHERE Clauses the
113       table equal joins.
114         This has not been tested!!!)
115
116       * It should also be relatively simple to add to the database using the
117       same data source string.
118         This has not been implemented...
119
120       * The aggregation functions are ignored and several data columns are
121       used instead
122         to avoid querying the same SQL several times when minimum, average
123       and maximum are needed for graphing...
124
125       * for DB efficiency you should think of having 2 tables, one containing
126       historic values and the other containing the latest data.
127         This second table should be kept small to allow for the least amount
128       of blocking SQL statements.
129         With mysql you can even use myisam table-type for the first and
130       InnoDB for the second.
131         This is especially interesting as with tables with +100M rows myisam
132       is much smaller then InnoDB.
133
134       * To debug the SQL statements set the environment variable RRDDEBUGSQL
135       and the actual SQL statements and the timing is printed to stderr.
136

Performance issues with MySQL backend

138       Previous versions of LibDBI have a big performance issue when
139       retrieving data from a MySQL server. Performance impact is
140       exponentially based on the number of values you retrieve from the
141       database.  For example, it would take more than 2 seconds to graph 5DS
142       on 150 hours of data with a precision of 5 minutes (against 100ms when
143       data comes from a RRD file). This bug has been fixed in version 0.9.0
144       of LibDBI.  You can find more information on this libdbi-users mailing
145       list thread:
146       http://sourceforge.net/mailarchive/message.php?msg_id=30320894
147

BUGS

149       * at least on Linux please make sure that the libdbi driver is
150       explicitly linked against libdbi.so.0
151         check via ldd /usr/lib/dbd/libmysql.so, that there is a line with
152       libdbi.so.0.
153         otherwise at least the perl module RRDs will fail because the dynamic
154       linker cannot find some symbols from libdbi.so.
155         (this only happens when the libdbi driver is actually used the first
156       time!)
157         This is KNOWN to be the case with RHEL4 and FC4 and FC5! (But
158       actually this is a bug with libdbi make files!)
159
160       * at least version 0.8.1 of libdbi exhibits a bug with BINARY fields
161         (shorttext,text,mediumtext,longtext and possibly also BINARY and BLOB
162       fields),
163         that can result in coredumps of rrdtool.
164         The tool will tell you on stderr if this occurs, so that you know
165       what may be the reason.
166         If you are not experiencing these coredumps, then set the environment
167       variable RRD_NO_LIBDBI_BUG_WARNING,
168         and then the message will not get shown.
169

AUTHOR

171       Martin Sperl <rrdtool@martin.sperl.org>
172
173
174
1751.7.2                             2021-07-23                RRDGRAPH_LIBDBI(1)
Impressum