[rrd-developers] SQL Backend request

[George]

Hi Robert,

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 
scripts, etc.

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.

Cheers!

George

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 
> wheel.
> 
> 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
> https://lists.oetiker.ch/cgi-bin/listinfo/rrd-developers