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 Virtual 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 Virtual Warehouse auto-scaling threshold settings

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

Impala Auto-scaling Setting Description
Disable AutoSuspend When you enable this control, your Virtual Warehouse does not suspend itself when the auto-scaler has scaled back to the last executor group, and those executors are idle. Instead, the Virtual Warehouse continues to consume cloud resources. You can override this behavior by enabling the Disable AutoSuspend control.
AutoSuspend Timeout Sets the maximum time the warehouse idles before shutting down. This setting only applies when the Disable AutoSuspend control is not enabled.
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.
Scale Up Delay Sets the length of time in seconds that the system waits before adding more executors when it detects queries waiting in the queue to execute. The time to auto-scale is affected by how the underlying Kubernetes system is configured.
Scale Down Delay Sets the length of time in seconds that the system waits before it removes executors when it detects idle executor groups. As with the Scale Up Delay setting, the time to auto-scale down is affected by how the underlying Kubernetes system is configured.