PolarDB:SQL throttling

Overview

The SQL throttling feature enables you to establish throttling rules based on endpoints. It identifies the SQL statements executed on the current endpoint using SQL templates and restricts its maximum concurrent operations or QPS. This feature is useful in the following scenarios:

• PolarDB clusters experience slow SQL queries that cause high database workloads and disrupt normal business operations.

• You want to limit the resources allocated to a particular type of risky SQL or block its execution altogether.

Procedure

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. In the upper-left corner, select the region of the cluster. In the cluster list, find the cluster and click its ID to go to the Basic Information page.

2. In the left-side navigation pane, click Settings and Management > Security.

3. On the SQL Throttling tab, click Add to create a new SQL throttling rule.

   

4. In the New SQL Throttling Rule pop-up window, set the following configuration items and click OK.

   Section	Parameter	Description
   Basic Information	Rule Name	The name of the throttling rule. It must meet the following requirements: Up to 30 characters in length. Can contain uppercase and lowercase letters and digits.
   	Rule Description	Optional. The relevant information about the throttling rule for subsequent management. It can be up to 64 characters length.
   	EndpointID	The endpoint to which the throttling rule applies. Note Only cluster endpoints and custom endpoints with load balancing based on active requests (both read/write or read-only) support throttling rules. Primary endpoints and read-only endpoints with load balancing based on connections do not support SQL throttling. Throttling rules configured on different endpoints do not affect each other. Throttling rules configured on the same endpoint only apply to business connections using that endpoint.
   Rule Configuration	Rule Type	The throttling rule mode. Valid values: Throttle Active Concurrent Statements and Throttle QPS per Connection. Note The Throttle QPS per Connection mode limits the number of requests per second for a single connection. It can be used in scenarios where the business side has a connection pool configured or uses persistent connections. In short-lived connection scenarios, The Throttle Active Concurrent Statements mode is recommended.
   	Current Mode	The matching mode of the SQL template. Valid values: Template Match and Full-text Match. For the differences between the two matching modes, see Template matching and full-text matching.
   	Database Account Name	The account to which the throttling rule applies. You can configure up to 10 accounts. Separate multiple account names with commas. If empty, it applies to all accounts by default.
   	Database Name	The database to which the throttling rule applies. You can configure up to 10 databases. Separate multiple database names with commas. If empty, it applies to all databases by default.
   	SQL Template	The SQL template. For more information, see SQL templates and matching modes.
   	Maximum Waiting Queue Length	The maximum number of waiting queues. Valid values: 0 to 1024. When the threshold for concurrent statements or QPS is reached, requests are enqueued for retry. When the maximum number of waiting queues is exceeded, new requests trigger errors. Reasonably configuring the maximum number of waiting queues can prevent waiting queues from growing indefinitely when many SQL statements are throttled, which may cause out-of-memory of PolarProxy.
   	Maximum Active Concurrent Statements	The maximum number of concurrent SQL statements. Note This parameter is required only when the Rule Type parameter is set to Throttle Active Concurrent Statements.
   	Maximum QPS Per Connection	The maximum QPS for each connection. Note This parameter is required only when the Rule Type parameter is set to Throttle QPS per Connection.

How it works

The SQL throttling feature executed on the PolarProxy side. It manages the concurrent number or QPS of particular SQL statements by setting up various throttling rules on PolarProxy, ensuring no extra impacts are imposed on the read/write or read-only nodes of the database cluster. You can configure this feature only for cluster endpoints or custom endpoints.

SQL templates and matching modes

Template matching and full-text matching

An SQL template must adhere to the standard syntax for a PolarDB for PostgreSQL (Compatible with Oracle) cluster. Once the SQL template is configured, PolarProxy will apply various preprocessing steps to it based on the selected matching mode.

• Consider a throttling rule configured with the following SQL template:

  SELECT * FROM tbl WHERE id < 1;

  • For template matching, the SQL template is regularized by removing extra spaces and comments, and replacing constants such as single-quoted strings and numbers with wildcard characters. The following result is obtained:

    -- Template result
    SELECT * FROM tbl WHERE id < ?

  • For full-text matching, the SQL template is also regularized, but constants are not replaced. The following result is obtained:

    -- Only formatted result
    SELECT * FROM tbl WHERE id < 1

  PolarProxy then generates a unique identifier for the formatted SQL statement for subsequent matching.

• When the throttling rule is active, PolarProxy preprocesses each incoming SQL statement in a manner similar to the SQL template. Consider the following SQL statement:

  SELECT * FROM tbl WHERE id < 100;

  Two formatted SQL statements are then generated, unique identifiers are calculated, and the system attempts to match the throttling rule:

  -- Template result
  SELECT * FROM tbl WHERE id < ?
  
  -- Only formatted result
  SELECT * FROM tbl WHERE id < 100

After you enable SQL throttling rules, PolarProxy examines the existing throttling rules before processing the current SQL statement. For the template matching mode, it compares the SQL against the configured template. For the full-text matching mode, it matches the SQL statement against the formatted template specified in the rules. If a rule is matched, the system records the concurrent statements or QPS and carries out the appropriate throttling action.

Therefore, the preceding SQL statement and SQL template hits rules only when template matching is configured.

Parameterized queries

SQL templates support parameterized queries, adhering to the standard parameter binding syntax of PostgreSQL:

SELECT * FROM tbl WHERE id < $1 AND name = $2 LIMIT 1;

Parameterized sections are formatted as wildcard characters for both template matching and full-text matching:

-- Template result
SELECT * FROM tbl WHERE id < ? AND name = ? limit ?

-- Only formatted result
SELECT * FROM tbl WHERE id < ? AND name = ? limit 1

For the following SQL statement:

SELECT * FROM tbl WHERE id < $1 AND name = 2 LIMIT 100;

The current mode hits the specified rule when template matching used, but not when full-text matching is used.

-- Invalid SQL template. It does not adhere to the standard PostgreSQL syntax, so no SQL statement will hit the rule.
SELECT ?, ?, ?;

-- Valid SQL template.
SELECT $1, $2, $3;

Prepared statements

When using prepared statements in business operations, the PREPARE statement does not trigger throttling. Only the Execute statement does. In the Execute phase, the SQL statements from the PREPARE statement are formatted or templated to match the specified rule.

Examples

Use the following SQL template to configure a throttling rule and select template matching:

SELECT * FROM tbl WHERE id < $1 AND name > $2;

For the following business SQL statement:

-- The PREPARE statement will not trigger throttling.
PREPARE s1 AS SELECT * FROM tbl WHERE id < $1 AND name > 100;

-- The EXECUTE statement will use the SQL part in the PREPARE statement to match the throttling rule.
EXECUTE s1;

EXECUTE s1;

EXECUTE s1;

The three Execute statements will trigger the throttling rule and throttling actions.

Similarly, when you incorporate a PREPARE statement into the SQL template of a throttling rule, only the SQL part within the PREPARE statement will be formatted or templated for throttling purposes. The following two SQL templates are equivalent for creating a rule:

-- Template 1
PREPARE s1 AS SELECT * FROM tbl WHERE id < $1 AND name > $2;

-- Template 2
SELECT * FROM tbl WHERE id < $1 AND name > $2;

Extended queries

Like in the PREPARE statement, when the business driver employs the extended protocol, throttling is only initiated by the Execute message. Each Execute message is matched with its corresponding Parse message to compute the SQL template, which is then used to determine if the throttling rule applies. Consequently, SQL throttling is compatible with the extended protocol, and typically, no extra consideration is needed for the protocol utilized by the business.

Limits

The SQL throttling feature has the following limits:

• Throttling does not support multi-statement transactions. Throttling rules are not enabled when multi-statements are used.

  A multi-statement is a series of SQL statements separated by semicolons within a single SQL text. Here is an example of executing a multi-statement via the JDBC driver:

  Statement statement = connection.createStatement();
  
  statement.execute("select 1; select 2; select 3");

  Multi-statement executions may trigger multiple throttling rules at a time. To prevent unexpected results, throttling is not implemented for multi-statement executions.

• Some special statements, such as transaction control statements and stored procedures, are not eligible for throttling. For example, applying throttling to transaction control statements such as COMMIT could prevent the transaction from executing. Hence they are exempt from throttling rules.

• In statement batching mode used by clients or drivers, only the throttling rule that is hit first will be triggered for the executed SQL statements. The following example shows the batch execution via the JDBC driver:

  Statement statement = connection.createStatement();
  
  statement.addBatch("select 1");
  statement.addBatch("select 2");
  statement.addBatch("select 3");
  
  int[] result = statement.executeBatch();
  statement.close();
  connection.close();

  Similar to multi-statement executions, statement batching often combines several SQL extended protocol messages into a single transmission, potentially triggering multiple throttling rules simultaneously. In such cases, only the throttling rule that is hit first will be applied. If three SQL templates have throttling rules configured at the endpoint in the preceding example:

  -- Template 1
  SELECT 1;
  
  -- Template 2
  SELECT 2;
  
  -- Template 3
  SELECT 3;

  Only the first template rule will be triggered.

• SQL templates do not support case-insensitive keywords. When setting up SQL templates, the case must match that of the expected throttled SQL text.

• SQL templates also do not support variable-length expressions that ignore the number of elements, such as in/any clauses. Example:

  -- SQL template
  SELECT * FROM tbl WHERE id IN ($1, $2, $3);
  
  -- SQL1, can hit the template
  SELECT * FROM tbl WHERE id IN (1, 6, 8);
  
  -- SQL2, cannot hit the template
  SELECT * FROM tbl WHERE id IN (1, 6, 8, 8);

• Newly added throttling rules are not applicable to existing connections if no throttling rules are configured at first. However, if any throttling rule is present in the console (no matter whether it is enabled or not), any new additions, changes, or deletions will be applicable immediately to all connections.

  Note

  • If your businesses relies on persistent connections and requires new rules to take immediate effect, it is advisable to set up a placeholder rule on the relevant endpoint and keep it disabled. This allows any subsequent additions or changes to be applicable to both new and existing connections.

  • If you are using PolarProxy 2.3.58 or later, adding, modifying, and deleting throttling rules can take effect on all connections in real time. You can check the PolarProxy version in the PolarDB console. If the version does not meet the requirements, update the PolarProxy version.

Throttling methods

SQL templates and waiting queues are used for SQL throttling based on QPS or concurrent number of SQL statements. When a business SQL hits a predefined throttling rule, counting QPS or concurrent number of SQL statements is triggered. If the threshold for QPS or concurrent number of SQL statements is reached, PolarProxy places the SQL statement into a waiting queue. PolarProxy will then attempt to execute the statement again after a specified period, ensuring that the threshold is not exceeded.

The delay interval in the waiting queue is inversely proportional to the specified QPS or concurrent number of SQL statements of the rule. If the maximum waiting queue length of the rule is exceeded, PolarProxy will not process SQL statements and return the following error to the client:

SELECT 123;
Current query is being throttled and waiting queue is full.

Additionally, if the maximum number of concurrent statements or the maximum QPS per connection is set to 0 in the SQL throttling rule, any SQL statement matching the rule will be denied and the client will immediately receive the preceding error. This approach effectively blocks the execution of specific types of SQL statements.

Best practices

SELECT * FROM generate_series(1, 100000);