Hologres:User guide on Serverless Computing

New instance

Visit the Hologres instance list, click Create Instance, and on the instance purchase page, set the Enable Serverless Computing parameter to Yes. For more information about purchasing a new instance, see Purchase Hologres.

Existing instance

Visit the Hologres instance list, select a region in the top menu bar, select the target instance, and click Actions > Upgrade. On the configuration change page, set the Enable Serverless Computing parameter to Yes.

If you do not need to enable Serverless Computing, you can perform the following operations:

You can access the Hologres instance list, select a region in the upper-left corner of the menu bar, select the destination instance, click Operation column , and select Downgrade. On the downgrade page, set the Enable Serverless Computing parameter to No.

Maximum available serverless computing resources for an instance

The serverless computing resource quota that is available for a Hologres instance varies based on the amount of computing resources of the instance. The following table describes the mappings between instance computing resource amounts and serverless computing resource quotas.

Specify the amount of serverless computing resources for an SQL statement

Hologres instances that are enabled with the Serverless Computing feature provide you with three parameters to specify the amount of serverless computing resources to be allocated to an SQL statement. The system uses the minimum value among the values of the three parameters to apply for resources for an SQL statement. The actual resources are applied for in increments of 15 CUs.

Sample scenarios:

A Hologres instance is deployed with 32 CUs and a maximum of 96 CUs of serverless computing resources are available for the instance. SQL statement A is executed by using 64 CUs of serverless computing resources. SQL statements B and C are queued in the serverless computing resource pool. The priority value of SQL statement B is 5 and the required resources are 48 CUs. The priority value of SQL statement C is 3 and the required resources are 32 CUs. In this case, the system does not use the remaining 32 CUs of serverless computing resources to execute SQL statement C first. Instead, the system uses 48 CUs of serverless computing resources to execute SQL statement B after SQL statement A is complete.

To specify serverless computing resources for multiple SQL statements, you can refer to the following advanced operations. These operations take effect in descending order of priority:

1. Set the hg_computing_resource parameter to 'serverless' at the session level (SQL level) to use serverless computing resources. See Serverless Computing priority.

2. Set the hg_computing_resource parameter to 'serverless' at the user level to use serverless computing resources.

3. Configure all SQL statements in a specific query queue to use serverless computing resources.

4. Use serverless computing resources if the conditions for automatic rotation are met.

5. If none of the preceding conditions are met, local instance resources are used. If the conditions for automatically rerunning large queries by using serverless computing resources are met, large queries are automatically fused and rerun by using serverless computing resources.

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.

-- Specify serverless computing resources for all SQL statements that are initiated by a specific user in a specific 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 query queues, see Query Queue.

• General-purpose instances

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

  -- Configure 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>');

Automatic rotation

The amount of serverless computing resources used by an SQL statement is determined by the minimum value among Quota, Max Cores, and Required Cores. Required Cores can be automatically estimated by the Hologres system.

Hologres V3.1 and later support automatic rotation for serverless computing resources. You can customize thresholds to automatically use serverless computing resources for queries with large Required Cores values that are automatically estimated by the system. The thresholds include an absolute threshold and a ratio threshold. When the Required Cores value that is automatically estimated by the system exceeds both thresholds, the query automatically uses serverless computing resources.

• Enable the automatic rotation feature. Both parameters must be enabled for this feature to take effect.

  -- Enable at the session level
  SET hg_enable_adaptive_serverless_computing = on;
  SET hg_adaptive_serverless_computing_enable_big_query_isolation = on;
  
  -- Enable at the user level
  ALTER USER "<role_name>" IN DATABASE <db_name> SET hg_enable_adaptive_serverless_computing = on;
  ALTER USER "<role_name>" IN DATABASE <db_name> SET hg_adaptive_serverless_computing_enable_big_query_isolation = on;
  
  -- Enable at the database level
  ALTER DATABASE <db_name> SET hg_enable_adaptive_serverless_computing = on;
  ALTER DATABASE <db_name> SET hg_adaptive_serverless_computing_enable_big_query_isolation = on;

• Modify the thresholds for the automatic rotation feature.

  When Required Cores > max(ratio threshold, absolute threshold), the query automatically uses serverless computing resources.

  • hg_adaptive_serverless_computing_min_resource_ratio_threshold: the ratio threshold coefficient. Default value: 0.3.

    • Virtual warehouse instances: ratio threshold = ratio threshold coefficient × computing resources of the virtual warehouse.

    • General-purpose instances: ratio threshold = ratio threshold coefficient × computing resources of the instance.

  • hg_adaptive_serverless_computing_min_cores_threshold: the absolute threshold. Default value: 256.

  -- Modify at the session level
  SET hg_adaptive_serverless_computing_min_resource_ratio_threshold = 0.3;
  SET hg_adaptive_serverless_computing_min_cores_threshold = 256;
  
  -- Modify at the user level
  ALTER USER "<role_name>" IN DATABASE <db_name> SET hg_adaptive_serverless_computing_min_resource_ratio_threshold = 0.3;
  ALTER USER "<role_name>" IN DATABASE <db_name> SET hg_adaptive_serverless_computing_min_cores_threshold = 256;
  
  -- Modify at the database level
  ALTER DATABASE <db_name> SET hg_adaptive_serverless_computing_min_resource_ratio_threshold = 0.3;
  ALTER DATABASE <db_name> SET hg_adaptive_serverless_computing_min_cores_threshold = 256;

Because the application of the automatic rotation feature is affected by the automatic estimation logic of the system, Hologres provides a resource estimation function (hg_estimate_adaptive_serverless_computing) to calculate the Required Cores value that is automatically estimated by the system for an SQL statement and determine whether the automatic rotation feature takes effect. The function returns the following values:

-- Use this when the SQL statement does not involve single quotation mark escaping
SELECT * FROM hologres.hg_estimate_adaptive_serverless_computing('SELECT * FROM tbl;');

-- Use this when the SQL statement involves single quotation mark escaping
SELECT * FROM hologres.hg_estimate_adaptive_serverless_computing($$INSERT INTO test_tbl_dst_1 SELECT * FROM test_tbl_src;$$);

Automatically rerun large queries by using serverless computing resources

If an SQL statement is already executed by using local instance resources, you can define queries that run for more than x seconds or encounter OOM errors as large queries. You can also configure automatic fusing for large queries and rerun them by using serverless computing resources. For more information, see Large query control.

View the monitoring metrics of an instance that uses serverless computing resources

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 needed, you can further configure monitoring alerts. 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 the historical tasks that use serverless computing resources in the hologres.hg_serverless_computing_query_log view.

  SELECT
      *
  FROM
      hologres.hg_serverless_computing_query_log;

  Note

  From version 2.1.18, to facilitate queries, the content related to Serverless Computing tasks in slow query logs is fixed as columns, and the hologres hg_serverless_computing_query_log view is added. Compared with slow query logs, the hologres.hg_serverless_computing_query_log view adds the following fields:

  • queue_time_ms: the queuing duration for the SQL statement to wait for serverless computing resources. Unit: milliseconds (ms).

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

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

  • serverless_resource_used_time_ms: the duration for which the SQL statement actually occupies serverless computing resources. Unit: milliseconds (ms).

  • The following information is added to the extended_info field:

    • serverless_computing_source: indicates the source of the SQL statement that uses serverless computing resources. Valid values:

      • user_submit: the SQL statement that is specified to use serverless computing resources, regardless of the query queue.

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

      • big_query_isolation_service: the query that uses serverless computing resources through the automatic rotation feature. For more information, see Automatic rotation.

      • query_queue_rerun: the SQL statement that is automatically rerun by using serverless computing resources through the large query control feature of the query queue.

    • query_id_of_triggered_rerun: This field exists only when serverless_computing_source is query_queue_rerun. It indicates the original query ID of the rerun SQL statement.

  For some special tasks such as COPY, CTAS, and INSERT OVERWRITE, multiple records are generated in slow query logs. These records include the task SQL statement itself and the INSERT records generated during its execution. The INSERT records actually use serverless computing resources. You can associate the task SQL statement with the INSERT records by using the trans_id field of the task SQL statement. The following example shows how to perform the association:

  SELECT 
      query_id,
      query,
      extended_info 
  FROM 
      hologres.hg_query_log 
  WHERE 
      extended_info ->> 'source_trx' = '<transaction_id>' -- You can obtain the transaction_id from the trans_id field of the COPY/CTAS/RESHARDING record itself
      -- extended_info ->> 'src_query_id' = 'query_id'	-- A stored procedure is not a transaction, so you need to associate it by using the query_id field
      -- query like '%<transaction_id>%'	-- RESHARDING tasks cannot be associated by using the extended_info field, so you need to use the query field
  ORDER BY 
      query_start
  ;

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 statement. Valid values: PARSE: The SQL statement is being parsed. OPTIMIZE: An execution plan is being generated. QUEUE: The SQL statement is waiting for resources. START: The SQL statement starts to be executed. EXECUTE: The SQL statement is being executed. FINISH: The SQL statement execution is complete.
  total_computing_resource	The total amount of serverless computing resources that are occupied by SQL statements in the EXECUTE stage in the current database.
  query_qty	The number of SQL statements.