Hologres:Adaptive serverless computing

How it works

As described in The amount of serverless resources used by an SQL statement, the amount of serverless resources is determined by the minimum of Quota, Max Cores, and Required Cores. Hologres can automatically estimate the Required Cores and determines the optimal amount of serverless computing resources for an SQL statement based on factors such as SQL complexity and the data volume of related tables.

Based on the optimal resource amount that Hologres automatically estimates for an SQL statement, you can define a resource threshold. SQL statements that exceed this threshold are defined as "large tasks." This enables the following adaptive serverless computing scenarios:

• Adaptive large task isolation: You define a resource threshold for "large tasks." All "large tasks" automatically run on serverless resources.

• Adaptive load isolation: The system dynamically adjusts the resource threshold for "large tasks" based on the load and runs "large tasks" on serverless resources only when the load is high.

Hologres supports two types of custom resource thresholds: a ratio threshold and an absolute threshold. An SQL statement automatically runs on serverless resources only when the system-estimated Required Cores value exceeds both thresholds: Required Cores > max(ratio threshold, absolute threshold). For the adaptive large task isolation feature, you can modify these two thresholds.

• hg_adaptive_serverless_computing_min_resource_ratio_threshold: The ratio threshold coefficient. The default value is 0.3. For virtual warehouse instances, the ratio threshold is the ratio threshold coefficient × the compute resources of the virtual warehouse. For general-purpose instances, the ratio threshold is the ratio threshold coefficient × the compute resources of the instance.

• hg_adaptive_serverless_computing_min_cores_threshold: The absolute threshold. The default value is 256, in CUs.

-- 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 DB 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;

Adaptive load isolation (Beta)

-- Enable adaptive load isolation at the DB level
ALTER DATABASE <db_name> SET hg_adaptive_serverless_computing_enable_load_isolation = on;

Adaptive large task isolation

-- 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 DB 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;

-- Use this if the SQL statement does not contain single quotation marks that need to be escaped.
SELECT * FROM hologres.hg_estimate_adaptive_serverless_computing('select * from tbl;');

-- Use this if the SQL statement contains single quotation marks that need to be escaped.
SELECT * FROM hologres.hg_estimate_adaptive_serverless_computing($$insert into test_tbl_dst_1 select * from test_tbl_src;$$);