[rrd-developers] RRD PostgreSQL extension

[Stanislav Sinyagin]

how is it supposed to handle large RRD files over the network?
If, say, the client and server are on different hosts, and you're updating RRD data in a BLOB. and RRD data can easily be several megabytes. So, how is the data transfer supposed to be organized? I have a feeling that you're going to transfer the whole file over the network, probably more than once per update.


Also it's no big deal to organize the history table alongside with the last value, and let the SQL engine handle the data in a traditional way.




----- Original Message -----
> From: Dustin Fisch <fooker at lab.sh>
> To: Anthony Johnson <ansoni at gmail.com>
> Cc: rrd-developers at lists.oetiker.ch
> Sent: Wednesday, September 12, 2012 5:32 AM
> Subject: Re: [rrd-developers] RRD PostgreSQL extension
> 
> Hm. Also a nice idea.
> 
> But the benefit I want to from the PostgreSQL extension is to use psql
> as a network protocol and feeding the RRD database from data I have in
> my PostgreSQL database.
> 
> The idea is to have a table with the following columns:
> id BIGSERIAL,
> name VARCHAR,
> value DOUBLE,
> history RRD
> 
> now I can use a trigger for the "value" column to update the history 
> on
> each update automatically.
> 
> And as almost all programming languages have a PostgreSQL driver, they
> can would have a network-able RRD interface out of the box.
> 
> cheers
> fooker
> 
> 
> Anthony Johnson <ansoni at gmail.com> wrote:
> 
>>  Just curious... couldn't you simply build or use one of the existing
>>  Database filesystems that work with Fuse?  Then you could mount a
>>  filesystem whose backend is Postgres.
>> 
>> 
> http://sourceforge.net/apps/mediawiki/fuse/index.php?title=DatabaseFileSystems
>> 
>> 
>>  The postgres FUSE project seems to be dead, but these seem to be
>>  pretty simple to create.  This would mean that all existing tools
>>  such as Cacti would not need any changes and migrating to such a
>>  setup would require only a cp /olddirectory/* /sqlbackeddirectory/
>> 
>>  I would hope that a database backed filesystem would also be able to
>>  take advantage of filesystem caches at the OS-level which might be a
>>  free performance gain.
>> 
>>  Good luck,
>> 
>>  Anthony
>> 
>>  On Tue, Sep 11, 2012 at 7:44 PM, Dustin Fisch <fooker at lab.sh> wrote:
>> 
>>  >
>>  >
>>  > Tobias Oetiker <tobi at oetiker.ch> wrote:
>>  >
>>  > > Hi Dustin,
>>  > >
>>  > > Yesterday Dustin Fisch wrote:
>>  > >
>>  > > > Hi,
>>  > > >
>>  > > > I am currently thinking about building an RRD extension for
>>  > > > PostgreSQL.
>>  > > >
>>  > > > The extension will provide a RRD data type which represents 
> a
>>  > > > complete database and functions for all the existing 
> functions
>>  > > > of the RRD tool, like create, update, first and graph...
>>  > > >
>>  > > > PostgreSQL has a nice API for LOB, which allows to store the
>>  > > > whole RRD database using the PostgreSQL file management.
>>  > > >
>>  > > > I already have taken a look to the RRD code. As far as I can
>>  > > > see, there are functions like rrd_create_r(...) which 
> provides
>>  > > > the functionality I need for such an extension.
>>  > > >
>>  > > > The PostgreSQL LOB API currently provides a file descriptor 
> for
>>  > > > the access to the data stored in the LOB.
>>  > > >
>>  > > > Unfortunately, it looks like the RRD API only allows the 
> usage
>>  > > > of a filename.
>>  > > >
>>  > > > Is there any way to use a file descriptor instead of the
>>  > > > filename? Or is there any chance such a patch would be 
> accepted?
>>  > >
>>  > > if done nicely, I'll be glad to integrate such a patch ... I 
> guess
>>  > > you would have to compile without mmap support ...
>>  > I'm not sure about mmap. I think I have to try it.
>>  >
>>  > The plan for the patch is to refactoring the rrd_*_r methods by
>>  > moving out the real work of the method into something called
>>  > rrd_*_x. The only thing remaining in the original method are the
>>  > calls to rrd_open and rrd_close and the call to the new method.
>>  >
>>  > The new rrd_*_x methods will mirror the interface of there rrd_*_r
>>  > counterparts expect the filename parameter, which will be replaced
>>  > by the already opened rrd_file_t.
>>  >
>>  > >
>>  > > how would this pg integration work, would the rrd data be stored
>>  > > in a blob ? wouldn't this get read from disk entirely on 
> every
>>  > > update and make things rather slow ?
>>  > Right. The idea is to store the RRD data in a BLOB.
>>  >
>>  > But I don't see any performance problem with this as a PostgreSQL
>>  > extension is allowed to work on the file backing the BLOB directly
>>  > using a file descriptor.
>>  >
>>  > This makes no difference to the way RRDtool currently works. Maybe
>>  > this allows to to use the current mmap code, too.
>>  >
>>  > >
>>  > > cheers
>>  > > tobi
>>  > >
>>  > >
>>  >
>>  > cheers
>>  > fooker
>>  >
>>  > --
>>  > http://lab.sh
>>  >
>>  > _______________________________________________
>>  > rrd-developers mailing list
>>  > rrd-developers at lists.oetiker.ch
>>  > https://lists.oetiker.ch/cgi-bin/listinfo/rrd-developers
>>  >
>>  >
> 
> 
> -- 
> http://lab.sh
> 
> _______________________________________________
> rrd-developers mailing list
> rrd-developers at lists.oetiker.ch
> https://lists.oetiker.ch/cgi-bin/listinfo/rrd-developers
>