Manage queries

Hologres is compatible with PostgreSQL. Use the hg_stat_activity (pg_stat_activity) view to monitor query runtime in an instance. This topic covers the following operations:

• hg_stat_activity (pg_stat_activity) view: View SQL runtime information.

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

• Troubleshoot locks: Identify whether a SQL statement holds or is blocked by a lock.

• Terminate a query: Terminate underperforming queries.

• Modify the timeout for an active query: Change the execution timeout to prevent deadlocks.

• Modify the timeout for an idle query: Change the idle query timeout to prevent deadlocks.

• Query slow query logs: Diagnose and optimize slow or failed queries.

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

Use the following SQL statements to view active queries:

1. View active queries, their execution stages, and resource consumption.

   Note

   Superusers can view runtime information for all users. Other users 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.

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

   -- 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 in the current instance.

   -- 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;
   
   -- 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 without completing.

View and manage active queries in the HoloWeb console.

1. Log on to the HoloWeb console. Connect to HoloWeb and run 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 Search to view and manage active queries for the current instance.

   The results include the following fields:

   Parameter	Description
   Query start	When the query started.
   Runtime	Execution duration.
   PID	Process ID of the backend handling the query.
   Query	SQL statement being executed.
   State	Connection state. Common values: active: Query is running. idle: Connection is idle. idle in transaction: Idle within a transaction. idle in transaction (Aborted): Idle within a failed transaction. \N: Null state, typically a system background process. Can be ignored.
   User name	Username of the current connection.
   Application	Application that initiated the query.
   Client address	Client IP address.

   To terminate a long-running query, click Cancel in its Actions column. You can also select multiple queries and click Batch Cancel.

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

   On the Details page, you can:

   • Copy: Copy the SQL statement.

   • Format: Format the SQL statement.

Terminate underperforming queries with the following commands.

• Terminate a single query:

  SELECT pg_cancel_backend(<pid>);

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