[collectd] [PATCH] postgresql_default.conf, postgresql plugin: Split default queries by category.

[Sebastian Harl]

The default queries have been split as follows:

 | old            | new                                |
 +----------------+------------------------------------+
 | database       | backends, transactions             |
 | user_tables    | queries, query_plans, table_states |
 | io_user_tables | disk_io                            |

This allows for a more fine-grained control over which statistics are to be
collected.

The documentation and default queries definition have been updated to reflect
this change.

Signed-off-by: Sebastian Harl <sh at tokkee.org>
---
 src/collectd.conf.pod       |   38 +++++++++++++++++++++++---------
 src/postgresql.c            |    9 +++++--
 src/postgresql_default.conf |   50 ++++++++++++++++++++++++++++++-------------
 3 files changed, 68 insertions(+), 29 deletions(-)

diff --git a/src/collectd.conf.pod b/src/collectd.conf.pod
index 58ffc78..40f5027 100644
--- a/src/collectd.conf.pod
+++ b/src/collectd.conf.pod
@@ -1087,23 +1087,39 @@ B<Column> options has to match the columns of the query result.
 
 =back
 
-The following predefined queries are available:
+The following predefined queries are available (the definitions can be found
+in the F<postgresql_default.conf> file which, by default, is available at
+C<I<prefix>/share/collectd/>):
 
 =over 4
 
-=item B<database>
+=item B<backends>
 
-This query collects general database statistics, i.E<nbsp>e. the number of
-backends and committed and rolled-back transactions.
+This query collects the number of backends, i.E<nbsp>e. the number of
+connected clients.
 
-=item B<user_tables>
+=item B<transactions>
 
-This query collects user-table usage statistics, i.E<nbsp>e. the numbers of
-various table scans and the numbers of various table modifications.
+This query collects the numbers of committed and rolled-back transactions of
+the user tables.
 
-=item B<io_user_tables>
+=item B<queries>
 
-This query collects block access counts for user-tables.
+This query collects the numbers of various table modifications (i.E<nbsp>e.
+insertions, updates, deletions) of the user tables.
+
+=item B<query_plans>
+
+This query collects the numbers of various table scans and returned tuples of
+the user tables.
+
+=item B<table_states>
+
+This query collects the numbers of live and dead rows in the user tables.
+
+=item B<disk_io>
+
+This query collects disk block access counts for user tables.
 
 =back
 
@@ -1184,8 +1200,8 @@ B<PostgreSQL Documentation> for details.
 
 Specify a I<query> which should be executed for the database connection. This
 may be any of the predefined or user-defined queries. If no such option is
-given, it defaults to "database", "user_tables" and "io_user_tables". Else,
-the specified queries are used only.
+given, it defaults to "backends", "transactions", "queries", "query_plans",
+"table_states", "disk_io". Else, the specified queries are used only.
 
 =back
 
diff --git a/src/postgresql.c b/src/postgresql.c
index 5bf0614..135a827 100644
--- a/src/postgresql.c
+++ b/src/postgresql.c
@@ -123,9 +123,12 @@ typedef struct {
 } c_psql_database_t;
 
 static char *def_queries[] = {
-	"database",
-	"user_tables",
-	"io_user_tables"
+	"backends",
+	"transactions",
+	"queries",
+	"query_plans",
+	"table_states",
+	"disk_io"
 };
 static int def_queries_num = STATIC_ARRAY_SIZE (def_queries);
 
diff --git a/src/postgresql_default.conf b/src/postgresql_default.conf
index d304de5..93a29b6 100644
--- a/src/postgresql_default.conf
+++ b/src/postgresql_default.conf
@@ -1,37 +1,57 @@
 # Pre-defined queries of collectd's postgresql plugin.
 
-<Query database>
-	Query "SELECT numbackends, xact_commit, xact_rollback \
-		FROM pg_stat_database \
+<Query backends>
+	Query "SELECT count(*) \
+		FROM pg_stat_activity \
 		WHERE datname = $1;"
 
 	Param database
 
 	Column pg_numbackends
+</Query>
+
+<Query transactions>
+	Query "SELECT xact_commit, xact_rollback \
+		FROM pg_stat_database \
+		WHERE datname = $1;"
+
+	Param database
+
 	Column pg_xact commit
 	Column pg_xact rollback
 </Query>
 
-<Query user_tables>
-	Query "SELECT sum(seq_scan), sum(seq_tup_read), \
-			sum(idx_scan), sum(idx_tup_fetch), \
-			sum(n_tup_ins), sum(n_tup_upd), sum(n_tup_del), \
-			sum(n_tup_hot_upd), sum(n_live_tup), sum(n_dead_tup) \
-		FROM pg_stat_user_tables"
-
-	Column pg_scan    seq
-	Column pg_scan    seq_tup_read
-	Column pg_scan    idx
-	Column pg_scan    idx_tup_fetch
+<Query queries>
+	Query "SELECT sum(n_tup_ins), sum(n_tup_upd), sum(n_tup_del), \
+			sum(n_tup_hot_upd) \
+		FROM pg_stat_user_tables;"
+
 	Column pg_n_tup_c ins
 	Column pg_n_tup_c upd
 	Column pg_n_tup_c del
 	Column pg_n_tup_c hot_upd
+</Query>
+
+<Query query_plans>
+	Query "SELECT sum(seq_scan), sum(seq_tup_read), \
+			sum(idx_scan), sum(idx_tup_fetch) \
+		FROM pg_stat_user_tables;"
+
+	Column pg_scan seq
+	Column pg_scan seq_tup_read
+	Column pg_scan idx
+	Column pg_scan idx_tup_fetch
+</Query>
+
+<Query table_states>
+	Query "SELECT sum(n_live_tup), sum(n_dead_tup) \
+		FROM pg_stat_user_tables;"
+
 	Column pg_n_tup_g live
 	Column pg_n_tup_g dead
 </Query>
 
-<Query io_user_tables>
+<Query disk_io>
 	Query "SELECT sum(heap_blks_read), sum(heap_blks_hit), \
 			sum(idx_blks_read), sum(idx_blks_hit), \
 			sum(toast_blks_read), sum(toast_blks_hit), \
-- 
1.6.0.rc2

-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 189 bytes
Desc: Digital signature
Url : http://mailman.verplant.org/pipermail/collectd/attachments/20080816/3af653a9/attachment.pgp