Configuring SQL Throttling Rules

Prerequisites

You have logged in using an account assigned the following actions: gaussdb:instance:listFlowlimit, gaussdb:instance:flowlimitAddOrUpdate, gaussdb:instance:createFlowLimit, gaussdb:instance:updateFlowLimit, gaussdb:instance:deleteFlowLimit, gaussdb:instance:getFlowLimitInfo, gaussdb:instance:getFlowLimitTemplate, gaussdb:instance:listFlowLimitInfo, gaussdb:instance:syncFlowLimit, and gaussdb:instance:flowlimitDelete

Constraints

• The DB engine version of GaussDB instances must be V2.0-8.0.1 or later, and the kernel version must be 505.0.0 or later.
• Auto throttling is unavailable to distributed instances.
• SQL ID-based throttling is unavailable to distributed instances.
• SQL throttling cannot be performed for system-level users (such as root).
• Currently, SQL throttling tasks cannot be deleted in batches.
• Each instance supports a maximum of 1,000 throttling tasks.
• For a keyword-based throttling task, keywords are separated by commas (,), and the sequence of keywords is not distinguished.
• If you used CLI commands to add, delete, or modify a SQL throttling task as a system user (for example, user root), the actual throttling task status will be different from that displayed on the web UI.
• When a throttling rule is being applied, CPU usage increases. To ensure system stability, do not create too many rules for a given instance.
• SQL statements that have been executed before a throttling rule is added are not counted.
• In DR scenarios, the throttling rules applied to the primary instance are automatically synchronized to the DR instance. After the DR instance is promoted to primary, the throttling rules cannot be added, deleted, modified, or queried. Throttling tasks can be managed again only after the original primary instance becomes primary.
• If data is lost when you restore a backup to a new DB instance or switch the original primary instance to primary, handle the data loss issue by following the instructions in Troubleshooting.
• You can still create, modify, and query a throttling rule if the instance status is abnormal or when instance specifications are being changed, but the operation may fail. If a failure or exception occurs, you can delete the throttling rule. Before deleting a throttling rule, ensure that the database is running properly.
• Throttling is not allowed for SQL statements involved in setting up database connections, for example, select setting from pg_settings where name=\'delimiter_name\', because new connections cannot be set up if such statements are blocked.
• When an instance node is being created or rebooted (for example, for adding replicas or shards), the node may fail to be connected. As a result, the SQL throttling task details cannot be queried.
• During a gray upgrade or when there are two primary instances after the original DR instance is promoted to primary, an error message, indicating another operation is in progress, will be displayed if you attempt to create, modify, or delete the throttling rule applied to the original DR instance, and the throttling task sticks in the creating, modifying, or deleting state. In this case, click Data Synchronization to rectify the fault.
• You may receive a kernel error message like ERROR: The workload rule takes effect and this request will be cancelled. rule_id: x, rule_name: "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx". The rule name in such a message indicates the throttling task ID on this page and the task ID in audit logs.
• If a SQL statement matches multiple throttling task rules at the same time, the rules are applied based on the following priorities:
  1. The smaller value of Max. Concurrency or Max. Active Sessions, the higher the priority.
  2. If the values of Max. Concurrency or Max. Active Sessions in different tasks are the same, the priority is as follows: auto throttling > SQL ID-based throttling > keyword-based throttling
  3. If the values of Max. Concurrency or Max. Active Sessions and the throttling type are the same in different tasks, the task created later is preferentially used to block requests.
• Do not create tables with the same name. If a table with the same name exists in different databases or schemas, the throttling rule may not take effect when you create a SQL ID-based throttling task related to the table.

Creating a Throttling Task

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 Database > GaussDB.
4. On the Instances page, click the name of the target instance to go to the Basic Information page.
5. In the navigation pane, choose Diagnostics and Optimization > SQL Diagnosis. Then, click the SQL Throttling tab.
6. On the SQL Throttling Tasks or Auto Throttling Tasks tab, click Create Task. In the right pane, set the throttling parameters described in Table 1.
   • Creating a SQL throttling task
     Figure 1 Creating a SQL throttling task
     

     Table 1 Parameters for creating a SQL throttling task

     Throttling Type	Parameter	Description
     SQL ID	Task Name	Name of the SQL throttling task.
     	SQL Template	The system records the SQL statement execution records of each node and converts the records into a template. You can select the template for performing SQL throttling on each node. NOTE: When an instance is rebooted, the SQL template of the instance node will be cleared.
     	Max. Concurrency	Maximum number of active connections for executing the throttled SQL statement on the specified node at the same time.
     	Time Range	Time period when the throttling takes effect.
     Keyword NOTE: 2 to 100 keywords can be specified for a single task. Each keyword can contain 2 to 64 characters. Keywords are case-insensitive. The fewer the keywords, the larger the range of SQL statements to be throttled.	Task Name	Name of the SQL throttling task.
     	SQL Statement Type	SQL statement type, which can be SELECT, INSERT, UPDATE, DELETE, or MERGE.
     	Keyword	Keywords used for throttling. The throttling task throttles the statements that match the specified keywords and SQL statement type.
     	Target Databases	Databases where the throttling takes effect.
     	Max. Concurrency	Maximum number of active connections for executing the throttled SQL statement on the specified node at the same time.
     	Time Range	Time period when the throttling takes effect.

   • Creating an auto throttling task
     Figure 2 Create Auto Throttling Task
     

     Table 2 Parameters for creating an auto throttling task

     Parameter	Description
     Scenario	Condition for triggering throttling. Currently, only Triggered by thresholds is available.
     Task Name	Name of the throttling task.
     CPU Usage	CPU usage threshold for triggering throttling on the specified node. Throttling takes effect if both the CPU and memory usage threshold conditions are met. The CPU usage and memory usage thresholds cannot both be set to 0. If you only need one of them for throttling, set the other threshold to 0.
     Memory Usage	Memory usage threshold for triggering throttling on the specified node. Throttling takes effect if both the CPU and memory usage threshold conditions are met. The CPU usage and memory usage thresholds cannot both be set to 0. If you only need one of them for throttling, set the other threshold to 0.
     Max. Active Sessions	Maximum number of active connections for executing SQL statements on the specified node at the same time.

7. Click OK.
8. After a throttling task is created, view throttling details or modify the task.
   • To view task details, click Details in the Operation column of the task.
     Figure 3 Viewing SQL throttling task details
     

   • To modify a throttling task, click Modify in the Operation column of the task.
     Figure 4 Modifying a throttling task
     

   • To delete a throttling task, click Delete in the Operation column of the task.
   

   After the throttling rule of a task is modified, the instance reboots, or a primary/standby switchover is performed, the number of blocked connections of the task will be cleared.

Synchronizing Data

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.
4. On the Instances page, click the name of the target instance to go to the Basic Information page.
5. In the navigation pane, choose Diagnostics and Optimization > SQL Diagnosis. Then, click the SQL Throttling tab.
6. Click Data Synchronization.
7. In the displayed dialog box, click OK to synchronize data after the original DB instance is backed up.
   Figure 5 Data synchronization