This topic describes how to diagnose and manage SQL queries on a Hologres instance.

Overview

Hologres is compatible with PostgreSQL and allows you to view the execution information about SQL queries on a Hologres instance by querying the pg_stat_activity view. This helps you analyze and diagnose SQL query execution. The query management feature involves the following operations:

Query the pg_stat_activity view

pg_stat_activity is a helpful system view. You can use pg_stat_activity to analyze and diagnose SQL queries that are being executed and troubleshoot issues. To query the execution information about SQL queries on a Hologres instance, execute the following statement. pg_stat_activity does not show all network connections. Specific backend processes do not occupy connections and therefore do not appear in the pg_stat_activity.
select * from pg_stat_activity ;
The following table describes the parameters in the query results of the pg_stat_activity view.
Parameter Description
datid The object identifier (OID) of the connected database at the Hologres backend.
datname The name of the connected database at the Hologres backend.
pid The ID of a process at the Hologres backend.
usesysid The OID of the user that is logged on to the Hologres backend.
usename The username of the current connection.

holo_admin is the username of a built-in service account in Hologres. The connection that is automatically created by using this username is a PostgreSQL connection. This connection is required. If the number of connections to your Hologres instance does not reach the upper limit, you do not need to optimize this connection. For information about how to query the number of connections, see Query connection information by using SQL statements.

application_name The type of the application on the client.
client_addr The IP address of the client.

The displayed IP address may have been resolved and may not be the actual IP address of the client.

client_hostname The hostname of the client.
client_port The port number of the client.
backend_start The start time of the backend process.
xact_start The start time of the current active transaction of the process.
  • If no transactions are active, this parameter is empty.
  • If the current query is the first transaction of the process, the value of this parameter is the same as the value of the query_start parameter.
query_start The start time of the current active query. If the current connection is not active, the value of this parameter is the start time of the last query.
state_change The time when the state of the connection was last changed.
wait_event_type The type of the event for which the backend is waiting. If the backend is not waiting for an event, the value of this parameter is NULL. Valid values:
  • LWLock: The backend is waiting for a lightweight lock.
  • Lock: The backend is waiting for a heavyweight lock. The wait_event parameter indicates the type of the lock for which the backend is waiting.
  • BufferPin: The server process is waiting to access a data buffer while no other process is checking the data buffer.
  • Activity: The server process is idle. This may be a system process that is waiting to be run in the main processing loop.
  • Extension: The server process is in an extension module and is waiting to be run.
  • Client: The server process is waiting for a query from a user application. In addition, the server is expecting an activity that is unrelated to its internal processing to happen.
  • PC: The server process is waiting for an activity of another process on the server.
  • Timeout: The server process is waiting for a timeout.
  • IO: The server process is waiting for the completion of an I/O.
wait_event The name of the event for which the backend is waiting. If the backend is not waiting for an event, the value of this parameter is NULL.
state The state of the connection. Valid values:
  • active: The connection is active.
  • idle: The connection is idle.
  • idle in transaction: The connection is idle in a long-running transaction.
  • idle in transaction (aborted): The connection is idle in a failed transaction.
  • \N: The value is empty, which indicates that the process is not a user connection. This is usually a maintenance process at the system backend and can be ignored.
backend_xid The identifier of the top-level transaction at the Hologres backend.
backend_xmin The xmin scope of the current backend.
query The last query that was run at the backend. If the value of the state parameter is active, the value of this parameter is the query that is being run. Otherwise, the value of this parameter is the query that was last run.
backend_type The type of the current backend. Supported types include autovacuum launcher, autovacuum worker, logical replication launcher, logical replication worker, parallel worker, background writer, client backend, checkpointer, startup, walreceiver, walsender, and walwriter. Backend execution components such as Panel Quality Engineering (PQE) are also supported.

Use HoloWeb to view and manage active queries in a visualized way

You can use HoloWeb to view and manage active queries in a visualized way.
  1. Log on to the HoloWeb console. For more information, see HoloWeb quick start.
  2. In the top navigation bar, click Diagnostics and Optimization.
  3. In the left-side navigation pane, click Active Query.
  4. On the Active Query page, view and manage the active queries on the current instance. For example, you can click Cancel in the Operation column corresponding to a query to cancel the query.
  5. Optional. To view the details of a query, click Details in the Operation column corresponding to the query. In the Details dialog box, you can click Copy to copy the SQL statements that are specified for the query.

Query the execution information about SQL statements

Superusers of a Hologres instance can query the execution information about SQL statements that are submitted by all users of the Hologres instance. RAM users can query the execution information about SQL statements that are submitted only by themselves. For more information about the parameters in the following statements, see the parameter descriptions in the "Query the pg_stat_activity view" section.
  1. To query the execution information about SQL statements that are submitted by a user of the current Hologres instance, execute one of the following statements:
    SELECT datname::text,usename,query,pid::text,state FROM pg_stat_activity;
  2. To query the information about SQL statements that are submitted by a user and are being executed, execute the following statement:
    SELECT datname::text,usename,query,pid::text,state
       FROM pg_stat_activity
       WHERE state != 'idle' ;
  3. To query the information about time-consuming SQL statements that are being executed, execute one of the following statements:
    SELECT current_timestamp - query_start as runtime, datname::text, usename, query, pid::text
        FROM pg_stat_activity
        WHERE state != 'idle'
        order by 1 desc;
    The following code shows a sample result. In this example, an UPDATE statement is time-consuming.
    runtime          | datname   | usename  |       current_query           | pid
    -----------------+-----------+----------+-------------------------------+-------------
     00:00:24.258388 | holotest  | 123xxx   |  UPDATE holo_order            |1267xx
                                                  : set gmt = now(),
                                                  : trade_id = $1,
                                                  : trade_create_time = $2;
     00:00:1.186394  | testdb    | 156xx    |  SELECT * FROM oder;          |1783xx 
    (2 rows)
  4. Cancel queries.
    If queries do not meet your expectations, you can cancel them by using one of the following methods:
    • Cancel the queries that use a specific connection.
      select pg_cancel_backend(<pid>);
    • Cancel multiple queries at a time.
      SELECT pg_cancel_backend(pid)
              ,query
              ,datname::text
              ,usename
              ,application_name
              ,client_addr
              ,client_port
              ,backend_start
              ,state
      FROM    pg_stat_activity
      WHERE   length(query) > 0
      AND     pid != pg_backend_pid()
      AND     backend_type = 'client backend'
      AND     application_name != 'hologres'
      AND     usename != 'holo_admin'
      AND     query not like '%pg_cancel_backend%';

Change the timeout period of active queries

Hologres allows you to change the timeout period of active queries by executing an SQL statement.

  • Sample code
    set statement_timeout = <time>;
  • Parameters
    time: the timeout period of active queries. Valid values: 0 to 2147483647. Unit: milliseconds. If you want to append a unit to the specified time value, enclose the value and unit in single quotation marks (' '). Otherwise, an error is returned. The current default timeout period is 10 hours. The settings of the timeout period take effect only for queries in the current session.
    Note The SET statement_timeout = <time> statement takes effect only when this statement is executed along with the SQL statements for which you want to change the timeout period.
  • Examples
    • Set the timeout period to 5,000 minutes. If you want to append a unit to the specified time value, enclose the value and unit in single quotation marks (' ').
      set statement_timeout = '5000min' ; 
      select * from tablename;
    • Set the timeout period to 5,000 ms.
      set statement_timeout = 5000 ; 
      select * from tablename;

Change the timeout period of idle queries

The idle_in_transaction_session_timeout parameter specifies the timeout behavior after a transaction becomes idle. If you do not set this parameter, a transaction that times out is not rolled back. As a result, deadlocks may occur during queries. Hologres allows you to change the timeout period of idle queries by executing an SQL statement.

  • Scenarios
    To prevent deadlocks during queries, you must specify the timeout period of idle queries. For example, the following code starts a transaction but does not commit it by using the COMMIT statement. This results in a transaction leak that eventually causes a database-level deadlock and affects the normal use of the database.
    begin; 
    select * from t;
    You can resolve the issue by setting the idle_in_transaction_session_timeout parameter to specify the timeout period. If a transaction has not been committed or rolled back after its connection is idle for a period longer than the timeout period specified by the idle_in_transaction_session_timeout parameter, Hologres automatically rolls back the transaction and closes the connection.
  • Sample code
    -- Change the timeout period for idle transactions in the current session.
    set idle_in_transaction_session_timeout=<time>;
    
    -- Change the timeout period for idle transactions in a database.
    alter database db_name set idle_in_transaction_session_timeout=<time>;
  • Parameters
    time: the timeout period of active queries. Valid values: 0 to 2147483647. Default unit: milliseconds. If you want to append a unit to the specified time value, enclose the value and unit in single quotation marks (' '). Otherwise, an error is returned. In Hologres V0.10 and earlier, the default value of this parameter is 0. This means that idle transactions are not automatically rolled back. In Hologres V1.1, the default value of this parameter is changed to 600000, which indicates 10 minutes. If a transaction is idle for more than 10 minutes, the transaction is rolled back.
    Note If the timeout period is too short, transactions that are in use may be rolled back by mistake. We recommend that you set an appropriate timeout period for idle transactions.
  • Examples
    Set the timeout period to 300,000 ms.
    -- Change the timeout period for idle transactions in the current session.
    set idle_in_transaction_session_timeout=300000;
    
    -- Change the timeout period for idle transactions in a database.
    alter database db_name set idle_in_transaction_session_timeout=300000;

View slow query logs

In Hologres V0.10 and later, you can view slow query logs. For more information, see Query and analyze slow query logs.