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:
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
You must ensure that EDB Wait States module of EDB Postgres Advanced Server is installed. Modify the
postgresql.conffile, adding the
edb_wait_stateslibrary to the list of libraries in the
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:
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.
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
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.
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:
SQLtab displays the list of SQL queries having wait events for the selected sample time.
Userstab displays the details of the wait events grouped by users for selected sample time.
Waitstab displays the number of wait events belonging to each wait event type for the selected sample time.
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.
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 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.
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.
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.