Hologres:Manage queries

Hologres is compatible with PostgreSQL. You can use the HG_STAT_ACTIVITY (PG_STAT_ACTIVITY) view to view the runtime information of queries in an instance for analysis and diagnosis. The specific operations are as follows:

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

• Manage active queries in HoloWeb: View and manage active queries visually using HoloWeb.

• Troubleshoot locks: Check for locks or lock contention that affect a SQL statement.

• Cancel queries: Use command statements to cancel unexpected queries.

• Set the timeout for active queries: Set a timeout for active queries to prevent deadlocks.

• Set the timeout for idle queries: Set a timeout for idle queries to prevent deadlocks.

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

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

To find active queries using SQL, use the following SQL statements:

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

   Note

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

   -- 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'
   
   -- 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 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 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 long-running queries:

   -- 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 result shows the UPDATE statement is taking a long time, having run for 24 seconds without completing.

Use HoloWeb to view and manage active queries visually.

1. Go to HoloWeb.

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

3. In the left navigation menu, choose Active Quert Tasks.

4. Click Search to view and manage active queries for the current instance.

   The query results list contains the following information:

   Parameter	Description
   Query Start	The time when the query starts.
   Runtime	The duration of the query.
   PID	The process ID of the query service.
   Query	The executed SQL statement.
   State	The current state of the connection. Common states include: active: The connection is active. idle: The component is not in use. idle in transaction: The connection is idlewithin a long transaction. idle in transaction (Aborted): The connection is idle in a failed transaction. \N: The state is empty. This typically indicates a system background maintenance process, not a user connection. You can ignore this.
   User Name	The username for the current connection.
   Application	The application type of the query.
   Client Address	The IP address of the client.

   If a query runs for too long, you can click Cancel in the Actions column to terminate it. To cancel multiple queries at once, select them and click Batch Cancel.

5. (Optional) To view the details of a target query, click Details in the Actions column.

   On the Details page, do the following:

   • Copy the SQL statement.

   • Format the executed SQL statement.

If a query does not perform as expected, cancel it using the following SQL commands.

• Cancel a query:

  SELECT pg_cancel_backend(<pid>);

• Cancel multiple 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'