[rrd-developers] rrdtool + libdbi / mysql speed test

[Olivier Doucet]

Hello everyone,

Happy new year 2013 !

I spent the last few weeks testing libdbi support in rrdtool. Some
people already reported that using libdbi is not very efficient, but I
couldn't find any numbers on this (just "it's slow").

I've done some investigation, to track where performance was hit and
on which criteria. This is of course when you want rrdtool graph, as
data creation / updates are handled outside rrdtool.
When using rrdtool file for storing data, it took the same time to
graph 1 hour of data or 200 hours. Retrieving data from 1 DS or 5 DS
(inside the very same rrd file) took approximately the same time.
When using libdbi (with mysql backend), time needed to render the
graph is skyrocketing, based on how many hours of data you want, and
how many DS you have.
The following chart is very straightforward :
http://www.olivierdoucet.info/blog/wp-content/uploads/2012/12/graph.png

What is interesting here is that time is not spent on the query itself
(took less than a second, even for 1 year of data).
I used oprofile tool to see that the process heavily used function
'mysql_data_seek' (inside libmysqlclient).

Good news is that using rrdtool with a mysql database can be much
faster (we can be very close to rrd file graphing). I hope
modifications are compatible with libdbi (and that we do not need
specific code for MySQL). If I read source code right, modifications
need to be made near
https://github.com/oetiker/rrdtool-1.x/blob/master/src/rrd_fetch_libdbi.c#L260


Bad news is that my knowledge in C is quite limited and if I rewrite
the source, it will be ugly ... But if anyone is willing to spend some
time, I will be glad to help with tests, code reviewing and debugging.


Olivier