collectd/collectd — pull request #104 by mwongatemma (2012-07-13 15:23:17+00:00)
Title: annotate postgresql plugins values

Annotate the value_list_t structure with information such that it can
stored postgresql database name, schema name, table name, and index name
as meta data. Store these pieces of information separately in addition
to appending them together for the plugin_instance value in order to
keep the data distinct.

For database stats:

SELECT datname, numbackends
FROM pg_stat_database

This query lets us get stats for all databases in a single query. In
the tag the following parameter needs to be set to annotate
the data with which database the data is from:

DatabasenameColumn 0

For table stats:

SELECT schemaname, relname, seq_scan
FROM pg_stat_all_tables

To keep the data distinct, the following parameters need to be set for
each value selected in the tag, resulting in a reversed order
naming scheme in type_instance:

InstancePrefix "seq_scan"
InstancesFrom "relname" "schemaname"

In the tag the following parameters need be set to annotate
the data with the schema and table information:

SchemanameColumn 0
TablenameColumn 1

For index stats:

SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_all_indexes

To keep the data distinct, the following parameters need to be set for
each value selected in the tag, resulting in a reversed order
naming scheme in type_instance:

InstancePrefix "idx_scan"
InstancesFrom "indexrelname" "relname" "schemaname"

In the tag the following parameters need be set to annotate
the data with the schema, table, and index information:

SchemanameColumn 0
TablenameColumn 1
IndexnameColumn 2

Note there is no need to select the database name when querying for
table and index stats because these stats can only be queried per
database.

In order to accomplish this several functions had to be pulled from the
utils_db_query.* files because of additional information that needs to
be passed through to annotate that values.

Using InstancesFrom is still required to ensure the instances are
distinct but the '-' separated value can still be ambiguous as database,
schema, table, and index names may contain '-'s.