Configuring SQL Throttling

Scenarios

SQL throttling allows you to create rules to control concurrent execution of SQL statements by specifying the SQL type, keywords, and maximum concurrency. The DB instance will throttle matching statements to maintain optimal performance during high concurrency. You can also view how many times SQL statements are throttled by the current rules.

You can also identify SQL statements that consume the most resources based on the execution duration or number of executions and then create throttling rules for these SQL statements.

Supported Versions

For details about how to check the kernel version, see How Can I Check the Version of a TaurusDB Instance?

Keyword Matching Rules

A throttling rule consists of a sequence of keywords separated by tildes (~). Throttling rules match keywords in plaintext order. Keywords are case-insensitive. If multiple keywords are configured, a SQL statement must contain all of the keywords in the exact configured order to trigger the throttling rule. Any content (or no content at all) is allowed between these keywords.

• Example 1: matching with intervening content or exact order

  Throttling rule format:

  key_word1~key_word2~key_word3 

  This rule is matched only when key_word1, key_word2, and key_word3 are contained in a SQL statement in that exact order.

  • The rule can be matched if there is intervening content between the keywords:

    SELECT key_word1 xxx  FROM table_name WHERE key_word2 xxx key_word3; 

  • The rule can be matched if there is no intervening content between the keywords:

    SELECT key_word1key_word2key_word3 FROM table_name; 

  • The rule cannot be matched if the keywords appear in a different order:

    SELECT key_word1 xxx key_word3 xxx key_word2 FROM table_name; 

• Example 2: plaintext matching

  Keyword matching is executed based on raw plaintext and is unaware of SQL keywords. For example, in the following throttling rule, keyword matching only checks whether SELECT and JOIN appear in sequence, regardless of whether it is INNER JOIN or LEFT JOIN.

  Throttling rule format:

  SELECT~JOIN 

  The rule can be matched if the keywords appear as follows:

  SELECT * FROM orders INNER JOIN users ON orders.user_id = users.id; 

  The rule can also be matched if the keywords appear as follows:

  SELECT * FROM orders LEFT JOIN users ON orders.user_id = users.id; 

Constraints

Adding a Throttling Rule

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region and project.
3. On the Instances page, click the target instance name.
4. In the navigation pane, choose SQL Analysis and Tuning under DBA Assistant.
5. Click the SQL Throttling tab.
6. On the displayed page, toggle on Enable SQL Throttling.
7. Click Add Rule. In the displayed dialog box, specify SQL Type, Keyword, and Max. Concurrent Requests.
   Figure 1 Adding a SQL throttling rule
   

   Table 2 Parameter description

   Parameter	Description
   SQL Type	There are four options: SELECT, UPDATE, DELETE, and INSERT.
   Keyword	A maximum of 128 keywords (case-insensitive) are supported. You can specify keywords in either of the following ways: Manually: Take select~a as an example. select and a are two keywords contained in a SQL throttling rule. The keywords are separated by a tilde (~). In this example, the rule restricts the execution of only the SQL statements containing keywords select and a. Using SQL statements: You can enter a SQL statement and then click Generate Keyword. The generated keywords are for reference only. Exercise caution when using them. SQL statements match the keywords from first to last. For example, if one rule contains the keywords a~and~b, the statement a>1 and b>2 can match the keywords but b>2 and a>1 cannot. Empty characters before and after each keyword will be ignored, for example, spaces, '\n', '\r', and '\t'.
   Max. Concurrent Requests	If the number of concurrent SQL statements matching the keyword exceeds this limit, the SQL statements will not be executed. The value ranges from 0 to 1000000000.
   Kill existing sessions that match this rule	If this option is selected, all sessions generated by users subject to this SQL throttling rule will be killed.
   Synchronize rules to other nodes	If this option is selected, new throttling rules will be synchronized to other nodes of a given instance.
   Setting the validity period for a throttling rule	The validity period of a rule is the specific timeframe during which the rule is in effect. Permanent: The new SQL throttling rule takes effect permanently. Limited: You need to specify the expiration time for the SQL throttling rule. The validity period starts when the request to add the rule is submitted. The expiration time may be delayed for a maximum of 1 minute. The expiration time can be a maximum of two weeks later than the current time. You can click Historical SQL Throttling Rules on the right to view the expired SQL throttling rules.

8. Confirm the settings and click OK.
9. In the displayed dialog box, click OK.

Recommended Rule

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region and project.
3. On the Instances page, click the target instance name.
4. In the navigation pane, choose SQL Analysis and Tuning under DBA Assistant.
5. Click the SQL Throttling tab and click Recommended Rule.
6. In the displayed dialog box, set the query criteria. You can filter top SQL statements by Avg. Duration or Executions.

   

7. Click OK.
8. To throttle a high-load SQL statement, click SQL Throttling in the Operation column. In the displayed dialog box, add a SQL throttling rule. For more information, see Adding a Throttling Rule.

Deleting a Throttling Rule

1. Log in to the TaurusDB console.
2. Click in the upper left corner and select a region and project.
3. On the Instances page, click the target instance name.
4. In the navigation pane, choose SQL Analysis and Tuning under DBA Assistant.
5. Click the SQL Throttling tab.
6. Locate a SQL throttling rule and click Delete in the Operation column.

   

   Alternatively, select one or more throttling rules and click Delete above the list.

   

7. In the displayed dialog box, confirm the throttling rules to be deleted. If you select Synchronize rules to other nodes, the throttling rules of other nodes of a given instance will also be deleted. Click OK.
   Figure 2 Deleting a throttling rule
   

8. (Optional) Click Historical SQL Throttling Rules to view the deleted and expired throttling rules.