PostgreSQL monitoring plugin

Verax NMS PostgreSQL management plugin enables easy monitoring, alerting, health check, management and performance reporting for PostgreSQL servers and databases.

Compatibility

Verax NMS: PostgreSQL database management (tables diagnostics view)

Verax NMS plugin supports multiple instances of PostgreSQL 8.1 and higher running in 32 and 64 bit environments. The communication between Verax NMS and PostgreSQL server(s) takes place via SQL queries (no management agent is required on PostgreSQL server).

General information view

The view presents general server and database information such as:

  • Server information: version, compiler and operating system
  • General configuration:
    • Configuration file path
    • TCP listen port for incoming connections
    • Archive mode status
    • Autovacuum mode status
  • Memory and disk usage statistics:
    • Total database size on disk
    • Block size
    • Working memory
    • Cache size
    • Shared and temporary buffers
    • Maintenance operations memory

Database settings view

The view displays a list of all current server and database configuration parameters.

Table view

The view displays a list of all database tables including name, size, average row length and expected vs. real pages used. It also provides a graphical presentation of the most fragmented tables which require optimization.

Process view

The view displays a list of processes currently accessing the database providing the following information:

  • Process id (PID)
  • Connecting user name, port, IP address and application name
  • Last transaction start time
  • Last SQL query start time
  • Current SQL query text

Vacuum view

PostgreSQL Vacuum is a background daemon for reclaiming unused storage (occupied by deleted records). The Vacuum view provides the following information, essential for optimizing database performance:

  • Auto-vacuum enable status and threshold
  • Delay time
  • Number of processes
  • Cost limit (on I/O operations used for vacuum in a single run), cost delay (number of seconds to suspend vacuum for, when cost limit is reached) and memory used by the vacuum daemon

Memory view

The view provides comprehensive statistics on database memory including:

  • Disk block size
  • Effective cache size
  • Shared buffer size
  • Temporary buffers size
  • Working memory size
  • Maintenance operations memory size
  • Number of checkpoint segments
  • WAL (Write-Ahead Logging) update method
  • WAL buffers size
  • Stack depth per process
  • A list of preloaded shared libraries
  • Maximum number of connections allowed
  • Maximum number of files that can be opened per connecting process

Performance view

Provides statistics for identifying database performance bottlenecks including:

  • Top processes by run time
  • Top objects by size
  • Top objects by rows read
  • Top objects by physical reads
  • Top objects by number of buffer gets

Transaction summary

Provides a comprehensive summary of top transactions for each database on the server including:

  • Buffer hit rate
  • Number of committed transactions
  • Number of transactions rolled-backs
  • Total number of rows inserted since database creation
  • Total number of rows updated since database creation
  • Total number of rows deleted since database creation

Diagnostics

The diagnostics report allows troubleshooting database performance. The report presents the following information:

  • Tables that are missing primary keys
  • Unanalyzed tables
  • Vacuumed tables (i.e. ones that are being cleaned up at the moment)
  • Tables with large number of sequence scans without indexes, along with problem severity classification (from minor to extreme)

Predefined PostgreSQL monitoring templates

The plugin provides predefined templates for most commonly monitored PostgreSQL items (listed in the table below). Other, user-defined sensors and performance counters can be added.

PostgreSQL monitoring templates
PostgreSQL Database tables number
PostgreSQL Number of commits
PostgreSQL Logged users number
PostgreSQL Number of rollbacks
PostgreSQL Blocks read
PostgreSQL Disk block fetch requests found in cache number
PostgreSQL Number of tuples updated
PostgreSQL Number of tuples deleted
PostgreSQL Buffer hits rate
PostgreSQL Active server processes number
PostgreSQL Disk block fetch requests number
PostgreSQL Number of tuples inserted

See also

Oracle RDBMS management plugin »

Microsoft SQL Server management plugin »

IBM DB2 management plugin »

MySQL management plugin »