{ "mode": "man", "parameter": "rrdgraph_libdbi", "section": "1", "url": "https://www.chedong.com/phpMan.php/man/rrdgraph_libdbi/1/json", "generated": "2026-06-03T00:21:25Z", "synopsis": " = sql///=/...", "sections": { "NAME": { "content": "rrdgraphlibdbi - fetching data for graphing in rrdtool graph via libdbi\n", "subsections": [] }, "SYNOPSIS": { "content": " = sql///=/...", "subsections": [ { "name": "[/rrdminstepsize=][/rrdfillmissing=]//", "content": "" }, { "name": "//[/derive]//.../", "content": "" } ] }, "DESCRIPTION": { "content": "This pseudo-rrd-filename defines a sql datasource:\n", "subsections": [ { "name": "sql//", "content": "magic cookie-prefix for a libdbi type datasource\n" }, { "name": "", "content": "which libdbi driver to use (e.g.: mysql)\n\n=\ndefines the parameters that are required to connect to the database with the given libdbi driver\n(These drivers are libdbi dependent - for details please look at the driver documentation of libdbi!)\n\n/rrdminstepsize=\ndefines the minimum number of the step-length used for graphing (default: 300 seconds)\n\n/rrdfillmissing=\ndefines the number of seconds to fill with the last value to avoid NaN boxes due to data-insertation jitter (default: 0 seconds)\n" }, { "name": "
", "content": "defines the table from which to fetch the resultset.\n\nIf there is a need to fetch data from several tables, these tables can be defined by separating the tablenames with a \"+\"\n\nhex-type-encoding via %xx are translated to the actual value, use %% to use %\n\n<[*]unixtimestamp column>\ndefines the column of
which contains the unix-timestamp\n- if this is a DATETIME field in the database, then prefix with leading '*'\n\nhex-type-encoding via %xx are translated to the actual value, use %% to use %\n" }, { "name": "", "content": "defines the column of
which contains the value column, which should be graphed\n\nhex-type-encoding via %xx are translated to the actual value, use %% to use %\n" }, { "name": "/derive", "content": "defines that the data value used should be the delta of the 2 consecutive values (to simulate COUNTER or DERIVE type datasources)\n" }, { "name": "/", "content": "defines one (ore more) where clauses that are joined with AND to filter the entries in the
\n\nhex-type-encoding via %xx are translated to the actual value, use %% to use %\n\nthe returned value column-names, which can be used as ds-names, are:\n\nmin, avg, max, count and sigma\nare returned to be used as ds-names in your DS definition.\nThe reason for using this is that if the consolidation function is used for min/avg and max, then the engine is used several times.\nAnd this results in the same SQL Statements used several times\n" } ] }, "EXAMPLES": { "content": "Here an example of a table in a MySQL database:\n\nDB connect information\ndbhost=127.0.0.1\nuser=rrd\npassword=secret\ndbname=rrd\n\nhere the table:\nCREATE TABLE RRDValue (\nRRDKeyID bigint(20) NOT NULL,\nUnixTimeStamp int(11) NOT NULL,\nvalue double default NOT NULL,\nPRIMARY KEY (RRDKeyID,UnixTimeStamp)\n);\n\nand the RRDKeyID we want to graph for is: 1141942900757789274\n\nThe pseudo rrd-filename to access this is:\n\"sql//mysql/host=127.0.0.1/dbname=rrd/username=rrd/password=secret//RRDValue/UnixTimeStamp\n/value/RRDKeyID=1141464142203608274\"\n\nTo illustrate this here a command to create a graph that contains the actual values.\n\nDSBASE=\"sql//mysql/host=127.0.0.1/dbname=rrd/username=rrd/password=passwd//RRDValue/UnixTimeStamp/value/RRDKeyID=1141942900757789274\"\nrrdtool graph test.png --imgformat=PNG --start=-1day --end=+3hours --width=1000 --height=600 \\\n\"DEF:min=$DSBASE:min:AVERAGE\" \\\n\"LINE1:min#FF0000:value\" \\\n\"DEF:avg=$DSBASE:avg:AVERAGE\" \\\n\"LINE1:avg#00FF00:average\" \\\n\"DEF:max=$DSBASE:max:AVERAGE\" \\\n\"LINE1:max#FF0000:max\" \\\n\"DEF:sigma=$DSBASE:sigma:AVERAGE\" \\\n\"CDEF:upper=avg,4,sigma,*,+\" \\\n\"LINE1:upper#0000FF:+4 sigma\" \\\n\"CDEF:lower=avg,4,sigma,*,-\" \\\n\"LINE1:lower#0000FF:-4 sigma\"\n", "subsections": [] }, "NOTES": { "content": "* Naturally you can also use any other kind of driver that libdbi supports - e.g. postgres,\n...\n\n* From the way the data source is joined, it should also be possible to do joins over\ndifferent tables\n(separate tables with \",\" in table and add in the WHERE Clauses the table equal joins.\nThis has not been tested!!!)\n\n* It should also be relatively simple to add to the database using the same data source\nstring.\nThis has not been implemented...\n\n* The aggregation functions are ignored and several data columns are used instead\nto avoid querying the same SQL several times when minimum, average and maximum are needed\nfor graphing...\n\n* for DB efficiency you should think of having 2 tables, one containing historic values and\nthe other containing the latest data.\nThis second table should be kept small to allow for the least amount of blocking SQL\nstatements.\nWith mysql you can even use myisam table-type for the first and InnoDB for the second.\nThis is especially interesting as with tables with +100M rows myisam is much smaller then\nInnoDB.\n\n* To debug the SQL statements set the environment variable RRDDEBUGSQL and the actual SQL\nstatements and the timing is printed to stderr.\n", "subsections": [ { "name": "Performance issues with MySQL backend", "content": "Previous versions of LibDBI have a big performance issue when retrieving data from a MySQL\nserver. Performance impact is exponentially based on the number of values you retrieve from\nthe database. For example, it would take more than 2 seconds to graph 5DS on 150 hours of\ndata with a precision of 5 minutes (against 100ms when data comes from a RRD file). This bug\nhas been fixed in version 0.9.0 of LibDBI. You can find more information on this libdbi-\nusers mailing list thread: http://sourceforge.net/mailarchive/message.php?msgid=30320894\n" } ] }, "BUGS": { "content": "* at least on Linux please make sure that the libdbi driver is explicitly linked against\nlibdbi.so.0\ncheck via ldd /usr/lib/dbd/libmysql.so, that there is a line with libdbi.so.0.\notherwise at least the perl module RRDs will fail because the dynamic linker cannot find\nsome symbols from libdbi.so.\n(this only happens when the libdbi driver is actually used the first time!)\nThis is KNOWN to be the case with RHEL4 and FC4 and FC5! (But actually this is a bug with\nlibdbi make files!)\n\n* at least version 0.8.1 of libdbi exhibits a bug with BINARY fields\n(shorttext,text,mediumtext,longtext and possibly also BINARY and BLOB fields),\nthat can result in coredumps of rrdtool.\nThe tool will tell you on stderr if this occurs, so that you know what may be the reason.\nIf you are not experiencing these coredumps, then set the environment variable\nRRDNOLIBDBIBUGWARNING,\nand then the message will not get shown.\n", "subsections": [] }, "AUTHOR": { "content": "Martin Sperl \n\n\n\n1.7.2 2022-03-17 RRDGRAPHLIBDBI(1)", "subsections": [] } }, "summary": "rrdgraphlibdbi - fetching data for graphing in rrdtool graph via libdbi", "flags": [], "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.", "DSBASE=\"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=$DSBASE:min:AVERAGE\" \\", "\"LINE1:min#FF0000:value\" \\", "\"DEF:avg=$DSBASE:avg:AVERAGE\" \\", "\"LINE1:avg#00FF00:average\" \\", "\"DEF:max=$DSBASE:max:AVERAGE\" \\", "\"LINE1:max#FF0000:max\" \\", "\"DEF:sigma=$DSBASE:sigma:AVERAGE\" \\", "\"CDEF:upper=avg,4,sigma,*,+\" \\", "\"LINE1:upper#0000FF:+4 sigma\" \\", "\"CDEF:lower=avg,4,sigma,*,-\" \\", "\"LINE1:lower#0000FF:-4 sigma\"" ], "see_also": [] }