Docs » Integrations Guide » Integrations Reference » PostgreSQL

image0 PostgreSQL

Metadata associated with the PostgreSQL collectd plugin can be found here. The relevant code for the plugin can be found here.

DESCRIPTION

Use the PostgreSQL plugin for collectd to send data about a PostgreSQL database to SignalFx.
The PostgreSQL plugin connects to and executes SQL statements on a PostgreSQL database. It then reads back the results and, depending on the configuration, the returned values are then converted into collectd value lists (the data structure used internally to pass statistics around).

Documentation for PostgreSQL can be found at http://www.postgresql.org/docs/manuals/.

FEATURES

Built-in dashboards

  • PostgreSQL Nodes: Overview of data from all PostgreSQL nodes.

image1

  • PostgreSQL Node: Focus on a single PostgreSQL node.

image2

REQUIREMENTS AND DEPENDENCIES

This plugin requires:

Software Version
collectd 4.5+

INSTALLATION

  1. Installing the plugin:

    • On RHEL/CentOS and Amazon Linux systems, run the following command to install this plugin:

      yum install collectd-postgresql
      
    • On Ubuntu and Debian systems, this plugin is included by default with the SignalFx collectd agent.

  2. Download SignalFx’s sample PostgreSQL configuration file to /etc/collectd/managed_config.

*Note:* For versions of PostgreSQL older than 0.92, use thisconfigurationfile instead.

  1. Modify the sample configuration file to provide values that make sense for your environment, as described in Configuration below.
  2. Restart collectd.

CONFIGURATION

Using the example configuration file 10-postgresql.conf or 10-postgresql_pre92.conf as a guide, provide values for the configuration options listed below that make sense for your environment and allow you to connect to the PostgreSQL database to be monitored.

configuration option definition example value
Host Hostname at which collectd can connect to PostgreSQL localhost
User Username that collectd will use to access PostgreSQL collectd_user
Password Password for the user specified in User Password123

No additional configuration is necessary if you are using SignalFx’s example configuration file. However, you may wish to change the name of the database from the default of “postgres” within the starting tag of the <Database> block. This name is what will be reported on the plugin_instance dimension for metrics coming from this database. For complete documentation of configuration options, see collectd’s manpagefor thisplugin.

System modifications

PostgreSQL plugin uses PostgreSQL’s statistics collector which should be enabled by default. The flags are track_activities and track_counts.

USAGE

Sample of built-in dashboard in SignalFx:

image3

METRICS

Below is a list of all metrics.

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

pg_blks.heap_hit

gauge

This metric shows how many read operations were served from the buffer in memory, so that a disk read was not necessary. This only includes hits in the PostgreSQL buffer cache, not the operating system’s file system cache.

pg_blks.heap_read

gauge

This metric shows how many disk blocks have been read.

pg_blks.idx_hit

gauge

This metric shows many index access operations have been served from the buffer in memory.

pg_blks.idx_read

gauge

This metric shows how many disk blocks have been read by index access operations.

pg_blks.tidx_hit

gauge

This metric shows how many index access operations on TOAST tables have been served from the buffer in memory accessing the actual tables.

pg_blks.tidx_read

gauge

This metric shows how many disk blocks have been read by index access operations on TOAST tables.

pg_blks.toast_hit

gauge

This metric shows how many TOAST table reads have been served from buffer in memory.

pg_blks.toast_read

gauge

This metric shows how many disk blocks have been read on TOAST tables.

pg_db_size

gauge

This metric shows the size of the database on disk, in bytes.

pg_n_tup_c.del

gauge

This metric shows how many delete operations have been performed.

pg_n_tup_c.hot_upd

gauge

This metric shows how many update operations have been performed without requiring an index update.

pg_n_tup_c.ins

gauge

This metric shows how many insert operations are performed.

pg_n_tup_c.upd

gauge

This metric shows how many update operations are performed.

pg_n_tup_g.dead

gauge

This metric shows how many dead rows in the database. Rows that are deleted or obsoleted by an update are not physically removed from their table; they remain present as dead rows until a VACUUM is done.

pg_n_tup_g.live

gauge

This metric shows the number of live rows in the database.

pg_numbackends

gauge

This metric shows how many server processes this database is using.

pg_scan.idx

gauge

This metric shows how many index scans have been performed across all the tables.

pg_scan.idx_tup_fetch

gauge

This metric shows how many rows have been read by index scans across all tables.

pg_scan.seq

gauge

This metric shows how many sequential scans have been performed across all the tables. This is generally referred to as table scan. It is usually the least optimized approach for randomly accessing data in the database tables.

pg_scan.seq_tup_read

gauge

This metric shows how many rows are read from sequential scans across all the tables. Sequential scans, sometimes referred to as table scans, are the least optimized approach of accessing the data.

pg_xact.commit

gauge

This metric shows how many commits have been processed by the database. This usually represents the number of transactions performed by the database.

pg_xact.num_deadlocks

gauge

This metric shows how many deadlocks have been detected by the database. Deadlocks are caused by two or more competing actions that are unable to finish because each is waiting for the other’s lock or resources.

pg_xact.rollback

gauge

This metric shows how many rollbacks have happened in the database. This could be caused by transactional failure, errors in statements or data validations.