{"id":74,"date":"2012-12-31T19:57:41","date_gmt":"2012-12-31T17:57:41","guid":{"rendered":"http:\/\/www.olivierdoucet.info\/blog\/?p=74"},"modified":"2013-01-09T17:43:27","modified_gmt":"2013-01-09T15:43:27","slug":"rrdtool-mysql","status":"publish","type":"post","link":"https:\/\/www.olivierdoucet.info\/blog\/2012\/12\/31\/rrdtool-mysql\/","title":{"rendered":"RRDTool with MySQL"},"content":{"rendered":"

A few months ago, I started some tests with RRDTool and a data source in MySQL. At that time, I just saw that performance was awful but did not investigate further. Now is time to check why, and if any improvements could be made.<\/p>\n

Convert existing data to SQL<\/h1>\n

First, let’s see how to convert existing data to MySQL. You would need ‘rrdtool dump’ + a tool that read data and insert it back to your database; The tool I wrote for this (in PHP) will be available in a few days on github (stay tuned). I’ll talk later about how to design your database, and what are the improvements you can make.
\nThe key here is how you’ll import the data : do not use INSERT INTO statement, this will take too much time (insert buffer is mono thread in MySQL and very inefficient when dealing with large amount of data). use LOAD DATA [LOCAL] INFILE instead.<\/p>\n

Graphing<\/h1>\n

rrdtool has support for libdbi, that supports several databases system (MySQL, PostgreSQL, SQLite, FreeTDS – i.e MS SQL and FireBird).<\/p>\n

You need to change your command line of course. Here is an example.
\nBEFORE :<\/p>\n

rrdtool graph - DEF:user=\/path\/to\/src.rrd:user:AVERAGE:step=600 AREA:user#66ff00:\"user\"<\/pre>\n
AFTER :<\/p>\n
rrdtool graph - DEF:user=sql\/\/mysql\/host=127.0.0.1\/dbname=testrrd\/username=myuser\/password=pwd\/\/data\/*ts\/val\/idds=1:avg:AVERAGE:step=600 AREA:user#66ff00:\"user\"<\/pre>\n
Notes :<\/p>\n