opengauss-WDR reporting practice

Introduction
Today I participated in the "8 Hours of OpenGauss Training Camp" course, thank you openGauss lecturers and evangelists for their wonderful sharing, each topic is very rich and benefited a lot.

Each topic is worth further study and understanding, among which "WDR report and performance tuning" I have done a simple practice.
Oracle's awr report is very convenient in solving problems on a daily basis. When encountering problems, we usually check whether there are snapshots of the corresponding time period, generate awr reports and make further analysis. By analyzing the status of the database, resource consumption and waiting events, the problem is initially located and verified on this basis. In the opengauss database, there is also such a "awr", which is called WDR (Workload Diagnostic Report) load diagnostic report, which is the workload diagnostic report of openGauss, and is often used to judge long-term performance problems of openGauss. The WDR report is based on the performance snapshot data of the system at two different time points, and generates a performance performance report between the two time points.

practice

1. Open the parameter enable_wdr_snapshot
   `postgres=# show enable_wdr_snapshot;

   enable_wdr_snapshot

   on
   (1 row)`
   After WDR Snaspshot is started, it will be in the user tablespace "pg_default", database " Create a new schema "snapshot" under postgres" to persist WDR snapshot data.

2. The number of WDR Snasphot performance snapshots is greater than or equal to 2.
   Operation steps:
   `1. Execute the following command to query the generated snapshot

postgres=# select * from snapshot.snapshot;
snapshot_id | start_ts | end_ts
-------------+-------- -----------------------+---------------------------- -----
1 | 2022-05-15 15:02:15.990876+08 | 2022-05-15 15:02:18.555272+08
2 | 2022-05-15 15:08:12.470218+08 | 2022-05 -15 15:08:14.514862+08
3 | 2022-05-15 16:02:16.709364+08 | 2022-05-15 16:02:17.643546+08
4 | 2022-05-15 17:02:17.617386+08 | 2022-05-15 17:02:20.626552+08
............
43 | 2021-07-10 07:02:36.418031+08 | 2021-07-10 07:02:37.380217 +08
44 | 2021-08-05 00:21:09.062745+08 | 2021-08-05 00:21:10.33016+08
(44 rows)`

1. You can choose to create a snapshot manually. This command requires the user to have sysadmin privileges. Or directly select an existing snapshot in the database.

`postgres=# select create_wdr_snapshot();

create_wdr_snapshot

WDR snapshot request has been submitted
(1 row)
postgres=# select * from snapshot.snapshot;
snapshot_id | start_ts | end_ts
-------------+----------- --------------------+---------------------------- --
1 | 2022-05-15 15:02:15.990876+08 | 2022-05-15 15:02:18.555272+08
2 | 2022-05-15 15:08:12.470218+08 | 2022-05-15 15 :08:14.514862+08
3 | 2022-05-15 16:02:16.709364+08 | 2022-05-15 16:02:17.643546+08
4 | 2022-05-15 17:02:17.617386+08 | 2022-05-15 17:02:20.626552+08
............
43 | 2021-07-10 07:02: 36.418031+08 | 2021-07-10 07:02:37.380217+08
44 | 2021-08-05 00:21:09.062745+08 | 2021-08-05 00:21:10.33016+08
45 | 00:39:43.777341+08 | 2021-08-05 00:39:44.760498+08 //This snapshot was generated manually just now
(45 rows)`

1. Perform the following steps to generate a node-level wdr report

1) Query the pgxc_node_name parameter value
`[omm@node1 ~]$ gsql -p 26000 postgres -c "show pgxc_node_name"

pgxc_node_name

dn_6001_6002
(1 row)`

2) \a \t \o The server file path generates a formatted performance report
such as
postgres=# \a \t \o /home/omm/wdr_sarah.html
Output format is unaligned.
Showing only tuples.
The parameters involved in the above command are described as follows:
\a: Toggle unaligned mode.
\t: Toggle output of field name info and line count footer.
\o: Send all query results to the server file.
Server file path: The storage path of the generated performance report file. The user needs to have read and write permissions for this path.

3) Write data to the performance report wdr_sarah.html.
gsql -p 26000 -d postgres
select generate_wdr_report(snapshot id1,snapshot id2,'all','node','pgxc_node_name parameter value');
for example
postgres=# select generate_wdr_report(44,45,'all','node', 'dn_6001_6002');
The corresponding wdr report is generated in the directory and viewed on
the browser:
The awr report of opengauss is similar to oracle's wdr, with resource consumption, wait events, TOPSQL, and parameter settings.
Snapshot related parameters

enable_wdr_snapshot
parameter description: Whether to enable the database monitoring snapshot function.
wdr_snapshot_retention_days
parameter description: The retention days of database monitoring snapshot data in the system. When the number of snapshots generated during the database running process exceeds the maximum number of snapshots allowed to be generated within the retention days, the system will clean up the snapshot data with the smallest snapshot_id every wdr_snapshot_interval interval.
wdr_snapshot_interval
parameter description: The time interval for the background thread Snapshot to automatically perform snapshot operations on database monitoring data.
wdr_snapshot_query_timeout
parameter description: When the system executes the database monitoring snapshot operation, set the execution timeout time of the SQL statement related to the snapshot operation. If the statement is not executed and the result is returned after the set time, the snapshot operation fails.