SQL throttling

Introduction

The SQL throttling feature allows you to configure throttling rules for specific endpoints. You can use an SQL template to match SQL statements executed on the current endpoint and limit their maximum concurrency or queries per second (QPS). This feature can be used in the following scenarios:

• A PolarDB cluster experiences high database load due to a slow SQL query, which affects normal business operations.

• You want to limit the resources available to specific types of risky SQL queries or block their execution entirely.

Procedure

1. Log on to the PolarDB console. In the left-side navigation pane, click Clusters. Select the region where the cluster is located, and click the cluster ID to open the cluster details page.

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

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

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

   Category	Parameter	Description
   Basic Information	Rule Name	The name of the throttling rule. The name must meet the following requirements: Be 30 characters or shorter. Contain only uppercase letters, lowercase letters, and digits.
   	Description	Optional. A description for the throttling rule for easier management. It must be 64 characters or shorter.
   	EndpointId	Select the endpoint to which the throttling rule applies. Note You can configure throttling rules only for a cluster endpoint or a custom endpoint (read/write or read-only) that uses active-request-based load balancing. SQL throttling is not supported for primary endpoints or for read-only endpoints that use connection-based load balancing. Rules are endpoint-specific. A rule configured for one endpoint affects only connections made to that endpoint.
   Configurations	Rule Type	Select a rule type. Throttle Active Concurrent Statements and Throttle QPS per Connection are supported. Note Throttle QPS per Connection limits the number of requests per second for a single connection. This type is suitable for scenarios where a connection pool or a persistent connection is used. For short-lived connections, use Throttle Active Concurrent Statements.
   	Current Mode	Select a matching mode for the SQL template. Template Match and Full-text Match are supported. For more information about the differences between the two modes, see Template match vs. full-text match.
   	Database Account Name	Specifies the accounts to which the rule applies. You can specify up to 10 accounts, separated by commas. If left empty, the rule applies to all accounts.
   	Database Name	Specifies the databases to which the rule applies. You can specify up to 10 databases, separated by commas. If left empty, the rule applies to all databases.
   	SQL Template	Configure the SQL template. For more information, see SQL templates and matching modes.
   	Maximum Waiting Queue Length	The maximum length of the waiting queue. The value can range from 0 to 1024. When the concurrency or QPS of matched SQL queries reaches the rule's limit, the proxy adds the queries to a waiting queue to be retried. If the number of queries in the queue exceeds this limit, new requests fail and an error is returned. Setting this parameter correctly prevents the waiting queue from growing indefinitely and causing an out of memory (OOM) error on the database proxy when many SQL queries are throttled.
   	Maximum Active Concurrent Statements	The maximum number of active concurrent statements. Note This parameter is required only when Throttle Active Concurrent Statements is set to Throttle Active Concurrent Statements.
   	Maximum QPS per Connection	The maximum QPS for each connection. Note This parameter is required only when Throttle QPS per Connection is set to Throttle QPS per Connection.

How it works

SQL throttling is implemented at the database proxy level. You configure throttling rules on the database proxy to control the concurrency or QPS of specific forwarded SQL statements. This process does not add overhead to the read/write or read-only nodes of the database cluster. As a result, you can configure rules only for cluster and custom endpoints, which route traffic through the proxy.

SQL templates and matching modes

Template match vs. full-text match

An SQL template can be any SQL statement that follows the standard syntax of a PolarDB for PostgreSQL or cluster. The database proxy preprocesses the template differently based on the selected matching mode.

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

  SELECT * FROM tbl WHERE id < 1;

  • If you select Template Match, the SQL template is normalized. Extra spaces and comments are removed, and constants such as strings in single quotation marks and numbers are replaced with a wildcard. The result is:

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

  • If you select Full-text Match, the SQL template is also normalized, but the constants are not replaced. The result is:

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

  The database proxy then generates a unique identifier for the processed SQL for later matching.

• After the throttling rule is enabled, the database proxy preprocesses each incoming SQL statement similarly. For example, consider the following incoming SQL statement:

  SELECT * FROM tbl WHERE id < 100;

  Two types of normalized SQL are generated, and their unique identifiers are calculated to match against the throttling rule:

  -- Templated result
  SELECT * FROM tbl WHERE id < ?
  -- Normalized result only
  SELECT * FROM tbl WHERE id < 100

After an SQL throttling rule is enabled, the database proxy evaluates the statement against the configured rules before forwarding an SQL statement. If the rule is configured for Template Match, it uses the templated result for matching. If the rule is configured for Full-text Match, the normalized-only result is used. Once a match is found, its concurrency or QPS is calculated, and the proxy performs the corresponding throttling action.

Therefore, for the SQL statement and SQL template in the preceding example, a match is found only when the rule is configured for Template Match.

Parameterized queries

SQL templates support parameterized queries that use the standard PostgreSQL parameter binding syntax:

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

In both Template Match and Full-text Match modes, the parameterized parts are formatted as wildcards:

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

Therefore, for the following SQL statement:

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

A match is found if the Current Mode is set to Template Match. A match is not found if the Current Mode is set to Full-text Match.

-- Invalid SQL template. This does not conform to standard PostgreSQL syntax and will not match any SQL.
SELECT ?, ?, ?;
-- Valid SQL template
SELECT $1, $2, $3;

Prepared statements

When your application uses a prepared statement, the PREPARE statement itself does not trigger throttling. Only the EXECUTE statement triggers throttling. For an EXECUTE statement, the SQL part of the corresponding PREPARE statement is formatted or templated to match the rules.

Example

Configure a throttling rule with the following SQL template and select Template Match for the matching mode:

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

For the following 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 from its corresponding PREPARE statement to match the throttling rule.
EXECUTE s1;
EXECUTE s1;
EXECUTE s1;

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

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

Similar to a prepared statement, when an application driver uses the extended query protocol, only the Execute message triggers throttling. For each Execute message, the database proxy finds the corresponding Parse message and uses its SQL to match throttling rules. Therefore, SQL throttling supports the extended query protocol, so you typically do not need to be concerned with the protocol your application uses.

Limitations

Currently, the SQL throttling feature has the following limitations:

• Throttling is not supported for multi-statements. If you use a multi-statement, no configured throttling rules are triggered.

  A multi-statement refers to a single SQL text that contains multiple SQL statements separated by semicolons. The following is an example of a multi-statement executed using a JDBC driver:

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

  A multi-statement could match multiple throttling rules simultaneously. To prevent unexpected behavior, throttling is not supported for multi-statements.

• Throttling is not supported for some special statements, such as transaction control statements and stored procedures. Throttling a transaction control statement like COMMIT would prevent transactions from ending normally. For this reason, they are exempt from throttling rules.

• When a client or driver uses statement batching mode, batched SQL queries only trigger the first throttling rule that is matched. The following is an example of statement batching using 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 a multi-statement, when you use statement batching, the driver usually combines the extended query protocol messages for multiple SQL queries and sends them at once. This can also result in a situation where multiple throttling rules are matched simultaneously. In this case, only the first matched throttling rule takes effect. In the preceding example, if throttling rules for the following three SQL templates are configured on the endpoint:

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

  Only Template 1 will be matched.

• The keyword case in your template must match the case in the SQL text you want to throttle.

• SQL templates do not support templating for variable-length expressions, such as IN or ANY, where the number of elements can differ. For example:

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

• When no throttling rules are configured, the first rule you add does not apply to existing connections. If any rule is already configured in the console (enabled or disabled), subsequent additions, modifications, or deletions of rules take effect in real time for all connections.

  Note

  • If your application uses a persistent connection and you want new rules to take effect immediately, we recommend that you configure and disable an arbitrary rule on the endpoint. Then, any subsequent rules you add or modify will take effect for both new and existing connections.

  • If your PolarProxy version is 2.3.58 or later, adding, modifying, and deleting throttling rules takes effect in real time for all connections. You can check the PolarProxy version in the console. If the minor version is outdated, you can update PolarProxy.

Throttling behavior

SQL throttling uses SQL templates and a waiting queue to limit QPS or active concurrency. An SQL statement must match a throttling rule before its QPS or concurrency is counted for that rule. When the concurrency or QPS exceeds the limit set in the rule, the database proxy places the SQL statement into a waiting queue to be retried after a delay. This ensures the concurrency or QPS on the database remains within the configured limit.

The delay time of the waiting queue is inversely proportional to the QPS or concurrency configured in the rule. The maximum number of SQL statements that can wait in the queue for a specific rule is limited by the Maximum Waiting Queue Length parameter. If this limit is exceeded, the proxy does not forward the SQL statement and returns the following error to the client:

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

Additionally, if you set Maximum Active Concurrent Statements or Maximum QPS per Connection to 0 in a throttling rule, any SQL statement that matches the rule is rejected and not forwarded. The client receives the preceding error directly. You can use this method to completely block a specific type of SQL statement.

Best practices

SELECT * FROM generate_series(1, 100000);