Performance Diagnostic

The Performance Diagnostic dashboard analyzes the database performance for Postgres instances by monitoring the wait events. To display the diagnostic graphs, PEM uses the data collected by the EDB wait states module. For more information on EDB wait states, see EDB wait states.

In the Performance Diagnostic dashboard, limit the data selection at the higher level of the graph to perform an analysis of the Wait States data on multiple levels. Each level of the graph at the lower level is populated based on the data selection at the higher level.

Note

• EDB wait states is available on

  • EDB Postgres Advanced Server and PostgreSQL version 11 and later

  • EDB Postgres Extended version 11 and later

• Performance Diagnostics is available for all the Postgres variants and versions, for which EDB wait states module is available.

Prerequisite:

• Install the EDB wait states package:

  • For PostgreSQL, see EDB Repository.

  • For EDB Postgres Advanced Server, see EDB wait states.

• Configure the list of libraries in the postgresql.conf file after installing the EDB Wait States module:

  shared_preload_libraries = ‘$libdir/edb_wait_states’

• Restart the database server, and create the extension in the maintenance database:

  CREATE EXTENSION edb_wait_states;

• A user logged into the PEM server must be assigned specific roles to access the Performance Diagnostic dashboard.

  • For example, a test1 user is logged into the PEM server and wants to access the Performance Diagnostic dashboard. Grant the pem_comp_performace_diagnostic role to the test1 user:

    GRANT pem_comp_performance_diagnostic TO test1;

• The Database user connected to the database server must have the EXECUTE privilege on all the functions of edb_wait_states and the pg_monitor role.

  • For example, the db_user user is connected to the database server and wants to access the Performance Diagnostic dashboard. Grant the EXECUTE privilege on all the functions of edb_wait_states to the pg_monitor role and grant the pg_monitor role to db_user.

    GRANT EXECUTE ON FUNCTION edb_wait_states_sessions TO pg_monitor;

    GRANT EXECUTE ON FUNCTION edb_wait_states_data TO pg_monitor;

    GRANT EXECUTE ON FUNCTION edb_wait_states_queries TO pg_monitor;

    GRANT EXECUTE ON FUNCTION edb_wait_states_samples TO pg_monitor;

    GRANT EXECUTE ON FUNCTION edb_wait_states_purge TO pg_monitor;

    GRANT pg_monitor TO db_user;

You get the following error while accessing the Performance Diagnostic dashboard if the above prerequisites are not met:

Performance Diagnostic Error dialog

To open the Performance Diagnostic dashboard, select Server and then Performance Diagnostic... from the Tools menu of the PEM client.

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.

You can also use the Preferences dialog to display Performance Diagnostic in a new browser tab. Use Open in New Browser Tab? 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 graph: 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 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.

Next section plots the following graphs based on the selected time interval in the first graph:

1. Donut graph - It shows 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.

2. Line graph - It plots a time series with each point representing the active sessions for each sample time.

To differentiate each wait event types and the 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 in 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 dashboard - Time range selection in the first Wait event types graph

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 dashboard - Show and hide a particular wait event type by click the respective graph 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.

SQL tab

Performance Diagnostic - SQL tab with filter applied

Users tab

Performance Diagnostic - Users tab

Waits tab

Performance Diagnostic - Waits tab

Click on the Eye icon in any row of the SQL tab to display a new tab with details of the query of that particular row. This page displays Query ID and its corresponding session IDs in a dropdown 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:

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

2. Timeline bar graph - It can be used to visualize trends in counts of 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 dashboard

The Wait events section has a table displaying all the wait events that occurred during the query execution. It shows data in decreasing order by the number of wait events. The second table displays the wait event with sample time that occurred over the period of the whole query execution. It allows us to analyze the wait events during the query execution over the period of time. It shows the actual samples collected by the EDB Wait States extension for that particular query ID and session ID.