[rrd-developers] PATCH: rrdtool with SQL DS using libdbi
Martin Sperl
rrdtool at martin.sperl.org
Sun Mar 26 15:10:33 MEST 2006
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Hi!
I have created a patch to rrdtool 1.2 (SVN) which allows to define a DS
from a SQL database for graphing using the libdbi library for DB
abstraction.
Tobi told me to post the patch to this list, as he thinks that several
people might be interrested in it immediately.
He is also thinking about adding it to rrdtool 1.3 when this patch has
been given some more testing...
List of features:
* db-abstration via libdbi with DB support for the following Databases
out of the box:
* mysql
* postgress
* calculation of the "derivative" of a database values (so that we get
behaviour simmilar to COUNTER/DERIVE type datasources)
* Prediction logic, which gives from DB data values for:
* prediction
* sigma
* SQL-DS can also be used for further transformations with CDEFs
This patch is only tested on mysql but should (hopefully) also support
postgress out of the box.
I also believe that there is an oracle driver for libdbi in the pipeline.
Here the some sample graphs from a database source generated from a
mysql database including prediction+sigma calculation (average
ping-time) using the command from the documentation:
And the same as a derived datasource (adding "/derive"):
Here the manual page of the rrdgraph_libdbi:
RRDGRAPH_LIBDBI(1) rrdtool
RRDGRAPH_LIBDBI(1)
NAME
rrdgraph_libdbi - fetching data for graphing in rrdtool graph via
lib-
dbi
SYNOPSIS
<rrdfile> = sql//<libdbi driver>/<driver-option-name>=<driver-option-
value>/...[/rrdminstepsize=<stepsize>][/rrdfillmissing=<fill
missing n
samples>][/rrdperiodlength=<periodlength>][/rrdperiods=<Peri-
ods>][/rrdsmoothing=<Smoothing>]//<table>/<unixtimestamp
column>/<data
value column>[/derive]/<where clause 1>/.../<where clause n>[/predic-
tion][/sigma]
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
<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 steps>
defines the number of steps to fill with the last value
to avoid NaN boxes due to data-insertation jitter (default: 0 steps)
/rrdperiodlength=<length of one prediction period>
defines the length of one period used for prediction
(default: 1 day or 86400 seconds)
/rrdperiods=<number of periods used for predictions>
defines the number of periods to talke into account for
calculations of the prediction (default: 7)
/rrdsmoothingE=<smoothing of prediction>
defines the smoothing factor for the prediction. The
number of steps that are used for calculation is 1+2*B<E<lt>smoothing of
predictionE<gt>> (default: 2)
<table>
defines the table from which to fetch the resultset
<unixtimestamp column>
defines the column of E<lt>tableE<gt> which contains
the unix timestamp
<data value column>
defines the column of E<lt>tableE<gt> which contains
the value column, which should be graphed
/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>
/predict
calculate the prediction and sigma instead of just
returning the value
the returned value column-names are:
prediction
is returned if "/prediction" is given, use "prediction"
as ds-name in your DS definition.
sigma
is returned if "/sigma" is given, use "sigma" as
ds-name in your DS definition.
value
is returned otherwise, use "value" as "ds-name" as
ds-name in your DS definition.
using the prediction requires more work on the database side
and uses temporary tables heavily!
The highest value and lowest value for each predicion values
are removed,
so that one time events are not included in calculations!
EXAMPLES
Here an example of a table in a mysql database:
DB connect information
dbhost=127.0.0.1
user=rrd
password=secret
database=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/pass-
word=secret//RRDValue/UnixTimeStamp/value/RRDKeyID=1141464142203608274"
To illustrate this here a command to create a graph that contains the
actual values plus the prediction and some margins.
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:value=$DS_BASE:value:AVERAGE" \
"LINE1:value#FF0000:value" \
"DEF:predict=$DS_BASE/predict:prediction:AVERAGE" \
"LINE1:predict#00FF00:prediction" \
"DEF:sigma=$DS_BASE/predict:sigma:AVERAGE" \
"CDEF:upper=predict,4,sigma,*,+" \
"LINE1:upper#0000FF:+4 sigma" \
"CDEF:lower=predict,4,sigma,*,-" \
"LINE1:lower#0000FF:-4 sigma"
NOTES
* Naturally you can also use any other kind of driver that libdbi
sup-
ports - e.g postgress,...
* From the way the datasource 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 datasource string, but this has not been implemented...
* The only supported aggregation functions are: MIN, AVERAGE and MAX
BUGS
* at least on Linux please make sure that the libdbi driver is
explic-
itly linked against libdbi.so.0
check via ldd /usr/lib/dbd/libmysql.so, that there is a line with
libdbi.so.0.
otherwise at least the perl module RRDs will fail because the
dynamic
linker can not 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 actu-
ally this is a bug with libdbi make files!)
AUTHOR
Martin Sperl <rrdtool at martin.sperl.org>
1.2.12 2006-03-26
RRDGRAPH_LIBDBI(1)
And attached the patch to svn - I believe it should also apply to
1.2.12, but you migth have to generate some files (configure,...)!
Please tell me your comments, ideas, or if you find bugs!
In the hope that it gets included quickly into rrdtool itself...
Ciao,
Martin
P.s: It might also be possible to add functionality, so that we could
add data to the database via "rrdtool update". The question is if this
is really a needed feature? Might be nice for mrtg!
-- Attached file removed by Ecartis and put at URL below --
-- Type: text/plain
-- Size: 28k (28743 bytes)
-- URL : http://lists.ee.ethz.ch/p/rrdtool+libdbi.patch
--
Unsubscribe mailto:rrd-developers-request at list.ee.ethz.ch?subject=unsubscribe
Help mailto:rrd-developers-request at list.ee.ethz.ch?subject=help
Archive http://lists.ee.ethz.ch/rrd-developers
WebAdmin http://lists.ee.ethz.ch/lsg2.cgi
More information about the rrd-developers
mailing list