MogDB
Ecological Tools
Doc Menu

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

Name Type Description Example
snapshot_id bigint WDR snapshot ID 1
start_ts timestamp Start time of a WDR snapshot 2019-12-28 17:11:27.423742+08
end_ts timestamp End time of a WDR snapshot 2019-12-28 17:11:43.67726+08

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

Name Type Description Example
snapshot_id bigint WDR snapshot ID 1
db_name text Database corresponding to a WDR snapshot tpcc1000
tablename text Table corresponding to a WDR snapshot snap_xc_statio_all_indexes
start_ts timestamp Start time of a WDR snapshot 2019-12-28 17:11:27.425849+08
end_ts timestamp End time of a WDR snapshot 2019-12-28 17:11:27.707398+08

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.

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 connect the postgres database.

    gsql -d postgres -p <Port number> -r
  2. Run the following command to query the generated snapshot and obtain snapshot_id:

    select * from snapshot.snapshot;
  3. (Optional) Run the following command on CCN to manually create a snapshot. If only one snapshot exists in the database or to view the monitoring data of the database in the current period, manually create a snapshot.

    select create_wdr_snapshot();

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

  4. Perform the following steps to generate a performance report:

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

    \a \t \o Server file path

    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.

    b. 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 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. -
    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 commands to disable the output options and format the output:

    \o \a \t 
  5. View the WDR report as required.

Table 4 WDR report

Item Description
Database Stat (database scope) Performance statistics information in database dimensions, including transaction, write/read, row activity, write conflict, deadlock, and so on
Load Profile (database scope) Performance statistics information in database dimensions, including CPU time, DB time, logical read/physical read, IO performance, login/logout, workload intensity, and workload performance, and so on
Instance Efficiency Percentages (database/node scope) Buffer Hit (buffer hit rate), Effective CPU (CPU usage), WalWrite NoWait (success rate of obtaining Wal Buffer), Soft Parse (soft parsing rate), and Non-parse CPU (percentage of CPU time spent in non-parsing activities) at the database or node level
Top 10 Events by Total Wait Time (node scope) Event that consumes the most time
Wait Classes by Total Wait Time (node scope) Class of wait events that consume the most time
Host CPU (node scope) CPU usage of the host
Memory Statistics (node scope) memory statistics in the kernel
Object stats (node scope) Performance statistics information in the table and index dimensions
Database Configuration (node scope) Node configuration
SQL Statistics (node scope) Performance statistics of a SQL statement in all dimensions, including end-to-end time, row activity, cache hit rate, CPU usage, time consumption segmentation
SQL Detail (node scope) SQL statement details

Examples

-- To query enable_wdr_snapshot, set this parameter to on.
mogdb=# show enable_wdr_snapshot;
 enable_wdr_snapshot 
---------------------
 on
(1 row)

-- 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');

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