Postgres Monitoring

Postgres or PostgreSQL is a relational database management system (RDBMS) emphasizing extensibility and technical standards compliance. It is designed to handle a range of workloads, from single machines to data warehouses or Web services with many concurrent users.

Cavisson enables you to keep an eye on the performance of your database servers. Using the PostgreSQL Monitoring tool, you can optimize the performance of your applications and ensure the availability of your SQL servers. It provides an end-to-end monitoring of PostgreSQL queries with insights into various aspects, such as SQL Activity, Temp files, and Server stats.

Overview

This document provides the capability for profiling Postgres. Cavisson machine agent establishes a connection with Postgres database server. After establishing the connection with database, monitor executes some queries to capture the required stats to get the health status of Postgres. To make connection with Postgres, you need to provide connectivity credential to the monitor program, so that it can make connection to the database. It also provides end-to-end monitoring of Postgres queries with insights into SQL Activity.

Benefits

Implementing effective Postgres monitoring offers the following benefits:

  • Improved application availability
  • Increased database performance
  • Quick detection of database outages, failures, and table corruption
  • Prognostic analysis of storage requirements and index performance

Features

  • Provides Query Execution Statistics and Query Plan
  • Provides all information on sessions created in the database
  • Provides information about any type of Locks created in the database.
  • Provides ability to view details of Temporary Files
  • Provides ability to check server configuration parameters and connection statistics.

Prerequisites

  1. For viewing PostgreSQL Execution Stats, Postgres server version should be 5 or later.
  2. Below steps need to be performed for PostgreSQL Execution Stats screen:
    • Add the below keywords in postgresql.conf

To enable pg_stat_statements (Queries)

pg_stat_statements.max = 10000
pg_stat_statements.track = all
track_activity_query_size = 2048

For Execution Plan

auto_explain.log_min_duration = 0
auto_explain.log_format = ‘XML’
auto_explain.log_nested_statements = ON — multiple statement
shared_preload_libraries = ‘pg_stat_statements, auto_explain’

    • Connect the database with super user. Make sure to enable pg_stat_statements extension on the databases which agent will connect.
      Execute below command after connecting to the database:

Create extension pg_stat_statements;

    • Restart the PostgreSQL server.

Monitoring Configuration

  1. Login to a machine and go to Configuration > Monitors.

2. Navigate to Postgres Monitoring section and expand it.

3. This lists all the Postgres Monitors. Click the Configure Monitor icon for any of the monitors.

Note:

  • Here we are providing configuration steps for PostgreSQLBGWriterStats. Similar configuration steps can be applied for other PostgreSQL Monitors.
  • In case of PostgreSQLAccesslog monitor configuration, you also need to provide the file path which is the absolute Postgres log file path or pattern.

4. This displays the Monitor Configuration window.

5. Provide the following details:

    • Server Name / Tier Name: Name of the server / tier on which the Postgres monitoring is to be applied.
    • Exclude Server / Tier
      • From the drop down list, select a tier(s) / server(s) that to exclude.
      • You can type a pattern as a regular expression to exclude a tier / server.
      • You can type multiple regular expressions separated by comma.
    • User Name and Password: Accessible username and password.
    • Advanced Settings
      • Host Name: Server IP or server name where PostgreSQL DB server is installed.
      • Port: PostgreSQL service running port.
      • DB Name: Database name as Postgres.

Once all the configurations are done and monitors are applied, you can see the monitor details and stats on the Monitor UI and Dashboard.

Monitoring UI

To access the Postgres Monitoring UI, login to NetDiagnostics and go to View > DB Monitoring > Postgres Monitoring.

This displays the Postgres Monitoring UI.

Note:

  • Each tab is further categorized into various sections. Details of each tab and its corresponding sections are provided in the subsequent topics.
  • You can perform some operations within tabs / sections. Details are provided within the “Operations to View Data” section.

Operations to View Data

You can perform the below operations to customize the viewing of data, such as:

  • Enable / Disable Aggregate Mode
  • Enable / Disable Analysis Mode
  • Selection of Database server
  • Configuration for viewing Real Time data samples
  • Configure Preset option for data samples
  • Rewind data samples to view historical data based on last sample time
  • Forward data samples which were rewind to view historical data
  • Filter or Search for records at column level
  • Show / Hide Columns based on the relevancy or requirements
  • Changing Chart types for representation of data differently
  • Download the records in CSV format for reference or analysis

Enable / Disable Aggregate Mode

  • It is represented with icon.
  • It is present in DB Query Stats and Session Stats tabs only.
  • If Enabled, the data displayed in the screens are the aggregated data of given time interval.
  • If Disabled, the cumulative data is displayed.

Enable / Disable Analysis Mode

  • It is represented with icon.
  • It is present in all screens and by default, it is Disabled.
  • If Enabled, the data will not be updated as we are analyzing data of a particular time interval.
  • If Disabled, the next data sample will update.

Selection of Database server

You can select which database is to monitor. To do this, click the icon. A drop-down list is displayed with the database names. Select the required database for monitoring.

Viewing Real Time Data / Sample Data

You can view either real time data or offline data. Real time data can be viewed for online tests only. To view real time data, click the Switch to Real Time icon at the top-right corner of the window. This prompts the time (in seconds) for the interval. Select the interval, such as 15 sec, 30 sec, or 45 sec for viewing the data. Default interval is 30 sec. You can change the interval from 30 sec to either 15 sec or 45 sec by clicking .

The data is displayed based upon the interval selection. Post that, there are further two options:

  • Pause the real time data for a certain duration by clicking the icon.
  • Move out from the real time viewing of data by clicking the icon. 

Preset

It is the time for which the data is to be displayed for monitoring. For example – Last 10 minutes, last 30 minutes, last 1 hour, and so on.

On selecting the Custom option, specify the Start date / time and End date / time.

View Last Sample Time

You can view the last sample time on the header of each table and graphical representation. This sample time is based on configured progress interval.

Rewind

This feature is used to view the data of last samples. You can view the historical data based on the last samples by clicking the Rewind  icon.

Upon clicking this icon, data of previous sample is displayed and last sample time can be seen on the table header. For example: If the current data sample is from 07/30/18 11:50:14 To 07/30/18 11:51:14, after clicking the Rewind icon, the data sample time becomes 07/30/18 11:49:14 To 07/30/18 11:50:14 and data is displayed accordingly. You can further click the Rewind icon to view the corresponding historical data. Each click displays the previous data based on sample interval.

To resume with the current time, click the  icon.

Forward

You can view the next to previous data (which was accessed by clicking the rewind icon) by clicking the  icon.

Upon clicking this icon, data of next sample is displayed and sample time can be seen on the table header. For example: If the current data sample is from 07/30/18 11:49:14 To 07/30/18 11:50:14 after clicking the Forward icon, the time for sample data becomes 07/30/18 11:50:14 To 07/30/18 11:51:14 and data is displayed accordingly. You can further click the Forward icon to view the corresponding historical data. Each click displays the next data based on sample interval.

To resume with the current time, click the  icon.

Filter

You can filter the data or search for specific records based on the value provide in the text boxes that are displayed upon clicking the  icon.

For example: To view the records with status as ‘Active’, specify ‘Active’ within the ‘Status’ column. This will filter out the records containing status as ‘Active’ and display accordingly.

Changing Chart Types

You can also change the chart type by clicking the  icon. There are various chart types available, such as Line, Pie, Stack Area, Bar, and Area.

Download as CSV

You can also download the records in CSV format. To do this, click the  button at the top-right corner of the window. This download the records in CSV format.

These were some operations for viewing data in customized way and downloading the records in CSV format. In the subsequent topics, we are providing how we can analyze the query by monitoring various statistics.

SQL Activity

The SQL Activity tab provides details of DB query for monitoring, such as DB Query Stats.  It includes:

  • DB Query details
  • Locks details
  • Sessions details

DB Query Details

In the DB Query Stats section, you can view the details of DB query including the SQL statement and execution plan. If you hover your mouse at a query, the complete query is displayed.

To analyze the DB details:

  1. Click the DB Query Stats
  2. The DB Query Stats window is displayed that contains one row per active lockable object, requested lock mode, and relevant transaction.

3. Click a DB Name tab to view the complete details of the query (with all columns).

You can identify the slow DB queries and other details from the DB Query section. This section displays the DB query information, such as count, query, and SQL handle. The following table provides information the DB query fields.

Column Name

Description

User Name

User who executed the statement

Query

Text of a representative statement

Count

Number of times executed

Total Time (hh:mm:ss:ms)

Total time spent in the statement, (in hh:mm:ss:ms)

Rows

Total number of rows retrieved or affected by the statement

Shared Blocks Hit

Total number of shared block cache hits by the statement

Shared Blocks Read

Total number of shared blocks read by the statement

SQL Handle

Is a token that refers to the batch or stored procedure that the query is part of

Shared Blocks Dirtied

Total number of shared blocks dirtied by the statement

Shared Blocks Written

Total number of shared blocks written by the statement

Local Blocks Hit

Total number of local block cache hits by the statement

Local Blocks Read

Total number of local blocks read by the statement

Local Blocks Dirtied

Total number of local blocks dirtied by the statement

Local Blocks Written

Total number of local blocks written by the statement

Temp Blocks Read

Total number of temp blocks read by the statement

Temp Blocks Written

Total number of temp blocks written by the statement

Block Read Time(ms)

Total time the statement spent reading blocks, in milliseconds

Block Write Time(ms)

Total time the statement spent writing blocks, in milliseconds

Database Name

Database in which the statement was executed

Query Id

Internal hash code, computed from the statement’s parse tree

SQL Statement

This section displays SQL statement of the selected query.

Execution Plan

This section provides an interactive and intuitive way to view the query execution plan and statement. The focus is on the SQL Statement selected on the Query Execution Statistics table. This section displays the sequence of execution for the selected query plan.

Click a node to view the node details.

  • To download the execution plan XML, click the icon.
  • To download the execution plan as diagrammatical format, click the icon.

Analyzing the Locks Details

To analyze the information about the locks held by open transactions within the database server:

  1. Click the Locks tab.
  2. The SQL Locks window is displayed that contains one row per active lockable object, requested lock mode, and relevant transaction.

Using the Locks tab, the database administrator can view information about the outstanding locks in the lock manager. For example, this capability can be used to:

  • View all the locks currently outstanding, all the locks on relations in a particular database, all the locks on a particular relation, or all the locks held by a particular PostgreSQL session.
  • Determine the relation in the current database with the most ungranted locks (which might be a source of contention among database clients).
  • Determine the effect of lock contention on overall database performance, as well as the extent to which contention varies with overall database traffic.

The following table provides information about the various fields in the SQL Locks window:

Field

Description

Process id (SPID)

Process ID of the server process holding or awaiting this lock

Resource Lock Type

Type of lock applied on the lockable object

DB Name

Database name associated with the backend

Lock Mode Name

Name of the lock mode held or desired 

State Change Time

Time when the currently active query changed its state

Request Status(t/f)

The status of the lock (t/f)

Analyzing the Session Details

Using the Session tab, you can view access information about all active user connections and internal tasks. To open the Sessions window:

  1. Click the Sessions tab.
  2. The Session Aggregate window is displayed, as shown below:

The following table provides information about the various fields in the Session Aggregate window:

Column Name

Description

PID

Process ID of this backend

SQL User

Name of the user logged into this backend

Query

Text of this backend most recent query

Status

Status of the session (active, idle, idle in truncation etc.)

Client Host Name

Host name of the connected client

Login Time

Time when this process was started, i.e. when the client connected to the server

DB Name

Name of the database this backend is connected to

Client Address

IP address of the client connected to this backend

Application Name

Name of the application that is connected to this backend

Client Port

TCP port number that the client is using for communication with this backend

Exact Start

Time when this process’ current transaction was started

Query Start Time

Time when the currently active query was started

State Change Time

Time when the state was last changed

Waiting

True if this backend is currently waiting on a lock

On clicking the PID, the user can further drill down the session and can see the following details:

  • Session Stats
  • SQL Plan
  • SQL Query
  • Session Locks

Session Stats

On clicking Session Stats, the aggregated data of session stats is displayed for the selected PID based on the specified time duration.

SQL Plan

On clicking Sql Plan, the complete plan is displayed.

SQL Query

On clicking Sql Query, the complete query is displayed.

 

Locks

On clicking Locks, the details of session locks are displayed, such as Process ID, Database Name, State Change Time, Resource Lock Type, Lock Mode Name, and Request Status.

Temp Files

You can use the Temp Files tab to view the temporarily stored objects. To view temp files:

  1. Click the Temp Files tab.
  2. Click the Usage tab.

 Following are the description of the columns in the Temp DB table.

Field

Description

DB Name

Database Name

Temp Files Count

Number of Temporary Files

Temp File Size(MB)

Size of temporary files in Megabytes

Temp Files Count and Temp Files Size are separately displayed in the widgets.

Server Stats

Click the Server Stats tab to analyse the details of connection for monitoring.

SQL Configuration

Memory stats graph provides details about free memory and usage of total memory by database. The values of memory space displayed in the graph in MB.

  • Total memory of database server
  • Total used memory of database server
  • Total free memory of database server
  • Total server memory used by database

This graph displays data for time interval according to preset as in other screens and refreshes accordingly.

DB Configuration 

Click the Configurations tab to view the database configuration detail of different configuration options and settings related to the database performance.

The following table describes the various fields in the SQL Configuration window:

Field

Description

Parameter Name

Configuration parameter name

Parameter Setting

Value to be assigned to the parameter

Minimum

Parameter Minimum value

Maximum

Parameter Maximum value

Source

Source of the current parameter value

Connection Stats

Sub-Section Connection Stats show row per server process, showing information related to the current activity of that process.

Details of connection stats for monitoring.

Field

Description

DB Name

Name of the database this backend is connected

Login Time

Time when this process was started, i.e., when the client connected to the server

Last Batch Time

Time when the state last changed

Status

Current overall state of this backend. Possible values are:

·          active: The backend is executing a query.

·          idle: The backend is waiting for a new client command.

·          idle in transaction: The backend is in a transaction, but is not currently executing a query.

·          idle in transaction (aborted): This state is similar to idle in transaction, except one of the statements in the transaction caused an error.

·          fastpath function call: The backend is executing a fast-path function.

·          disabled: This state is reported if track_activities is disabled in this backend.

Client Hostname

Host name of the connected client

Login Name

Name of the user logged into Database

Number of Connections

Number of established connections per database

Configuration and Logs

You can configure logs within the ‘Configuration & Logs’ section. This can be viewed only if you have the read-write permission. On selecting  icon, a pop up appears. Here you can change the values as per requirement.

Provide the following configurations:

  1. Sample Progress Interval: The interval at which data is updated in UI.
  2. Batch Job History Duration: To view job history for batch job based on the specified duration.
  3. Timeout for Custom Query: To terminate the custom query execution in case data from query is not received.
  4. Threshold for SQL Plan: To set the min & max value (threshold) for SQL Plan.

This section displays configuration and logs of database that includes Information about Postgres internal connections, Services completion time, Query completion time, and Custom Query execution.

It is further divided into following tabs:

  • Postgres
  • Services Logs
  • DB Query Logs
  • Query Execution

Postgres

This displays overall connection stats that includes max connections, reserved connections, used connections, and available connections. Apart from this, you can view the detailed connection stats based on the database and application.

Detailed Connection Stats (Based on Database)

This section displays the detailed connection stats based on the database:

  • Database Name: Name of the database.
  • Used: Number of used connections.
  • Active: Number of active connections. The backend is executing a query.
  • Idle: Number of idle connections. The backend is waiting for a new client command.
  • Idle in Transaction: Identifies connections against which your app has run a BEGIN but it is now waiting somewhere in a transaction and not doing work.
  • Idle in Transaction (Aborted): Identifies connections that were idle in the transactions that have been aborted.
  • Fastpath Function Call: This state is similar to idle in transaction, except one of the statements in the transaction caused an error.
  • Disabled: This state is reported if track_activities is disabled in the backend.
  • Blank: The backend is executing a fast path function.

Detailed Connection Stats (Based on Application)

This section displays the detailed connection stats based on the application. The same fields are displayed in this section too.

Service Logs

This displays the services completion time. The details include – Timestamp, database, class, method, service, and execution time.

DB Query Logs

This displays the query completion time. The details include – Timestamp, database, class, method, service, and execution time.

Query Execution

Here, you can run custom query and predefined procedures and get the output. Select the Custom Query option, provide the query, and click Execute. The out is displayed in the Query Output section.

Monitoring Capabilities

Postgres monitors are categorized into Detailed Monitors and Metrics Monitors.

Detailed Monitors

The description of detailed monitors is provided in the Monitoring UI section.

  • PostgreSQLAccesslog
  • PostgreSQLConnectionStats
  • PostgreSQLExecutionStats
  • PostgreSQLLocksStats
  • PostgreSQLSessionStats

Metrics Monitors

The metrics of these monitors are captured and displayed on the Dashboard.

  • PostgreSQL Background Writer
  • PostgreSQL Activity
  • PostgreSQL Connections
  • PostgreSQL Table Heap
  • PostgreSQL IO Activity
  • PostgreSQL Lock Mode
  • PostgreSQL Query
  • PostgreSQL Temporary File

PostgreSQL Background Writer

PostgreSQL background writer stats provides metrics related to background writer activity in the database.

 

#

Metric

Description

1

PostgreSQL Buffer Scheduled Checkpoints/Sec

Number of scheduled checkpoints that have been performed per second.

2

PostgreSQL Buffer Requested Checkpoints/Sec

Number of requested checkpoints that have been performed per second.

3

PostgreSQL Buffer Written/Sec

Number of buffers written during checkpoints per second.

4

PostgreSQL Buffer Written By BG/Sec

Number of buffers written by the background writer per second.

5

PostgreSQL Max Written Clean/Sec

Number of times the background writer stopped a cleaning scan because it had written too many buffers.

6

PostgreSQL Buffer Written By Backend/Sec

Number of buffers written directly by a backend per second.

7

PostgreSQL Buffer Allocated/Sec

Number of buffers allocated per second.

PostgreSQL Activity

PostgreSQL database activity stats provides metrics related to activity in the database.

#

Metric

Description

1

PostgreSQL Connected Backends

Number of backends currently connected to this database.

2

PostgreSQL Active Connections

Number of active connections to this database.

3

PostgreSQL Idle Connections

Number of idle connections to this database.

4

PostgreSQL Committed Transactions/Sec

Number of transactions in this database that have been committed per second.

5

PostgreSQL Rollback Transactions/Sec

Number of transactions in this database that have been rolled back per second.

6

PostgreSQL Disk Read/Sec

Number of disk blocks read per second.

7

PostgreSQL Cache Hits/Sec

Number of times disk blocks were found already in the buffer cache per second.

8

PostgreSQL Tuple Returned/Sec

Number of rows returned by queries per second.

9

PostgreSQL Tuple Fetched/Sec

Number of rows fetched by queries per second.

10

PostgreSQL Tuple Inserted/Sec

Number of rows inserted by queries per second.

11

PostgreSQL Tuple Updated/Sec

Number of rows updated by queries per second.

12

PostgreSQL Tuple Deleted/Sec

Number of rows deleted by queries per second.

13

PostgreSQL Locks Consumed

Total number of locks held by open transactions within the database server.

14

PostgreSQL Tuple Write/Sec

Sum of rows inserted, updated and deleted per second.

15

PostgreSQL Hit (%)

Disk hits percentage w.r.t total read requests.

PostgreSQL Connections

PostgreSQL database Connection statistics.

#

Metric

Description

1

PostgreSQL Connections

Total connection statistics database.

PostgreSQL Table Heap

PostgreSQL database table heap ratio.

#

Metric

Description

1

PostgreSQL Table Heap Read/Sec

Number of disk blocks read from user’s table per second.

2

PostgreSQL Table Heap Hit/Sec

Number of buffer hits in user’s table per second.

3

PostgreSQL Table Heap Hit (%)

Buffer hits percentage w.r.t total read requests.

PostgreSQL IO Activity

PostgreSQL IO activity stats provides metrics related to IO activity in the database.

#

Metric

Description

1

PostgreSQL Heap Blocks Read/Sec

Number of disk blocks read from this schema per second.

2

PostgreSQL Heap Blocks Hit/Sec

Number of buffer hits in this schema per second.

3

PostgreSQL Index Blocks Read/Sec

Number of disk blocks read from all indexes on this schema per second.

4

PostgreSQL Index Blocks Hit/Sec

Number of buffer hits in all indexes on this schema per second.

5

PostgreSQL Toast Blocks Read/Sec

Number of disk blocks read from this schema TOAST table per second.

6

PostgreSQL Toast Blocks Hit/Sec

Number of buffer hits in this schema in TOAST table per second.

7

PostgreSQL Toast Index Read/Sec

Number of disk blocks read from this schema in TOAST table indexes per second.

8

PostgreSQL Toast Index Hit/Sec

Number of buffer hits in this schema in TOAST table indexes per second

PostgreSQL Lock Mode

PostgreSQL lock mode stats provide metrics related to lock mode like AccessShareLock, ExclusiveLock etc. Table level lock modes are ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE and Row level lock modes are FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE.

#

Metric

Description

1

PostgreSQL Lock Mode Count

Number of occurrences of specific lock modes for the sample interval.

 

PostgreSQL Query

Provide PostgreSQL health stats.

#

Metric

Description

1

PostgreSQL Executed Queries/Sec

Number of queries successfully executed per second in database.

2

PostgreSQL Query Response Time (ms)

Query average response time.

3

PostgreSQL Active Queries

Number of queries running in database.

4

PostgreSQL Query With Duration <1 Min

Number of queries taking less than 1 minute.

5

PostgreSQL Query With Duration 1-5 Min

Number of queries taking 1-5 minute.

6

PostgreSQL Query With Duration 5-15 Min

Number of queries taking 5-15 minute.

7

PostgreSQL Query With Duration >15 Min

Number of queries taking more than 15 minute.

PostgreSQL Temporary File

PostgreSQL temporary database space stats.

#

Metric

Description

1

PostgreSQL Temporary File Count

Number of temporary file count of database.

2

PostgreSQL Temporary File Size (MB)

Temporary file size per database in Megabytes.

 

Note: You can see the data of temporary files only if there is temporary data in the Postgres.