Performance Diagnostic

You can use the Performance Diagnostic dashboard to analyze the database performance for Advanced Server instances by monitoring the wait events. To display the diagnostic charts, PEM uses the data collected by Advanced Server’s EDB Wait States module.

For more information on EDB Wait States, see the EDB Postgres Advanced Server Guide, available at: https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/user-guide/12/EDB_Postgres_Advanced_Server_Guide.1.78.html

You can analyze the Wait States data on multiple levels by narrowing down your selection of data. Each level of the chart is populated on the basis of your selection of data at the higher level.

Prerequisites:

  • You must have super user privileges to access the Performance Diagnostic dashboard.

  • You must ensure that EDB Wait States module of EDB Postgres Advanced Server is installed. Modify the postgresql.conf file, adding the edb_wait_states library to the list of libraries in the shared_preload_libraries parameter: shared_preload_libraries = '$libdir/edb_wait_states'.

Restart the database server, and then create the following extension for the maintenance database: CREATE EXTENSION edb_wait_states;

You will receive the following error message if the above prerequisites are not met:

Performance diagnostic error

Performance Diagnostic Error dialog

To open the Performance Diagnostic dashboard, go to the Tools menu of the PEM client, select the Server option and then select the Performance Diagnostics option.

Performance diagnostic intro

Performance Diagnostic dashboard

By default, the top most Performance Diagnostic graph pulls the data of last one hour, starting from current date and time. This graph shows the time series containing the number of active sessions. Each point of this time series represents the active sessions and wait events at a particular time and last 15 seconds. These sessions may or may not be waiting for an wait event, or using the CPU at a particular point in time. This time series is generated based on the wait event samples collected by the edb_wait_states extension.

Select Open in New Browser Tab? on the Preferences dialog to display the Performance Diagnostics dashboard in a new browser tab

The range selection in the first graph is 10 minutes. You can use the Last drop-down list box to select the duration for which you want to see the chart: select the last 1 hour, last 4 hours, last 12 hours, or last 24 hours. You can also select the date and time through which you want the data to be displayed.

Performance diagnostic dropdown

Performance Diagnostic dashboard - Time Changes option

The first graph displays the number of active sessions (and - wait event types) for the selected time interval. You can narrow down the timeline in the first graph to analyze the data for a specific time period.

The next section plots the following charts based on the selected interval in the first graph:

  • Donut graph - The donut graph displays total wait event types according to the time range selection in the first graph. It helps you understand how much time was spent by those session on waiting for an event.

  • Line graph - The line graph plots a time series with each point representing the active sessions for each sample time.

To differentiate wait event types and CPU usage clearly, the graph for each wait event type is displayed in a different color.

Select a particular time on the Line graph for which you wish to analyze the wait events; the third section displays the wait event details on the Performance Diagnostics dashboard on the basis of your selected particular time in the second graph. The third section displays wait event details on three tabs:

  • The SQL tab displays the list of SQL queries having wait events for the selected sample time.

  • The Users tab displays the details of the wait events grouped by users for selected sample time.

  • The Waits tab displays the number of wait events belonging to each wait event type for the selected sample time.

Performance diagnostic - time range selection in the first Wait event types charts

Performance Diagnostic dashboard - Time range selection in the first wait event types chart

You can click on the graph legends to show or hide a particular wait event type in all the graphs. This will make the analysis of a specific wait event type easier.

Performance diagnostic - show/hide the wait event type using the chart legends

Performance Diagnostic dashboard - Show and hide a particular wait event type by click the respective chart legend

You can filter the data displayed in the rows under all the three tabs. You can also sort the data alphabetically by clicking on the column headers.

Performance diagnostic query table filter

Performance Diagnostic - SQL table with filter applied

Performance diagnostic users table

Performance Diagnostic - Users table

Performance diagnostic wait events table

Performance Diagnostic - Wait events table

Click on the Eye icon in any row of the SQL tab to display a new tab with details of the query to that particular row. This page displays the Query ID and its corresponding sessions IDs in a drop down list at that particular selected sample time in the Query information section. You can select the session ID for the selected query for which you want to analyze the data. You will see the details corresponding to the selected session ID and query ID. The Query information table also displays the SQL query. If the SQL query is being displayed partially, click the down arrow at the bottom of the section to view the complete SQL query.

The Wait event types section displays the total number of wait event types for the selected session ID and query ID. It shows two type of graphs:

  • Donut graph - The donut graph shows the proportions of categorical data, with the size of each piece representing the proportion of each wait event type.

  • Timeline bar graph - This graph can be used to visualize trends in wait event types over time.

To differentiate clearly, each wait event type is represented by a different color in the bar graph.

Performance diagnostic query dashboad

Performance Diagnostic - Query dashboard

The Wait events section includes a table displaying all the wait events occurring during the query execution. It displays data in decreasing order by number of wait events. A second table displays the wait event by name with the Sample time over the period of the query execution. This table allows you to analyze the wait events during the query execution. It shows the actual samples collected by the EDB Wait States extension for a particular query ID and session ID.