Elastically Adding or Deleting DWS Virtual Warehouses (Storage-Compute Decoupled Cluster)

Function

Elastic compute groups can be manually or automatically added or deleted. Elastic compute groups can be created and deleted during the scheduled period of time to dynamically scale computing resources.

• A custom scheduled scaling plan creates a compute group that provides computing power. Once such a compute group is associated, user's queries are handled by it, while table creation statements are still managed by the original compute group. This method is suitable if you have an understanding of your service workloads that fluctuate periodically and mainly involve query requests, such as periodic batch jobs.
• An auto scaling plan creates a compute group that facilitates parallel expansion. Once such a compute group is associated with the primary compute group, specific queries from the primary compute group are routed to the compute group, but table creation statements are still executed in the original compute group. This method is suitable if you are a beginner. It does not require knowledge of service workloads or manual intervention. It applies to workloads that mainly involve complex queries and jobs that may last for dozens of minutes to a few hours.

  To reduce statement queuing time, use parallel expansion. When the primary compute group is overwhelmed by high-concurrency tasks and lacks resources like memory, DWS will automatically add extra capacity to process the increased read and write statements. The performance and data consistency are consistent for users, regardless of whether the statement is executed on the primary compute group or the parallel extended compute group. You can set up a resource pool to control which statements are directed to a parallel extended compute group. When parallel expansion is enabled, qualifying statements are directed to the concurrent expansion cluster instead of being queued.

Application Scenarios

Compared with manual addition and deletion of virtual warehouses, elastic addition and deletion are more suitable for the following scenarios:

• Periodic load fluctuation: Service loads fluctuate periodically. For example, The load is high in the daytime and low at night. You can use scheduled addition and deletion plans for automatic adjustment of resources.
• Burst load: There may be a burst in job load over dozens of minutes to several hours. Automatic addition and deletion can cope with such a burst.
• Cost optimization: Compute resources can be released during off-peak hours and expanded on demand during peak hours to avoid resource waste.

Differences Between Scheduled and Automatic Addition and Deletion of Virtual Warehouses

The following table compares the two modes of elastic addition and deletion.

Notes and Constraints

• Compute groups can be added and deleted only in storage-compute decoupled clusters. Compute groups can be automatically added and deleted only in storage-compute decoupled clusters using ECSs of version 9.1.0.200 or later.
• The fine-grained snapshot feature is automatically enabled in the cluster. An error will be reported when you create or delete a plan. You need to manually disable this feature.
• In a yearly/monthly storage-compute decoupled cluster, nodes are automatically added when compute groups are added as scheduled. Nodes are billed on a pay-per-use basis.

Constraints for custom scheduled scaling plans:

• A user can be bound to only one elastic compute group.
• If a user associated with a compute group has workloads in progress when the compute group is deleted, an error may be reported.
• To avoid affecting services and ensure resources are available at the scheduled time, the plan may be skipped if it conflicts with O&M operations, and may be executed about 20 minutes earlier than planned if the cluster creation is time-consuming.
• You are advised to manually add nodes to a compute group based on the following rules:
  • Parallel workloadsnim_iNSmax_peak_memoryGS_WLM_SESSION_INFOmax_dynamic_memoryPGXC_TOTAL_MEMORY_DETAIL

    
  • Serial workloadsMNS

    

Constraints for auto scaling of compute groups:

You are advised to enable and use the concurrent extension feature based on the following rules:

• Only V3 tables and foreign tables are supported. If the table is a replication table, only SELECT is supported.
• Only SELECT, INSERT, UPDATE, and DELETE statements are supported.
• COPY cannot be used to import data.
• The UPSERT statement is not supported.
• Transaction blocks are not supported.
• Stored procedures are not supported.
• Recursive statements with the RETURNING clause and WITH RECURSIVE are not supported.
• Lightweight update is not supported.
• INSERT statements generated by a single VALUES or generate_series are not supported.

Configuring a Custom Scheduled Scaling Plan

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List.
3. In the cluster list, click the name of the target cluster to go to the Cluster Information page.
4. Click the Computed Group tab and then Scheduled Incremental/Decrement Plan.
5. On the displayed page, click Add/Delete Plan to configure a proper scheduling plan. Once Storage-Compute Decoupled clusters are created, they operate in virtual warehouse mode. You can add up to two active plans.

   

6. Select a plan type. It can be:
   • Periodicity: The plan is executed once in every specified period (week or month). A compute group is created or deleted as scheduled as long as it does not conflict with other O&M operations. Logical cluster creation may start later than the time you specified in case of a conflict with other operations.

     Table 2 Parameters

     Parameter	Description
     Cluster Name	Name of the compute group used to speed up compute
     Bind to master computed group	Primary compute group to be bound
     Bind User	Select a user to be bound from the drop-down list. After a user is bound, tables created by the user are still in the original compute group, but the user's compute workloads are processed by the current compute group.
     Nodes	Number of nodes in the compute group
     Time Range	Select the start date and end date.
     Period Type	Select Every Week or Monthly.
     Time Configuration	Select the time when the creation is complete and the time when the deletion starts. If Period Type is set to Every Week, you can add one or more time segments.

   • One-time: The plan is executed only once in the specified period.

     Table 3 One-time plan parameters

     Parameter	Description
     Cluster Name	Name of the compute group used to speed up compute
     Bind to master computed group	Primary compute group to be bound
     Bind User	Select a user to be bound from the drop-down list. After a user is bound, tables created by the user are still in the original compute group, but the user's compute workloads are processed by the current compute group.
     Nodes	Number of nodes in the compute group
     Create Logical Cluster Before	Select the date when the creation is complete. The creation task will be triggered in advance so that the required logical cluster can be created within the time period you specified.
     Delete Logical Cluster After	Select the date when the deletion starts.

7. Select I agree and click Next.
8. Confirm the settings and click Submit. In the scheduled plan list, you can view the plan details and next execution time.

Auto Scaling of Compute Groups

1. Log in to the DWS console.
2. In the navigation pane on the left, choose Cluster > Cluster List.
3. In the cluster list, click the name of the target cluster to go to the Cluster Information page.
4. click the Compute Groups tab. A Storage-Compute Decoupled cluster runs in compute group mode by default.
5. Enable Auto Resiliency Switch.

   Once enabled, the system provisions idle nodes (which are free of charge) and monitors workload. It then automatically creates and deletes read-only elastic compute groups during peak and off-peak hours, respectively.

   Figure 1 Enabling auto scaling for logical clusters
   

6. After auto resiliency is enabled, run SQL statements after completing the operation described in Using the SQL Editor to Connect to a Cluster to create and set a resource pool, and bind a user to the resource pool, and enable auto scaling for the specified resource pool. In the command, poolg indicates the resource pool name, which can be set as needed.

   1 2 3

   create resource pool "poolg" with(nodegroup = 'v3_logical'); alter user "write_user" with resource pool "poolg"; alter resource pool "poolg" with (enable_concurrency_scaling=true);

   Figure 2 Setting a resource pool
   

Documentation

On-demand VW Scaling in the Storage and Compute Decoupling: Cost-Effective Adaptation to Flexible and Changing Service Requirements