WDR Snapshot Schema

After the WDR snapshot function is enabled (enable_wdr_snapshot is set to on), schema snapshot is created in user tablespace pg_default in database postgres to flush WDR snapshot data. By default, the initial user or the monadmin user can access the snapshot schema.

You can set the parameter wdr_snapshot_retention_days to automatically manage the snapshot lifecycle.

Original Information Table of WDR Snapshots

SNAPSHOT.SNAPSHOT

SNAPSHOT records the index information, start time, and end time of WDR snapshots stored in the current system. The results can only be queried in the system library, but not in the user library.

Table 1 SNAPSHOT attributes

SNAPSHOT.TABLES_SNAP_TIMESTAMP

TABLES_SNAP_TIMESTAMP records the start time and end time of data collection, as well as corresponding databases, and table objects for all stored WDR snapshots.

Table 2 TABLES_SNAP_TIMESTAMP attributes

SNAP_SEQ

SNAP_SEQ is an ascending sequence, which provides IDs for WDR snapshots.

WDR Snapshot Data Table

The naming rule of a WDR snapshot data table is snap_{Source data table}.

WDR snapshot data tables come from all views in DBE_PERF Schema.

All WDR Snapshot data tables can be queried by running the following command.

select * from pg_catalog.pg_tables where schemaname='snapshot';

The following table lists all WDR Snapshot data tables and related introduction pages for your reference.

Performance Report Generated Based on WDR Snapshot

A performance report is generated by summarizing and collecting statistics based on WDR snapshot data tables.

Prerequisites

A report can be generated after the WDR snapshot function is enabled (that is, 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 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 the sysadmin user.

   select create_wdr_snapshot();

   Note: Run the cm_ctl query -Cdvi command. The command output returned in the Central Coordinator State part is the CCN information.

5. Run the following commands to generate a WDR in HTML format on the local PC:

   a. Run the following commands 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

   b. 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 cluster-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);

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

   Table 3 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.snapshot 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, empty or set to NULL.	node: a node name in MogDB cluster: This value is omitted, left blank,empty or set to NULL.

   c. Run the following command to disable the output options and format the output:

   \o \a \t

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

Table 4 WDR report

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.
MogDB=# 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**.
MogDB=# \a \t \o /home/om/wdrTestNode.html
Output format is unaligned.
Showing only tuples.

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

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

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

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

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