Hologres:User guide on Serverless Computing

Specify the maximum amount of serverless computing resources that are allocated to an SQL statement

• You can run the following code to specify the maximum amount of serverless computing resources that can be allocated to each SQL statement.

  -- Specify the maximum number of CUs of serverless computing resources that can be allocated to each SQL statement. The default value is 512. 
  SET hg_experimental_serverless_computing_max_cores = 512;
  
  -- Reset the configurations.
  reset hg_experimental_serverless_computing_max_cores;

• Examples

  -- Use serverless computing resources to execute an SQL statement.
  SET hg_computing_resource = 'serverless';
  
  -- Specify that up to 32 CUs of serverless computing resources can be allocated to the SQL statement.
  SET hg_experimental_serverless_computing_max_cores = 32;
  
  -- Execute the SQL statement.
  INSERT INTO sink_tbl SELECT * FROM source_tbl;
  
  -- Reset the configurations.
  reset hg_computing_resource;
  reset hg_experimental_serverless_computing_max_cores;

Specify the amount of serverless computing resources to be allocated to an SQL statement

The system automatically estimates the value of the hg_experimental_serverless_computing_required_cores parameter based on the complexity of SQL statements. This helps balance the serverless computing resource utilization and SQL statement execution duration. If you want to allocate more serverless computing resources to an SQL statement, you can specify the hg_experimental_serverless_computing_required_cores parameter.

• You can run the following code to specify the amount of serverless computing resources to be allocated to an SQL statement.

  -- The default value is 0, which indicates that the amount of serverless computing resources allocated to the SQL statement is based on system estimation results.
  SET hg_experimental_serverless_computing_required_cores = XX;
  
  -- Reset the configurations.
  reset hg_experimental_serverless_computing_required_cores;

• Examples

  -- Use serverless computing resources to execute an SQL statement.
  SET hg_computing_resource = 'serverless';
  
  -- Allocate 96 CUs of serverless computing resources to the SQL statement.
  SET hg_experimental_serverless_computing_required_cores = 96;
  
  -- Execute the SQL statement.
  INSERT INTO sink_tbl SELECT * FROM source_tbl;
  
  -- Reset the configurations.
  reset hg_computing_resource;
  reset hg_experimental_serverless_computing_required_cores;

Specify the priority of allocating serverless computing resources to an SQL statement

• You can run the following code to specify the priority of allocating serverless computing resources to an SQL statement.

  -- Priority values range from 1 to 5. A larger value indicates a higher priority of allocating resources. The priority does not affect the amount of resources allocated.
  -- The default priority value is 3.
  SET hg_experimental_serverless_computing_query_priority to 5;
  -- Reset the configurations.
  reset hg_experimental_serverless_computing_query_priority;

• Examples

  -- Use serverless computing resources to execute an SQL statement.
  SET hg_computing_resource = 'serverless';
  
  -- Set the priority of allocating resources to the SQL statement to 5.
  SET hg_experimental_serverless_computing_query_priority to 5;
  
  -- Execute the SQL statement.
  INSERT INTO sink_tbl SELECT * FROM source_tbl;
   
  -- Reset the configurations.
  reset hg_computing_resource;
  reset hg_experimental_serverless_computing_query_priority;

Specify serverless computing resources for all SQL statements that are initiated by a user or role

You can run the following code to specify serverless computing resources for all SQL statements that are initiated by a user or role.

-- Use serverless computing resources to execute SQL statements that are initiated by a user in a database.
ALTER USER <user_name> IN DATABASE <db_name> SET hg_computing_resource = 'serverless';

Enable all SQL statements in a specific query queue to be executed by using serverless computing resources

You can execute the following code to enable all SQL statements in a specific query queue to be executed by using serverless computing resources. For more information about how to configure a query queue, see Query queue (beta).

• General-purpose instances

  -- Enable all queries in a specific query queue to be executed by using serverless computing resources.
  CALL hg_set_query_queue_property('<query_queue_name>', 'computing_resource', 'serverless');
  
  -- Optional. Specify the priority of using serverless computing resources for queries in a specific query queue. Valid values: 1 to 5. Default value: 3.
  CALL hg_set_query_queue_property('<query_queue_name>', 'query_priority_when_using_serverless_computing', '<priority>');

• Virtual warehouse instances

  -- Enable all queries in a specific query queue to be executed by using serverless computing resources.
  CALL hg_set_query_queue_property('<warehouse_name>', '<query_queue_name>', 'computing_resource', 'serverless');
  
  -- Optional. Specify the priority of using serverless computing resources for queries in a specific query queue. Valid values: 1 to 5. Default value: 3.
  CALL hg_set_query_queue_property('<warehouse_name>', '<query_queue_name>', 'query_priority_when_using_serverless_computing', '<priority>');

View the metrics related to the use of serverless computing resources for an instance

You can perform the following steps to view the Serverless Computing Longest Active Query Time(milliseconds), Serverless Computing Queue Size(count), and Serverless Computing Quota Usage(%) metrics. If necessary, you can further configure related alert rules. For more information, see CloudMonitor.

1. Log on to the Hologres console.

2. In the top navigation bar, select the region in which your instance resides.

3. In the left-side navigation pane, click Instances.

4. On the Instances page, click the name of the desired instance to go to the details page.

   You can also find the desired instance and click Manage in the Actions column to go to the details page.

5. In the left-side navigation tree of the details page, click Monitoring Information.

Query the status of SQL tasks that use serverless computing resources

• Query the SQL tasks that are running by using serverless computing resources.

  SELECT
      *,
      (running_info::json) ->> 'computing_resource' AS computing_resource,
      (running_info::json) ->> 'current_stage' AS current_stage
  FROM
      hg_stat_activity
  WHERE (running_info::json) ->> 'computing_resource' = 'Serverless'
      AND (running_info::json) -> 'current_stage'->>'stage_name' = 'EXECUTE'

• Query the SQL tasks that are queued to use serverless computing resources.

  SELECT
      *,
      (running_info::json) ->> 'computing_resource' AS computing_resource,
      (running_info::json) ->> 'current_stage' AS current_stage
  FROM
      hg_stat_activity
  WHERE (running_info::json) ->> 'computing_resource' = 'Serverless'
      AND (running_info::json) -> 'current_stage'->>'stage_name' = 'QUEUE'

• Query the status of all SQL tasks that use serverless computing resources.

  SELECT
      *,
      (running_info::json) ->> 'computing_resource' AS computing_resource,
      (running_info::json) ->> 'current_stage' AS current_stage
  FROM
      hg_stat_activity
  WHERE 
      query_id = '<query_id>';

Query historical tasks that use serverless computing resources

• In slow query logs, query the historical tasks that use serverless computing resources.

  SELECT
      *,
      extended_cost::json ->> 'queue_time_ms' AS queue_time_ms,-- The queuing duration for the SQL statement to wait for serverless computing resources. 
      extended_cost::json ->> 'serverless_allocated_cores' AS serverless_allocated_cores,-- The number of CUs of serverless computing resources allocated to the SQL statement. 
      extended_cost::json ->> 'serverless_allocated_workers' AS serverless_allocated_workers,-- The number of workers of serverless computing resources allocated to the SQL statement. 
      extended_cost::json ->> 'serverless_resource_used_time_ms' AS serverless_resource_used_time_ms-- The duration for which the SQL statement occupies serverless computing resources. 
  FROM
      hologres.hg_query_log
  WHERE
      query_extinfo @> ARRAY['serverless_computing'::text];

• Query historical tasks that use serverless computing resources from the hologres.hg_serverless_computing_query_log view.

  SELECT
      *
  FROM
      hologres.hg_serverless_computing_query_log;

  Note

  In Hologres V2.1.18 and later, slow query logs include fields that indicate information about serverless computing resources, and the hologres.hg_serverless_computing_query_log view is added. The hologres.hg_serverless_computing_query_log view provides the following fields in addition to the fields in slow query logs:

  • queue_time_ms: the queuing duration for an SQL statement to wait for serverless computing resources, in milliseconds.

  • serverless_allocated_cores: the number of CUs of serverless computing resources that are actually allocated to an SQL statement.

  • serverless_allocated_workers: the number of workers of serverless computing resources that are actually allocated to an SQL statement.

  • serverless_resource_used_time_ms: the duration that an SQL statement actually occupies serverless computing resources, in milliseconds.

  • extended_info: the extended information, including:

    • serverless_computing_source: the way in which serverless computing resources are specified to execute the SQL statement. Valid values:

      • user_submit: The user specifies that serverless computing resources are used to execute the SQL statement. The execution of the SQL statement is not related to a query queue.

      • query_queue: All SQL statements in the specified query queue are executed by using serverless computing resources.

      • query_queue_rerun: The system automatically uses serverless computing resources to re-execute the SQL statement based on the large query control capability provided by the query queue feature.

    • query_id_of_triggered_rerun: This field exists only when the value of serverless_computing_source is query_queue_rerun. This field specifies the original query ID corresponding to the re-executed SQL statement.

Query the amount of serverless computing resources occupied by SQL tasks that are running in a database

• Query the total amount of serverless computing resources that are occupied by SQL tasks that are running.

  SELECT
      datname::text as db_name,
      (running_info::json) -> 'current_stage' ->> 'stage_name' AS current_stage,
      SUM(((running_info::json) -> 'current_stage' ->> 'serverless_allocated_cores')::int) AS total_computing_resource,
      count(1) as query_qty
  FROM
      hg_stat_activity
  WHERE (running_info::json) ->> 'computing_resource' = 'Serverless'
  GROUP BY
      1,
      2;

• The following table describes parameters in the returned result.

  Parameter	Description
  db_name	The name of the database.
  current_stage	The execution stage of the SQL task. Valid values: PARSE: The SQL task is being parsed. OPTIMIZE: An execution plan is being generated. QUEUE: The SQL task is waiting for resources. START: The SQL task starts to be executed. EXECUTE: The SQL task is being executed. FINISH: The SQL task execution is complete.
  total_computing_resourcet	The total amount of serverless computing resources that are occupied by the SQL task in the EXECUTE stage in the current database.
  query_qty	The number of SQL statements.