Managing Virtual WarehousesPDF version

Configuring Workload Aware Auto-Scaling (Preview)

You configure Workload Aware Auto-Scaling, available as a technical preview, when you create a Virtual Warehouse following a step-by-step procedure using the CDW UI.

  • Obtain the entitlement for using the Workload Aware Auto-Scaling technical preview from your account team.
  • You are creating a Virtual Warehouse for running BI-type queries.
  • You enabled AutoSuspend.
  • You obtained the DWAdmin role.

You perform the initial configuration of Workload Aware Auto-Scaling when you create a Virtual Warehouse, and later, you can edit your configuration. Tune your configuration based on meeting an SLA or reducing cost.

  1. Create an Impala Virtual Warehouse completing steps in Creating a Virtual Warehouse up to, and including, naming and selecting a Database Catalog for the Virtual Warehouse.
  2. In Size, select Use Workload Aware Auto-Scaling.
  3. In Small, which is an executor group set, configure Executors Per Group.
    The number of executors per group is analogous to the number of nodes you would need on a bare metal system to handle your workload. A small executor group set helps improve cost and prevents larger groups from starting up.
  4. In Large, which is your second executor group set, configure Executors Per Group.

    Choose a value that is a multiple of the value you set for the small Executors Per Group. The greater the spread between values of Executors Per Group the better the query routing.

  5. In Min No. of Groups, set the initial number of groups to use with no scaling down unless the Virtual Warehouse is suspended.
  6. In Max No. of Groups, set the upper limit for scaling, which is often based on budget or intolerance for query latency.

    An executor group set can contain up to 200 executors total. A group size (number of executors per group) must be within 1-100. The max no. of groups is limited by the group size . For example, a set with group size 2 can have up to 100 groups. A set with group size 100 can have up to 2 groups.

  7. Optionally, click Add Custom Set to configure more scaling flexibility.
    First, try using Workload Aware Auto-Scaling without a custom group. Later, if warranted, add up to three custom sets for a total of five executor group sets. Only use more than two executor groups under the following conditions:
    • Your workload is well understood.
    • You have a large mixed workload with clear categories of queries.
    A row for configuring the Custom Set appears.
  8. In Custom Set 1, configure executors per group, minimum number of groups, and maximum number of groups.
    Executors per group for a custom set cannot be less than executors per group for the small set or greater than executors per group for the large set.
    Custom sized executor group sets are named group-set-custom-1, group-set-custom-2, and so on.
  9. Run varying workloads, establish benchmarks for your queries, and add or delete custom sets.
  10. Tune your configuration using the REQUEST_POOL query option or COMPUTE_PROCESSING_COST query option.
    Query pool option example:
    set request_pool=group-set-custom-1;
    Restrict parallelism example:
    Set COMPUTE_PROCESSING_COST=1;

We want your opinion

How can we improve this page?

What kind of feedback do you have?