Usage - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to use AWR, including generating and viewing global AWR reports, and connecting to Grafana to view real-time data.

Generate and view global AWR reports

Trigger snapshots
Like Oracle AWR, PolarDB for PostgreSQL(Compatible with Oracle) supports explicit triggering of performance data snapshots. The differences are that PolarDB for PostgreSQL(Compatible with Oracle) global AWR supports routine collection and allows you to set collection intervals for different data types and that performance reports can be generated without periodically triggering snapshots. For more information about metrics and collection intervals, see Data parsing.
- Trigger snapshots
```
SELECT polar_gawr_collection.snapshot();
```
  A similar output is displayed:
```
 snapshot
----------
        2
(1 row)
```
  Note Snapshot triggering is asynchronously performed. You may wait for up to 10 seconds to view a complete list of snapshots.
- View the list of snapshots
```
SELECT polar_gawr_collection.list_snapshots();
```
  A similar output is displayed:
```
          list_snapshots
----------------------------------
 (1,"2021-08-11 07:06:53.678668")
 (2,"2021-08-11 11:18:12.747412")
(2 rows)
```

Generate reports

Syntax

psql -Aqtc "SELECT polar_gawr_report.make_report()" > index.html

Output mode
We recommend that you use the psql client and add the following parameters when you execute the statement:
- -A or --no-align: displays compact output without alignment.
- -q or --quiet: displays only query results.
- -t or --tuples-only: displays only result rows, not column names.
- -c or --command (=COMMAND): executes the SQL statement once and exits.

Output functions

The report-related output functions are defined in polar_gawr_report mode in the postgres database. Only the three overloaded make_report functions are described.

Generate the default performance report of the current instance between begin_time and end_time.

polar_gawr_report.make_report(
      begin_time TIMESTAMP WITH TIME ZONE,
      end_time TIMESTAMP WITH TIME ZONE, 
      ins_id TEXT DEFAULT '', 
      comment TEXT DEFAULT '')
RETURNS TEXT

Generate the performance report of the current instance in the latest time_range. The default value of time_range is 1 hour.

polar_gawr_report.make_report(
      ins_id TEXT DEFAULT '', 
      time_range INTERVAL DEFAULT '1 hour'::INTERVAL, 
      comment TEXT DEFAULT '')
RETURNS TEXT

Generate the performance report between two specified snapshot id values. snapshot is asynchronously triggered. The default waiting time is five minutes after the report is generated, so that you can obtain complete data.
```
polar_gawr_report.make_report(
    begin_snapshot_no INTEGER, end_snapshot_no INTEGER,
    end_snapshot_delta_time INTERVAL DEFAULT '5 min',
    ins_id TEXT DEFAULT '', comment TEXT DEFAULT '')
RETURNS TEXT
```

The AWR feature provides multiple parameters to control what reports display and track the report generation process. The following table lists several important parameters, which can be defined by using the SET statement.

Table 1. Parameters
Parameter	Default value	Description
polar_gawr_report.enable_role_ro	off	Generates the reports of read-only nodes.
polar_gawr_report.log_full_error	off	Displays complete error messages when reports are generated.
polar_gawr_report.log_error_sql	off	Displays error-related SQL statements.
polar_gawr_report.log_timing	off	Displays the duration of each SQL query when reports are generated.

Examples
Only the method of using the psql client to print performance reports is described.
- Basic mode: By default, the performance report of the primary node in the last hour is generated.
```
SELECT polar_gawr_report.make_report()
```
- You can specify a time range to generate the performance report of the primary node.
```
SELECT polar_gawr_report.make_report(begin_time => '2021-08-12 19:00:00', end_time => '2021-08-12 19:30:00')"
```
- Generate reports of both the primary node and read-only nodes: By default, the reports of read-only nodes are not generated. You must add the parameter to explicitly specify that.
```
SET polar_gawr_report.enable_role_ro='on'; SELECT polar_gawr_report.make_report()
```

View global AWR reports
The global AWR feature generates reports as HTML files. You can open the files in a browser (Google Chrome is preferred) or start an HTTP server to remotely access the files. The method to use an HTTP server to remotely access HTML files:
```
 # Run the following command in the directory where index.html is located to start an HTTP server with port 7070:
nohup python -m SimpleHTTPServer 7070 &
```

Connect to Grafana to view real-time data

Download and install Grafana.
For more information, see Download Grafana. We recommend that you use Grafana 8.1.2 or later.
Add a data source.
Add the PolarDB for PostgreSQL(Compatible with Oracle) database instance as a PostgreSQL data source.
1. Start Grafana. In the left-side navigation pane, choose Configuration > Data sources.
2. On the Data sources tab, click Add data source in the upper-right corner.
Import the dashboard configuration file.
1. In the left-side navigation pane, choose + > Import.
2. On the Import page, click Upload JSON file to import the dashboard configuration file.
  Note You can click Dashboard configuration file to download the dashboard configuration file in the ZIP format. Decompress the configuration file before you can use it.