From b2c2f2af614515f2c5fec3dfad91e84e595bdd71 Mon Sep 17 00:00:00 2001 From: Bob Cotton Date: Sat, 18 Apr 2009 07:02:47 -0600 Subject: [PATCH] first commit --- README | 174 +++++++++++++++++++++++++++++++++++++++ perl/Collectd/Plugin/DBStore.pm | 143 ++++++++++++++++++++++++++++++++ sql/create_tables.sql | 3 + sql/metrics.sql | 178 ++++++++++++++++++++++++++++++++++++++++ 4 files changed, 498 insertions(+) create mode 100644 README create mode 100644 perl/Collectd/Plugin/DBStore.pm create mode 100644 sql/create_tables.sql create mode 100644 sql/metrics.sql diff --git a/README b/README new file mode 100644 index 0000000..1f0dd97 --- /dev/null +++ b/README @@ -0,0 +1,174 @@ +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 +3. psql -U -f /sql/metrics.sql +4. psql -U -f /sql/create_tables.sql +5. Add the following to your collectd.conf + + IncludeDir "" + BaseName "Collectd::Plugin" + LoadPlugin "DBStore" + + DBIDriver "Pg" + DatabaseHost "" + DatabasePort "5432" + DatabaseName "" + DatabaseUser "" + DatabasePassword "" + + +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 + _ (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 + diff --git a/perl/Collectd/Plugin/DBStore.pm b/perl/Collectd/Plugin/DBStore.pm new file mode 100644 index 0000000..48a487e --- /dev/null +++ b/perl/Collectd/Plugin/DBStore.pm @@ -0,0 +1,143 @@ +# A Perl/DBI collectd plugin for recording samples into a relational +# datanase. Currently only Postgres has been tested. +# +# Written by Bob Cotton +# +# 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: +# +# +# IncludeDir "" +# LoadPlugin DBStore +# +# DBIDriver "Pg" +# DatabaseHost "dbhost" +# DatabasePort "5432" +# DatabaseName "metrics_database" +# DatabaseUser "metrics" +# DatabasePassword "secret" +# +# +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; diff --git a/sql/create_tables.sql b/sql/create_tables.sql new file mode 100644 index 0000000..624ea9c --- /dev/null +++ b/sql/create_tables.sql @@ -0,0 +1,3 @@ +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(); diff --git a/sql/metrics.sql b/sql/metrics.sql new file mode 100644 index 0000000..c43ce87 --- /dev/null +++ b/sql/metrics.sql @@ -0,0 +1,178 @@ +-- Written by Bob Cotton +-- 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'; -- 2.11.0