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: 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.
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.
Query Isolation Enables the Virtual Warehouse to determine, based on the value you set for the hive.query.isolation.scan.size.threshold configuration parameter, whether to spawn dedicated executor nodes to execute scan-heavy, data-intensive queries in isolation.
Max Concurrent Isolated Queries Available if Query Isolation is enabled. Sets the maximum number of isolated queries that can execute concurrently in their own dedicated executor nodes. Select this number based on the scan size of the data for your average scan-heavy, data-intensive query.
Max Nodes Per Isolated Query Available if Query Isolation is enabled. Sets how many executor nodes can be spawned for each isolated query.

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 disabling 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 is 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.