Abstract:In this article, you will learn what WDR is, how to create snapshots of performance data, and how to generate WDR reports.
This article is shared by Zhang Jingyao from The Performance Monitoring Report of WDR-gaussdb (DWS) in huawei Cloud community.
GaussDB(DWS) 8.1.1 introduces the Workload Diagnosis Report (WDR for short) function, which provides performance data within a specified period of time and presents it to users as an HTML Report. By analyzing the report, users can find exceptions, diagnose problems, and optimize performance. The report is rich in content and intuitive, and is a powerful tool for database tuning.
This paper briefly introduces the principle and usage of WDR. In this article, you will learn what WDR is, how to create snapshots of performance data, and how to generate WDR reports.
1. Introduction of WDR
WDR is a derivative of the GaussDB(DWS) database monitoring feature. The database kernel accumulates a large amount of first-hand data during its operation, such as the number of SQL executed, the number and time of accessing tables and indexes, and the running status of underlying hardware and software such as CPU and memory. Users can query the data in real time through the system view provided by the database. For example, query the PGXC_WORKLOAD_SQL_COUNT view to obtain the number of times each TYPE of SQL has been executed since the database started:
postgres=# select * from PGXC_WORKLOAD_SQL_COUNT; node_name | workload | select_count | update_count | insert_count | delete_count | ddl_count | dml_count | dcl_count -----------+--------------+--------------+--------------+--------------+--------------+-----------+-----------+--------- -- cn_5001 | default_pool | 1125616 | 1236 | 322303 | 0 | 13 | 1573048 | 102 cn_5002 | default_pool | 1128231 | 1322 | 321048 | 0 | 2 | 1575517 | 173973 cn_5003 | default_pool | 1128191 | 1130 | 309138 | 0 | 0 | 1575430 | 173980 (3 rows)By saving these first-hand performance monitoring data and conducting automated statistical analysis, we need to know how the database is running during a certain period of time, such as whether it is busy, whether there is unreasonable SQL, what abnormal events, etc., so as to provide reference for problem diagnosis and performance tuning. This is where WDR comes in.
Figure 1 WDR principle
2. Create a performance snapshot
As described in the previous chapter, monitoring data should be preserved before being analyzed. For performance reasons, the database kernel keeps various monitoring data in memory for quick updates and reads. Once the process restarts, this data will be lost. Therefore, the first thing WDR does is to save the detection data in memory to the peripheral. The saved performance monitoring data is called snapshots, and the saving process is called creating snapshots.
Be careful not to confuse “Snapshot” here with the Snapshot of the database. The latter refers to the state of the data in the Database ata point in time, while WDR creates a “snapshot” that stores the contents of the system view queried ata point in time in a dedicated table.
Snapshots created by WDR are saved in the table under DBMS_OM Schema. Each system view corresponds to a table whose name = “DBMS_om.snap_” + view name. For example, the snapshot of the PGXC_WORKLOAD_SQL_COUNT view is saved in the DBMS_om. snap_PGXC_workload_SQL_COUNT table.
WDR supports snapshot creation in two ways. One is created periodically by the background thread. The interval is specified by the wdr_snapshot_interval parameter. The default interval is once every hour. The other is to run the create_wdr_snapshot system function to create the snapshot in real time. Note that this function requires administrator rights.
postgres=# select create_wdr_snapshot();
create_wdr_snapshot
------------------------------------------
WDR snapshot request has been submitted.
(1 row)Either way, set the enable_wdr_snapshot parameter to ON.
Each time a snapshot is completed, rows are added to the snapshot related table under DBMS_OM. You can view the IDS and creation start and end time of existing snapshots in table dbMS_om. snapshot. Such as:
postgres=# select * from dbms_om.snapshot order by snapshot_id desc limit 5; snapshot_id | start_ts | end_ts -------------+-------------------------------+------------------------------- 312 | The 2021-05-29 12:36:32. 509763 + 08 | 2021-05-29 12:36:39. 938167 + 08 311 | 2021-05-29 11:36:32. 489812 + 08 | 2021-05-29 11:36:40. 23865 + 08 310 | 2021-05-29 10:36:32. 214843 + 08 | 10:36:37. 2021-05-29 (436173 + 309 | 2021-05-29 09:48:11. 587959 + 08 | 2021-05-29 09:48:18. 247602 + 08 308 | 2021-05-29 09:36:31. 687527 + 08 | 2021-05-29 09:36:36. 897983 + 08 (5 rows)For example, snapshot 312 is created from 2021-05-29 12:36:32 to 2021-05-29 12:36:39. Snapshot_id is the unique id of each snapshot.
WDR creates snapshots for multiple system views at a time. You can query the start and end time of a snapshot for a single view by using the dbMS_om. tables_snap_TIMESTAMP table. Take the snapshot whose ID is 312 as an example:
postgres=# select * from dbms_om.tables_snap_timestamp where snapshot_id=312; snapshot_id | db_name | tablename | start_ts | end_ts -------------+----------+----------------------------------+-------------------------------+---------------------------- 2021-05-29-312 | postgres | snap_pgxc_os_run_info | 12:36:32. 511503 + 08 | 2021-05-29 12:36:32. 691519 + 08 312 | Postgres | snap_pgxc_wait_events | 2021-05-29 12:36:32. 692543 + 08 | 12:36:33. 2021-05-29 (982895 + 312 | postgres | Snap_pgxc_instr_unique_sql | 2021-05-29 12:36:33. 983801 + 08 | 2021-05-29 12:36:34. 131792 + 08... (19 rows)Compared with DBMS_om. snapshot, table DBMS_om. tables_SNAP_TIMESTAMP records a more accurate snapshot time.
To avoid taking up too much space, snapshots that are too old are deleted periodically. The value ranges from 1 to 15 days by wdr_snapshot_retention_days. By default, snapshots created eight days ago are deleted. If you want to retain the snapshot data for a longer period, you need to dump the snapshot data by yourself.
3. Generate a WDR report
After a snapshot is created, you can calculate and analyze the performance monitoring data and generate a report for users to view. Different analysis angles and methods can produce different reports. Currently, WDR can compare the snapshot data of the first and second snapshots and generate a performance monitoring report (WDR Report for short) for this period.
As a distributed database, GaussDB(DWS) is composed of multiple nodes, which are classified into Coordinators (CN) and Datanodes (DN). Accordingly, WDR reports are divided into two scope types: cluster and node. The report contents of the two scopes are different. The former consists of the overall performance data of the cluster, while the latter analyzes and calculates the performance performance of a single node. For single-node scope reports, the report contents on CN and DN are also different. The report scope is specified by the user when the report is generated.
GaussDB(DWS) provides the system function generate_wDR_report () to create a WDR report. Before generating a report, determine the start and end snapshot_id. Query the DBMS_om. snapshot table and obtain the snapshot_id of the two time points in the time range you are interested in. For example, to view the performance between 2021-02-21 03:00:00 and 2021-02-21 04:00:00:
postgres=# select * from dbms_om.snapshot where start_ts > '2021-02-21 03:00:00'::timestamptz and start_ts < '2021-02-21 04:00:00'::timestamptz order by snapshot_id; snapshot_id | start_ts | end_ts -------------+-------------------------------+------------------------------- 2147 | The 2021-02-21 03:02:40. 000716 + 08 | 2021-02-21 03:03:17. 840595 + 08 2148 | 2021-02-21 03:12:39. 873876 + 08 | 2021-02-21 03:13:15. 963517 + 08 2149 | 2021-02-21 03:22:39. 875301 + 08 | 03:23:16. 2021-02-21 (659778 + 2150 | 2021-02-21 03:32:40. 857761 + 08 | 2021-02-21 03:33:18. 477795 + 08 2151 | 2021-02-21 03:42:41. 454982 + 08 | 2021-02-21 03:43:17. 977323 + 08 2152 | 2021-02-21 03:52:41. 794683 + 08 | 2021-02-21 03:53:18. 676577 + 8 (6 rows)Based on the previous query, the start and end values of snapshot_id are 2147 and 2152 respectively.
Next, determine the type of report to generate. The current version supports generating three types of WDR: Summary, Detail, and All. Abstract type only includes brief analysis and calculation results, detailed type only includes detailed index data, and all type includes all contents of summary type and detailed type.
Finally, the generate_wDR_report () function is executed to generate the WDR report. For example, to generate a summary cluster-scoped WDR report between 2021-02-21 03:00:00 and 2021-02-21 04:00:00, execute:
postgres=# select generate_wdr_report(2147, 2152, 'summary', 'cluster', '');
generate_wdr_report
--------------------------------------------------------------------------
Report summary-cluster-2147-2152-20210301125740.html has been generated.
(1 row)To generate all types of WDR reports for node CN_5001 between 2021-02-21 03:00:00 and 2021-02-21 04:00:00, do the following:
postgres=# select generate_wdr_report(2147, 2152, 'all', 'node', 'cn_5001');
generate_wdr_report
----------------------------------------------------------------------
Report all-cn_5001-2147-2152-20210301125906.html has been generated.
(1 row)The $report file name has been generated message is displayed. The report file is stored in the pg_log directory of the current CN node by default and can be opened using a web browser:
Figure 2. WDR report
Each part in the report is expanded by default. If you double click the title, relevant parts will collapse and the “-” before the title will become “+”, as shown in Figure 3:
Figure 3 Shows the collapsed WDR report style
For details about the indicators in the report, see the product documentation. How to use WDR reports to analyze and locate problems will be discussed in subsequent articles.
4. Precautions
As mentioned earlier, the WDR feature relies on various database kernel monitoring capabilities to provide data. For performance reasons, some kernel monitoring functions are turned off by default and need to be turned on with the GUC parameter. If this function is not enabled, the query result of the corresponding performance view is empty and snapshots and WDR reports cannot be generated. To get the full WDR report, set the GUC parameters in the following table to the values in the last column:
Want to know more information about GuassDB(DWS), welcome to wechat search “GaussDB DWS” follow the wechat public account, and share with you the latest and most complete PB series warehouse black technology, background can also obtain a lot of learning materials oh ~
Click follow to learn about the fresh technologies of Huawei Cloud