Auto-scale threshold settings

When you create new Virtual Warehouse instances, you can set auto-scaling thresholds. These thresholds set limits on automatic cluster scaling to meet workload demands. Setting these limits prevents warehouses from consuming too many resources when workload demands increase or decrease.

Another important benefit of enabling auto-scaling for your Virtual Warehouse is that it further enforces node isolation, increasing warehouse fault tolerance. You can adjust the following auto-scaling thresholds:

Hive-LLAP data warehouse auto-scaling threshold settings

The following settings are available to configure auto-scaling for Hive-LLAP Virtual Warehouses:

Hive-LLAP Auto-scaling Threshold Description
AutoSuspend Timeout Sets the maximum time the warehouse idles before shutting down.
Nodes

Sets the minimum and maximum number of nodes for the warehouse cluster. The maximum number of nodes is limited by your cloud account limits.

Choose the minimum and maximum number of nodes based on two factors:

  • Average number of queries that must be run concurrently for your workloads. The more queries that must be run concurrently, the larger number of nodes are needed.
  • The size of the data your workloads access. Larger numbers of nodes can cache more data, which enhances performance.
HEADROOM Sets the number of concurrently executing queries that trigger auto-scaling. For example, if Desired Free Capacity is set to 1 for a 10-node cluster, when there are over 9 queries concurrently executing, the cluster auto-scales up without waiting for 10 queries to fill the cluster.
WAIT TIME Sets how long queries wait in the queue to execute. For example, if WaitTime Seconds is set to 10, then when executing queries are waiting in the queue for 10 seconds, the cluster auto-scales up to meet query demand.

Impala data mart auto-scaling threshold settings

The following settings are available to configure auto-scaling for Impala Virtual Warehouses:

Impala Auto-scaling Setting Description
AutoSuspend Timeout Sets the maximum time the warehouse idles before shutting down.
Nodes: Min: <n> Max: <n>

Sets the minimum and maximum number of nodes for the warehouse cluster. The maximum number of nodes is limited by your cloud account limits.

Choose the minimum and maximum number of nodes based on two factors:

  • Average number of queries that must be run concurrently for your workloads. The more queries that must be run concurrently, the larger number of nodes are needed.
  • The size of the data your workloads access. Larger numbers of nodes can cache more data, which enhances performance.
Autoscale Mode

You can choose from the following settings:

  • Conservative: This mode causes the data mart to auto-scale up approximately 60 seconds after maximum utilization of resources is reached. Then when demand decreases, it immediately auto-scales down.
  • Balanced: In this mode, the data mart auto-scales up approximately 30 seconds after maximum utilization of resources is reached and after approximately 30 seconds after demand decreases, it auto-scales down.
  • Aggressive: For this mode the data mart immediately auto-scales up when maximum utilization of resources is reached and it auto-scales down approximately 60 seconds after demand decreases.