Hologres:Automatic throttling (beta)

Scenarios

The principle of automatic throttling is to dynamically adjust the maximum concurrency of query queues based on load conditions. By controlling the number of concurrently executing queries in a virtual warehouse, it prevents computing resources from becoming overloaded. This feature is suitable for the following scenarios:

• High concurrency scenarios: If the concurrency is high and the overhead of each request is relatively uniform, the automatic throttling feature will dynamically adjust the concurrency limit. The CPU curve will ultimately stabilize at a high level without reaching 100% utilization.

  Note

  • If the concurrency is too low, when the automatic throttling feature dynamically adjusts the concurrency limit, the CPU curve can easily fluctuate up and down, creating a "sawtooth" pattern and resulting in low CPU utilization.

  • In more extreme cases, if there is only a single large query request, the automatic throttling feature will not take effect.

• Scenarios where request delays are tolerable: Since this feature limits traffic, queries exceeding the limit will queue in the query queue, which will increase the latency of these requests accordingly.

• Unpredictable high-concurrency periods: When it is difficult to predict when traffic requests will surge, making it challenging to reserve computing resources in advance, you can use the automatic throttling feature to reduce system stability risks.

For the following scenarios, other solutions are recommended:

• Obvious periodic time patterns: If the daily traffic peak periods are relatively fixed, it is recommended to use the time-specific scaling feature of virtual warehouses for scheduled scaling.

• Large queries with low concurrency: The automatic throttling feature has limited effect on large queries with low concurrency. It is recommended to use serverless computing resources to execute these requests. For more information, see Serverless Computing guide.

• Zero tolerance for request delays: You need to increase computing resources to accommodate both high concurrency and low latency requirements. You can scale out computing resources or use the large query control feature of query queues to forward slow-executing requests to serverless resources for execution. For more information, see Large query control.

Prerequisites

You have created a query queue.

Usage notes

• Only virtual warehouse instances of Hologres V3.1 or later support automatic throttling. General-purpose instances do not support this feature.

  Note

  You can first convert a general-purpose instance to a virtual warehouse instance and then use this feature. For more information, see Change the instance type from general-purpose to virtual warehouse for an instance.

• Automatic throttling applies only to requests in query queues. This includes queries with engine_type such as HQE, PQE, SQE and HiveQE, as well as query types like SELECT, INSERT, UPDATE, DELETE, and INSERT with COPY and CREATE TABLE AS (CTAS).

• Because queries based on fixed plans do not enter query queues, they are not subject to the concurrency limits of the automatic throttling feature. For more information, see Accelerate the execution of SQL statements by using fixed plans. It is recommended to use different virtual warehouses to isolate fixed plan requests from other requests for better resource and request management. For more information, see Getting started with virtual warehouses.

Use automatic throttling

This section describes how to configure automatic throttling for virtual warehouses and query queues.

Monitoring and O&M

You can click the target instance ID in the Hologres console and view the Instance CPU Usage(%) metric on the Monitoring Information page to monitor the usage of the automatic throttling feature.

Example

This section uses PostgreSQL's native performance testing tool pgbench to demonstrate an example and the effects of automatic throttling.

1. Create test tables in Hologres and insert data.

   CREATE TABLE tbl_1 (col1 INT, col2 INT, col3 TEXT);
   CREATE TABLE tbl_2 (col1 INT, col2 INT, col3 TEXT);
   INSERT INTO tbl_1 SELECT i, i+1, md5(RANDOM()::TEXT) FROM GENERATE_SERIES (0, 500000) AS i;
   INSERT INTO tbl_2 SELECT i, i+1, md5(RANDOM()::TEXT) FROM GENERATE_SERIES (0, 500000) AS i;

2. Set the maximum concurrency of the query queue to 100 in Hologres, using the virtual warehouse init_warehouse and query queue default_queue as an example.

   CALL hg_set_query_queue_property ('init_warehouse', 'default_queue', 'max_concurrency', '100');

3. Create a performance test SQL file select.sql in the bin directory where pgbench is located in the client, and write the following SQL statement:

   EXPLAIN ANALYZE SELECT * FROM tbl_1 LEFT JOIN tbl_2 ON tbl_1.col3 = tbl_2.col3 ORDER BY 1;

4. Add the following command to the server's configuration file and save it. Set the password as an environment variable.

   export PGPASSWORD='<AccessKey_Secret>'

5. Go to the bin directory where pgbench is located in the client and execute the following performance test command:

   ./pgbench
   -c 30 \
   -j 30 \
   -f select.sql \
   -d <Database> \
   -U <AccessKey_ID> \
   -h <Endpoint> \
   -p <Port> \
   -T 1800

   For parameter configuration details, see Connect to a Hologres instance for data development.

6. During the performance test, enable and disable the automatic throttling feature in Hologres in sequence, and observe the CPU utilization metric of the virtual warehouse until the test is complete.

   -- Enable automatic throttling.
   CALL hg_set_warehouse_adaptive_concurrency_limiting ('init_warehouse', true);
   
   -- Disable automatic throttling.
   CALL hg_set_warehouse_adaptive_concurrency_limiting ('init_warehouse', false);