cancel
Showing results for 
Search instead for 
Did you mean: 

How to Use PEM & Performance Diagnostic In CDS

Note: The Performance Diagnostic feature is available only on EDB Postgres Advanced Server 11.

 

Prerequisite:

 

Configuration Steps:

  1. Create a cluster on EDB Postgres Advanced Server 11 in your preferred region.

  2. IP whitelisting for PEM server:
    • Select the cluster and click on the Administrative Settings icon.
    • Add the IP address of the PEM server for port 5444 and apply the changes.

      Screen Shot 2019-02-18 at 11.02.09 AM.png

    • Click on the Confirm button to save the changes.

      Screen Shot 2019-02-18 at 10.58.01 AM.png

  3. Registering the Cluster to PEM: In this step, we are going to add the database cluster to the PEM server using the remote monitoring agent.
    • Login to your PEM server
    • Choose a server group and create a new server. Note: I have created a new server group “Performance Diagnostic” as part of this testing.

      Screen Shot 2019-02-18 at 11.07.05 AM.png

    • General tab: Provide a name identifier for displaying the cluster name in the PEM server group. In my example, ‘enterprise postgres 11’.

      Screen Shot 2019-02-18 at 11.09.00 AM.png

    • Connection tab: Complete the required fields.

      Example:

          1. Host Name: DNS name (Elastic IP Address) of the Cluster created
          2. Port: 5444
          3. Maintenance Database: edb
          4. Username: enterprisedb
          5. Password

    • Screen Shot 2019-02-18 at 11.09.21 AM.png

    • PEM Agent tab: Since we are going to use a remote monitoring agent for the CDS instance, we have to use the bound agent feature for monitoring.

      Example:

          1. Bound agent: Select the agent from the dropdown list.
          2. Remote Monitoring: Select “yes”
          3. Host: DNS/Elastic IP of the cluster
          4. Port: 5444
          5. SSL: Prefer
          6. Database: edb
          7. Username: enterprisedb
          8. Password

    • Screen Shot 2019-02-18 at 11.09.38 AM.png

      Screen Shot 2019-02-18 at 11.10.07 AM.png
    • Using Performance Diagnostic Extension
      • Expand your database > right click on Extension > Create > Extension Note: Please make sure that you are using edb database.

        Screen Shot 2019-02-19 at 6.04.40 PM.png

      • Search for and select the extension ‘edb_wait_states’ and click on Save. This will create an extension in the edb database.

        Screen Shot 2019-02-19 at 6.05.05 PM.png

      • Now, go to Management > Performance Diagnostic and it will open a new tab for you.

        Screen Shot 2019-02-18 at 11.19.19 AM.png

      • You may or may not see any data depending upon whether or not you are running a workload in your database and if DB operations are causing locks or a wait state.
      • As a part of this test, I have created a test table ‘table_pd’ and inserted some data.
      • Open two query sessions.
        • In one session run an ‘update’ statement inside a ‘begin’ block
        • In another session run a ‘vacuum full’ on the table. You will notice that the ‘vacuum full’ on the table keeps running unlike in the other session where we have not committed an ‘update’ statement.

          Screen Shot 2019-02-18 at 11.59.40 AM.png

      • In the performance diagnostic window, you will be able to see some activity in the graph. Select a smaller interval and it will expand the interval in more detail under # wait events type.

        Screen Shot 2019-02-18 at 11.48.28 AM.png

      • When you select a specific time frame (Sample time), you will be able to find what queries are running and what queries are in a wait state.

      • In my example, you can see two SQL statements running (queries executed in Step vi), user details of wait events in the bottom section called Wait Event Details.

        Screen Shot 2019-02-18 at 12.03.39 PM.png

      • For more detailed analysis of a particular query, you can click on the “eye” icon and it will open a new window where you can see how many times this query has been executed, session details, and wait events which helps to determine if the query has to wait for IO or was getting blocked by any other queries for a timely execution.

      • For a more realistic test, I have a series of SQL running. It gives me insight as a DBA to find out when the IO was high on the system, which queries are getting blocked or waiting for IO’s. It also helps to understand what is happening in the database overall statistics, find out queries that may need tuning or frequently getting blocked or in a waiting state.

        Screen Shot 2019-02-18 at 12.07.21 PM.png

        Screen Shot 2019-02-18 at 12.10.01 PM.png

        I hope this is helpful. If you have any questions or comments, please feel free to send an email to cds-help@enterprisedb.com.

Version history
Revision #:
8 of 8
Last update:
a month ago
Updated by:
 
Labels (1)
Contributors