1RRDGRAPH_LIBDBI(1) rrdtool RRDGRAPH_LIBDBI(1)
2
6 rrdgraph_libdbi - fetching data for graphing in rrdtool graph via
7 libdbi
8
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
16 This pseudo-rrd-filename defines a sql datasource:
17 sql//
19 magic cookie-prefix for a libdbi type datasource
20 <libdbi driver>
22 which libdbi driver to use (e.g.: mysql)
23 <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 /rrdminstepsize=<minimum step size>
29 defines the minimum number of the step-length used for graphing (default: 300 seconds)
30 /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 <table>
35 defines the table from which to fetch the resultset.
36 If there is a need to fetch data from several tables, these tables can be defined by separating the tablenames with a "+"
38 hex-type-encoding via %xx are translated to the actual value, use %% to use %
40 <[*]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 hex-type-encoding via %xx are translated to the actual value, use %% to use %
46 <data value column>
48 defines the column of <table> which contains the value column, which should be graphed
49 hex-type-encoding via %xx are translated to the actual value, use %% to use %
51 /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 /<where clause(s)>
56 defines one (ore more) where clauses that are joined with AND to filter the entries in the <table>
57 hex-type-encoding via %xx are translated to the actual value, use %% to use %
59 the returned value column-names, which can be used as ds-names, are:
61 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
68 Here an example of a table in a MySQL database:
69 DB connect information
71 dbhost=127.0.0.1
72 user=rrd
73 password=secret
74 dbname=rrd
75 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 and the RRDKeyID we want to graph for is: 1141942900757789274
85 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 To illustrate this here a command to create a graph that contains the
90 actual values.
91 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
107 * Naturally you can also use any other kind of driver that libdbi
108 supports - e.g. postgres, ...
109 * 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 * 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 * 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 * 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 * To debug the SQL statements set the environment variable RRDDEBUGSQL
135 and the actual SQL statements and the timing is printed to stderr.
136
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
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 * 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
171 Martin Sperl <rrdtool@martin.sperl.org>
1721.7.2 2020-08-01 RRDGRAPH_LIBDBI(1)