PolarDB:SQL throttling

Overview

The SQL throttling feature lets you configure throttling rules based on endpoints. It uses SQL templates to match SQL statements that are executed on the current endpoint and limits their maximum concurrency or queries per second (QPS). You can use this feature in the following scenarios:

• A PolarDB cluster has slow SQL statements that cause a high database load and affect normal business operations.

• You want to limit the resources available to a specific type of high-risk SQL statement or completely deny its execution.

Procedure

1. Log on to the PolarDB console. In the navigation pane on the left, click Cluster List. Select the region where the cluster is located. Click the ID of the target cluster to open the cluster details page.

2. In the navigation pane on the left, choose Settings and Management > Security.

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

   

4. In the Create SQL Throttling Rule dialog box, set the following parameters and click OK.

   Section	Parameter	Description
   Basic Information	Rule Name	The name of the throttling rule. It must meet the following requirements: It can be up to 30 characters in length. It must consist of uppercase letters, lowercase letters, and digits.
   	Description	Optional. The description of the throttling rule. This helps with future management. The description can be up to 64 characters in length.
   	EndpointID	Select the endpoint to which the throttling rule applies. Note Currently, you can configure throttling rules only on cluster endpoints and custom endpoints that use load balancing based on active requests. These endpoints can be read/write or read-only. Primary endpoints and read-only endpoints that use load balancing based on connections do not support SQL throttling. Throttling rules that are configured on different endpoints do not affect each other. Throttling rules that are configured on the same endpoint apply only to business connections that use that endpoint.
   Configurations	Rule Type	Select the throttling rule mode. Valid values are Throttle Active Concurrent Statements and Throttle QPS per Connection. Note Throttling QPS per connection limits the number of requests per second for a single connection. You can use this mode in scenarios where the business side has a connection pool or uses persistent connections. In scenarios with short-lived connections, we recommend that you use active concurrent statement throttling.
   	Current Mode	Select the matching mode for the SQL template. Valid values are Template Match and Full-text Match. For more information about the differences between the two matching modes, see Template matching and full-text matching.
   	Database Account Name	Specify the database accounts to which the throttling rule applies. You can specify up to 10 accounts. Separate multiple account names with commas (,). If you leave this parameter empty, the rule applies to all accounts by default.
   	Database Name	Specify the databases to which the throttling rule applies. You can specify up to 10 databases. Separate multiple database names with commas (,). If you leave this parameter empty, the rule applies to all databases by default.
   	SQL Template	Configure the SQL template. For more information, see SQL templates and matching modes.
   	Maximum Waiting Queue Length	Configure the maximum length of the waiting queue. The value must be in the range of 0 to 1024. When the concurrency or QPS of a matching SQL statement reaches the rule limit, the statement enters a delay queue for a retry. When the number of SQL statements in the delay queue exceeds the maximum length, new requests directly return an error. A reasonable maximum waiting queue length can prevent the delay queue from growing indefinitely when many SQL statements are throttled. This avoids an out-of-memory (OOM) error in the database proxy.
   	Maximum Active Concurrent Statements	Set the maximum number of active concurrent statements. Note This parameter is required only when the Rule Type parameter is set to Throttle Active Concurrent Statements.
   	Maximum QPS per Connection	Set 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

SQL throttling is a feature implemented on the database proxy. You configure throttling rules on the database proxy to control the concurrency or QPS of specific forwarded SQL statements. This does not affect the read-write or read-only nodes of the database cluster. Therefore, you can configure this feature only for cluster or custom endpoints.

SQL templates and matching modes

Template matching and full-text matching

An SQL template can be any SQL statement that follows the standard syntax of a PolarDB for PostgreSQL or cluster. After you configure an SQL template, the database proxy preprocesses it based on the matching mode you select.

• Assume you configure a throttling rule with the following SQL template:

  SELECT * FROM tbl WHERE id < 1;

  • If you select template matching, the SQL template is regularized. Extra spaces and comments are removed. Constant parts in the SQL statement, such as single-quoted strings and numbers, are replaced with wildcard characters. The result is as follows:

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

  • If you select full-text matching, the SQL template is also regularized. However, the constant parts are not replaced. The result is as follows:

    -- Formatted result only
    SELECT * FROM tbl WHERE id < 1

  The database proxy then generates a unique identifier for the formatted SQL statement for subsequent matching.

• When the throttling rule is enabled, the database proxy also preprocesses every business SQL statement in a way similar to the SQL template. Take the following business SQL statement as an example:

  SELECT * FROM tbl WHERE id < 100;

  Two formatted SQL statements are also generated, a unique identifier is calculated, and a match with the throttling rule is attempted:

  -- Template result
  SELECT * FROM tbl WHERE id < ?
  
  -- Formatted result only
  SELECT * FROM tbl WHERE id < 100

After you enable an SQL throttling rule, the database proxy traverses the currently configured throttling rules before it forwards the current SQL statement. If the throttling rule is configured for template matching, the templated result is used to match the SQL template in the rule. If the throttling rule is configured for full-text matching, the formatted result is used to match the SQL template in the rule. After a match is hit, the concurrency or QPS is recorded, and the corresponding throttling operation is performed.

Therefore, for the business SQL statement and SQL template in the preceding example, the rule is hit only when template matching is configured.

Parameterization support

SQL templates allow parameterized syntax, which is consistent with the standard parameter binding syntax of PostgreSQL:

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

The parameterized part is formatted as a wildcard character in both template matching and full-text matching:

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

-- Formatted result only
SELECT * FROM tbl WHERE id < ? AND name = ? limit 1

Therefore, for the following business SQL statement:

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

If the current mode is set to template matching, the preceding rule can be hit. If the current mode is set to full-text matching, the preceding rule cannot be hit.

-- This is an invalid SQL template. It does not follow the standard PostgreSQL syntax and will not match any SQL statement.
SELECT ?, ?, ?;

-- This is a valid SQL template.
SELECT $1, $2, $3;

Prepared statements

When your business uses prepared statements, the PREPARE statement itself does not trigger throttling. Only the EXECUTE statement triggers throttling. For an EXECUTE statement, the SQL part in its corresponding PREPARE statement is formatted or templated to match the rule.

Example

Use the following SQL template to configure a throttling rule. Select template matching as the matching mode.

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

For the following business SQL statement:

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

-- The EXECUTE statement uses the SQL part in its corresponding PREPARE statement to match the throttling rule.
EXECUTE s1;

EXECUTE s1;

EXECUTE s1;

The three EXECUTE statements will hit the throttling rule and be throttled.

Similarly, when you use a PREPARE statement in the SQL template of a throttling rule, only the SQL part within the PREPARE statement is formatted or templated for throttling. Therefore, the following two SQL templates are equivalent when you create 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 protocol support

Similar to prepared statements, when a business driver uses the extended protocol, only Execute messages trigger throttling. For each Execute message, the database proxy finds its corresponding Parse message and calculates the SQL template to match the throttling rule. Therefore, SQL throttling supports the extended protocol. You do not need to be concerned about the protocol that your business uses.

Limits

The SQL throttling feature has the following limits:

• Multi-statement SQL is not supported for throttling. If you use multi-statement SQL, no configured throttling rules are triggered.

  Multi-statement SQL refers to a single SQL text that contains multiple SQL statements separated by semicolons. The following is an example of a multi-statement execution that uses a Java Database Connectivity (JDBC) driver:

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

  A multi-statement execution might hit multiple throttling rules at the same time. To avoid unexpected results, throttling is not supported.

• Some special statements, such as transaction control statements and stored procedures, are not supported for throttling. Throttling transaction control statements such as COMMIT can prevent transactions from ending normally. Therefore, these statements do not hit throttling rules.

• When a client or driver uses statement batching mode, only the first throttling rule that is hit is triggered for the batched SQL statements. The following is an example of a batch execution that uses a 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, when you use statement batching, the driver usually combines the extended protocol messages of multiple SQL statements and sends them at once. This can also lead to a situation where multiple throttling rules are hit at the same time. In this case, only the first throttling rule that is hit is enabled. For the batch execution example, if throttling rules for the following three SQL templates are configured on the endpoint at the same time:

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

  Only Template 1 is hit.

• SQL templates do not support case-insensitive keywords. When you configure an SQL template, the case must match the case of the SQL text that you want to throttle.

• SQL templates do not support templating for variable-length expressions such as `in` or `any` that ignore the number of elements. For 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);

• If no throttling rules are configured, a newly added rule cannot take effect on existing connections. If any throttling rule exists in the console, regardless of whether it is enabled, subsequent add, modify, or delete operations take effect on all connections in real time.

  Note

  • If your business uses persistent connections and you want new rules to take effect immediately at any time, we recommend that you configure an arbitrary rule on the corresponding endpoint, disable it, and keep it. Subsequent additions or modifications to rules can then take effect on both new and old connections.

  • If the database proxy version is 2.3.58 or later, adding, modifying, or deleting throttling rules can take effect on all connections in real time. You can view the version of the database proxy in the console. If the minor engine version does not meet the requirements, you can upgrade the database proxy version.

Throttling methods

Currently, SQL throttling uses SQL templates and a delay queue to implement QPS or active concurrency throttling. A business SQL statement must hit a throttling rule before the QPS or concurrency on the corresponding rule is recorded. When the concurrency or QPS exceeds the limit that is configured in the rule, the database proxy places the SQL statement in a delay queue. After a period of time, the proxy retries the statement. This maintains the concurrency or QPS on the database side at a level that does not exceed the rule limit.

The delay time in the delay queue is inversely proportional to the QPS or concurrency that is configured in the rule. The maximum number of SQL statements that can hit a rule and wait in the delay queue is limited by the maximum waiting queue length that is configured in the rule. If the limit is exceeded, the proxy does not forward the SQL statement. Instead, it returns the following error to the client:

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

In addition, if the maximum active concurrency or maximum QPS per connection in a configured SQL throttling rule is 0, any SQL statement that hits the rule is denied forwarding. The preceding error is returned directly to the client. You can use this method to completely deny the execution of a class of SQL statements.

Best practices

SELECT * FROM generate_series(1, 100000);