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 EDB Postgres Advanced Server Guide, available at: https://www.enterprisedb.com/docs/en/11.0/EPAS_Guide_v11/EDB_Postgres_Advanced_Server_Guide.1.077.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.
- You must have super user privileges to access the Performance Diagnostic dashboard.
- 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 get the following error if the above prerequisites are not met:
To open the Performance Diagnostic dashboard, select Performance Diagnostic… from the Management menu of the PEM client. By default, the top most Performance Diagnostic chart pulls the data for one hour, starting from current date and time.
By default, the top most Performance Diagnostic chart pulls the data for one hour, starting from current date and time. The default range selection in hours in the first chart can be customized in the Performance Diagnostic section of the Preferences dialog under the File menu. 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.
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.
The first chart displays the number of wait events that have occurred over the period of time that is selected for the charts. You can narrow down the timeline in the first chart to analyze the data for a specific period. The second chart displays the total number of wait events of each type, for the duration that you select in the first chart. To make differentiation easier, the graph for each wait event type is displayed in a different color. You can click on Legend to identify the color in which a particular wait event type is displayed in the graph. You can select the specific wait event types for which you want to analyze the data.
Select a point in time on the second chart for which you wish to analyze the wait events; the third section displayed on the Performance Diagnostics dashboard is populated on the basis of your selection in the second chart. The third section makes wait details available 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.
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.
Click on the Eye icon in any row to display a new tab with details of the query to that particular row. This tab displays the Query ID and its corresponding sessions IDs in a dropdown list. 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 Waits information table 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 Types panel displays the total number of wait events for the selected session ID and query ID. Select a time range in the first chart to analyze the data for a specific period. The Wait Types bar graph displays the total number of wait event types for the duration that you select. To make differentiation easier, each wait event type is represented by a different color in the bar graph.
In the Wait Types bar graph, you can select the range of bar graphs for which you want to analyze the wait event types and their corresponding wait events; the Wait Events donut chart is populated on the basis of your selection. In the Wait Events donut chart, all the wait event types applicable to the selected duration are displayed in the percentage format. You can select any one wait event type to see all the wait events belonging to that particular wait event type and their count. Click Read More to read about the various wait event types and wait events.