EnglishFrenchSpanish

OnWorks favicon

rrdgraph_libdbi - Online in the Cloud

Run rrdgraph_libdbi in OnWorks free hosting provider over Ubuntu Online, Fedora Online, Windows online emulator or MAC OS online emulator

This is the command rrdgraph_libdbi that can be run in the OnWorks free hosting provider using one of our multiple free online workstations such as Ubuntu Online, Fedora Online, Windows online emulator or MAC OS online emulator

PROGRAM:

NAME


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>

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 (e.g: mysql)

<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 seconds>
defines the number of seconds to fill with the last value to avoid NaN boxes due to data-insertation jitter (default: 0 seconds)

<table>
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 %

/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>

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

EXAMPLES


Here 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
(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 data source
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.

Performance issues with MySQL backend


LibDBI 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
information on this libdbi-users mailing list thread :
http://sourceforge.net/mailarchive/message.php?msg_id=30320894

Use rrdgraph_libdbi online using onworks.net services


Free Servers & Workstations

Download Windows & Linux apps

  • 1
    strace
    strace
    The strace project has been moved to
    https://strace.io. strace is a
    diagnostic, debugging and instructional
    userspace tracer for Linux. It is used
    to monitor a...
    Download strace
  • 2
    gMKVExtractGUI
    gMKVExtractGUI
    A GUI for mkvextract utility (part of
    MKVToolNix) which incorporates most (if
    not all) functionality of mkvextract and
    mkvinfo utilities. Written in C#NET 4.0,...
    Download gMKVExtractGUI
  • 3
    JasperReports Library
    JasperReports Library
    JasperReports Library is the
    world's most popular open source
    business intelligence and reporting
    engine. It is entirely written in Java
    and it is able to ...
    Download JasperReports Library
  • 4
    Frappe Books
    Frappe Books
    Frappe Books is a free and open source
    desktop book-keeping software that's
    simple and well-designed to be used by
    small businesses and freelancers. It'...
    Download Frappe Books
  • 5
    Numerical Python
    Numerical Python
    NEWS: NumPy 1.11.2 is the last release
    that will be made on sourceforge. Wheels
    for Windows, Mac, and Linux as well as
    archived source distributions can be fou...
    Download Numerical Python
  • 6
    CMU Sphinx
    CMU Sphinx
    CMUSphinx is a speaker-independent large
    vocabulary continuous speech recognizer
    released under BSD style license. It is
    also a collection of open source tools ...
    Download CMU Sphinx
  • More »

Linux commands

crm
crm
Use crm online using onworks.net
services. ...
Run crm
  • 4
    crmgr
    crmgr
    crmgr - administration utility for QDBM
    Curia ...
    Run crmgr
  • 5
    gappletviewer
    gappletviewer
    gappletviewer - Load and runs an applet
    ...
    Run gappletviewer
  • 6
    gaps
    gaps
    mummer - package for sequence alignment
    of multiple genomes ...
    Run gaps
  • s-processed="true">
    g15stats
    g15stats - A CPU/Memory/Swap usage
    meter for G15Daemon DESCRIPTION: The
    packages provides the following usage
    meter for LCD on some Logitech
    keyboards, usind g...
    Run g15stats
  • More »
  • Ad