Configuring SQL Throttling

Scenarios

SQL throttling keeps TaurusDB instances stable regardless of how many SQL statements are concurrently submitted. You can check how many times SQL statements are intercepted by throttling rules. If you add or delete a rule, the number of interceptions for corresponding statements will be cleared. If you reboot a node or enable a feature, the number of interceptions for all SELECT, UPDATE, DELETE, and INSERT statements will be cleared.

Supported Versions

Constraints

• A single throttling rule can contain a maximum of 128 keywords. Backslashes (\) or null characters (' ') cannot be used as keywords. Spaces at the beginning and end of a keyword and special null characters (such as \'t', \'r', and \'n') will be ignored.
• A throttling rule cannot end with a tilde (~).
• Keywords in a throttling rule will be matched in sequence. For example, if a rule contains a~and~b, only xxx a>1 and b>2 will be matched. xxx b>2 and a>1 will not be matched.
• Each SQL throttling rule is applied to only the SQL statements that your database received after the rule is created.
• If different rules are created for the primary node and read replicas of an instance, the rules still apply to the primary node and read replicas after their roles are switched over.
• If a SQL statement matches multiple throttling rules, only the most recently added rule is applied.
• SQL statements that have been executed before a SQL throttling rule is added are not counted.
• If the kernel version of your TaurusDB instance is 2.0.54.240600 or later, the total length of all rules and concurrent requests of SELECT, UPDATE, DELETE, or INSERT statements cannot exceed 4,000 bytes. The length of a single rule cannot exceed 1,000 bytes.
• If the kernel version of your TaurusDB instance is earlier than 2.0.54.240600, the total length of all rules and concurrent requests of SELECT, UPDATE, or DELETE statements cannot exceed 1,024 bytes.
• If there are too many throttling rules, performance of SELECT, UPDATE, DELETE, and INSERT statements will deteriorate.
• Throttling rules are applied based on SQL statement prefixes. For example, if a throttling rule is SELECT~COUNT~t1, SELECT COUNT(*) FROM t1 and SELECT COUNT(*) FROM t1 LIMIT 1 will both be intercepted.
• If SQL throttling is triggered, error "ERROR 1317 (70100): Query execution was interrupted" is reported.
• If you add or delete a throttling rule for a SQL type, all rules of this type will be regenerated. If you reboot a node or enable SQL throttling, throttling rules of the SELECT, UPDATE, DELETE, and INSERT statements will be regenerated. The number of concurrent and intercepted requests of the regenerated throttling rules will be cleared and recalculated.
• This function controls how many statements can run at the same time. However, it does not limit concurrency for:
  • System tables
  • SQL statements not used to query data, such as SELECT SLEEP(xxx);
  • Account root
  • SQL statements in stored procedures, triggers, and functions

Adding a Throttling Rule

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

   Table 1 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 keyword a~and~b, the statement *** a>1 and b>2 can match the keyword, 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.

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

Deleting a Throttling Rule

1. Log in to the management console.
2. Click in the upper left corner and select a region and project.
3. Click in the upper left corner of the page and choose Databases > TaurusDB.
4. On the Instances page, click the instance name.
5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
6. Click SQL Explorer and then SQL Throttling.
7. 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.

   

8. 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