Multi-cluster Warehouses

Multi-cluster warehouses can be used to scale warehouse resources to manage your user and query concurrency needs as they change, such as during peak and off hours.

In this Topic:

What is a Multi-cluster Warehouse?

By default, a warehouse consist of a single cluster of servers that determines the total resources available to the warehouse for executing queries. As queries are submitted to a warehouse, the warehouse allocates server resources to each query and begins executing the queries. If sufficient resources are not available to execute all the queries submitted to the warehouse, Snowflake queues the additional queries until the necessary resources become available.

With multi-cluster warehouses, Snowflake supports allocating, either statically or dynamically, a larger pool of resources to each warehouse. A multi-cluster warehouse is defined by specifying the following properties:

  • Maximum number of server clusters (up to 10).
  • Minimum number of server clusters (default is 1).

Additionally, multi-cluster warehouses support all the same properties as single-cluster warehouses, including:

  • Specifying a warehouse size.
  • Resizing a warehouse at any time.
  • Auto-suspending a warehouse due to inactivity; note that this does not apply to individual clusters, but rather the entire warehouse; i.e. it only applies to a multi-cluster warehouse when all the currently running clusters for the warehouse have been inactive for the specified amount of time.
  • Auto-resuming a suspended warehouse when new queries are submitted.

Maximized Mode vs. Auto-scale Mode

You can choose to run a multi-cluster warehouse in either of the following modes:

Maximized:

This mode is enabled by specifying the same value (greater than 1) for both maximum and minimum clusters. In this mode, when the warehouse is started, Snowflake always starts all the clusters so that maximum resources are available while the warehouse is running.

This mode is effective for statically controlling the available warehouse resources, particularly if you have large numbers of concurrent user sessions and/or queries and the numbers do not fluctuate significantly.

Auto-scale:

This mode is enabled by specifying different values for maximum and minimum clusters. In this mode, Snowflake starts and stops clusters as needed to dynamically manage the load on the warehouse:

  • As the number of concurrent user sessions and/or queries for the warehouse increases, and queries start to queue due to insufficient resources, Snowflake automatically starts additional clusters, up to the maximum number defined for the warehouse.
  • Similarly, as the load on the warehouse decreases, Snowflake automatically shuts down clusters to reduce the number of running servers and, correspondingly, the number of credits used by the warehouse.

To help ensure efficient use of credits in auto-scale mode, Snowflake continuously monitors the warehouse to calculate the amount of resources needed for the current load and determine the optimal time-frame in which to start or shut down additional clusters. For more information, see the next section (Multi-cluster Warehouse Size and Credit Usage) in this topic.

Tip

When specifying the maximum and minimum clusters for a warehouse, start small, e.g. maximum = 2, minimum = 1 (i.e. auto-scale mode). As you track how your warehouse load fluctuates over time, you can increase the maximum and minimum clusters until you determine the numbers that best support the upper and lower boundaries of your user/query concurrency.

Multi-cluster Warehouse Size and Credit Usage

The number of servers in each cluster is determined by warehouse size:

  • The total number of servers for the warehouse is calculated by multiplying the warehouse size by the maximum number of clusters. This also indicates the maximum number of credits used by the warehouse per hour if all clusters run during the hour.
  • If a multi-cluster warehouse is resized, the new size applies to all the clusters for the warehouse, including clusters that are currently running and any clusters that are started after the warehouse is resized.

The actual number of credits used per hour depends on the number of clusters running during each hour that the warehouse is running.

Note

To help minimize the credits used by starting and stopping clusters in auto-scale mode, Snowflake only starts additional clusters as needed and automatically shuts them down when they are no longer needed. The algorithm used to determine when to shut down a cluster incorporates a 5-minute delay to help account for changing query load.

Benefits of Multi-cluster Warehouses

With a single-cluster warehouse, if your user/query load increases to the point where you need more compute resources:

  1. You must either increase the size of the warehouse or start additional warehouses and explicitly redirect the additional users/queries to these warehouses.
  2. Then, when the resources are no longer needed, to conserve credits, you must manually downsize the larger warehouse or suspend the additional warehouses.

To start with, a multi-cluster warehouse enables larger numbers of users to connect to the same warehouse.

Additionally:

  • In auto-scale mode, a multi-cluster warehouse eliminates the need for resizing the warehouse or starting and stopping additional warehouses to handle fluctuating workloads. Snowflake automatically starts and stops additional clusters as needed.
  • In maximized mode, you can control the capacity of the warehouse by increasing or decreasing the number of clusters as needed.

Tip

Multi-cluster warehouses are best utilized for scaling resources to improve concurrency for users/queries. They are not as beneficial for improving the performance of slow-running queries or data loads. For these types of operations, resizing the warehouse provides more benefits.

Multi-cluster Warehouse Credit Usage Examples

The following five examples illustrate credit usage for a multi-cluster warehouse with the following properties:

  • Medium size warehouse (each cluster uses 4 credits per hour that the cluster is running)
  • Example 1: Warehouse running in maximized mode (maximum and minimum clusters = 3)
  • Examples 2 to 5: Warehouse running in auto-scale mode (maximum clusters = 3, minimum clusters = 1)
Example 1 — Maximized warehouse running for 1 to 2 hours:

All 3 clusters run for the duration.

  Cluster 1 Cluster 2 Cluster 3 Total Credits
1st Hour 4 4 4 12
2nd Hour 4 4 4 12
Total Credits 8 8 8 24
Example 2 — Auto-scale warehouse running for 1 to 2 hours:
  • Cluster 1 runs for the duration.
  • Cluster 2 starts during the second hour.
  • Cluster 3 never starts.
  Cluster 1 Cluster 2 Cluster 3 Total Credits
1st Hour 4 0 0 4
2nd Hour 4 4 0 8
Total Credits 8 4 0 12
Example 3 — Auto-scale warehouse running for 1 to 2 hours:
  • Cluster 1 runs for the duration.
  • Cluster 2 starts during the second hour.
  • Cluster 3 starts, shuts down, and restarts within the second hour.
  Cluster 1 Cluster 2 Cluster 3 Total Credits
1st Hour 4 0 0 4
2nd Hour 4 4 4 12
Total Credits 8 4 4 16
Example 4 — Auto-scale warehouse running for 2 to 3 hours:
  • Cluster 1 runs for the duration.
  • Cluster 2 starts in the second hour and runs for longer than 1 hour.
  • Cluster 3 starts in the third hour and shuts down before the hour ends.
  Cluster 1 Cluster 2 Cluster 3 Total Credits
1st Hour 4 0 0 4
2nd Hour 4 4 0 8
3rd Hour 4 4 4 4
Total Credits 12 8 4 24
Example 5 — Auto-scale warehouse running for 1 to 2 hours (with a resize):
  • Cluster 1 runs for the duration.
  • Cluster 2 starts in the second hour.
  • Cluster 3 never starts.
  • Warehouse is resized from Medium to Large during the second hour.
  Cluster 1 Cluster 2 Cluster 3 Total Credits
1st Hour 4 0 0 4
2nd Hour 4+4 4+4 0 16
Total Credits 12 8 0 20

Creating a Multi-Cluster Warehouse

You can create a multi-cluster warehouse through the web interface or using SQL:

Web Interface:

Click on Warehouses > Create:

  1. In the Maximum Clusters field, select a value greater than 1.
  2. In the Minimum Clusters field, optionally select a value greater than 1.
  3. Enter other information for the warehouse, as needed, and click Finish.
SQL:

CREATE WAREHOUSE with:

  • Set MAX_CLUSTER_COUNT to a value greater than 1.
  • Optionally set MIN_CLUSTER_COUNT to a value greater than 1.

To view information about the multi-cluster warehouses you create:

Web Interface:

Click on Warehouses

The Clusters column displays the minimum and maximum clusters for each warehouse, as well as the number of clusters that are currently running if the warehouse is started.

SQL:

SHOW WAREHOUSES

The output includes three columns (min_cluster_count, max_cluster_count, started_clusters_column) that display the same information provided in the Clusters column in the web interface.

All other tasks for multi-cluster warehouses are identical to tasks for single-cluster warehouses.

Increasing or Decreasing Clusters for a Multi-cluster Warehouse

You can increase or decrease the number of clusters for a warehouse at any time, even while the warehouse is running and executing statements. Clusters can be increased or decreased for a warehouse through the web interface or using SQL:

Web Interface:Click on Warehouses > warehouse_name > Configure
SQL:ALTER WAREHOUSE

The effect of changing the maximum and minimum clusters for a running warehouse depends on whether it is running in maximized or auto-scale mode:

  • Maximized Mode:
    ▲ max & min:Specified number of clusters start immediately.
    ▼ max & min:Specified number of clusters shut down when they finish executing statements and the built-in time delay has elapsed.
  • Auto-scale Mode:
    ▲ max:If new_max_clusters > running_clusters, no changes until additional clusters are needed.
    ▼ max:If new_max_clusters < running_clusters, excess clusters shut down when they finish executing statements and the built-in time delay has elapsed.
    ▲ min:If new_min_clsuters > running_clusters, additional clusters immediately started to meet the minimum.
    ▼ min:If new_min_clusters < running_clusters, excess clusters shut down when they finish executing statements and the built-in time delay has elapsed.

Monitoring Multi-cluster Warehouses

You can monitor usage of multi-cluster warehouses through the web interface:

Web Interface:

Click on History or Worksheet

These pages include a column, Cluster Number, that specifies the cluster used to execute the statements submitted to each warehouse.