Tuning Hive-LLAP Virtual Warehouses

When you tune Hive-LLAP Virtual Warehouses, you set the auto-suspend timeout, the minimum and maximum number of nodes for your virtual cluster, when your cluster should scale up, and when it should scale down.

Required role: DWAdmin

  1. Log in to the CDP web interface and navigate to the Data Warehouse service.
  2. In the Data Warehouse service, navigate to the Overview page.
  3. On the Overview page under Virtual Warehouses, click the edit icon for a warehouse:

  4. The next page provides properties that you can adjust to tune auto-scaling for your Virtual Warehouse:
    1. Set the AutoSuspend Timeout, which determines how many seconds the warehouse cluster is idle before it suspends itself:

      This setting helps to ensure you are not paying for idle resources.

    2. Set the minimum and maximum number of nodes that the cluster can contain:

      Use the minimum number of nodes setting to ensure that your workloads always have resources and use the maximum number of nodes setting to contain your costs. Decide the minimum and maximum number of nodes based on your workloads similarly to how you determine node counts for your on-premises clusters. Consider the number of concurrent queries, the complexity of queries, and the volume of queries in your workloads to determine the appropriate number of nodes to set on each Virtual Warehouse instance.

    3. Choose when your cluster auto-scales up based on either the HEADROOM or the WAIT TIME settings:
      • Set the HEADROOM, which sets the number of nodes to keep free to handle increases in numbers of queries. For example, you might configure a Small-sized Hive-LLAP Virtual Warehouse with 10 nodes to accommodate a peak workload of 60 concurrent users, but a minimum of 5 concurrent users. The system can auto-scale up to support 60 concurrent users to meet the peak demand. In this case, you leave the default setting for HEADROOM Desired Free Capacity at 1:

        When HEADROOM is set to 1 for a 10-node cluster, then if there are over 9 queries concurrently executing, the cluster auto-scales up without waiting for 10 queries to fill the cluster.

      • Alternatively, you can also configure your cluster to auto-scale based on query wait times. The WAIT TIME auto-scaling configuration option sets how long queries wait in the queue to execute before the cluster auto-scales up. 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:

        However, this is a less predictable method because the scaling might react to non-scalable factors to spin up clusters. For example, query wait times might increase because of inefficient queries and not because of query volume.