Using PostgreSQL Performance Investigator (PI)

The PostgreSQL Performance Investigator (PostgreSQL PI) provides a more in-depth analysis and investigation of the Instance activity and resource consumption. The PostgreSQL PI dashboard displays the following components:

The Instance View - Provides the ability to investigate and analyze the resource consumption of the instance.

The Performance Tree - Provides iterative access to any dimension associated with PostgreSQL database activity. For more information, refer to Using the Performance Tree.

The Resource Menu - Selecting one resource updates the performance tree, resource consumption charts, and overview section to display only the activity related to the selected resource. For more information, refer to Understanding the overall Instance Activity with the Resource Selector.

This section covers the following key ares:

• Understanding the overall Instance Activity with the Resource Selector
  
• Using the Performance Tree
• Viewing the Historical Metrics
• Analyzing Blocked Sessions

Understanding the overall Instance Activity with the Resource Selector

The PostgreSQL PI dashboard includes a resource selector that allows you to filter activity by the resource it is waiting for. Each resource indicates a group of several wait events that relate to the same area. By selecting a resource, you set the PI dashboard to show only activity relevant to the resource selected. It also highlights relevant metrics for investigation issues related to the selected resource. For example, by selecting I/O, the performance tree is filtered to show only activity that was waiting for I/O and show metrics like Tuple Deleted/Inserted/Updated.
The following table explains the resources available:

Resource	Description
Workload	This resource enables you to review the instance workload by consolidating all resources into a single view.
Client	This resource indicates a backend process is waiting for communication with the client application. Possible causes include network delays, slow client processing, or waiting for user input.
I/O	This resource allows you to review I/O-related data, including wait events, and physical reads and writes.
IPC	This resource indicates a delay in inter-process communication (IPC) mechanisms, which are used by processes to share data and synchronize actions.
LWLock	This resource indicates that the process is waiting for a lightweight lock (LWLock), which is utilized for internal synchronization within PostgreSQL. Excessive LWLock waits may suggest internal contention points.
Lock	This resource indicates that the backend process is waiting to acquire a lock on a table or row currently held by another transaction.
Other	Miscellaneous instance waits include infrequent or special-purpose wait states that should typically remain close to zero.
Process Time	This resource refers to the time spent by the database engine processing a statement. The server process is active and not waiting for resources. A high processing time may indicate a CPU bottleneck.

Using the Performance Tree

The performance tree provides iterative (up to three levels) access to any of the key dimensions associated with PostgreSQL Server database activity, based on the OLAP multidimensional model and an instance view of the instance activity. Domain nodes offer a hierarchical view of all types of PostgreSQL Server activity characteristics.

Selecting a dimension from the tree determines what subset of activity is displayed. Iterative drill-down into domains of interest provides increasingly refined focus and diagnosis.

For example, to begin the investigation by first identifying the most active DB User, follow the steps described below:

1. Select the DB Users node, to display the most active database users in the selected time range. That is, the database users who consumed the highest amount of the selected resource.
2. Select the first user, to focus the entire window on that user’s activity.
3. Identify the most demanding PostgreSQL statement that this specific user has executed, by expanding the user node and then selecting the PostgreSQL statement dimension node. This displays the most active PostgreSQL statements executed by this user.
4. Select a specific PostgreSQL statement to focus the entire window on the selected statement’s activity.
5. Select Client Machines under the selected PostgreSQL Statement, to view the computers on which the statement was run.

In a similar manner, such iterative drilldowns can be carried out into any PostgreSQL Server dimension of interest, to gain a complete understanding of the causes of its behavior.

The default PostgreSQL Server dimensions are as follows:

• SQL Statements - Executed SQL commands are listed in a dropdown menu. You can select a specific SQL statement to view details such as Resource Breakdown, Workload Metrics, and Blocked Sessions.
• Databases - The database context in which the session performs its operations. A session may switch between multiple databases during its lifetime.
• Users - Operating system users executing the client program.
• Client Machines - The machines on which the client executable, connected to the PostgreSQL Server, is running.
• Programs - The name of the program that connects to the PostgreSQL Server and executes SQL statements, as specified in the Program column of the PostgreSQL Server session information.
• Sessions - This displays the top sessions that consumed the most active time during the selected time frame.
• Backend Type - The various types of processes currently running on the PostgreSQL server.

Viewing the Historical Metrics

The PostgreSQL PI dashboard is divided into three sections that are correlated to each other:

• Resource breakdown charts - This section displays data in different charts:
  • Baseline chart - Displays the instance workload compared to the baseline over time.
  • Breakdown chart - Activity of the instance by second.
  • Resource Breakdown Pie chart - Displays the resource breakdown usage by % of the total instance activity.
  • All wait events pop up - Displays details of the wait events that the instance is waiting on during the selected time range.
• Workload Metrics - A table that displays various resource consumption metrics, providing an in-depth view of instance activity. Each resource includes its default metrics. Selecting each dimension in the performance tree along with a specific resource affects the data displayed at each level. For example, selecting the Lock resource will cause the Instance view dimension to show only lock-related data. The SQL Statements dimension will display only the statements that were experiencing locks, and the DB Users dimension will present only the users affected by locks. This filtering applies across all dimensions and resources
• Blocked Sessions - This section displays the list of blocked sessions for a specific timeframe. For more information, refer to Analyzing Blocked Sessions.

The Workload Metrics section displays parameters such as name, resource, average, min, max, total, and per execution for each metric.

The following table displays the metrics available under each resource menu:

Resource menu	Default Metrics Available
Workload	- Active Time - Displays the sum of all active waits and processing time represents the total activity within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval is calculated as the elapsed time divided by the execution count. - Commits Rate - Displays the frequency at which transactions are committed. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. - Rollbacks Rate - Displays the frequency at which transactions are rolled back. - Used Connections - Displays the number of connections (sessions) established between client applications and the PostgreSQL database server. - Wait Time Percent - Displays the percentage of time spent waiting for resources, such as CPU and I/O, relative to the total active time. - Waiting Connections - Displays the number of connections (sessions) that are waiting for resources to proceed with their tasks.
Client	- Active Time - Displays the sum of all active waits and processing time represents the total activity within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval is calculated as the elapsed time divided by the execution count. - Commits Rate - Displays the frequency at which transactions are committed. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. - Rollbacks Rate - Displays the frequency at which transactions are rolled back. - Used Connections - Displays the number of connections (sessions) established between client applications and the PostgreSQL database server. - Wait Time Percent - Displays the percentage of time spent waiting for resources, such as CPU and I/O, relative to the total active time. - Waiting Connections - Displays the number of connections (sessions) that are waiting for resources to proceed with their tasks.
I/O	- Active Time - Displays the sum of all active waits and processing time represents the total activity within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval is calculated as the elapsed time divided by the execution count. - Commits Rate - Displays the frequency at which transactions are committed. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. - Rollbacks Rate - Displays the frequency at which transactions are rolled back. - Tuple Deleted Rate - Displays the frequency at which tuples (rows of data) are deleted. - Tuple Inserted Rate - Displays the frequency at which tuples (rows of data) are inserted. - Tuple Updated Rate - Displays the frequency at which tuples (rows of data) are updated. - Used Connections - Displays the number of connections (sessions) established between client applications and the PostgreSQL database server. - Waiting Connections - Displays the number of connections (sessions) that are waiting for resources to proceed with their tasks. - I/O Wait - Displays the time spent by the various sessions waiting for data to be read from or written to disk.
LWLock	- Active Time - Displays the sum of all active waits and processing time represents the total activity within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval is calculated as the elapsed time divided by the execution count. - Commits Rate - Displays the frequency at which transactions are committed. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. - Rollbacks Rate - Displays the frequency at which transactions are rolled back. - Used Connections - Displays the number of connections (sessions) established between client applications and the PostgreSQL database server. - Waiting Connections - Displays the number of connections (sessions) that are waiting for resources to proceed with their tasks. - LWLOCK Wait - Displays the time spent by the various sessions waiting for internal synchronization within PostgreSQL.
Lock	- Active Time - Displays the sum of all active waits and processing time represents the total activity within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval is calculated as the elapsed time divided by the execution count. - Commits Rate - Displays the frequency at which transactions are committed. - Deadlocks - Displays the total number of deadlocks. Deadlock occurs when two or more transactions are struck, each waiting for the other to release a resource they need. - Executions - Displays the number of times statements were executed that were active during the current interval. - Rollbacks Rate - Displays the frequency at which transactions are rolled back. - Used Connections - Displays the number of connections (sessions) established between client applications and the PostgreSQL database server. - Waiting Connections - Displays the number of connections (sessions) that are waiting for resources to proceed with their tasks. - Lock Wait - Displays the time spent by the various sessions waiting to acquire a lock on a table or row that is currently held by another transaction.
Process Time	- Active Time - Displays the sum of all active waits and processing time represents the total activity within the current time interval. - Average SQL Response Time - Displays the average duration of the statements executed during the current time interval is calculated as the elapsed time divided by the execution count. - Commits Rate - Displays the frequency at which transactions are committed. - Elapsed Time - Displays the total time consumed for executing the statements. - Executions - Displays the number of times statements were executed that were active during the current interval. - Rollbacks Rate - Displays the frequency at which transactions are rolled back. - Used Connections - Displays the number of connections (sessions) established between client applications and the PostgreSQL database server. - Waiting Connections - Displays the number of connections (sessions) that are waiting for resources to proceed with their tasks. - Process Time - Displays the time spent by DB engine to process statement (the server process is active and does not wait for resources).

Analyzing Blocked Sessions

This section displays the details of the blocked session, the session that is causing the block, and any deadlock sessions. To view blocked sessions for a specific timeframe, click the Duration dropdown list, select the minimum and maximum time range limit, and then click Apply. You can view the following metric parameters for the sessions:

• Start Date- (Default) Displays the start date of the session (date-time format).
• End Date- Displays the end date of the session (date-time format).
• Session- (Default) Displays the session ID.
• Blocked By- (Default)
• Status - (Default) A selection out of possible values
• Object Name
• Duration- (Default)
• Program- (Default) Name of a program that connects to PostgreSQL Server and executes the SQL statements, as specified in the Program column of the PostgreSQL Server session information.
• DB User- (Default) PostgreSQL Server login names used for logging in to PostgreSQL Server.
• Client Machine- Displays the machines on which the client executable (connected to PostgreSQL server) is running.
• SQL ID- Displays the SQL ID of the session.
• SQL Text- (Default) Displays the SQL text for the selected SQL statement.