--- /dev/null
+DBStore - A collectd output plugin to store values in an RDBMS.
+
+Most of the SQL here is PostgreSQL specific. It has not been tested
+with any other database. Please don't ask me how to make it work with
+your database as I don't use your database (unless it is postgres :-)
+
+This has been tested with Posgtres 8.3 and 8.4. I don't pretend to be
+a DBA. I'm sure there are things that could be done better.
+
+Dependencies:
+1. collectd with perl
+2. Postgres (8.4 if you want to interesting things with COUNTERS, see below)
+3. Perl DBI
+4. Perl DBD driver for postgres
+
+Quick Start Guide
+
+1. Have postgres installed and running
+2. createdb <database>
+3. psql -U <username> -f <path to here>/sql/metrics.sql
+4. psql -U <username> -f <path to here>/sql/create_tables.sql
+5. Add the following to your collectd.conf
+ <Plugin perl>
+ IncludeDir "<path to this directory>"
+ BaseName "Collectd::Plugin"
+ LoadPlugin "DBStore"
+ <Plugin DBStore>
+ DBIDriver "Pg"
+ DatabaseHost "<dbhost>"
+ DatabasePort "5432"
+ DatabaseName "<database>"
+ DatabaseUser "<username>"
+ DatabasePassword "<password>"
+ </DBStore>
+ </Plugin>
+6. configure postgres to turn on constrain exclusion.
+
+Rationale and Approach
+
+We wanted to collect system stats at full resolution, possibly longer
+than the configured RRAs, to go back in time to analyize performance.
+
+After looking at the collectd Wiki about a table structure to store
+the data, it occured to me that this could be handled as a
+"dimensional model" or "star schema". Basically build a data
+warehouse.
+
+Putting the redundant information (hostname, plugin and plugin type)
+into their own tables creates a very skinny "fact" table to hold
+the measurements. The next problem was data volume.
+
+Postgres supports data partitioning which will allow you to store
+metrics data into "child" tables that have been partitioned by some
+range of dates/times. Insertion and query time can be inproved for
+very large data sets by only deailing with a subset of the data.
+
+Insertions into the "parent" table are redirected to the appropriate
+child table. The time-span of a child table can be any duration.
+
+Indices are only kept on child tables and old data can quickly be
+removed with a DROP TABLE.
+
+While postgres does support data partitioning, the maintenance of the
+required tables and triggers has to be done manually. That's what most
+of the included SQL is doing.
+
+Configuration
+
+Depending on volume of data coming from collectd you may need to adjust
+the time duration of your child tables.
+
+There are two aspects of data partitioning that need to be created (and maintained):
+ 1. Child tables and indices
+ 2. The insert trigger function
+
+The create_tables.sql file is the entry point for the functions that
+will create the tables and trigger functions. There are two functions,
+they both take the same arguments:
+ 1. create_partition_tables()
+ 2. create_partition_trigger()
+
+The arguments (and postgres types) to these functions are:
+ 1. The parent table name (text)
+ 2. The start timestamp (timestamp)
+ 3. The length of time in the future to create tables (interval)
+ 4. The "step" for each table (e.g. month, day) (text)
+ 5. The format string for the table suffix. The table name will be
+ <parent>_<suffix> (e.g. metrics_2009_02) (text)
+
+create_partition_tables() will create the child tables with the
+appropriate range checks.
+
+create_partition_trigger() will create the trigger function that will
+redirect the insert into the appropriate child table. This function
+still needs to be associated with an insert trigger.
+
+The insert trigger function is one giant if/then/else statement. So
+you don't want the interval too far in the past, or generate too far
+in the future and not update. At some point it will have some impact
+on performance. I haven't tested this impact.
+
+Maintenance
+
+Depending on how far into the future you generate tables and the
+trigger function, you will need to create new child tables and
+regenerate the trigger function. I would suggest putting this into cron
+just before you period is about to expire. I'll let you work out the
+math as to when to do this.
+
+Should you forget, all rows will be inserted into the parent
+table. You won't loose data, but it will hurt performance.
+
+Querying with partitions
+
+To enable the query planner to use the table partitions you need to do
+two things:
+ 1. Turn on constrain exclusion:
+ SET constraint_exclusion = on;
+ or set it in postgresql.conf
+ 2. Include in the where clause of your queries static timestamps.
+ e.g. select * from metrics where timestamp between
+ '2009-01-01'::timestamp and '2009-02-01'::timestamp
+ functions that return timestamps don't count as 'static'. If in
+ doubt use EXPLAIN.
+
+Inserting Data
+
+Because of the dimensional model, "fact" inserts need to lookup,
+possibly create and attach the dimensions. This is accomplished
+through the function insert_metric() whose signature looks like:
+
+ insert_metric(in_timestamp timestamp,
+ in_measure double precision,
+ in_hostname text,
+ in_ds_type datasource_type,
+ in_plugin text,
+ in_plugin_instance text,
+ in_type text,
+ in_type_name text,
+ in_type_instance text) returns void
+
+Where in_timestamp must be something that postgres can convert to a
+timestamp.
+
+datasource_type is either 'GUAGE' or 'COUNTER'
+
+Working with COUNTERS
+
+Many of the values collected by collectd are of type COUNTER. Ethernet
+interfaces, for example, simply keep a counter of the number of
+bytes/packets/octects etc sent. To calculate bytes/second you need to
+know the difference in time, and the difference in the counter between
+two samples.
+
+Postgres introduced in 8.4 "window" functions which allow you to do
+calculations among the rows returned from a query. One of those
+functions is lag() which will subtract the value in one row from
+another. This is a handy way of working with COUNTERS.
+
+There is an example VIEW definition at the bottom on metrics.sql that
+illustrates this use of this feature. Using views and partitioned
+tables do not really work well as when the view is constructed it
+will query the entire table without the needed WHERE clauses
+illustrated above. This will be slow.
+
+Patches and suggestions welcome.
+
+Bob Cotton
+bob.cotton@gmail.com
+
+Further Reading
+http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html
+http://www.slideshare.net/xzilla/postgresql-partitioning-pgcon-2007-presentation
+
--- /dev/null
+# A Perl/DBI collectd plugin for recording samples into a relational
+# datanase. Currently only Postgres has been tested.
+#
+# Written by Bob Cotton <bob.cotton@gmail.com>
+#
+# This is free software; you can redistribute it and/or modify it under
+# the terms of the GNU General Public License as published by the Free
+# Software Foundation; only version 2 of the License is applicable.
+
+# Notes:
+# Depends on Perl DBI and
+# A sample perl plugin config may look like this:
+#
+# <Plugin perl>
+# IncludeDir "<path to this file>"
+# LoadPlugin DBStore
+# <Plugin DBStore>
+# DBIDriver "Pg"
+# DatabaseHost "dbhost"
+# DatabasePort "5432"
+# DatabaseName "metrics_database"
+# DatabaseUser "metrics"
+# DatabasePassword "secret"
+# </Plugin>
+# </Plugin>
+package Collectd::Plugin::DBStore;
+use strict;
+use warnings;
+
+use Collectd qw( :all );
+use DBI;
+use POSIX qw(strftime);
+
+plugin_register (TYPE_INIT, 'DBStore', 'dbstore_init');
+plugin_register (TYPE_CONFIG, 'DBStore', 'dbstore_config');
+plugin_register (TYPE_WRITE, 'DBStore', 'dbstore_write');
+
+my $dbh;
+my $dbi_driver;
+my $db_host;
+my $db_port;
+my $db_name;
+my $db_user;
+my $db_password;
+
+sub dbstore_init
+{
+ if (!defined $dbi_driver) {
+ plugin_log (LOG_ERR, "DBStore: No DBIDriver configured.");
+ return 0;
+ }
+
+ if (!defined $db_host) {
+ plugin_log (LOG_ERR, "DBStore: No DatabaseHost configured");
+ return 0;
+ }
+
+ if (!defined $db_port) {
+ plugin_log (LOG_ERR, "DBStore: No DatabasePort configured");
+ return 0;
+ }
+
+ if (!defined $db_name) {
+ plugin_log (LOG_ERR, "DBStore: No DatabaseName configured");
+ return 0;
+ }
+
+ if (!defined $db_user) {
+ plugin_log (LOG_ERR, "DBStore: No DatabaseUser configured");
+ return 0;
+ }
+
+ if (!defined $db_password) {
+ plugin_log (LOG_ERR, "DBStore: No DatabasePassword configured");
+ return 0;
+ }
+ return 1;
+}
+
+sub dbstore_config
+{
+ my $config = shift;
+ my $count = scalar(@{$config->{'children'}});
+ for (my $i = 0; $i < $count; $i++) {
+ my $key = $config->{'children'}[$i]->{'key'} || "";
+ my $value = $config->{'children'}[$i]->{'values'}[0];
+ if ($key eq "DatabaseHost") { $db_host = $value }
+ elsif ($key eq "DatabasePort") { $db_port = $value }
+ elsif ($key eq "DatabaseName") { $db_name = $value }
+ elsif ($key eq "DatabaseUser") { $db_user = $value }
+ elsif ($key eq "DatabasePassword") { $db_password = $value }
+ elsif ($key eq "DBIDriver") { $dbi_driver = $value }
+ }
+ return 1;
+}
+
+sub dbstore_write
+ {
+ my $type = shift;
+ my $ds = shift;
+ my $vl = shift;
+ my $return;
+
+ if (scalar (@$ds) != scalar (@{$vl->{'values'}})) {
+ plugin_log (LOG_WARNING, "DS number does not match values length");
+ return;
+ }
+
+ my $dbh = DBI->connect("dbi:$dbi_driver:dbname=$db_name;host=$db_host;port=$db_port;",
+ "$db_user", "$db_password");
+ unless(defined $dbh) {
+ plugin_log(LOG_ERR, "DBStore: could not connect to database " . DBI->errstr);
+ return 0;
+ }
+
+ my $stmt = $dbh->prepare("select insert_metric(?::timestamp, ?, ?, ?, ?, ?, ?, ?, ?)");
+ unless(defined $stmt) {
+ plugin_log(LOG_ERR, "DBStore: could not prepare statement " . $dbh->errstr);
+ return 0;
+ }
+
+ my $timestamp = strftime("%G-%m-%d %H:%M:%S", localtime($vl->{'time'}));
+ for (my $i = 0; $i < scalar (@$ds); ++$i) {
+ $return = $stmt->execute($timestamp,
+ $vl->{'values'}->[$i],
+ $vl->{'host'},
+ @{$ds}[$i]->{'type'} == 1 ? 'GUAGE' : 'COUNTER',
+ $vl->{'plugin'},
+ $vl->{'plugin_instance'},
+ $type,
+ @{$ds}[$i]->{'name'},
+ $vl->{'type_instance'});
+ if($return < 1) {
+ plugin_log(LOG_ERR, "DBStore: insert failed: ". $dbh->errstr);
+ $dbh->disconnect();
+ return 0;
+ }
+ $stmt->finish();
+ }
+ $dbh->disconnect();
+ return 1;
+ }
+return 1;
--- /dev/null
+select create_partition_tables('metrics', now()::timestamp, '6 months'::interval, 'month', 'YYYY_MM');
+select create_partition_trigger('metrics', now()::timestamp, '6 months'::interval, 'month', 'YYYY_MM');
+CREATE TRIGGER insert_metrics_trigger BEFORE INSERT ON metrics FOR EACH ROW EXECUTE PROCEDURE metrics_insert_trigger();
--- /dev/null
+-- Written by Bob Cotton <bob.cotton@gmail.com>
+-- This is free software; you can redistribute it and/or modify it under
+-- the terms of the GNU General Public License as published by the Free
+-- Software Foundation; only version 2 of the License is applicable.
+drop table metrics cascade;
+drop table hostname_dimension cascade;
+drop table plugin_dimension cascade;
+drop table type_dimension cascade;
+drop type datasource_type cascade;
+
+create type datasource_type as ENUM ('GUAGE', 'COUNTER');
+
+create table metrics (id serial primary key,
+ timestamp timestamp,
+ measure double precision default 0,
+ hostname_id integer not null,
+ plugin_id integer not null,
+ type_id integer not null
+ );
+
+create table hostname_dimension (id serial primary key,
+ hostname varchar(64) not null);
+
+create table plugin_dimension (id serial primary key,
+ plugin varchar(64) not null,
+ plugin_instance varchar(64));
+
+create table type_dimension (id serial primary key,
+ ds_type datasource_type,
+ type varchar(64) not null,
+ type_name varchar(64) not null,
+ type_instance varchar(64));
+
+create or replace function insert_metric(in_timestamp timestamp,
+ in_measure double precision,
+ in_hostname text,
+ in_ds_type datasource_type,
+ in_plugin text,
+ in_plugin_instance text,
+ in_type text,
+ in_type_name text,
+ in_type_instance text) returns void as $$
+ DECLARE
+ host_id integer;
+ a_plugin_id integer;
+ a_type_id integer;
+ BEGIN
+ select into host_id id from hostname_dimension where hostname = in_hostname;
+ IF NOT FOUND THEN
+ insert into hostname_dimension (hostname) values (in_hostname) returning id into host_id;
+ END IF;
+
+ IF in_plugin_instance IS NULL THEN
+ select into a_plugin_id id from plugin_dimension where plugin = in_plugin and plugin_instance is null;
+ ELSE
+ select into a_plugin_id id from plugin_dimension where plugin = in_plugin and plugin_instance = in_plugin_instance;
+ END IF;
+
+ IF NOT FOUND THEN
+ insert into plugin_dimension (plugin, plugin_instance) values (in_plugin, in_plugin_instance) returning id into a_plugin_id;
+ END IF;
+
+ IF in_type_instance IS NULL THEN
+ select into a_type_id id from type_dimension where type = in_type and type_name = in_type_name and type_instance is null;
+ ELSE
+ select into a_type_id id from type_dimension where type = in_type and type_name = in_type_name and type_instance = in_type_instance;
+ END IF;
+
+ IF NOT FOUND THEN
+ insert into type_dimension (type, ds_type, type_name, type_instance) values (in_type, in_ds_type, in_type_name, in_type_instance) returning id into a_type_id;
+ END IF;
+
+ insert into metrics (timestamp, measure, hostname_id, plugin_id, type_id) values (in_timestamp, in_measure, host_id, a_plugin_id, a_type_id);
+ END;
+$$ LANGUAGE plpgsql;
+
+create or replace function get_interval(start_timestamp timestamp, length interval, step text) returns SETOF timestamp as $$
+ DECLARE
+ v_timestamp timestamp;
+ end_timestamp timestamp;
+ BEGIN
+ v_timestamp := start_timestamp;
+ end_timestamp := start_timestamp + length;
+ WHILE v_timestamp <= end_timestamp LOOP
+ RETURN NEXT v_timestamp;
+ v_timestamp := v_timestamp + ('1' || step)::interval;
+ END LOOP;
+ END;
+$$ language plpgsql;
+
+create or replace function create_partition_trigger(parent text,
+ start_timestamp timestamp,
+ length interval,
+ step text,
+ format text) returns void as $trigger$
+ DECLARE
+ v_function text;
+ v_body text;
+ v_current_date date;
+ v_start_date date;
+ v_suffix text;
+ BEGIN
+ v_current_date := date(start_timestamp);
+ v_function := 'CREATE OR REPLACE FUNCTION ' || parent || '_insert_trigger() '
+ || 'RETURNS TRIGGER LANGUAGE plpgsql AS $$ '
+ || 'BEGIN ';
+
+ FOR v_start_date in select * from get_interval(start_timestamp, length, step) LOOP
+ select trim(to_char(v_start_date, format)) into v_suffix;
+ IF v_current_date = v_start_date THEN
+ v_body := ' IF ';
+ ELSE
+ v_body := ' ELSEIF ';
+ END IF;
+ v_body := v_body || ' NEW.timestamp >= ''' || v_start_date || '''::timestamp and '
+ || ' NEW.timestamp < ''' || v_start_date + ( '1' || step)::interval || '''::timestamp THEN '
+ || ' INSERT INTO ' || parent || '_' || v_suffix
+ || ' values (NEW.*); ';
+ v_function := v_function || v_body;
+ END LOOP;
+ v_function := v_function || 'ELSE RETURN NEW; END IF; RETURN NULL; END; $$';
+ EXECUTE v_function;
+ END;
+$trigger$ LANGUAGE plpgsql;
+
+create or replace function create_partition_tables(parent text, start_timestamp timestamp, length interval, step text, format text) returns void as $$
+ DECLARE
+ sql text;
+ v_suffix text;
+ v_start_date date;
+ table_name text;
+ BEGIN
+ FOR v_start_date in select * from get_interval(start_timestamp, length, step) LOOP
+ select trim(to_char(v_start_date, format)) into v_suffix;
+ select parent || '_' || v_suffix into table_name;
+ select 'create table ' || table_name
+ || ' (CHECK (timestamp >= ' || quote_literal(v_start_date)
+ || '::timestamp and timestamp < ' || quote_literal(v_start_date + ( '1' || step)::interval)
+ || '::timestamp)) INHERITS (' || parent || ');'
+ into sql;
+ EXECUTE sql;
+ EXECUTE 'create index index_' || table_name || '_on_timestamp_hostname_and_plugin_and_type on ' || table_name || ' (timestamp, hostname_id, plugin_id, type_id);';
+ END LOOP;
+ END;
+$$ LANGUAGE plpgsql;
+
+-- if you are using postgres 8.4, which introduced window functions
+-- you can use something like this to to create a view on rows that come from
+-- COUNTER plugins. Because the real measurement is the difference of
+-- the last sample to the next sample, use the lag() function to do that math.
+-- This will create a combined view of both COUNTER and GUAGE types.
+--
+-- WARNING - for large datasets THIS WILL BE SLOW!
+
+-- create view metrics_view as
+-- SELECT timestamp,
+-- ((m.measure - lag(m.measure)
+-- over(partition by m.hostname_id,
+-- p.plugin,
+-- p.plugin_instance,
+-- t.type,
+-- t.type_instance
+-- order by timestamp, m.hostname_id, p.plugin, p.plugin_instance, t.type, t.type_instance))) AS metric,
+-- m.hostname_id,
+-- m.plugin_id,
+-- m.type_id
+-- FROM metrics m, plugin_dimension p, type_dimension t
+-- where m.type_id = t.id
+-- and m.plugin_id = p.id
+-- and t.ds_type = 'COUNTER'
+-- UNION
+-- select timestamp, m.measure as metric,
+-- m.hostname_id,
+-- m.plugin_id,
+-- m.type_id
+-- FROM metrics m, type_dimension t
+-- where m.type_id = t.id
+-- and t.ds_type = 'GUAGE';
projects / collectd_dbstore.git / commitdiff