Performance Report Generated Based on WDR Snapshots

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

1. Run the following command to create a report file:

   touch  /home/om/wdrTestNode.html

2. Run the following command to connect to the postgres database.

   gsql -d postgres -p [Port number] -r

3. Run the following command to query the generated snapshot and obtain snapshot_id:

   select * from snapshot.snapshot;

4. (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();

5. Run the following command to generate a WDR in HTML format on the local PC:
   1. 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

   2. 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).
   3. Disable the output options and format the output.

      \o \a \t 

6. 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.