Skip to content

Latest commit

 

History

History
182 lines (142 loc) · 8.4 KB

collectd-postgresql.md

File metadata and controls

182 lines (142 loc) · 8.4 KB
Raw

collectd/postgresql

Monitor Type: collectd/postgresql (Source)

Accepts Endpoints: Yes

Multiple Instances Allowed: Yes

Overview

This monitor is deprecated in favor of the postgresql monitor.

Monitors a PostgreSQL database server using collectd's PostgreSQL plugin.

You have to specify each database you want to monitor individually under the databases key. If you have a common authentication to all databases being monitored, you can specify that in the top-level username/password options, otherwise they can be specified at the database level.

Sample YAML configuration:

monitors:
- type: collectd/postgresql
  host: 127.0.0.1
  port: 5432
  username: "username1"
  password: "password1"
  databases:
  - name: "testdb"
    username: "test_user"
    password: "test_pwd"

Sample YAML configuration with custom query:

monitors:
- type: collectd/postgresql
  host: 127.0.0.1
  port: 5432
  username: "username1"
  password: "password1"
  queries:
  - name: "exampleQuery"
    params:
    - "hostname"
    statement: "Select * From test Where host = $1;"
    results:
    - type: "gauge"
      valuesFrom:
      - "test"
      instancePrefix: "test"
 databases:
 - name: "test"
   username: "username2"
   password: "password2"
   queries:
   - "exampleQuery"

metricsToInclude:
   - metricNames:
     - gauge.test
     monitorType: collectd/postgresql

Note that the metric names for the additional metrics picked up from the queries provided depend on the type, instancePrefix and/or instancesFrom parameters being passed in. See PostgreSQL plugin for details.

Configuration

To activate this monitor in the Smart Agent, add the following to your agent config:

monitors:  # All monitor config goes under this key
 - type: collectd/postgresql
   ...  # Additional config

For a list of monitor options that are common to all monitors, see Common Configuration.

Config option Required Type Description
host yes string
port yes integer
databases yes list of objects (see below) A list of databases along with optional authentication credentials.
queries no list of objects (see below) PostgreSQL queries and metric mappings
username no string A username that serves as a default for all databases if not overridden
password no string A password that serves as a default for all databases if not overridden
reportHost no bool A SignalFx extension to the plugin that allows us to disable the normal behavior of the PostgreSQL collectd plugin where the host dimension is set to the hostname of the PostgreSQL database server. When false (the recommended and default setting), the globally configured hostname config is used instead. (default: false)

The nested databases config object has the following fields:

Config option Required Type Description
name yes string The name of the database
username no string Username used to access the database
password no string Password used to access the database
interval no integer Interval to query the database in seconds (default: 0)
expireDelay no integer Skip expired values in query output (default: 0)
sslMode no string Specify whether to use an ssl connection with PostgreSQL. (prefer(default), disable, allow, require)
krbSrvName no string Specify the Kerberos service name used to authenticate with kerberos 5 or GSSAPI
queries no list of strings Queries used to generate metrics. These will override the default set. If no queries are specified, the default set will be used [custom_deadlocks, backends, transactions, queries, queries_by_table, query_plans, table_states, query_plans_by_table, table_states_by_table, disk_io, disk_io_by_table, disk_usage]

The nested queries config object has the following fields:

Config option Required Type Description
name yes string Name used to refer to the query in the database block
statement yes string Statement is a SQL statement to execute
results yes list of objects (see below) Result blocks that define mappings of SQL query results to metrics
params no list of strings Parameters used to fill in $1,$2,$... tokens in the SQL statement. Acceptable values are hostname, database, instance, username, interval
pluginInstanceFrom no string Specifies the column that should be used to populate plugin instance
minVersion no integer The minimum version of PostgreSQL that the query is compatible with. The version must be specified as a two decimal digit. Ex. 7.2.3 -> 70203 (default: 0)
maxVersion no integer The maximum version of PostgreSQL that the query is compatible with. The version must be specified as a two decimal digit. Ex. 7.2.3 -> 70203 (default: 0)

The nested results config object has the following fields:

Config option Required Type Description
type yes string Type defines a metric type
valuesFrom yes list of strings Specifies columns in the SQL result to use as the metric value. The number of columns must match the expected number of values for the metric type.
instancePrefix no string A prefix for the type instance
instancesFrom no list of strings Specifies columns in the SQL result to uses for the type instance. Multiple columns are joined with a hyphen "-".

Metrics

These are the metrics available for this monitor. Metrics that are categorized as container/host (default) are in bold and italics in the list below.

  • pg_blks.heap_hit (gauge)
    Number of buffer hits
  • pg_blks.heap_read (gauge)
    Number of disk blocks read
  • pg_blks.idx_hit (gauge)
    Number of index buffer hits
  • pg_blks.idx_read (gauge)
    Number of index blocks read
  • pg_blks.tidx_hit (gauge)
    Number of TOAST index buffer hits
  • pg_blks.tidx_read (gauge)
    Number of TOAST index blocks read
  • pg_blks.toast_hit (gauge)
    Number of TOAST buffer hits
  • pg_blks.toast_read (gauge)
    Number of disk blocks read
  • pg_db_size (gauge)
    Size of the database on disk, in bytes
  • pg_n_tup_c.del (gauge)
    Number of delete operations
  • pg_n_tup_c.hot_upd (gauge)
    Number of update operations not requiring index update
  • pg_n_tup_c.ins (gauge)
    Number of insert operations
  • pg_n_tup_c.upd (gauge)
    Number of update operations
  • pg_n_tup_g.dead (gauge)
    Number of dead rows in the database
  • pg_n_tup_g.live (gauge)
    Number of live rows in the database
  • pg_numbackends (gauge)
    Number of server processes
  • pg_scan.idx (gauge)
    Number of index scans
  • pg_scan.idx_tup_fetch (gauge)
    Number of rows read from index scans
  • pg_scan.seq (gauge)
    Number of sequential scans
  • pg_scan.seq_tup_read (gauge)
    Number of rows read from sequential scans
  • pg_xact.commit (gauge)
    Number of commits
  • pg_xact.num_deadlocks (gauge)
    Number of deadlocks detected by the database
  • pg_xact.rollback (gauge)
    Number of rollbacks

Non-default metrics (version 4.7.0+)

To emit metrics that are not default, you can add those metrics in the generic monitor-level extraMetrics config option. Metrics that are derived from specific configuration options that do not appear in the above list of metrics do not need to be added to extraMetrics.

To see a list of metrics that will be emitted you can run agent-status monitors after configuring this monitor in a running agent instance.