This article provides an overview of the function definition, core advantages, and usage guidelines for ByteHouse cloud data warehouse virtual warehouse.

The virtual warehouse (Virtual Warehouse, VW) is a cluster of calculate resources in ByteHouse, offering core resources such as CPU, memory, and temporary storage. It provides stable computing power for various data operations, supporting users in running SQL queries and write tasks, including:

• Running SQL statements that require calculate resources (such as retrieving rows from tables and views), covering both short-term and long-term query scenarios;
• Running DML operations, such as:
  • Performing row-level operations like deleting, inserting, and updating data in tables;
  • Loading data into tables or exporting data from tables.

The virtual warehouse provides independent isolation and elastic scaling of calculate resources. Different virtual warehouses operate without interfering with each other, making them suitable for multi-tenant and multi-business workload scenarios.

• Calculate isolation: Provides independent calculate resources for different users or workloads to avoid resource contention.
• Elastic scaling: The virtual warehouse can dynamically increase or decrease calculate nodes based on workload, optimizing cost and performance.

ByteHouse virtual warehouse offers the following advantages:

• Resources Isolation and read/write separation: Supports read/write separation, allowing data import and query operations to be assigned to different virtual warehouses. Resource isolation can also be implemented for different departments and business queries, preventing interference between business queries and improving operational stability.
• Elasticity Scaling and cost optimization: Elastic scaling according to business needs reduces resource waste while ensuring performance, achieving controllable costs and maximizing resource utilization.
• Support for OLAP and ELT type virtual warehouses: You can select the corresponding type as needed and flexibly customize parameters based on business scenarios. This meets the optimization requirements for query performance in OLAP analysis scenarios and adapts to the stability requirements of ELT tasks in batch processing scenarios.
• Custom virtual warehouse parameters: ByteHouse provides a fixed parameter template for virtual warehouses, allowing you to configure virtual warehouse parameters to lift data warehouse performance. ByteHouse also supports adding custom SQL parameters to better satisfy specific business scenarios.
• Fine-grained access control: Supports fine-grained access control for virtual warehouse resources, meeting privilege access control requirements for different roles.
• Flexible spec selection: You can select XS, S, M, L, or XL virtual warehouse specifications as needed. After creation is complete, you can manually adjust the virtual warehouse specification according to actual CPU or memory load, referring to Edit virtual warehouse. If the current cluster performance does not satisfy your requirements, you can contact ByteHouse through a ticket for customized configuration of the virtual warehouse.

Automatic start and stop #

ByteHouse cloud data warehouse virtual warehouse supports an automatic start and stop strategy, helping you save resource costs. When the virtual warehouse is idle for longer than the set period, the automatic start and stop feature will automatically shut down the cluster. No fees are incurred while the virtual warehouse is terminated, saving costs and meanwhile avoiding unnecessary resource waste during low-load periods. When you have a search requirement, after sending a search request, the virtual warehouse will launch automatically, waking up resources and carrying on the search. This mode enables flexible adjustment of resource usage based on actual load, achieving cost optimization.

Read/write separation #

ByteHouse natively supports storage and calculate separation. Write and background missions can use a dedicated virtual warehouse for writing, while foreground search uses a dedicated virtual warehouse for reading. There is no impact between read and write missions, making it suitable for busy read/write business scenarios.

• Write: Assign a dedicated virtual warehouse to handle write missions, ensuring real-time update and accuracy of data. Write missions typically require high data consistency and reliability, so dedicated calculate resources are needed to ensure efficient execution.
• Query: Assign a dedicated virtual warehouse to handle query tasks, ensuring support for a large number of concurrent query requests. Query tasks often require fast response times and high data accuracy. By using dedicated computing resources, you can achieve faster query speeds and more stable service quality.

Scheduled elasticity #

ByteHouse supports flexible configuration of scheduled scaling strategies for virtual warehouses based on business needs. You can Scheduled tasksset a time period, and the system will automatically trigger elastic scaling according to the schedule, matching query traffic and workload across different time periods. You can plan in advance the expansion or reduction of computing resources based on business requirements and resource usage predictions, ensuring efficient resource utilization and timely response.
With ByteHouse's scheduled elasticity capability, you will gain the following benefits:

• Cost optimization: Automatically reduce resource scale during business off-peak periods, significantly lowering computing costs.
• Automated operations and maintenance: Achieve intelligent resource scheduling and configuration adjustments without relying on external tools or manual intervention, improving operations and maintenance efficiency.

Load elasticity #

Load elasticity means that ByteHouse virtual warehouses support automatic scaling. For L and above specifications, virtual warehouses can dynamically allocate elastic resources and automatically adjust their specifications according to preset strategies to adapt to business load fluctuations. Elastic resources are billed based on actual usage.
You can refer to Create or edit virtual warehouse for instructions to enable load elasticity. After enabling, the virtual warehouse consists of basic resources and elastic resources. When CPU or memory metrics reach the set threshold, the system automatically scales elastic resources. The trigger mechanism is as follows:

• Scaling trigger mechanism: When the average value of either CPU or memory metric exceeds the set threshold (80% or 90%) for 5 consecutive periods (each period is 1 minute, totaling 5 minutes), the system will automatically scale up.
• Downsizing trigger mechanism: After elastic resources are scaled up, when the average value of either the CPU or memory metric remains below the set threshold (40%) for five consecutive periods, the system will automatically downsize.

During scaling operations, load elasticity adopts a stepwise adjustment strategy, scaling up or down by one specification of elastic resources at a time:

• The scaling up scope covers up to three specifications above the base resources. For example, when the base resources is L, elastic resources can be gradually scaled up to L, XL, and 2XL specifications.
• When downsizing, elastic resources will be gradually reduced from the current highest specification to 0, that is, elastic resources will be terminated.
• When the base resources specification of the virtual warehouse is adjusted, elastic resources will automatically adapt to the latest base resources specification in the next scaling up period and run scaling actions according to the new scope. To expand elastic resources to a higher specification when the resources of the virtual warehouse after scaling up still do not satisfy your business needs, please refer to Edit virtual warehouse configuration to increase the specification of the base resources.

The following table uses a virtual warehouse with base resources specification L as an example to demonstrate the load elasticity function:

• Trigger condition:

  • Scaling up: CPU or memory load exceeds 80% and lasts for five periods.
  • Downsizing: CPU or memory load is less than 40% and lasts for five periods.

• Scaling up process:

  • For a virtual warehouse with base resources specification L, if the CPU load exceeds the set threshold of 80% for five consecutive periods (T-1 to T-5), the system will scale up stepwise in period T-6, first scaling up by one specification of elastic resources (that is, L→L+L).
  • In the next five periods (T-7 to T-11), if the CPU load still exceeds the threshold, the system will scale up to a higher specification in period T-12 (that is, L+L→L+XL).
  • In the following five periods (T-13 to T-17), if the CPU load continues to exceed the threshold, the system will continue scaling up in period T-18 (that is, L+XL→L+2XL). At this point, the max scale 2XL is reached, and no further scaling up will occur.
  • To satisfy your business needs when the resources of the current virtual warehouse still do not suffice, please refer to Edit virtual warehouse configuration to adjust the specification of the base resources. Elastic resources will automatically adapt to the latest base resources specification in the next scaling up period and continue scaling up.

  Time	CPU load	Memory load	Basic resources	Elastic resources	Scaling action
  T-0	10%	30%	L	/	No scaling
  T-1	85%	30%	L	/	No scaling
  T-2	85%	30%	L	/	No scaling
  T-3	85%	30%	L	/	No scaling
  T-4	85%	30%	L	/	No scaling
  T-5	85%	30%	L	/	No scaling
  T-6	85%	30%	L	L	Scale down to L
  T-7	85%	30%	L	L	No scaling
  T-8	85%	40%	L	L	No scaling
  T-9	85%	40%	L	L	No scaling
  T-10	85%	40%	L	L	No scaling
  T-11	85%	40%	L	L	No scaling
  T-12	85%	40%	L	XL	Scale down to XL
  T-13	85%	40%	L	XL	No scaling
  T-14	85%	40%	L	XL	No scaling
  T-15	85%	40%	L	XL	No scaling
  T-16	85%	40%	L	XL	No scaling
  T-17	85%	40%	L	XL	No scaling
  T-18	85%	40%	L	2XL	Scale down to 2XL

• Downsizing process:

  • During periods of low business load, if the CPU load remains below the set threshold of 40% for five consecutive periods (T-1 to T-5), the system will scale down in period T-6 in steps from the current maximum specification (2XL), first reducing one specification of elastic resources (that is, L+2XL→L+XL).
  • For the next five periods (T-7 to T-11), if the CPU load continues to be below the threshold, the system will scale down to a lower specification in period T-12 (that is, L+XL→L+L).
  • In the following five periods (T-13 to T-17), if the CPU load remains below the threshold, the system will continue to scale down in period T-18 (that is, L+L→L). At this point, elastic resources are 0, and the virtual warehouse retains only the basic resources and will not scale down further.

  Time	CPU load	Memory load	Basic resources	Elastic resources	Scaling action
  T-0	60%	60%	L	2XL	No scale-down
  T-1	30%	40%	L	2XL	No scale-down
  T-2	30%	40%	L	2XL	No scale-down
  T-3	30%	40%	L	2XL	No scale-down
  T-4	30%	40%	L	2XL	No scale-down
  T-5	30%	40%	L	2XL	No scale-down
  T-6	30%	40%	L	XL	Scale down to XL
  T-7	30%	40%	L	XL	No scale-down
  T-8	30%	40%	L	XL	No scale-down
  T-9	30%	40%	L	XL	No scale-down
  T-10	30%	40%	L	XL	No scale-down
  T-11	30%	40%	L	XL	No scale-down
  T-12	30%	40%	L	L	Scale down to L
  T-13	30%	40%	L	L	No scale-down
  T-14	30%	40%	L	L	No scale-down
  T-15	30%	40%	L	L	No scale-down
  T-16	30%	40%	L	L	No scale-down
  T-17	30%	40%	L	L	No scale-down
  T-18	30%	40%	L	/	Shut down elastic resources

• Virtual warehouse billing rules:
  • Specifications and pricing
  • Compute Group Specification Change
• Virtual warehouse use:
  • Create or edit virtual warehouse
  • Manage parameters
  • Start and stop virtual warehouse
  • Delete virtual warehouse