Stackstate-PostgreSQL Integration

Overview

PostgreSQL Graph Connect PostgreSQL to Stackstate in order to:

  • Visualize your database performance.
  • Correlate the performance of PostgreSQL with the rest of your applications.

Installation

To get started with the PostgreSQL integration, create at least a read-only stackstate user with proper access to your PostgreSQL Server. Start psql on your PostgreSQL database and run:

create user stackstate with password '<PASSWORD>';
grant SELECT ON pg_stat_database to stackstate;

To verify the correct permissions you can run the following command:

psql -h localhost -U stackstate postgres -c \
"select * from pg_stat_database LIMIT(1);"
&& echo -e "\e[0;32mPostgres connection - OK\e[0m" || \
|| echo -e "\e[0;31mCannot connect to Postgres\e[0m"

When it prompts for a password, enter the one used in the first command.

Configuration

  1. Configure the Agent to connect to PostgreSQL. Edit conf.d/postgres.yaml:

    init_config:
    
    instances:
      - host: localhost
        port: 5432
    
  2. Restart the agent.

Configuration Options

  • username (Optional)
  • password (Optional)
  • dbname (Optional) - Name of the database you want to monitor
  • ssl (Optional) - Defaults to False. Whether to use SSL to connect.
  • tags (Optional) - Dictionary of tags
  • relations (Optional) - Dictionary of per-relation (table) metrics that you want to track. Each relation generates 10 metrics + 10 metrics per index.

    By default all schemas are included. To track relations from specific schemas only, use the following syntax:

    relations:
      - relation_name: another_table
        schemas:
          - public
          - prod
    
  • collect_function_metrics (Optional) - Collect metrics regarding PL/pgSQL functions from pg_stat_user_functions
  • collect_count_metrics (Optional) - Collect count metrics, default value is True for backward compatibility but they migth be slow, suggested value is False.

Custom metrics

Below are some examples of commonly used metrics, which are implemented as custom metrics. Uncomment them if you want to use them as is, or use as an example for creating your own custom metrics.

The format for describing custome metrics is identical with the one used for common metrics in postgres.py. Be extra careful with ensuring proper custom metrics description format. If your custom metric does not work after an agent restart, look for errors in the output of /etc/init.d/stackstate-agent info command, as well as /var/log/stackstate/collector.log file.

custom_metrics:
- # Londiste 3 replication lag
  descriptors:
    - [consumer_name, consumer_name]
  metrics:
    >
      GREATEST(0, EXTRACT(EPOCH FROM lag)) as lag:
      [postgresql.londiste_lag, GAUGE]
    >
      GREATEST(0, EXTRACT(EPOCH FROM lag)) as last_seen:
      [postgresql.londiste_last_seen, GAUGE]
    pending_events: [postgresql.londiste_pending_events, GAUGE]
  query:
    >
      SELECT consumer_name, %s from pgq.get_consumer_info()
      where consumer_name !~ 'watermark$';
  relation: false
collect_function_metrics: False
collect_count_metrics: False

For more details about configuring this integration refer to the following file(s) on GitHub:

Validation

After you restart the agent, you should be able to run the info command which will now include a section like this if the PostgreSQL integration is working:

Checks
======

  [...]

  postgres
  --------
      - instance #0 [OK]
      - Collected 47 metrics & 0 events

Metrics

postgresql.connections
(gauge)
The number of active connections to this database.
shown as connection
postgresql.commits
(gauge)
The number of transactions that have been committed in this database.
shown as transaction/second
postgresql.rollbacks
(gauge)
The number of transactions that have been rolled back in this database.
shown as transaction/second
postgresql.disk_read
(gauge)
The number of disk blocks read in this database.
shown as block/second
postgresql.buffer_hit
(gauge)
The number of times disk blocks were found in the buffer cache, preventing the need to read from the database.
shown as hit/second
postgresql.rows_returned
(gauge)
The number of rows returned by queries in this database
shown as row/second
postgresql.rows_fetched
(gauge)
The number of rows fetched by queries in this database
shown as row/second
postgresql.rows_inserted
(gauge)
The number of rows inserted by queries in this database
shown as row/second
postgresql.rows_updated
(gauge)
The number of rows updated by queries in this database
shown as row/second
postgresql.rows_deleted
(gauge)
The number of rows deleted by queries in this database
shown as row/second
postgresql.database_size
(gauge)
The disk space used by this database.
shown as byte
postgresql.deadlocks
(gauge)
The number of deadlocks detected in this database
postgresql.temp_bytes
(gauge)
The amount of data written to temporary files by queries in this database.
shown as byte/second
postgresql.temp_files
(gauge)
The number of temporary files created by queries in this database.
shown as file/second
postgresql.bgwriter.checkpoints_timed
(count)
The number of scheduled checkpoints that were performed.
postgresql.bgwriter.checkpoints_requested
(count)
The number of requested checkpoints that were performed.
postgresql.bgwriter.buffers_checkpoint
(count)
The number of buffers written during checkpoints.
postgresql.bgwriter.buffers_clean
(count)
The number of buffers written by the background writer.
postgresql.bgwriter.maxwritten_clean
(count)
The number of times the background writer stopped a cleaning scan due to writing too many buffers.
postgresql.bgwriter.buffers_backend
(count)
The number of buffers written directly by a backend.
shown as buffer
postgresql.bgwriter.buffers_alloc
(count)
The number of buffers allocated
postgresql.bgwriter.buffers_backend_fsync
(count)
The of times a backend had to execute its own fsync call instead of the background writer.
postgresql.bgwriter.write_time
(count)
The total amount of checkpoint processing time spent writing files to disk.
shown as millisecond
postgresql.bgwriter.sync_time
(count)
The total amount of checkpoint processing time spent synchronizing files to disk.
shown as millisecond
postgresql.locks
(gauge)
The number of locks active for this database.
shown as lock
postgresql.seq_scans
(gauge)
The number of sequential scans initiated on this table.
postgresql.seq_rows_read
(gauge)
The number of live rows fetched by sequential scans.
shown as row/second
postgresql.index_scans
(gauge)
The number of index scans initiated on this table.
postgresql.index_rows_fetched
(gauge)
The number of live rows fetched by index scans.
shown as row/second
postgresql.rows_hot_updated
(gauge)
The number of rows HOT updated, meaning no separate index update was needed.
shown as row/second
postgresql.live_rows
(gauge)
The estimated number of live rows.
shown as row
postgresql.dead_rows
(gauge)
The estimated number of dead rows.
shown as row
postgresql.index_rows_read
(gauge)
The number of index entries returned by scans on this index.
shown as row/second
postgresql.table_size
(gauge)
The total disk space used by the specified table. Includes TOAST, free space map, and visibility map. Excludes indexes.
shown as byte
postgresql.index_size
(gauge)
The total disk space used by indexes attached to the specified table.
shown as byte
postgresql.total_size
(gauge)
The total disk space used by the table, including indexes and TOAST data.
shown as byte
postgresql.table.count
(gauge)
The number of user tables in this database.
shown as table
postgresql.max_connections
(gauge)
The maximum number of client connections allowed to this database.
shown as connection
postgresql.percent_usage_connections
(gauge)
The number of connections to this database as a fraction of the maximum number of allowed connections.
shown as fraction
postgresql.heap_blocks_read
(gauge)
The number of disk blocks read from this table.
shown as block/second
postgresql.heap_blocks_hit
(gauge)
The number of buffer hits in this table.
shown as hit/second
postgresql.index_blocks_read
(gauge)
The number of disk blocks read from all indexes on this table.
shown as block/second
postgresql.index_blocks_hit
(gauge)
The number of buffer hits in all indexes on this table.
shown as hit/second
postgresql.toast_blocks_read
(gauge)
The number of disk blocks read from this table's TOAST table.
shown as block/second
postgresql.toast_blocks_hit
(gauge)
The number of buffer hits in this table's TOAST table.
shown as hit/second
postgresql.toast_index_blocks_read
(gauge)
The number of disk blocks read from this table's TOAST table index.
shown as block/second
postgresql.toast_index_blocks_hit
(gauge)
The number of buffer hits in this table's TOAST table index.
shown as block/second