Configuring SQL Statement Concurrency Control

Scenarios

SQL statement concurrency control aims to keep GaussDB(for MySQL) instances running stably even if there is a sudden increase in concurrent SQL statements.

Constraints

• This function is available only to the GaussDB(for MySQL) instances that meet the following requirements:
  • 2.0.28.40 > kernel version ≥ 2.0.28.15
  • Kernel version ≥ 2.0.29.1
• Each SQL concurrency control rule can contain up to 128 keywords.
• The keywords in a rule cannot contain \t, \r, and \n, and cannot be a backslash (\) or a single null character ('').
• Spaces at the start, end of or in the middle of a keyword are ignored.
• The SQL concurrency control rule cannot end with a tilde (~).
• Keywords in a concurrency control rule are sorted in a specific order, and the system will match them from first to last. For example, if one rule contains the keyword a~and~b, the system only matches xxx a>1 and b>2.
• Each SQL concurrency control rule applies 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 a DB instance, the rules still apply to the primary node and read replicas after their roles are switched over.
• If an SQL statement matches multiple concurrency control rules, only the most recently created rule is applied.
• SQL statements that have been executed before a concurrency control rule is added are not counted.
• The total length of all rules for SELECT, UPDATE, or DELETE statements and the Concurrency value in each rule cannot exceed 1024 bytes.
• If you add too many SQL concurrency control rules for your instance, the execution of SELECT, UPDATE, or DELETE statements will slow down.
• SQL concurrency control rules are applied based on prefix match. For example, if the concurrency control rule is SELECT~COUNT~t1, SQL statements SELECT COUNT(*) FROM t1 and SELECT COUNT(*) FROM t1 LIMIT 1 will both be intercepted.
• After concurrency control is triggered, an execution error is reported on the service side, indicating that query execution was interrupted. The error code is ERROR 1317 (70100).
• This function controls how many statements can run at the same time. However, it does not limit concurrency for:
  • system catalog
  • Queries where no database data is involved, such as select sleep(xxx)
  • Account root
  • SQL statements in stored procedures, triggers, and functions

Procedure

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 > GaussDB(for MySQL).
4. On the Instances page, click the instance name.
5. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
6. Choose SQL Explorer > Concurrency Control.
7. On the displayed page, enable Concurrency Control.
   Figure 1 Enabling SQL statement concurrency control
   

8. Click Add Rule. In the displayed dialog box, specify SQL Type, Keyword, and Max. Concurrency.
   • Keyword: You can enter keywords or copy an existing SQL statement to the text box and click Generate Keyword.

     Keyword: Take select~a as an example. select and a are two keywords contained in a concurrency control 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.

   • Max. Concurrency: SQL statements that meet the specified SQL type and keyword and exceed the value of Max. Concurrency will not be executed.
   • If you select Kill existing sessions that meet this rule, the sessions that meet the rule will be killed.
   • If you select Synchronize rules to other nodes, the new rules can be synchronized to other nodes in the same instance.

9. Confirm the settings and click OK.
10. If a concurrency control rule is not required, select the rule and click Delete above the rule list. In the displayed dialog box, click OK.