Tuning Hive Virtual Warehouses on private clouds

This topic describes how to tune Hive Virtual Warehouses in Cloudera Data Warehouse (CDW) Private Cloud.

When you tune Hive 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.

  1. Log in to the CDP web interface and navigate to the Data Warehouse service.
  2. In the CDW service, navigate to the Overview page.
  3. On the Overview page under Virtual Warehouses, click the edit icon for the warehouse in the upper right corner of the tile.
  4. The next page provides properties that you can adjust to tune auto-scaling for your data 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 performance is not impacted by having 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 having too many idle resources. 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 the WAIT TIME setting, which 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.