Hologres:Adaptive Serverless Computing

How it works

The serverless resources a SQL query consumes are determined by the minimum of three factors: the instance's serverless resource quota, the maximum serverless resources allowed per query, and the query's required serverless resources. Hologres estimates the required resources based on SQL complexity and data size.

Adaptive Serverless Computing allows users to define usage thresholds. When a query's required serverless resources exceed these thresholds, it signals a "big task." Hologres then automatically routes these tasks to run on serverless resources.

Use the feature

You can define two types of resource thresholds: absolute and percentage. Hologres automatically routes a query to Serverless Computing if the required serverless resources for the query exceed both thresholds.

• Enable Adaptive Serverless Computing

  Set both hg_enable_adaptive_serverless_computing and hg_adaptive_serverless_computing_enable_big_query_isolation to on.

  -- 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. A query automatically uses serverless resources when the following condition is met: Required Serverless Resources > max(ratio threshold, percentage threshold).

  • hg_adaptive_serverless_computing_min_resource_ratio_threshold: The coefficient for the percentage threshold. Default: 0.3.

    • For virtual warehouse instances: Percentage Threshold = Coefficient * Reserved Resources of a Virtual Warehouse

    • For general-purpose instances: Percentage Threshold = Coefficient * Reserved Instance Resources

  • hg_adaptive_serverless_computing_min_cores_threshold: The absolute threshold. Default: 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;

Verify if the feature is triggered

Adaptive Serverless Computing is triggered based on a query's estimated resources. Use the built-in resource estimation function to verify this behavior; it returns the estimated serverless resources required and indicates if Adaptive Serverless Computing will be activated based on your configurations.

-- Use this syntax if the SQL statement does not require single quote escaping.
SELECT * FROM hologres.hg_estimate_adaptive_serverless_computing('select * from tbl;');

-- Use this syntax if the SQL statement requires single quote escaping.
SELECT * FROM hologres.hg_estimate_adaptive_serverless_computing($$insert into test_tbl_dst_1 select * from test_tbl_src;$$);

The function returns the following values: