Hologres:Manage virtual warehouses

Scale up (vertical scaling)

Scaling up increases the per-virtual warehouse CU allocation. The instance must have enough unallocated resources before you scale up a virtual warehouse. Scale up the instance first if needed.

Impact on running queries and writes

Increasing instance resources doesn't affect currently executing queries and writes. Allocating resources to virtual warehouses have these impact:

• Starting from V3.0.28: queries and writes are not interrupted during scale-up and scale-out. Flink tasks may fail and retry.

• V2.2 - V3.0.28: queries and writes are interrupted for about 15 seconds, caused by automatic rebalance.

• Before V2.2: queries and writes are interrupted for about 15 seconds after manual rebalance.

Multi-row DML transactions are interrupted during scale-up regardless of version. This includes transactions with hg_experimental_enable_transaction enabled, native INSERT OVERWRITE statements, and incremental refreshes of Dynamic Tables.

-- Scale up the warehouse
CALL hg_alter_warehouse ('read_warehouse_1', 64);

-- Wait for the warehouse to be running
SELECT wait_until_warehouse_running ('read_warehouse_1');

-- Redistribute shards
SELECT hg_rebalance_warehouse ('read_warehouse_1');

-- Wait for rebalancing to complete
SELECT hg_wait_until_rebalance_warehouse_done ('read_warehouse_1');

Scale down (vertical scaling)

Scaling down triggers shard rebalancing first, migrating shards off the pods being taken offline, then removing those pods. Released resources return to the instance's unallocated resource pool.

Impact on running queries and writes

• Starting from V3.1 (Beta):

  • Major queries and writes are not interrupted.

  • Connections on the pods being taken offline experience a transient disconnection of a few seconds.

  • Flink tasks may fail and retry.

  • If a large query or write is running on the virtual warehouse when the scale-down is triggered and needs to continue for more than 60 minutes, it will error 60 minutes after scale-down starts.

• Before V3.1: queries and writes are interrupted for approximately 15 seconds during rebalancing.

Multi-row DML transactions are interrupted during scale-down regardless of version. This includes transactions with hg_experimental_enable_transaction enabled, native INSERT OVERWRITE statements, and incremental refreshes of Dynamic Tables.

After scale-down, use the freed resources to create new virtual warehouses, scale up other warehouses, or release resource from the instance.

Scale out (horizontal scaling)

Scaling out adds more clusters to the virtual warehouse. The same usage notes as scale-up apply.

Scale in (horizontal scaling)

Scaling in reduces the number of clusters in the virtual warehouse. The same usage notes as scale-down apply.

View virtual warehouses

-- View all virtual warehouses in the current instance
SELECT * FROM hologres.hg_warehouses;

-- View the virtual warehouse currently in use
SELECT current_warehouse();

The hologres.hg_warehouses system table contains the following columns:

Stop a virtual warehouse

You cannot stop the default virtual warehouse (init_warehouse) or a primary virtual warehouse whose table group has a secondary virtual warehouse.

Stopping is asynchronous. Use hg_get_warehouse_status to verify the warehouse has stopped.

CALL hg_suspend_warehouse ('<warehouse_name>');

Resume a virtual warehouse

Resuming a virtual warehouse is asynchronous. Use hg_get_warehouse_status to verify the warehouse is running before sending queries.

CALL hg_resume_warehouse ('<warehouse_name>');

Restart a virtual warehouse

Restarting a virtual warehouse is asynchronous. Use hg_get_warehouse_status to verify the warehouse is running before sending queries.

CALL hg_restart_warehouse('<warehouse_name>');

Check virtual warehouse status

All create, scale, stop, resume, and restart operations are asynchronous. Use the following functions to monitor status.

-- Get the current status of a virtual warehouse
select hg_get_warehouse_status('<warehouse_name>');

To block until a virtual warehouse reaches the Running state, use wait_until_warehouse_running:

select wait_until_warehouse_running('<warehouse_name>', <timeout_seconds>);

Example: Scale up warehouse_1 before a data load, then scale it back down.

-- Scale up to 64 CUs
CALL hg_alter_warehouse ('warehouse_1', 64);

-- Wait until running
SELECT wait_until_warehouse_running('warehouse_1');

-- Run the data load
INSERT INTO res_data SELECT * FROM org_data;

-- Scale back down to 32 CUs
CALL hg_alter_warehouse ('warehouse_1', 32);

Rename a virtual warehouse

CALL hg_rename_warehouse('<org_warehouse_name>', '<new_warehouse_name>');

Rebalance a virtual warehouse

Worker nodes in a virtual warehouse evenly load shard metadata under normal conditions. After fast recovery, scale-up, or scale-out, shards may become unevenly distributed. Trigger a rebalance to redistribute shards so each worker node loads an approximately equal number.

• Starting from V2.1: Hologres automatically triggers rebalancing when a warehouse has empty worker nodes.

• Starting from V2.2: SELECT queries that run during rebalancing are automatically retried, preventing errors such as ERROR: fail to execute query internal error: Get rundown is not allowed in recovering state.

A rebalance operation typically takes 2–3 minutes, depending on the number of table groups. Write operations are interrupted for approximately 15 seconds during rebalancing.

Syntax

SELECT hg_rebalance_warehouse ('<warehouse_name>');

Return values

When the difference in the number of shards loaded by each worker node is 1 or fewer, this signals rebalance is finished. For example:

• 2 workers, 2 shards: each worker loads 1 shard.

• 2 workers, 3 shards: one worker loads 1 shard and the other loads 2 shards.

Because rebalancing is asynchronous, monitor progress with:

SELECT hg_get_rebalance_warehouse_status('<warehouse_name>');

DOING means rebalancing is in progress. DONE means it is complete.

Set the statement timeout for a virtual warehouse

Virtual warehouse-level timeout has lower priority than session-level and database-level configurations. To make the warehouse-level setting effective, first reset the database-level configuration.

-- Reset the database-level setting
ALTER DATABASE <db_name> RESET statement_timeout;

-- Set the timeout for the virtual warehouse (value in milliseconds)
CALL hg_alter_warehouse ('<warehouse_name>', <cu>, '{"configs":{"gucs":{"statement_timeout":"600000"}}}');

For more information about statement_timeout and other GUC parameters, see GUC parameters.