Protocol Integration

PostgreSQL protocol metrics related to requests and response are available out-of-the-box within the Netsil AOC. The user can either look at pre-canned dashboards for PostgreSQL or use the Analytics Sandbox to plot charts without any additional configuration.

Note: In order to see additional infrastructure metrics the user can follow the steps listed under the Configuration section below (Optional).

Attributes

Below attributes can be used in filters and group by operations in analytics within alerts, dashboards, etc.

API Usage Name UI Display Name Description Value / Example
postgresql.user User User name requesting the queries
postgresql.db Database Name Name of database accessed in queries
postgresql.application_name Application Name Name of the application that is connected to the PostgreSQL server
postgresql.server_version Server Version PgSQL server version number 9.6.1
postgresql.client_message Client Message Client message type indicating the state of connection full details of message flow Startup, PasswordMessage, Query, Terminate
postgresql.server_message Server Message Server message type indicating the state of connection full details of server message AuthenticationMD5Password, AuthenticationOk, ReadyForQuery, BackendKeyData, ErrorResponse, NoticeResponse, ParameterStatus
postgresql.error.severity Server Error Severity The severity field in error response full details of error message ERROR, FATAL, PANIC
postgresql.error.string Error String Error message including No Error database pgbench does not exist
postgresql.query Query String Query statements captured as strings create table pgbench_accounts(aid int)
There are some attributes that are common across the integrations. The description to the relevant attributes for this integration can be found here: shared attributes

Protocol Datasources

Datasource Available Aggregations Unit Description
postgresql.request_response.latency sum avg min max std_dev millisecond The aggregated latency across all PostgreSQL request/response.
postgresql.request_response.throughput throughput /s The total number of PostgreSQL request/response per second.
postgresql.request_response.count count The total number of PostgreSQL request/response.
postgresql.request_response.latency.p5 avg min max millisecond 5th percentile latency i.e., 5% of PostgreSQL request/response is faster than the given latency.
postgresql.request_response.latency.p50 avg min max millisecond 50th percentile latency i.e., 50% of PostgreSQL request/response is faster than the given latency.
postgresql.request_response.latency.p95 avg min max millisecond 95th percentile latency i.e., 95% of PostgreSQL request/response is faster than the given latency.
postgresql.request.bytes sum avg min max byte The aggregated number of bytes across all PostgreSQL requests.
postgresql.request.bytes.rate rate byte/s The rate of bytes transferred across all PostgreSQL requests.
postgresql.request.count count The total number of PostgreSQL requests.
postgresql.request.packets sum avg min max packet The aggregated number of packets across all PostgreSQL requests.
postgresql.request.packets.rate rate packet/s The rate of packets transferred across all PostgreSQL requests.
postgresql.request.throughput throughput /s The rate of total number of PostgreSQL requests.
postgresql.response.bytes sum avg min max byte The aggregated number of bytes across all PostgreSQL responses.
postgresql.response.bytes.rate rate byte/s The rate of bytes transferred across all PostgreSQL responses.
postgresql.response.count count The total number of PostgreSQL responses.
postgresql.response.packets sum avg min max packet The aggregated number of packets across all PostgreSQL responses.
postgresql.response.packets.rate rate packet/s The rate of packets transferred across all PostgreSQL responses.
postgresql.response.throughput throughput /s The rate of total number of PostgreSQL responses.

Infrastructure Integration

Configuration:

Note: Netsil AOC can provide stats for PostgreSQL queries via protocol parsing without any configuration. The following steps are required to poll for stats reported by the PostgreSQL server itself.

  1. Create a read-only Netsil user with proper access to your PostgreSQL Server. Start psql on your PostgreSQL database and run:

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

    Verify the privilege grant above was successful:

    psql -h localhost -U netsil 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"</pre>
    
  2. Configure the Agent to connect to PostgreSQL by editing /etc/netsil-dd-agent/conf.d/postgres.yaml.

    init_config:
    instances:
      - host: <localhost | PostgreSQL Server Host>
        port: <5432 | PostgreSQL Server Port>
        username: netsil
        password: <PASSWORD>
    
  3. Restart the Agent

    /etc/init.d/netsil-collectors restart
    
  4. Execute the info command

    /etc/init.d/netsil-collectors info
    

    The output of the command should contain a section similar to the following:

    Checks
    ======
    
      [...]
      postgres
      -----
          - instance #0 [OK]
          - Collected 8 metrics & 0 events
    

Infrastructure Datasources

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