Overview of WDR Diagnostic Reports
⚫ WDR (Workload Diagnosis Report) is based on the performance snapshot data of the system at two different time points, and generates a performance performance report between the two time points to diagnose the performance failure of the database kernel.
⚫ The WDR report is the primary means of diagnosing long-term performance problems. Based on SNAPSHOT's performance baseline, multi-dimensional performance analysis can help DBAs grasp the busyness of the system load, the performance of each component, and performance bottlenecks.
⚫ SNAPSHOT is also an important data source for follow-up performance problem self-diagnosis and self-optimization suggestions.
WDR Diagnostic Report
⚫ WDR mainly depends on two components:
SNAPSHOT performance snapshot: A performance snapshot can be configured to collect a certain amount of performance data from the kernel at certain intervals and persist it in the user tablespace. Any SNAPSHOT can be used as a performance baseline, and the results of other SNAPSHOTs can be compared to analyze the performance of the baseline.
WDR Reporter: The report generation tool is based on two SNAPSHOTs, analyzes the overall performance of the system, and can calculate the changes of more specific performance indicators between these two time periods, and generate two different levels of Summary and DETAIL. performance data.
WDR report generation
After WDR Snasphot is started (that is, when the parameter enable_wdr_snapshot is on), and the number of snapshots is greater than or equal to 2.
⚫ Operation steps
Execute the following command to query the snapshot that has been generated to obtain the snapshot_id of the snapshot.
select * from snapshot.snapshot;
(Optional) Execute the following command to create a snapshot manually. If there is only one snapshot in the database or you need to view the monitoring data of the database in the current time period, you can choose to perform the snapshot operation manually. This command requires the user to have the sysadmin privilege.
WDR report generation example
⚫ Query the snapshot that has been generated.
postgres=# select * from snapshot.snapshot;
snapshot_id | start_ts | end_ts
1 | 2020-09-07 10:20:36.763244+08 | 2020-09-07 10:20:42.166511+08
2 | 2020-09-07 10:21:13.416352+08 | 2020-09-07 10:21:19.470911+08
⚫ Execute the following command to write the queried information into the performance report.
select generate_wdr_report(begin_snap_id Oid, end_snap_id Oid, varchar report_type, varchar report_scope, int node_name );
⚫ Generate formatted performance report wdrTestNode.html.
postgres=# \a \t \o /home/om/wdrTestNode.html Output format is unaligned. Showing only tuples.
⚫ Turn off performance report
postgres=# \o \a \t Output format is aligned. Tuples only is off.