[rrd-developers] SQL Backend request
george at alink.co.za
Sun Jul 29 15:30:33 CEST 2007
I'm using the SQL patches extensively. I am polling data into the SQL
equivalent of 135,000 RRD's (at 1 metric per rrd).
My code is more complex than just updating (I'm using a hacked up
version of munin), so a full run also gets config data and a whole bunch
of other updates.
My server is a Dell PE1950 with 8GB RAM and 4 x 15K disks.
A full run at the moment consists of about 450 hosts and anywhere from
90to 135K metrics. Including loading configurations from the database
as well as writing the configs back after the run, this takes about 60
seconds. The actual data inserting takes about 45 seconds. I figure I
can probably push is slightly harder, but pulling the data off the host
can take some time as the host needs to supply the metrics, runs
I'm breaking my data up and rotating it on a monthly basis. Each
monthly table has about 800,000,000 rows and 1 multi column index. This
weighs in at about 30Gb of data.
I'm currently storing the data in an InnoDB table. I tried MyISAM but
the table locking was killing it. I didn't try delayed inserts,
although I'd think that might fix things.
As for accessing the data, I've found that it's quick as long as there
are loads of data sources. Generating a 30hr graph for 1 datasource
takes about 0.1 seconds. Week is not much slower, although a month can
be up to 2 seconds.
The slowest graph I've found is multiple data sources. I've got some
with 700 metrics on them and this can take up to 20 seconds to draw.
Alot of the overhead is due to the SQL patches' use of temporary tables.
This can be minimised by making sure temporary tables happen on a
memory fs which lowers latency, but it's not a magic bullet. Another
disadvantage of the temporary table is that the transactions are written
to the binlog which means the slave server has to do the same work for a
drawing. I've not thought about a way round this yet.
The patches work well and the flexibility of data in an SQL engine far
outweighs the negatives.
Robert Halstead wrote:
> I while ago I noticed a topic that let rrdtool use a database backend.
> What is the status with this project? Also, has this turned out to be a
> viable solution when updating large amounts of rrd files? We're
> currently exploring other storage media to help our writes become
> faster. We are currently writing just under 100K rrd files and have
> distributed writing. We were thinking of modifying rrdtool to use a
> MySql database and would like to modify the database beackend I saw on
> the list (if needed) accordingly so that we don't have to re-invent the
> I tried to look through the archives, but was only seeing patches to the
> library file. Is there a place to get the whole thing at? I couldn't
> see it in the /lib or /contrib download directories of the main site.
> "A fool acts, regardless; knowing well that he is wrong. The ignoramus
> acts on only what he knows, but all that he knows.
> The ignoramus may be saved, but the fool knows that he is doomed."
> Robert Halstead
> rrd-developers mailing list
> rrd-developers at lists.oetiker.ch
More information about the rrd-developers