README

   1 DBStore - A collectd output plugin to store values in an RDBMS.
   2
   3 Most of the SQL here is PostgreSQL specific. It has not been tested
   4 with any other database. Please don't ask me how to make it work with
   5 your database as I don't use your database (unless it is postgres :-)
   6
   7 This has been tested with Posgtres 8.3 and 8.4. I don't pretend to be
   8 a DBA. I'm sure there are things that could be done better.
   9
  10 Dependencies:
  11 1. collectd with perl
  12 2. Postgres (8.4 if you want to interesting things with COUNTERS, see below)
  13 3. Perl DBI
  14 4. Perl DBD driver for postgres
  15
  16 Quick Start Guide
  17
  18 1. Have postgres installed and running
  19 2. createdb <database>
  20 3. psql -U <username> -f <path to here>/sql/metrics.sql
  21 4. psql -U <username> -f <path to here>/sql/create_tables.sql
  22 5. Add the following to your collectd.conf
  23 LoadPlugin perl
  24 <Plugin perl>
  25         IncludeDir "<path to this directory>/perl"
  26         BaseName "Collectd::Plugin"
  27         LoadPlugin DBStore
  28         <Plugin DBStore>
  29            DBIDriver "Pg"
  30            DatabaseHost "<hostname>"
  31            DatabasePort "5432"
  32            DatabaseName "<database name>"
  33            DatabaseUser "<username>"
  34            DatabasePassword "<password>"
  35         </Plugin>
  36 </Plugin>
  37 6. configure postgres to turn on constrain exclusion.
  38
  39 Rationale and Approach
  40
  41 We wanted to collect system stats at full resolution, possibly longer
  42 than the configured RRAs, to go back in time to analyize performance.
  43
  44 After looking at the collectd Wiki about a table structure to store
  45 the data, it occured to me that this could be handled as a
  46 "dimensional model" or "star schema". Basically build a data
  47 warehouse.
  48
  49 Putting the redundant information (hostname, plugin and plugin type)
  50 into their own tables creates a very skinny "fact" table to hold
  51 the measurements. The next problem was data volume.
  52
  53 Postgres supports data partitioning which will allow you to store
  54 metrics data into "child" tables that have been partitioned by some
  55 range of dates/times. Insertion and query time can be inproved for
  56 very large data sets by only deailing with a subset of the data.
  57
  58 Insertions into the "parent" table are redirected to the appropriate
  59 child table. The time-span of a child table can be any duration.
  60
  61 Indices are only kept on child tables and old data can quickly be
  62 removed with a DROP TABLE.
  63
  64 While postgres does support data partitioning, the maintenance of the
  65 required tables and triggers has to be done manually. That's what most
  66 of the included SQL is doing.
  67
  68 Configuration
  69
  70 Depending on volume of data coming from collectd you may need to adjust
  71 the time duration of your child tables.
  72
  73 There are two aspects of data partitioning that need to be created (and maintained):
  74       1. Child tables and indices
  75       2. The insert trigger function
  76
  77 The create_tables.sql file is the entry point for the functions that
  78 will create the tables and trigger functions. There are two functions,
  79 they both take the same arguments:
  80      1. create_partition_tables()
  81      2. create_partition_trigger()
  82
  83 The arguments (and postgres types) to these functions are:
  84     1. The parent table name (text)
  85     2. The start timestamp (timestamp)
  86     3. The length of time in the future to create tables (interval)
  87     4. The "step" for each table (e.g. month, day) (text)
  88     5. The format string for the table suffix. The table name will be
  89        <parent>_<suffix> (e.g. metrics_2009_02) (text)
  90
  91 create_partition_tables() will create the child tables with the
  92 appropriate range checks.
  93
  94 create_partition_trigger() will create the trigger function that will
  95 redirect the insert into the appropriate child table. This function
  96 still needs to be associated with an insert trigger.
  97
  98 The insert trigger function is one giant if/then/else statement. So
  99 you don't want the interval too far in the past, or generate too far
 100 in the future and not update. At some point it will have some impact
 101 on performance. I haven't tested this impact.
 102
 103 Maintenance
 104
 105 Depending on how far into the future you generate tables and the
 106 trigger function, you will need to create new child tables and
 107 regenerate the trigger function. I would suggest putting this into cron
 108 just before you period is about to expire. I'll let you work out the
 109 math as to when to do this.
 110
 111 Should you forget, all rows will be inserted into the parent
 112 table. You won't loose data, but it will hurt performance.
 113
 114 Querying with partitions
 115
 116 To enable the query planner to use the table partitions you need to do
 117 two things:
 118     1. Turn on constrain exclusion:
 119           SET constraint_exclusion = on;
 120        or set it in postgresql.conf
 121     2. Include in the where clause of your queries static timestamps.
 122        e.g. select * from metrics where timestamp between
 123             '2009-01-01'::timestamp and '2009-02-01'::timestamp
 124        functions that return timestamps don't count as 'static'. If in
 125        doubt use EXPLAIN.
 126
 127 Inserting Data
 128
 129 Because of the dimensional model, "fact" inserts need to lookup,
 130 possibly create and attach the dimensions. This is accomplished
 131 through the function insert_metric() whose signature looks like:
 132
 133     insert_metric(in_timestamp timestamp,
 134                   in_measure double precision,
 135                   in_hostname text,
 136                   in_ds_type datasource_type,
 137                   in_plugin text,
 138                   in_plugin_instance text,
 139                   in_type text,
 140                   in_type_name text,
 141                   in_type_instance text) returns void
 142
 143 Where in_timestamp must be something that postgres can convert to a
 144 timestamp.
 145
 146 datasource_type is either 'GUAGE' or 'COUNTER'
 147
 148 Working with COUNTERS
 149
 150 Many of the values collected by collectd are of type COUNTER. Ethernet
 151 interfaces, for example, simply keep a counter of the number of
 152 bytes/packets/octects etc sent. To calculate bytes/second you need to
 153 know the difference in time, and the difference in the counter between
 154 two samples.
 155
 156 Postgres introduced in 8.4 "window" functions which allow you to do
 157 calculations among the rows returned from a query. One of those
 158 functions is lag() which will subtract the value in one row from
 159 another. This is a handy way of working with COUNTERS.
 160
 161 There is an example VIEW definition at the bottom on metrics.sql that
 162 illustrates this use of this feature. Using views and partitioned
 163 tables do not really work well as when the view is constructed it
 164 will query the entire table without the needed WHERE clauses
 165 illustrated above. This will be slow.
 166
 167 Patches and suggestions welcome.
 168
 169 Bob Cotton
 170 bob.cotton@gmail.com
 171
 172 Further Reading
 173 http://www.postgresql.org/docs/8.3/interactive/ddl-partitioning.html
 174 http://www.slideshare.net/xzilla/postgresql-partitioning-pgcon-2007-presentation
 175