All Products
Search

This Product

    Hologres:User guide on Serverless Computing

    Document Center

    Hologres:User guide on Serverless Computing

    Last Updated:Sep 25, 2024

    Hologres allows you to use independent serverless computing resources to run big SQL jobs. This ensures resource isolation and prevents resource contention and mutual interference between tasks. Big SQL jobs include SQL jobs that generate large CPU or memory overheads. This topic describes how to use the Serverless Computing feature.

    Prerequisites

    • The instances are general-purpose instances or virtual warehouse instances.

    • The instances reside in the following regions and zones: Zone J in China (Hangzhou), Zone F and Zone D in China (Shenzhen), Zone E in China (Shanghai), Zone I in China (Beijing), and Zone B in China (Hong Kong).

      Note

      If your instances reside in other zones of the preceding regions and you want to use this feature, you can migrate your business data to the preceding supported zones by submitting a ticket or joining the Hologres DingTalk group. Currently, hot migration between zones is supported. The impact of a hot migration on instances is the same as the impact of a hot upgrade on instances. For more information, see Upgrade instances.

    • The instance versions are V2.1.17 or later.

      Note

      If the version of your Hologres instance is earlier than V2.1.17, you can manually upgrade your Hologres instance or join the Hologres DingTalk group to apply for an upgrade. For more information about how to manually upgrade a Hologres instance, see Upgrade instances. For more information about how to join the Hologres DingTalk group, see Obtain online support for Hologres.

    Usage notes

    • Serverless Computing is not supported in the following scenarios:

      • Queries on read-only secondary instances. You can use the Serverless Computing feature for queries on primary instances and virtual warehouse instances.

      • Queries that allow multiple data manipulation language (DML) statements to be executed in a transaction, such as queries that contain set hg_experimental_enable_transaction = on.

      • SQL queries that use fixed plans.

      • Data queries and writes by using Data Lake Formation (DLF) foreign tables.

      • Cross-database data queries and writes.

      • Queries that involve encrypted tables, including encrypted internal tables and foreign tables.

    • The following extensions are supported:

      • ClickHouse

      • Flow Analysis

      • PostGIS

      • RoaringBitmap

      • BSI

      • Proxima

    • In versions earlier than Hologres V2.2.14, the Serverless Computing feature is supported only for the INSERT (excluding INSERT OVERWRITE), INSERT ON CONFLICT (UPSERT), DELETE, and UPDATE operations in DML statements. In Hologres V2.2.14 and later, the Serverless Computing feature supports data query language (DQL) statements. In Hologres V3.0 and later, the Serverless Computing feature supports the COPY statement.

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

      Note

      • A quota is the maximum amount of serverless computing resources that are allocated to SQL statements in the EXECUTE state in an instance.

      • The serverless computing resource pool of a zone is shared by all instances in the zone.

      • If the quota of an instance is used up or the serverless computing resource pool is fully utilized, subsequent SQL statements enter the QUEUE state to wait for resources.

      • Serverless computing resources that are applied for an SQL statement are exclusive and cannot be used by other SQL statements.

      Instance computing resource amount (unit: CU)

      Serverless computing resource quota

      Instance computing resource amount < 32 CUs

      The Serverless Computing feature is not supported.

      32 CUs ≤ Instance computing resource amount < 688 CUs

      The serverless computing resource quota is three times the amount of the instance computing resources. For example, if an instance has 32 CUs, the serverless computing resource quota is 96 CUs, which is calculated by using the following formula: 32 × 3 = 96.

      Instance computing resource amount ≥ 688 CUs

      The serverless computing resource quota can be up to 2,048 CUs.

    Enable Serverless Computing

    New instances

    Go to the Instances page of the Hologres console and click Create Instance. On the buy page, set the Enable Serverless Computing parameter to True. For more information about how to purchase a Hologres instance, see Purchase a Hologres instance.

    image

    Existing instances

    Go to the Instances page of the Hologres console. In the top navigation bar, select a region. Find the desired instance and click Upgrade in the Actions column. On the page that appears, set the Enable Serverless Computing parameter to True.

    Important

    Your instance will restart if you enable Serverless Computing for your instance. We recommend that you change the configuration during off-peak hours and make sure that your application supports automatic reconnections. Upgrading the computing resource specifications of an instance requires operations such as computing resource reallocation and metadata changes. This operation may take 2 to 5 minutes.

    image

    Disable Serverless Computing

    Go to the Instances page of the Hologres console. In the top navigation bar, select a region. Find the desired instance and click Upgrade in the Actions column. On the page that appears, set the Enable Serverless Computing parameter to False.

    Note

    • Your instance will restart if you disable Serverless Computing for your instance. We recommend that you change the configuration during off-peak hours and make sure that your application supports automatic reconnections. Upgrading the computing resource specifications of an instance requires operations such as computing resource reallocation and metadata changes. This operation may take 2 to 5 minutes.

    • After the Serverless Computing feature is disabled, you cannot specify serverless computing resources, and all running and queued tasks that require serverless computing resources fail.

    Suggestions

    When you use serverless computing resources to execute SQL statements, we recommend that you configure a timeout period for active queries for sessions. This prevents subsequent tasks from being blocked due to excessive runtime or waiting time of current SQL statements.

    • Syntax

      set statement_timeout = <time>;

      Parameter

      time: the timeout period. Valid values: 0 to 2147483647. Default unit: milliseconds. If you want to append a unit to a specific time value, enclose the value and the unit in single quotation marks ('). Otherwise, an error message is returned. The default timeout period is 8 hours.

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

-- Set the timeout period for active queries to 2 hours.
SET statement_timeout = '2h';

-- Execute the SQL statement.
INSERT INTO sink_tbl SELECT * FROM source_tbl;

-- Reset the configurations.
reset hg_computing_resource;
reset statement_timeout;

    Use serverless computing resources to execute SQL statements

    • You can run the following code to use serverless computing resources to execute SQL statements. 

      -- Use serverless computing resources to execute SQL statements. The default value of the hg_computing_resource parameter is local, which indicates that instance computing resources are used to execute SQL statements. 
SET hg_computing_resource = 'serverless';

-- Reset the configuration after a DML statement is submitted. This ensures that serverless computing resources are not used for subsequent SQL statements. 
reset hg_computing_resource;
      Important

      • We recommend that you configure these settings for sessions, instead of databases.

      • We recommend that you execute the reset hg_computing_resource statement to reset configurations after a DML statement is submitted. This ensures that serverless computing resources are not used for subsequent SQL statements.

    • Examples

      -- Prepare data. --
-- Create a source table.
CREATE TABLE source_tbl ( id int );
-- Create a destination table.
CREATE TABLE sink_tbl ( id int );
-- Write sample data.
INSERT INTO source_tbl
SELECT * FROM generate_series(1, 99999999);

-- Use serverless computing resources to execute an SQL statement. --
SET hg_computing_resource = 'serverless';
-- Execute the SQL statement.
INSERT INTO sink_tbl SELECT * FROM source_tbl;
-- Reset the configuration.
reset hg_computing_resource;

      You can run the following code to view the execution plan of the SQL statement. 

      -- Use serverless computing resources to execute an SQL statement.
SET hg_computing_resource = 'serverless';

-- Execute the SQL statement.
EXPLAIN INSERT INTO sink_tbl SELECT * FROM source_tbl;

-- Reset the configuration.
reset hg_computing_resource;

      The following code shows the returned result. In the result, Computing Resource: Serverless indicates that the SQL statement is executed by using serverless computing resources. 

                                              QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Gather  (cost=0.00..5.18 rows=100 width=4)
   ->  Insert  (cost=0.00..5.18 rows=100 width=4)
         ->  Redistribution  (cost=0.00..5.10 rows=100 width=4)
               ->  Local Gather  (cost=0.00..5.10 rows=100 width=4)
                     ->  Decode  (cost=0.00..5.10 rows=100 width=4)
                           ->  Seq Scan on source_tbl  (cost=0.00..5.00 rows=100 width=4)
 Computing Resource: Serverless
 Optimizer: HQO version 2.1.0
(8 rows)

    Advanced operations

    You can configure the upper limit of resources and priority for an SQL statement in the serverless computing resource pool to ensure stable and ordered execution of SQL statements.

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

      Parameter

      Description

      Quota

      The maximum amount of serverless computing resources that are available for a Hologres instance. For more information, see Specify the maximum amount of serverless computing resources that are allocated to an SQL statement in this topic.

      hg_experimental_serverless_computing_max_cores

      The maximum amount of serverless computing resources that can be allocated to each SQL statement. Default value: 512 CUs. You can modify this parameter.

      hg_experimental_serverless_computing_required_cores

      The amount of serverless computing resources that are required by an SQL statement. The value of this parameter is automatically estimated by the system. You can also modify the value. If you modify the parameter value, the system no longer automatically estimates the amount of required serverless computing resources.

    • Hologres also allows you to configure priorities for allocating serverless computing resources to SQL statements. Priority values range from 1 to 5. A larger value indicates a higher priority level. The default value is 3.

    Example

    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.

    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 configuration.
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;
    Note

    • We recommend that you configure these settings for databases.

    • In actual situations, the system automatically allocates resources based on the complexity of SQL statements.

    • The system estimates the amount of serverless computing resources that are required by an SQL statement based on the smaller value between the hg_experimental_serverless_computing_max_cores parameter value and the quota.

    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 configuration.
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;
    Note

    • We recommend that you configure these settings for sessions, instead of databases.

    • In actual situations, the system applies for resources based on the smallest value among the hg_experimental_serverless_computing_required_cores parameter value, the hg_experimental_serverless_computing_max_cores parameter value, and the quota.

    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 configuration.
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';
    Note

    • This configuration has a higher priority than the database-level GUC parameter setting and has a lower priority than the session-level GUC parameter setting. For more information, see GUC parameters.

    • To cancel the preceding configuration, run the ALTER USER <user_name> IN DATABASE <db_name> RESET hg_computing_resource; code.

    More operations

    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.

    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.