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