HomeMogDBMogDB StackUqbar
v3.1

Documentation:v3.1

Supported Versions:

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.

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.

schemaname tablename
snapshot tables_snap_timestamp
snapshot snapshot
snapshot snap_global_os_runtime
snapshot snap_global_os_threads
snapshot snap_global_instance_time
snapshot snap_summary_workload_sql_count
snapshot snap_summary_workload_sql_elapse_time
snapshot snap_global_workload_transaction
snapshot snap_summary_workload_transaction
snapshot snap_global_thread_wait_status
snapshot snap_global_memory_node_detail
snapshot snap_global_shared_memory_detail
snapshot snap_global_stat_db_cu
snapshot snap_global_stat_database
snapshot snap_summary_stat_database
snapshot snap_global_stat_database_conflicts
snapshot snap_summary_stat_database_conflicts
snapshot snap_global_stat_bad_block
snapshot snap_summary_stat_bad_block
snapshot snap_global_file_redo_iostat
snapshot snap_summary_file_redo_iostat
snapshot snap_global_rel_iostat
snapshot snap_summary_rel_iostat
snapshot snap_global_file_iostat
snapshot snap_summary_file_iostat
snapshot snap_global_replication_slots
snapshot snap_global_bgwriter_stat
snapshot snap_global_replication_stat
snapshot snap_global_transactions_running_xacts
snapshot snap_summary_transactions_running_xacts
snapshot snap_global_transactions_prepared_xacts
snapshot snap_summary_transactions_prepared_xacts
snapshot snap_summary_statement
snapshot snap_global_statement_count
snapshot snap_summary_statement_count
snapshot snap_global_config_settings
snapshot snap_global_wait_events
snapshot snap_summary_user_login
snapshot snap_global_ckpt_status
snapshot snap_global_double_write_status
snapshot snap_global_pagewriter_status
snapshot snap_global_redo_status
snapshot snap_global_rto_status
snapshot snap_global_recovery_status
snapshot snap_global_threadpool_status
snapshot snap_statement_responsetime_percentile
snapshot snap_global_statio_all_indexes
snapshot snap_summary_statio_all_indexes
snapshot snap_global_statio_all_sequences
snapshot snap_summary_statio_all_sequences
snapshot snap_global_statio_all_tables
snapshot snap_summary_statio_all_tables
snapshot snap_global_stat_all_indexes
snapshot snap_summary_stat_all_indexes
snapshot snap_summary_stat_user_functions
snapshot snap_global_stat_user_functions
snapshot snap_global_stat_all_tables
snapshot snap_summary_stat_all_tables
snapshot snap_class_vital_info
snapshot snap_global_record_reset_time

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

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

    img 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

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

--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.
Copyright © 2011-2024 www.enmotech.com All rights reserved.