Hologres:Manage queries

Hologres is compatible with PostgreSQL. You can view the runtime information of queries in an instance using the HG_STAT_ACTIVITY (PG_STAT_ACTIVITY) view. This helps you analyze and diagnose running SQL statements. The operations include the following:

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

• Manage active queries in the HoloWeb console: View and manage active queries in the HoloWeb console.

• Troubleshoot locks: Use active queries to check whether the current SQL statement holds a lock or is blocked by one.

• Stop a query: Use a command to stop a query that does not meet expectations.

• Modify the timeout period for active queries: Modify the timeout period for active queries to prevent deadlocks.

• Modify the timeout period for idle queries: Modify the timeout period for idle queries to prevent deadlocks.

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

• FAQ: Learn the causes of and solutions to the ERROR: canceling statement due to statement timeout error.

If you prefer to query active queries using SQL, run the following SQL statements:

1. View the current active queries and their execution stages and resource consumption:

   Note

   Superusers can view the SQL runtime information of all users. Non-superusers can only view their own SQL runtime information.

   -- 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'

   Sample result:

   -------------------------------------------------------------------------------
   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. Sort the currently running queries by CPU consumption:

   -- 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;

   Sample result:

   ---------------------------------------------------------------------------------
   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. Sort the currently running queries by memory consumption:

   -- 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;

   Sample result:

   ---------------------------------------------------------------------------------
   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 the long-running queries in the current instance:

   -- 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;

   Sample result:

   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 update statement has been running for 24 seconds and has not finished.

You can view and manage active queries in the HoloWeb console.

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

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

3. In the navigation pane on the left, choose Management for Information About Active Queries > Active Query Tasks.

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

   The query result list contains the following information:

   Parameter	Description
   Query Start	The time when the query started.
   Runtime	The running time of the query.
   PID	The ID of the query service process.
   Query	The SQL statement that is run.
   State	The status of the current connection. Common statuses are: active: The connection is active. idle: Idle. idle in transaction: The connection is idle within a long-running transaction. idle in transaction (Aborted): The connection is idle within a failed transaction. \N: The status is empty. This indicates a process that is not a user connection. It is usually a background maintenance process of the system and can be ignored.
   User Name	The username of the current connection.
   Application	The type of the query application.
   Client Address	The IP address of the client that initiated the query.

   If a query runs for a long time, click Cancel in the Actions column of the query to stop it. You can also select multiple queries and click Batch Cancel.

5. (Optional) Click Details in the Actions column of the target query to view its details.

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

   • Copy: Copy the SQL statement that is run.

   • Format: Format the SQL statement that is run.

To stop a query that does not meet expectations, run the following commands.

• Stop a single query:

  SELECT pg_cancel_backend(<pid>);

• Stop queries in a batch:

  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'