Performance Report Generated Based on WDR Snapshots

Performance reports are generated based on the summary and statistics of WDR snapshot data tables. By default, the initial user or monitor administrator can generate reports.

Prerequisites

The WDR snapshots have been started (enable_wdr_snapshot is set to on) and the number of snapshots is greater than or equal to 2.

Procedure

Run the following command to create a report file:
```
touch  /home/om/wdrTestNode.html
```
Run the following command to connect to the postgres database.
```
gsql -d postgres -p [Port number] -r
```
Run the following command to query the generated snapshot and obtain snapshot_id:
```
select * from snapshot.snapshot;
```
(Optional) Run the following command to manually create a snapshot. If only one snapshot exists in the database or you want to view the monitoring data of the database in the current period, manually create a snapshot. This command is only available to users with the sysadmin permission.
```
select create_wdr_snapshot();
```

Run the following command to generate a WDR in HTML format on the local PC:

Run the following command to set the report format: \a indicates that table row and column symbols are not displayed. \t indicates that column names are not displayed. \o specifies an output file.
```
gsql> \a      
gsql> \t 
gsql> \o /home/om/wdrTestNode.html
```

Run the following command to generate a WDR in HTML format:

gsql> select generate_wdr_report(begin_snap_id Oid, end_snap_id Oid, int report_type, int report_scope, int node_name );

Example 1: Generate a database instance-level report.

select generate_wdr_report(1, 2, 'all', 'cluster',null);

Example 2: Generate a report for a node.

select generate_wdr_report(1, 2, 'all', 'node', pgxc_node_str()::cstring);

Currently, the name of the centralizedopenGauss node is fixed to dn_6001_6002_6003. You can also replace it with the actual node name.

**Table 1** Parameters of the generate_wdr_report function
Parameter	Description	Value Range
begin_snap_id	ID of a snapshot when a query starts, which is specified by snapshot_id in the snapshot.snaoshot table.	-
end_snap_id	ID of a snapshot when a query ends. By default, the value of end_snap_id is greater than that of begin_snap_id table (snapshot_id in the snapshot.snaoshot table).	-
report_type	Type of the generated report. The value can be summary, detail, or all.	summary: summary data detail: detailed data all: summary data and detailed data
report_scope	Range of the generated report. The value can be cluster or node.	cluster: database-level information node: node-level information
node_name	When report_scope is set to node, set this parameter to the name of the corresponding node. (You can run the *select from pg_node_env; command to query the node name.) If report_scope is set to cluster, this parameter can be omitted, left blank, or set to NULL**.	node: a node name in GaussDB cluster: This value is omitted, left blank or set to NULL.

The two snapshots used to generate the report should meet the following conditions:

No node is restarted between two snapshots.
No primary/standby switchover is performed between two snapshots.
Performance indicators cannot be reset between two snapshots.
No DROP operation is performed on the database between two snapshots.
If a negative value exists in the generated WDR, it indicates that the indicator cannot reflect the performance of the database.
The time required for generating a report depends on the amount of performance data in the performance snapshot. Generally, a report can be generated in minutes. If the report cannot be generated within 5 minutes, collect the statistics ANALYZE | ANALYSE about the tables in the snapshot schema (considering the snap_global_statio_all_tables and snap_global_statio_all_indexes tables first), and then generate the report again. Alternatively, set set statement_timeout to * to terminate report generation.
When generating a report, ensure that the character set of the client is the same as that of the GaussDB database. (You can run set client_encoding to * to set the character set of the client).

Disable the output options and format the output.
```
\o \a \t 
```

View the WDR in /home/om/ as required.

Examples

-- Create a report file.
touch  /home/om/wdrTestNode.html

-- Connect to the database.
gsql -d postgres -p [Port number] -r

-- Query the snapshots that have been generated.
openGauss=# 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
(2 rows)


-- Generate the formatted performance report wdrTestNode.html.
openGauss=# \a \t \o /home/om/wdrTestNode.html
Output format is unaligned.
Showing only tuples.

-- Write data into the performance report wdrTestNode.html.
openGauss=# select generate_wdr_report(1, 2, 'all', 'node', 'dn_6001_6002_6003');

-- Close the performance report wdrTestNode.html.
openGauss=# \o

-- Generate the formatted performance report wdrTestCluster.html.
openGauss=# \o /home/om/wdrTestCluster.html

-- Write data into the performance report wdrTestCluster.html.
openGauss=# select generate_wdr_report(1, 2, 'all', 'cluster');

-- Close the performance report wdrTestCluster.html.
openGauss=# \o \a \t
Output format is aligned.
Tuples only is off.