Performance Diagnostic v9

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.

To analyze the wait states data on multiple levels, narrow down the data you select. The data you select at the higher level of the graph populates the lower level.

Prerequisites

• Install the EDB wait states package:

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

• After you install the EDB wait states module of EDB Postgres Advanced Server:

  1. Configure the list of libraries in the postgresql.conf file:

     Toggle WrapCopy

     shared_preload_libraries = '$libdir/edb_wait_states'

  2. Restart the database server.

  3. Create the following extension in the maintenance database:

     Toggle WrapCopy

     CREATE EXTENSION edb_wait_states;

• If you want to access the Performance Diagnostic dashboard as a user without superuser privileges, specific roles must be granted to the user based on the server to which the user is connected or logged into:

  • Grant pem_comp_performance_diagnostic role to the user (test1 in this example), who is logged into the PEM server.

    Toggle WrapCopy

    GRANT pem_comp_performance_diagnostic TO test1;

  • Grant pg_monitor role and EXECUTE privilege on all functions of edb_wait_states to the database user (test2 in this example). The test2 user is connected to the database server with EDB wait states.

    Toggle WrapCopy

    # Grant `pg_monitor` role
    GRANT pg_monitor TO test2;
          
    # Grant `Execute` privilege on all functions
    GRANT EXECUTE ON FUNCTION edb_wait_states_sessions TO test2;
    GRANT EXECUTE ON FUNCTION edb_wait_states_data TO test2;
    GRANT EXECUTE ON FUNCTION edb_wait_states_queries TO test2;
    GRANT EXECUTE ON FUNCTION edb_wait_states_samples TO test2; 
    GRANT EXECUTE ON FUNCTION edb_wait_states_purge TO test2;

If the prerequisites aren't met, an error appears when you access the Performance Diagnostic dashboard.

Using the Performance Diagnostic dashboard

To open the Performance Diagnostic dashboard, on the PEM client select Tools > Server > Performance Diagnostic.

By default, the top Performance Diagnostic graph pulls the data of the last hour, starting from the 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 along with the wait events at a particular time and in the last 15 seconds. These sessions might be waiting for a 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.

To open the Performance Diagnostic dashboard in a new browser tab, select Preferences > Open in New Browser Tab?.

The duration selection in the first graph is 10 minutes. To see the duration on a graph, select a duration from the Last list. To display the data for a specified date and time, select a date and time from the Until list.

The first graph displays the number of active sessions and wait event types for the selected duration. You can change the duration in the first graph to analyze the data for a specific time period.

The next section plots the following graphs based on the selected duration in the first graph:

Donut graph — Shows total wait event types according to the duration selection in the first graph. It can provide a better understanding of how much time was spent by those sessions on waiting for an event.

Line graph — Plots a time series with each point representing the active sessions for each sample time.

To differentiate each wait event type and the CPU usage more clearly, the graph for each wait event type displays in a different color.

Select a time on the Line graph to analyze the wait events. The third section displays the wait event details in the Performance Diagnostics dashboard based on your selected duration 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 the selected sample time.
• The Waits tab displays the number of wait events belonging to each wait event type for the selected sample time.

To show or hide a wait event type in all the graphs, select the graph legends. The analysis is simpler when you can only see the wait event types you want to analyze.

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

Select the eye in any row of the SQL tab to display a window with details on the query of that row. This window displays a query ID and its corresponding session IDs in a list at that selected sample time in the Query information section. You can select the Session ID list for the selected query that you want to analyze the data. The details corresponding to the selected session ID and query ID appear. The Query information table also displays the 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 types of graphs:

Donut graph — Shows the proportions of categorical data.

Timeline bar graph — Visualizes trends in counts of wait event types over time.

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

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

• On this page
• Prerequisites
• Using the Performance Diagnostic dashboard