Performance Report Generated Based on WDR Snapshots

Prerequisites

The number of WDR 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 on the CCN 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 cm_ctl query -Cdvi command. In the command output, the information under Central Coordinator State is the CCN information.

5. Generate a performance report.
   1. Run the following command to generate a formatted performance report file:

      \a \t \o /home/om/wdrTestNode.html

      The parameters in the preceding command are described as follows:

      • \a: switches the unaligned mode.
      • \t: switches the information and row count footer of the output column name.
      • \o: specifies that all the query results are sent to the server file.
      • Server file path: indicates the path for storing the generated performance report file. The user must have the read and write permissions on the path.
   2. Run the following command to write the queried information to the performance report:

      select generate_wdr_report(begin_snap_id bigint, end_snap_id bigint, report_type cstring, report_scope cstring, node_name cstring);

      The description of the parameters in the preceding command is as follows:

      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 database operation is performed 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 openGauss. 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.