Multi-cluster warehouses enable you to scale compute resources to manage your user and query concurrency needs as they change, such as duringpeak and off hours.
What is a multi-cluster warehouse?¶
By default, a virtual warehouse consists of a single cluster of compute resources available to thewarehouse for executing queries. As queries are submitted to a warehouse, the warehouse allocates resources to each query and beginsexecuting the queries. If sufficient resources are not available to execute all the queries submitted to the warehouse, Snowflake queues theadditional queries until the necessary resources become available.
With multi-cluster warehouses, Snowflake supports allocating, either statically or dynamically, additional clusters to make a larger poolof compute resources available. A multi-cluster warehouse is defined by specifying the following properties:
Maximum number of clusters, greater than 1 (up to 10).
Minimum number of clusters, equal to or less than the maximum (up to 10).
Additionally, multi-cluster warehouses support all the same properties and actions as single-cluster warehouses, including:
Specifying a warehouse size.
Resizing a warehouse at any time.
Auto-suspending a running warehouse due to inactivity; note that this does not apply to individual clusters, but rather the entiremulti-cluster warehouse.
Auto-resuming a suspended warehouse when new queries are submitted.
Maximized vs. auto-scale¶
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 for both maximum and minimum number of clusters (note that thespecified value must be larger than 1). In this mode, when the warehouse is started, Snowflake starts all the clusters sothat maximum resources are available while the warehouse is running.
This mode is effective for statically controlling the available compute resources, particularly if you have large numbers of concurrentuser 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 number of 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 toinsufficient 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 ofrunning clusters and, correspondingly, the number of credits used by the warehouse.
To help control the usage of credits in Auto-scale mode, Snowflake provides a property, SCALING_POLICY, that determines the scaling policyto use when automatically starting or shutting down additional clusters. For more information, see Setting the scaling policy for a multi-cluster warehouse (inthis topic).
Tip
To create a multi-cluster warehouse, see Creating a multi-cluster warehouse (in this topic).
Note the following:
For multi-cluster warehouses, the maximum number of clusters in the Maximum Clusters field (Web Interface) or for the MAX_CLUSTER_COUNT property (SQL) must be greater than 1.
For single-cluster warehouses, the maximum and minimum number of clusters must both be equal to 1.
For auto-scale mode, the maximum number of clusters must be greater than the minimum number of clusters.
For maximized mode, the maximum number of clusters must be equal to the minimum number of clusters.
When determining the maximum and minimum number of clusters to use for a multi-cluster warehouse, start with Auto-scale mode and startsmall (e.g. maximum = 2 or 3, minimum = 1). As you track how your warehouse load fluctuates over time, you can increase the maximum andminimum number of clusters until you determine the numbers that best support the upper and lower boundaries of your user/query concurrency.
Multi-cluster size and credit usage¶
The amount of compute resources in each cluster is determined by the warehouse size:
The total number of clusters for the multi-cluster warehouse is calculated by multiplying the warehouse size by the maximum number ofclusters. This also indicates the maximum number of credits consumed by the warehouse per full hour of usage (i.e. ifall clusters run during the hour).
For example, the maximum number of credits consumed per hour for a Medium-size multi-cluster warehouse with 3 clusters is 12 credits.
If a multi-cluster warehouse is resized, the new size applies to all the clusters for the warehouse, includingclusters that are currently running and any clusters that are started after the multi-cluster warehouse is resized.
The actual number of credits consumed per hour depends on the number of clusters running during each hour that the warehouseis running. For more details, see Examples of multi-cluster credit usage (in this topic).
Benefits of multi-cluster warehouses¶
With a standard, single-cluster warehouse, if your user/query load increases to the point where you need more compute resources:
You must either increase the size of the warehouse or start additional warehouses and explicitly redirect the additional users/queries tothese warehouses.
Then, when the resources are no longer needed, to conserve credits, you must manually downsize the larger warehouse or suspend the additionalwarehouses.
In contrast, a multi-cluster warehouse enables larger numbers of users to connect to the same size warehouse. In addition:
In Auto-scale mode, a multi-cluster warehouse eliminates the need for resizing the warehouse or starting and stopping additionalwarehouses to handle fluctuating workloads. Snowflake automatically starts and stops additional clusters as needed.
In Maximized mode, you can control the capacity of the multi-cluster warehouse by increasing or decreasing the number of clusters asneeded.
Tip
Multi-cluster warehouses are best utilized for scaling resources to improve concurrency for users/queries. They are not as beneficial forimproving the performance of slow-running queries or data loading. For these types of operations, resizing the warehouse providesmore benefits.
Examples of multi-cluster credit usage¶
The following four examples illustrate credit usage for a multi-cluster warehouse. Refer to Virtual warehouse credit usage forthe number of credits billed per full hour by warehouse size.
Note
For the sake of simplicity, all these examples depict credit usage in increments of 1 hour, 30 minutes, and 15 minutes. In a real-worldscenario, with per-second billing, the actual credit usage would contain fractional amounts, based on the number of seconds that eachcluster runs.
Example 1: Maximized (2 Hours)¶
In this example, a Medium-size warehouse with 3 clusters runs in Maximized mode for 2 hours:
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 (2 Hours)¶
In this example, a Medium-size warehouse with 3 clusters runs in Auto-scale mode for 2 hours:
Cluster 1 runs continuously.
Cluster 2 runs continuously for the 2nd hour only.
Cluster 3 runs for 30 minutes during the 2nd hour.
Cluster 1 | Cluster 2 | Cluster 3 | Total Credits | |
1st Hour | 4 | 4 | ||
2nd Hour | 4 | 4 | 2 | 10 |
Total Credits | 8 | 4 | 2 | 14 |
Example 3: Auto-scale (3 Hours)¶
In this example, a Medium-size warehouse with 3 clusters runs in Auto-scale mode for 3 hours:
Cluster 1 runs continuously.
Cluster 2 runs continuously for the entire 2nd hour and 30 minutes in the 3rd hour.
Cluster 3 runs for 30 minutes in the 3rd hour.
Cluster 1 | Cluster 2 | Cluster 3 | Total Credits | |
1st Hour | 4 | 4 | ||
2nd Hour | 4 | 4 | 8 | |
3rd Hour | 4 | 2 | 2 | 8 |
Total Credits | 12 | 6 | 2 | 20 |
Example 4: Auto-scale (3 Hours) with resize¶
In this example, the same warehouse from example 3 runs in Auto-scale mode for 3 hours with a resize from Medium to Large:
Cluster 1 runs continuously.
Cluster 2 runs continuously for the 2nd and 3rd hours.
Warehouse is resized from Medium to Large at 1:30 hours.
Cluster 3 runs for 15 minutes in the 3rd hour.
Cluster 1 | Cluster 2 | Cluster 3 | Total Credits | |
1st Hour | 4 | 4 | ||
2nd Hour | 4+2 | 4+2 | 12 | |
3rd Hour | 8 | 8 | 2 | 18 |
Total Credits | 18 | 14 | 2 | 34 |
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:
In the Maximum Clusters field, select a value greater than 1.
In the Minimum Clusters field, optionally select a value greater than 1.
Enter other information for the warehouse, as needed, and click Finish.
- SQL:
Execute a CREATE WAREHOUSE command with:
MAX_CLUSTER_COUNTset to a value greater than1.
MIN_CLUSTER_COUNT(optionally) set to a value greater than1.
To view information about the multi-cluster warehouses you create:
- Classic Console:
Click on Warehouses
The Clusters column displays the minimum and maximum clusters for each warehouse, as well as the number ofclusters that are currently running if the warehouse is started.
- SQL:
Execute a SHOW WAREHOUSES command.
The output includes three columns (
min_cluster_count,max_cluster_count,started_clusters_column) that displaythe same information provided in the Clusters column in the web interface.
All other tasks for multi-cluster warehouses (except for the remaining tasks described in this topic) are identical to single-clusterwarehouse tasks.
Setting the scaling policy for a multi-cluster warehouse¶
To help control the credits consumed by a multi-cluster warehouse running in Auto-scale mode, Snowflake provides scaling policies, which areused to determine when to start or shut down a cluster.
The scaling policy for a multi-cluster warehouse only applies if it is running in Auto-scale mode. In Maximized mode, all clusters runconcurrently so there is no need to start or shut down individual clusters.
Snowflake supports the following scaling policies:
Policy | Description | Warehouse Starts… | Warehouse Shuts Down… |
|---|---|---|---|
Standard (default) | Prevents/minimizes queuing by favoring starting additional clusters over conserving credits. | The first cluster starts immediately when either a query is queued or the system detects that there’s one more query thanthe currently-running clusters can execute. Each successive cluster waits to start 20 seconds after the prior one has started. For example, if your warehouse isconfigured with 10 max clusters, it can take a full 200+ seconds to start all 10 clusters. | After 2 to 3 consecutive successful checks (performed at 1 minute intervals), which determine whether the load on the least-loadedcluster could be redistributed to the other clusters without spinning up the cluster again. |
Economy | Conserves credits by favoring keeping running clusters fully-loaded rather than starting additional clusters, which may resultin queries being queued and taking longer to complete. | Only if the system estimates there’s enough query load to keep the cluster busy for at least 6 minutes. | After 5 to 6 consecutive successful checks (performed at 1 minute intervals), which determine whether the load on the least-loadedcluster could be redistributed to the other clusters without spinning up the cluster again. |
Note
A third scaling policy, Legacy, was provided for backward compatibility. In contrast to the other policies, it used a static approachbased on length of time a warehouse is active/inactive.
Legacy has been obsoleted/removed. All warehouses that were using the Legacy policy now use the default Standard policy.
The scaling policy for a multi-cluster warehouse can be set when it is created or at any time afterwards, either through the web interfaceor using SQL:
- Classic Console:
Click on:
Warehouses » Create or
Warehouses » <warehouse_name> » Configure
In the Scaling Policy field, select the desired value from the drop-down list.
- SQL:
Execute a CREATE WAREHOUSE or ALTER WAREHOUSE command with
SCALING_POLICYset to the desired value.
For example, in SQL:
ALTER WAREHOUSE mywh SET SCALING_POLICY = 'ECONOMY';Copy
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 it is running and executingstatements. Clusters can be increased or decreased for a warehouse through the web interface or using SQL:
- Classic Console:
Click on Warehouses » <warehouse_name> » Configure
- SQL:
Execute an ALTER WAREHOUSE command.
The effect of changing the maximum and minimum clusters for a running warehouse depends on whether it is running inMaximized or Auto-scale mode:
Maximized:
- ↑ max & min:
Specified number of clusters start immediately.
- ↓ max & min:
Specified number of clusters shut down when they finish executing statements and the auto-suspend period elapses.
Auto-scale:
- ↑ 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 thescaling policy conditions are met.- ↑ min:
If
new_min_clusters > 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 thescaling policy conditions are met.
Monitoring multi-cluster warehouses¶
You can monitor usage of multi-cluster warehouses through the web interface:
- Classic Console:
Click on History
This page includes a column, Cluster Number, that specifies the cluster used to execute the statements submitted to eachwarehouse.
