Query profiling statistics - AnalyticDB for PostgreSQL - Alibaba Cloud Documentation Center

AnalyticDB for PostgreSQL provides the query profiling statistics feature to collect and record statistics during query execution. You can use system views to check for slow query statements.

In AnalyticDB for PostgreSQL, a query consists of multiple operators that are executed in sequence. In specific cases, query statistics are required to identify and analyze query exceptions. The query statistics include the amount of time consumed by an operator, the number of input rows, the number of output rows, and the usage of memory and I/O resources. AnalyticDB for PostgreSQL provides query profiling statistics to help you identify issues from the execution information of operators and then troubleshoot system failures.

Note This feature can be used for AnalyticDB for PostgreSQL instances of V6.3.8.2 or later. For information about how to update the minor engine version of an instance, see Update the minor engine version.

Enable the query profiling statistics feature

By default, the query profiling statistics feature is disabled. You can use the queryprofile.enable parameter to enable or disable this feature.

Query the status of the query profiling statistics feature.
```
SHOW queryprofile.enable;
```
Enable the query profiling statistics feature for a session.
```
SET queryprofile.enable = ON;
```
Disable the query profiling statistics feature for a session.
```
SET queryprofile.enable = OFF;
```
Enable the query profiling statistics feature for a database.
```
ALTER DATABASE <dbname> SET queryprofile.enable = ON;
```

To enable or disable the query profiling statistics feature for an instance, Submit a ticket to contact technical support personnel.

Query the query profiles

After the query profiling statistics feature is enabled, you can query the basic and execution process information of queries being executed and historical queries from query profile views.

Query profile views

queryprofile.query_exec_history: displays the basic information of historical queries.
queryprofile.query_exec_status: displays the basic information of queries being executed.
queryprofile.node_exec_history: displays the execution process information of historical queries.
queryprofile.node_exec_status: displays the execution process information of queries being executed.

The queryprofile.query_exec_history and queryprofile.query_exec_status views share the same schema. The following table describes the fields involved in these views.


Field	Data type	Description
queryid	int8	The ID of the query. It is a unique identifier of the query.
sessid	integer	The ID of the session that contains the query.
commandid	integer	The command ID of the query in the session.
starttime	timestamptz	The start time of the query.
runtime	float8	The execution duration of the query. Unit: seconds.
stmt_text	text	The SQL statement text of the query.

The queryprofile.node_exec_history and queryprofile.node_exec_status views share the same schema. The following table describes the fields involved in these views.


Field	Data type	Description
queryid	int8	The ID of the query that contains the operator. It is a unique identifier of the query.
stmtid	int8	The SQL statement ID corresponding to the ID of the query that contains the operator.
sessid	integer	The ID of the session that contains the operator.
commandid	integer	The command ID of the query that contains the operator in the session.
nodeid	integer	The ID of the operator in the query execution plan.
sliceid	integer	The slice ID of the operator in the query execution plan.
nodetypeid	integer	The type ID of the operator.
nodename	text	The name of the operator.
tuplesout	int8	The number of rows output by the operator during query execution.
tuplesin	int8	The number of rows input by the operator during query execution.
tuplesplan	int8	The number of rows input by the operator in the query execution plan.
execmem	float8	The amount of memory allocated by the executor to the operator.
workmem	float8	The amount of memory used by the operator.
starttime	timestamptz	The execution start time of the operator.
endtime	timestamptz	The execution end time of the operator.
duration	float8	The execution duration of the operator. Unit: seconds. Note This field is the amount of time consumed when the operator is being executed, not the interval between the execution start time and end time of the operator. The preceding interval may contain the execution duration of underlying operators.
diskreadsize	int8	The amount of data that the operator reads from disks.
diskreadtime	float8	The amount of time consumed for the operator to read data from disks. Unit: seconds.
netiosize	int8	The amount of data transmitted between nodes.
netiotime	float8	The amount of time consumed to transmit data between nodes. Unit: seconds.

In the queryprofile.node_exec_history and queryprofile.node_exec_status views, each row displays the basic execution information and resource consumption of an operator during query execution. You can analyze the information in the views to identify exceptions and optimize the query execution process.

Query the profiles of queries being executed

Example 1

Query the basic information of queries being executed.

SELECT * FROM queryprofile.query_exec_status;

Example 2

Query the execution process information of queries being executed.

SELECT * FROM queryprofile.node_exec_status;

Query the profiles of historical queries

Example 1

Query the basic information of historical queries.

SELECT * FROM queryprofile.query_exec_history;

Example 2

Query the execution process information of historical queries.

SELECT * FROM queryprofile.node_exec_history;

Change the update frequency of profile views of queries being executed

AnalyticDB for PostgreSQL provides the queryprofile.refresh_interval parameter to determine the update frequency of queryprofile.query_exec_status and queryprofile.node_exec_status. The value of this parameter indicates the number of rows processed by operators between two updates of statistics.

The default value of queryprofile.refresh_interval is 1000. By default, statistics are updated once every 1,000 rows of data being read by operators. A value of 0 indicates that the query profiling statistics feature is disabled.

You can execute the following statement to query the update frequency of query profile views:

SHOW queryprofile.refresh_interval;

Sample query result:

 queryprofile.refresh_interval
-------------------------------
 1000
(1 row)

Note To disable the query profiling statistics feature or change the data update frequency, Submit a ticket to contact technical support personnel.

Recycle historical query profiles

AnalyticDB for PostgreSQL provides the queryprofile.max_query_num and queryprofile.query_time_limit parameters to determine how to recycle historical query profiles.

queryprofile.max_query_num: the maximum number of queries to retain. The default value of this parameter is 10000. You can execute the following statement to query the maximum number of queries retained:
```
SHOW queryprofile.max_query_num;
```
queryprofile.query_time_limit: the minimum execution duration used to recycle queries. Queries whose execution duration is less than the value of this parameter are preferentially recycled. Unit: seconds. This parameter is used to clear short-running queries. The default value is 1. You can execute the following statement to query the minimum execution duration of queries retained:
```
SHOW queryprofile.query_time_limit;
```

For example, assume that the value of queryprofile.max_query_num is 10000 and the value of queryprofile.query_time_limit is 1. In this case, queries can be recycled if the number of queries exceeds 10,000 and the queries whose execution duration is less than 1 second are preferentially recycled. If no queries whose execution duration is less than 1 second exist, the earliest queries are preferentially recycled.

Note To modify the preceding parameters, Submit a ticket to contact technical support personnel.

Examples

Query profiles can be used to identify and analyze execution exceptions. If the query profiling statistics feature is enabled, the profiles of all queries are collected. You can obtain the following information from the query profiles:

Slow queries or queries being executed.
The information of operators and the number of rows input or output by each operator.
Operators that take a long time to execute.
The amount of resources occupied by a single operator, such as memory, disk I/O, and network I/O.

Perform the following operations:

Enable the query profiling statistics feature for the current session.
```
SET queryprofile.enable = ON;
```
Query the basic information of most recent 10 historical queries.
```
SELECT * FROM queryprofile.query_exec_history ORDER BY starttime DESC limit 10;
```
The following figure shows a sample query result.
Find the query profiles to be analyzed from the preceding query result. Use a value of the queryid field obtained from the queryprofile.query_exec_history view to query the execution process information of the query from the queryprofile.node_exec_history view.
```
SELECT * FROM queryprofile.node_exec_history WHERE queryid = 6902*********93;
```
The following figure shows a sample query result.
The query result shows information such as the operator name, the execution duration, the number of input rows, the number of output rows, and the resource usage. You can analyze the execution information of each operator to identify and troubleshoot performance issues.