Help Center > > Developer Guide> WDR Performance Analysis> Workload Diagnosis Report> Contents

Contents

Updated at: Sep 17, 2021 GMT+08:00

A WDR consists of a header, a summary, and details.

Each part consists of several subheaders and tables, displaying different indicators.

By default, all parts are expanded. There is a hyphen (-) before each subheader, as shown in the following figure.

Figure 1 Default WDR style

If you double-click the hyphen (-) before a subheader, content under the subheader is collapsed, and the hyphen (-) changes to the plus sign (+).

Figure 2 Style of a WDR with collapsed subheaders

You can click Back to... to go to the specified location.

Take the preceding figure as an example. You can click Back to Summary to go to the beginning of the summary part and click Back to Top to go to the beginning of the report.

Header

All types of reports have a header. A header includes the report type, scope, generation time, configuration of the CN that generates the report, and database version.

Figure 3 WDR header

Summary

Only WDRs of the Summary and All types have a summary. WDRs of the Detail type do not have a summary.

Figure 4 and Figure 5 show the summary indicators in a cluster WDR and node WDR, respectively. Compared with a cluster WDR, a node WDR does not have the Load Profile indicator, but it has four more indicators, which are Top 10 Events by Total Wait Time, Wait Classes by Total Wait Time, Host CPU, and Memory Statistics.

Figure 4 Cluster WDR indicators
Figure 5 Node WDR indicators

Indicators are described as follows:

  • Database Stat

    This indicator displays the statistics of each database between two snapshots.

    For the meaning of each field under this indicator, see GLOBAL_STAT_DATABASE (for clusters) and PG_STAT_DATABASE (for nodes).

  • Load Profile

    This indicator displays the system load between two snapshots. The following table lists the metrics.

    Table 1 Metrics in Load Profile

    Metric Name

    Description

    DB Time(s)

    Total elapsed time of running jobs, in seconds.

    DB CPU(s)

    Total CPU time of running jobs, in seconds.

    Redo size (blocks)

    Size of the generated WAL (number of blocks).

    Logical read (blocks)

    Number of logical reads for a table or index (number of blocks).

    Physical read (blocks)

    Number of physical reads for a table or index (number of blocks).

    Physical write (blocks)

    Number of physical writes for a table or index (number of blocks).

    Read IO requests

    Number of reads for a table or index.

    Write IO requests

    Number of writes for a table or index.

    Read IO (MB)

    Size of reads for a table or index, in MB.

    Write IO (MB)

    Size of writes for a table or index, in MB.

    Executes (SQL)

    Number of times that SQL statements are executed.

    Rollbacks

    Number of rollback transactions.

    Transactions

    Number of transactions.

    SQL response time P90

    SQL response time P85

    90% SQL response time.

    85% SQL response time.

    The meanings of the other three columns are as follows:

    • Per Second indicates the average statistical value per second. It is equal to the metric value divided by the interval between two snapshots.
    • Per Transaction indicates the average statistical value per transaction. It is equal to the metric value divided by the number of transactions between two snapshots.
    • Per Exec indicates the average statistical value per SQL statement. It is equal to the metric value divided by the number of SQL statements between two snapshots.
  • Instance Efficiency Percentages

    This indicator displays the cache hit ratio between two snapshots.

    It is recommended that the cache hit ratio be greater than 90%. The lower the cache hit ratio, the larger the amount of data that needs to be read from the external storage, and the lower the processing efficiency. If the cache hit ratio is lower than 70%, the memory is insufficient. In this case, increase the memory size or adjust the execution policies.

  • IO Profile

    This indicator displays the disk I/O (including other types of external storage media) between two snapshots.

    It includes the I/O generated by SQL statement execution and Xlog redo. Redo only collects statistics on output operations.

  • Top 10 Events by Total Wait Time

    This indicator displays 10 events with the longest total wait time between two snapshots in descending order. It includes the event name, total wait time, and average wait time.

    For the meaning of each field in the table, see the GS_WAIT_EVENTS view.

  • Wait Classes by Total Wait Time

    This indicator displays the total and average wait time of various wait events between two snapshots in descending order.

    For the meaning of each field in the table, see the GS_WAIT_EVENTS view.

  • Host CPU

    This indicator displays the CPU usage between two snapshots on a specific node.

    The following table lists the metrics.

    Table 2 Metrics in Host CPU

    Metric Name

    Description

    CPUS

    Number of CPUs.

    Cores

    CPU cores.

    Sockets

    Number of CPU sockets.

    Load Average Begin

    Average load of the start snapshot.

    Load Average End

    Average load of the end snapshot.

    %User

    Percentage of CPU time occupied when the system is running in user mode.

    %System

    Percentage of CPU time occupied when the system is running in kernel mode.

    %WIO

    Percentage of CPU time occupied when the system is running in wait I/O mode.

    %Idle

    Percentage of CPU time occupied when the system is running in idle mode.

  • Memory Statistics

    This indicator compares the memory usage between two snapshots on a specific node. Begin corresponds to snapshot 1 and End corresponds to snapshot 2. The memory types are listed as follows:

    Table 3 Memory types in Memory Statistics

    Memory Type

    Description

    max_process_memory

    Size of the maximum process memory, in MB.

    process_used_memory

    Size of the used process memory, in MB.

    max_shared_memory

    Size of the maximum shared memory, in MB.

    shared_used_memory

    Size of the used shared memory, in MB.

Details

Only WDRs of the Detail and All types have this part. WDRs of the Summary type do not have this part.

As shown in Figure 6, details of a node WDR consist of seven indicators, such as Time Statistics, whereas details of a cluster WDR consist of only SQL Statistics, Object stats, and SQL Detail. You can click an indicator to view its details.

Figure 6 Details in a node WDR
  • Time Statistics

    This indicator displays the time statistics on a specific node between two snapshots in descending order. The unit is μs.

    The following table lists metric names of the time statistics (Stat Name).

    Table 4 Metrics in Time Statistics

    Stat Name

    Description

    DB_TIME

    Total service execution time.

    CPU_TIME

    CPU time.

    PARSE_TIME

    Parsing time.

    REWRITE_TIME

    Rewriting time.

    PLAN_TIME

    Plan generation time.

    EXECUTION TIME

    Execution time.

    NET_SEND_TIME

    Network transfer time.

    PL_COMPILATION_TIME

    Compilation time of PL/pgSQL.

    PL_EXECUTION_TIME

    Execution time of PL/pgSQL.

    DATA_IO_TIME

    I/O time.

  • SQL Statistics

    This indicator displays statistics about SQL statements between two snapshots.

    To correctly calculate this indicator, ensure that Unique SQL is enabled during two snapshots, that is, enable_resource_track is set to on and the value of instr_unique_sql_count is greater than 0. For details, see the GS_INSTR_UNIQUE_SQL view.

    As shown in Figure 7, SQL statistics are displayed in descending order based on the elapsed time, CPU time, number of returned rows, number of read tuples, execution time, number of physical reads, and number of logical reads. Up to 200 records can be displayed for each metric.

    Figure 7 SQL statement statistics
    • Unique SQL can be queried only on the CN. Therefore, only a cluster WDR and CN WDR contain the SQL Statistics table.
    • The Min Elapse Time(μs) and Max Elapse Time(μs) fields display the accumulated minimum and maximum value since the last statistics reset and before the second snapshot occurs, respectively. The other fields display the statistics between two snapshots.
    • The table contains a large number of rows. For readability, the SQL Text column displays up to 25 bytes. You can click the Unique SQL ID in each row to go to the SQL Detail area and view details about the SQL statement.
    • Wait Events

      This indicator displays statistics on wait events or statuses between two snapshots. The statistics are sorted by wait time and by number of times that a wait event or status occurs in descending order. Up to 200 records can be displayed.

      To correctly calculate this indicator, ensure that the function of collecting statistics on wait events is enabled during the two snapshots, that is, enable_resource_track and enable_track_wait_event are set to on. For details, see the GS_WAIT_EVENTS view.

      The Max Wait Time(us) field displays the accumulated maximum value since the last statistics reset and before the second snapshot occurs. The other fields display the statistics between two snapshots.

  • Utility status

    This indicator consists of the following three metrics:

    • Background writer stat

      This metric displays the database backend write activity between two snapshots. For the meaning of each field under this metric, see the PG_STAT_BGWRITER view.

    • Replication Slot

      This metric displays the replication slot usage at the time when the second snapshot is generated. For the meaning of each field under this metric, see the PG_REPLICATION_SLOTS view.

    • Replication stat

      This metric displays log synchronization statistics between two snapshots. For the meaning of each field under this metric, see the PG_STAT_REPLICATION view.

      Because logs are synchronized between DNs, only the DN WDRs contain valid data for Replication Slot and Replication stat.

  • Object stats

    This indicator displays the statistics of each object. In the current version, only information about bad blocks is collected.

    • Bad block stats

      This metric displays bad block statistics between two snapshots. For the meaning of each field under this metric, see the PG_STAT_BAD_BLOCK view.

      In the current version, only information about bad blocks on DNs is collected.

  • Configuration settings

    This indicator displays the GUC parameter settings on a specific node when the second snapshot is generated. For the meaning of each field under this metric, see the PG_SETTINGS view.

  • SQL Detail

    This indicator displays details about a SQL statement, including the Unique SQL ID, the CN where the SQL statement is executed (only for clusters), and the normalized SQL text. The maximum length of the SQL text is the same as that of the query column in the GS_INSTR_UNIQUE_SQL view.

Did you find this page helpful?

Submit successfully!

Thank you for your feedback. Your feedback helps make our documentation better.

Failed to submit the feedback. Please try again later.

Which of the following issues have you encountered?







Please complete at least one feedback item.

Content most length 200 character

Content is empty.

OK Cancel