X-Git-Url: https://git.tokkee.org/?a=blobdiff_plain;f=doc%2Frrdgraph_libdbi.txt;fp=doc%2Frrdgraph_libdbi.txt;h=3eeb4f2803fd36f7a03ea9f2d369c4fcb2fe9ece;hb=645054bac6187b0e83fd4125fd59e4feda216b64;hp=0000000000000000000000000000000000000000;hpb=ffa00ac697dccce18dca8880ca7a14066521ac5c;p=pkg-rrdtool.git diff --git a/doc/rrdgraph_libdbi.txt b/doc/rrdgraph_libdbi.txt new file mode 100644 index 0000000..3eeb4f2 --- /dev/null +++ b/doc/rrdgraph_libdbi.txt @@ -0,0 +1,164 @@ +RRDGRAPH_LIBDBI(1) rrdtool RRDGRAPH_LIBDBI(1) + + + +NNAAMMEE + rrdgraph_libdbi - fetching data for graphing in rrdtool graph via + libdbi + +SSYYNNOOPPSSIISS + = ssqqll////<>//<>==<>//......[[//rrrrddmmiinnsstteeppssiizzee==<>]][[//rrrrddffiillllmmiissssiinngg==<>]]////<>//<>//<>[[//ddeerriivvee]]//<>//......//<> + +DDEESSCCRRIIPPTTIIOONN + This pseudo-rrd-filename defines a sql datasource: + + ssqqll//// + magic cookie-prefix for a libdbi type datasource + + <> + 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!) + + //rrrrddmmiinnsstteeppssiizzee=<> + defines the minimum number of the step-length used for graphing (default: 300 seconds) + + //rrrrddffiillllmmiissssiinngg=<> + defines the number of steps to fill with the last value to avoid NaN boxes due to data-insertation jitter (default: 0 steps) + + <> + 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 % + + <<[[**]]uunniixxttiimmeessttaammpp ccoolluummnn>> + defines the column of EtableE 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 % + + <> + defines the column of EtableE which contains the value column, which should be graphed + + hex-type-encoding via %xx are translated to the actual value, use %% to use % + + //ddeerriivvee + defines that the data value used should be the delta of the 2 consecutive values (to simulate COUNTER or DERIVE type datasources) + + //<> + defines one (ore more) where clauses that are joined with AND to filter the entries in the table + + 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: + + mmiinn, aavvgg, mmaaxx, ccoouunntt and ssiiggmmaa + 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 + +EEXXAAMMPPLLEESS + 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/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" + +NNOOTTEESS + * Naturally you can also use any other kind of driver that libdbi + supports - e.g postgres, ... + + * 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. + This has not been implemented... + + * The aggregation functions are ignored and several data columns are + used instead + to avoid querying the same SQL several times when minimum, average + and maximum are needed for graphing... + + * for DB efficiency you should think of having 2 tables, one containing + historic values and the other containing the latest data. + This second table should be kept small to allow for the least ammount + of blocking SQL statements. + Whith mysql you can even use myisam table-type for the first and + InnoDB for the second. + This is especially interresting as with tables with +100M rows myisam + is much smaller then InnoDB. + + * To debug the SQL statements set the environment variable RRDDEBUGSQL + and the actual SQL statements and the timing is printed to stderr. + +BBUUGGSS + * at least on Linux please make sure that the libdbi driver is + explicitly 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 + actually this is a bug with libdbi make files!) + + * at least version 0.8.1 of libdbiexhibits a bug with BINARY fields + (shorttext,text,mediumtext,longtext and possibly also BINARY and BLOB + fields), + that can result in coredumps of rrdtool. + The tool will tell you on stderr if this occures, so that you know + what may be the reason. + If you are not experiencing these coredumps, then set the environment + variable RRD_NO_LIBDBI_BUG_WARNING, + and then the message will not get shown. + +AAUUTTHHOORR + Martin Sperl + + + +1.3.999 2009-06-09 RRDGRAPH_LIBDBI(1)