Hologres:Manage queries

Hologres is compatible with PostgreSQL. You can view the execution information about queries on a Hologres instance by using the HG_STAT_ACTIVITY (PG_STAT_ACTIVITY) view. This helps you analyze query execution information and diagnose the related issues. The query management feature involves the following operations:

• Use the HG_STAT_ACTIVITY (PG_STAT_ACTIVITY) view: View the runtime information about SQL statements to better manage SQL statements.

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

• Troubleshoot lock issues: Check whether an SQL statement of an active query acquires a lock or is waiting for a lock to be released.

• Cancel queries: Cancel a query that does not meet expectations by executing statements.

• Change the timeout period of active queries: Change the timeout period of active queries to prevent deadlocks.

• Change the timeout period of idle queries: Change the timeout period of idle queries to prevent deadlocks.

• Query slow query logs: Query slow query logs to diagnose, analyze, and optimize slow queries or failed queries.

• FAQ: Resolve the issue if the error message ERROR: canceling statement due to statement timeout is reported.

This section describes how to view active queries by using SQL statements.

1. View the current active query and the execution stage and resource consumption of the current active query.

   Note

   Superusers of a Hologres instance can query the execution information about SQL statements that are submitted by all users of the Hologres instance. Users other than Superusers can query the execution information about only SQL statements that are submitted by themselves.

   -- Syntax for Hologres V2.0 and later
   SELECT query,state,query_id,transaction_id,running_info, extend_info 
   FROM hg_stat_activity 
   WHERE state = 'active' 
   AND     backend_type = 'client backend'
   AND     application_name != 'hologres'
   
   -- Syntax for Hologres V1.3 and earlier
   SELECT query,state,pid
   FROM pg_stat_activity 
   WHERE state = 'active' 
   AND     backend_type = 'client backend'
   AND     application_name != 'hologres'

   The following result is returned:

   -------------------------------------------------------------------------------
   query          | insert into test_hg_stat_activity select i, (i % 7) :: text, (i % 1007) from generate_series(1, 10000000)i;
   state          | active
   query_id       | 100713xxxx
   transaction_id | 100713xxxx
   running_info   | {"current_stage" : {"stage_duration_ms" :5994,
                                         "stage_name" :"EXECUTE" },
                      "engine_type" :"{HQE,PQE}",
                      "fe_id" :1,
                      "warehouse_id" :0 }
   extend_info    | {"affected_rows" :9510912,
                     "scanned_rows" :9527296 }

2. View running queries that are sorted based on the CPU utilization.

   -- Syntax for Hologres V2.0 and later
   SELECT query,((extend_info::json)->'total_cpu_max_time_ms')::text::bigint AS cpu_cost,state,query_id,transaction_id 
   FROM hg_stat_activity WHERE state = 'active' ORDER BY 2 DESC;

   The following result is returned:

   ---------------------------------------------------------------------------------
   query          | select xxxxx
   cpu_cost       | 523461
   state          | active
   query_id       | 10053xxxx
   transaction_id | 10053xxxx
   ---------------------------------------------------------------------------------
   query          | insert xxxx
   cpu_cost       | 4817
   state          | active
   query_id       | 1008305xxx
   transaction_id | 1008305xxx

3. View running queries that are sorted based on the memory usage.

   -- Syntax for Hologres V2.0 and later
   SELECT query,((extend_info::json)->'total_mem_max_bytes')::text::bigint AS mem_max_cost,state,query_id,transaction_id 
   FROM hg_stat_activity WHERE state = 'active' ORDER BY 2 DESC;

   The following result is returned:

   ---------------------------------------------------------------------------------
   query          | update xxxx;
   mem_max_cost   | 5727634542
   state          | active
   query_id       | 10053302784827629
   transaction_id | 10053302784827629
   ---------------------------------------------------------------------------------
   query          | select xxxx;
   mem_max_cost   | 19535640
   state          | active
   query_id       | 10083259096119559
   transaction_id | 10083259096119559

4. View queries that are running on the current instance and take a long period of time.

   -- Syntax for Hologres V2.0 and later
   SELECT current_timestamp - query_start AS runtime, datname::text, usename, query, query_id
       FROM hg_stat_activity
       WHERE state != 'idle'
       AND backend_type = 'client backend'
       AND application_name != 'hologres'
       ORDER BY 1 DESC;
   
   -- Syntax for Hologres V1.3 and earlier
   SELECT current_timestamp - query_start AS runtime, datname::text, usename, query, pid
       FROM pg_stat_activity
       WHERE state != 'idle'
       AND backend_type = 'client backend'
       AND application_name != 'hologres'
       ORDER BY 1 DESC;

   The following result is returned:

   runtime          |  datname  | usename  | query_id    |      current_query
   -----------------+----------------+----------+------------------------------------
    00:00:24.258388 | holotest  | 123xxx   | 1267xx | UPDATE xxx;
    00:00:1.186394  | testdb    | 156xx    | 1783xx | select xxxx;

   The result indicates that the UPDATE query takes a long period of time and has been running for 24 seconds.

This section describes how to use HoloWeb to view and manage active queries in a visualized way.

1. Log on to the HoloWeb console. For more information, see Connect to HoloWeb and perform queries.

2. In the top navigation bar, click Diagnostics and Optimization.

3. In the left-side navigation pane, choose Management for Information about Active Queries > Active Query Tasks.

4. On the Active Query Tasks page, click Query to view and manage the active queries on the current instance.

   The following table describes the parameters in the queried result.

   Parameter	Description
   Query Start	The time when the query starts to be executed.
   Runtime	The period of time for which the query has been running.
   PID	The process identifier (PID) of the query.
   Query	The SQL statement that is executed to query data.
   State	The status of the current 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: This value indicates that the process is not a user connection process. In most cases, this state is returned for a maintenance process at the system backend and can be ignored.
   User Name	The username that is used to create the current connection.
   Application	The type of the application that initiates the query.
   Client Address	The IP address of the client that sends the query request.

   If a query does not complete after a long period of time, you can click Cancel in the Operation column to cancel the query. You can also select multiple queries and click Batch Cancel to cancel multiple queries at a time.

5. (Optional) Click Details in the Operation column of a query to view the details of the query.

   On the Details page, you can perform the following operations:

   • Copy: Copy the SQL statement that is executed for the query.

   • Format: Format the SQL statement that is executed for the query.

This section describes how to cancel queries that do not meet expectations by executing statements.

• Cancel a single query:

  SELECT pg_cancel_backend(<pid>);

• Cancel multiple queries at a time:

  SELECT pg_cancel_backend(pid)
          ,query
          ,datname
          ,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'