rds_pg_sql_ccl

Introduction

To prevent highly concurrent SQL statements that consume too many resources from causing instance instability, RDS for PostgreSQL provides rds_pg_sql_ccl, a Huawei-developed extension. ccl is short for concurrent control. SQL statement concurrency control can ensure instance stability, optimize performance, and protect resources in the following scenarios:

• Sharp increase of workloads: The instance stability is ensured by limiting the execution of a certain type of SQL statements.
• Core task assurance: The success of core tasks is ensured by limiting the execution of other SQL statements and reducing resource consumption.

This extension provides two concurrency control methods:

• Method 1: Limit the number of SQL statements that can be executed at the same time. This number is specified by the rds_pg_sql_ccl.max_concurrent_sql parameter. The default value is -1, indicating that the number is not limited.
• Method 2: Limit the number of a certain type of SQL statements (with the same query ID) that can be executed at the same time. This number is specified by concurrency control rules. For details about concurrency control rules, see below.

Supported Versions

This extension is available to the latest minor versions of RDS for PostgreSQL 11.20, 12.15, 13.11, 14.8, 15.4, 16.2, and above. You can run the following SQL statement to check whether your DB instance supports this extension:

SELECT * FROM pg_available_extension_versions WHERE name = 'rds_pg_sql_ccl'; 

If this extension is not supported, upgrade the minor version of your DB instance or upgrade the major version using dump and restore.

To see more extensions supported by RDS for PostgreSQL, go to Supported Extensions.

Notes

SQL statement concurrency control rules must be configured based on workloads and resource usage.

Creating Rules

1. In any given database, each rule must have a unique query ID, but rules in different databases can have the same query ID.
2. A rule does not take effect immediately after being created. You need to call the enable_ccl_rule function to make it applied.
3. The get_query_id function cannot obtain the query IDs of SQL statements using bind variables, and the add_ccl_rule_by_query function cannot limit the execution of such SQL statements.
4. You can obtain the query ID of a SQL statement with bind variables using the pg_stat_statements extension. Then, you can use add_ccl_rule_by_queryid to create rules. For details, see "Concurrency Control for SQL Statements with Bind Variables".

How Rules Take Effect

1. Method 1 limits the number of SQL statements that can be concurrently executed. Rules creating using this method take effect preferentially. On the basis of method 1, you can use method 2 to further limit concurrent execution of a specific type of SQL statements.
2. After an instance reboot, none of the rules are applied.
3. Read replicas synchronize the rules of the primary instance and call the enable_ccl_rule function to make the rules apply.
4. A rule is only applied to SQL statements executed after the rule was enabled.

Concurrency Control for SQL Statements Without Bind Variables

Prerequisites:

1. Install the kernel extension rds_pg_sql_ccl on the console or by running SQL statements.

   SELECT control_extension ('create', 'rds_pg_sql_ccl');

2. Set kernel parameters.

   rds_pg_sql_ccl.enable_ccl = on

Then, perform the following operations:

1. On the Instances page, click the instance name to go to the Overview page.
2. In the navigation pane, choose DBA Assistant > Historical Diagnosis.
3. Choose SQL Explorer > Concurrency Control.
4. Toggle on the concurrency control switch.
5. Click Add Rule and configure required parameters.
   Figure 1 Adding a concurrency control rule
   
   • Locate the rule and click Enable in the Operation column to enable the rule.
     Figure 2 Enabling a rule
     
   • To disable a rule, click Disable.
     Figure 3 Disabling a rule
     
   • To delete a rule, click Delete. After a concurrency control rule that is enabled is deleted, the rule is not applied anymore.
     Figure 4 Deleting a rule
     

Concurrency Control for SQL Statements with Bind Variables

Drivers such as JDBC support prepared statements. They precompile parameterized SQL statements and execute them after parameters are entered. In the pg_stat_statements view, the statements are displayed as bind variables. For SQL statements using bind variables, the query IDs calculated by the kernel are different from those of the same statements using actual parameter values. The concurrent execution of such statements cannot be limited by adding the statements.

Such SQL statements can only be limited by executing them first and then adding concurrency control rules.

1. Execute a SQL statement with bind variables. In this way, the kernel calculates its query ID. An example of the JDBC-based prepared statement program is as follows:

   String sql = "select pg_sleep(?);";
   PreparedStatement preparedStatement = conn.prepareStatement(sql);
   preparedStatement.setInt(1, 500);
   ResultSet resultSet = preparedStatement.executeQuery();

2. Query the query ID of the SQL statement in the pg_stat_statements view.

   select queryid from pg_stat_statements where query like '%select pg_sleep%';

3. Add a concurrency control rule based on the query ID.

   select rds_pg_sql_ccl.add_ccl_rule_by_queryid($queryid);

4. Enable the rule based on the return value (rule_id) of the previous SQL statement.

   select rds_pg_sql_ccl.enable_ccl_rule($rule_id);

5. Obtain all rules applied from the get_all_enabled_rule view provided by the extension.

   select * from rds_pg_sql_ccl.get_all_enabled_rule;

Parameters

Function Interface Description

Description of some parameters:

• max_concurrency: The maximum number of SQL statements that can be concurrently executed.
• max_wait: The maximum waiting time after which new SQL statements of a specified type will fail to be executed when the maximum number of concurrent SQL statements is reached.
• new_max_concurrency: The new maximum number of concurrent SQL statements.
• new_max_wait: The new maximum waiting time.

View Interface Description