Store To RDBMS

The idea has been around for a while and with the implementation of the DBI plugin it has come to my mind once again.

The big challenge with this is to map the data collected by collectd into tables in an efficient and elegant way.

collectd_dbstore

Bob Cotton has put together some SQL for PostgreSQL and a Perl based plugin to store values into it. This could serve as a starting point as a C based plugin with the same SQL backend.

See also: http://github.com/bcotton/collectd_dbstore/tree/master

Mapping identifiers

There are several ways to map identifiers to tables and they all have their pros and cons.

The naïve way

By far the easiest way would be to simply define a table which holds the information:

 CREATE TABLE identifier
 (
   id              INT NOT NULL auto_increment,
   hostname        VARCHAR(64) NOT NULL DEFAULT "localhost"
   plugin          VARCHAR(64) NOT NULL,
   plugin_instance VARCHAR(64) NOT NULL,
   type            VARCHAR(64) NOT NULL,
   type_instance   VARCHAR(64) NOT NULL,
   PRIMARY KEY(id),
   UNIQUE(hostname, plugin, plugin_instance, type, type_instance),
   INDEX(plugin, plugin_instance),
   INDEX(type, type_instance)
 );

The advantage of this layout it that it's easy. It's easy to read, easy to add rows to and will only require a single SQL statement to get the ID for a specific identifier. The disadvantage is that data is being held redundantly. If there are 200 values for a host, there will be 200 rows in this table which have the same value for hostname..