ApsaraDB RDS:SQL throttling (rds_ccl)

Scenarios

This function can be called only on the primary instance to create an SQL throttling rule.

Syntax

• Syntax 1: Perform throttling on a specified SQL statement.

  SELECT rds_add_ccl_rule(
  	query_string varchar,
  	node_tag int,
  	max_concurrency int,
  	max_waiting int,
  	is_enabled boolean,
  	comment varchar,		-- Optional
  	search_path varchar		-- Optional
  );

• Syntax 2: Perform throttling based on a specified query_id.

  SELECT rds_add_ccl_rule_with_query_id(
    query_id		bigint,
    node_tag		int,
    max_concurrency	int,
    max_waiting		int,
    is_enabled		boolean,
    query_string		varchar,	-- Optional
    comment		varchar,	-- Optional
    search_path           varchar		-- Optional (Supported in version 1.2 and later)
  );

Parameters

-- Use $$ to wrap the SQL statement.
$$SELECT * FROM my_table;$$

-- If the SQL statement already contains $$, use $ccl$ to wrap the SQL statement.
$ccl$SELECT * FROM my_table WHERE my_column = $$hello$$;$ccl$

Usage notes

• In Syntax 1, if you do not configure the comment and search_path parameters, you can set them to an empty string (''). If you set them to NULL, the function performs no operation and returns immediately.

• In Syntax 2:

  • If you do not configure the query_string and comment parameters, you can set them to an empty string (''). If you set them to NULL, the function performs no operation and returns immediately.

  • If you configure both query_id and query_string, the AliPG kernel performs SQL throttling based on query_id.

• This function returns a ccl_id, which is the unique identifier for the throttling rule in the current database.

SQL examples

In the ccl_test database of the primary instance, a data table named ccl_tbl exists. Create an SQL throttling rule that meets the following requirements:

1. Throttle all SELECT * FROM ccl_tbl statements. Assume their query_id=1.

2. Allow a maximum of three such SQL statements to execute concurrently.

3. Allow a maximum of two such SQL statements to wait.

4. The rule takes effect immediately after it is created.

• Syntax 1:

  SELECT rds_add_ccl_rule(
    $$SELECT * FROM ccl_tbl;$$,		-- The SQL statement to be throttled, wrapped in $$.
    1,					-- Throttles the primary instance only.
    3,					-- Allows a maximum of 3 such SQL statements to execute concurrently.
    2,					-- Allows a maximum of 2 such SQL statements to wait.
    true,					-- The rule takes effect immediately.
    'limit constant select',		-- The rule description.
    ''					-- Uses the default search_path.
  );

• Syntax 2:

  SELECT rds_add_ccl_rule_with_query_id(
    1,					-- The query_id of the SQL statement to be throttled.
    1,					-- Throttles the primary instance only.
    3,					-- Allows a maximum of 3 such SQL statements to execute concurrently.
    2,					-- Allows a maximum of 2 such SQL statements to wait.
    true,					-- The rule takes effect immediately.
    '',					-- The text of the SQL statement. This is not specified in this example.
    'limit constant select',		-- The rule description.
    'public'                              -- The search_path.
  );

Scenarios

You can use this command on a primary instance or a read-only instance to query created SQL throttling rules.

Syntax

• View all SQL throttling rules in all databases of the current instance:

  SELECT * FROM rds_enabled_ccl_rule;

  Note

  • In the query result of this command, the information for query_string and comment is truncated to the first 200 characters because of display limitations. To view the complete information, you can run the SELECT * FROM rds_show_current_db_ccl_rule(); command.

  • rds_ccl version 1.2 and later supports returning search_path.

• Query all SQL throttling rules in the current database:

  SELECT * FROM rds_show_current_db_ccl_rule();

Scenarios

This function can be called only on the primary instance and is applicable to the following scenarios:

• If the is_enabled parameter is set to false when you create an SQL throttling rule, you can use this function to enable the rule.

• Enable a rule that was previously disabled.

Syntax

SELECT rds_enable_ccl_rule(ccl_id int);

Parameters

Usage notes

None.

SQL example

SELECT rds_enable_ccl_rule(1);

Scenarios

You can call this function on a primary instance or a read-only instance to load an SQL throttling rule. Only loaded rules can perform SQL throttling.

This function is applicable to the following scenarios:

• Primary instance:

  • When you create an SQL throttling rule, if is_enabled is set to true, the rule is automatically loaded on the primary instance. You do not need to call this function.

  • When you create an SQL throttling rule, if is_enabled is set to false, the rule is automatically loaded on the primary instance when you call the rds_enable_ccl_rule function to enable it. You do not need to call this function.

  • If the primary instance restarts, you must manually call this function to load the rule.

• Read-only instance:

  If a rule needs to take effect on a read-only instance, you must create the rule on the primary instance with the node_tag parameter set to 2 or 3. Then, you must call this function on the read-only instance to manually load the rule.

Syntax

SELECT rds_load_ccl_rule(ccl_id int);

Parameters

Usage notes

• You can load a rule on a read-only instance only after you create the SQL throttling rule on the primary instance by calling the rds_add_ccl_rule function with the node_tag parameter set to 2 or 3.

• Disabled rules cannot be loaded. If a read-only instance needs to load a disabled rule, you must first enable the rule on the primary instance. For more information, see Enable an SQL throttling rule.

SQL example

SELECT rds_load_ccl_rule(1);

Scenarios

You can call this function only on the primary instance to change the maximum concurrency and maximum waiting count in an SQL throttling rule.

Syntax

SELECT rds_update_ccl_rule(
	ccl_id int,
	new_max_concurrency int,
	new_max_waiting int
);

Parameters

Usage notes

• After you update an SQL throttling rule, the rule takes effect immediately.

• You can change only the maximum concurrency and maximum waiting count in an SQL throttling rule.

SQL example

SELECT rds_update_ccl_rule(
 2,     -- ccl_id
 4,     -- The new maximum concurrency is 4.
 5      -- The new maximum waiting count is 5.
);

Scenarios

You can call this function only on the primary instance to disable an SQL throttling rule. The rule no longer performs SQL throttling.

Syntax

• Disable a specific SQL throttling rule:

  SELECT rds_disable_ccl_rule(ccl_id int);

• Disable all SQL throttling rules in the current database:

  SELECT rds_disable_all();

Parameters

Usage notes

• Disabled rules cannot be loaded. If a read-only instance needs to load a disabled rule, you must first enable the rule on the primary instance. For more information, see Enable an SQL throttling rule.

• When you disable an SQL throttling rule, the rule is automatically unloaded from the primary instance and no longer performs SQL throttling.

SQL example

SELECT rds_disable_ccl_rule(1);

Scenarios

You can call this function on a primary instance or a read-only instance to unload an SQL throttling rule. An unloaded rule no longer performs SQL throttling.

This function is applicable to the following scenarios:

• Primary instance:

  • If you call the rds_disable_ccl_rule or rds_disable_all function on the primary instance to disable a rule, the rule is automatically unloaded from the primary instance. You do not need to call this function.

  • If you call the rds_del_ccl_rule function on the primary instance to delete a rule, the rule is automatically unloaded from the primary instance. You do not need to call this function.

  • You can also call this function to manually unload the rule from the primary instance.

• Read-only instance:

  If you do not want a rule to perform throttling on a read-only instance, you can call this function to manually unload it on the read-only instance.

Syntax

SELECT rds_unload_ccl_rule(ccl_id int, db_name varchar default '');

Parameters

Usage notes

After a rule is unloaded, it no longer performs SQL throttling. To use it again, you must reload it. For more information, see Load an SQL throttling rule.

SQL example

SELECT rds_unload_ccl_rule(1,'');

Scenarios

You can call this function only on the primary instance to delete an SQL throttling rule. When a rule is deleted, it is automatically unloaded from the primary instance.

Syntax

SELECT rds_del_ccl_rule(ccl_id int);

Parameters

Usage notes

None.

SQL example

SELECT rds_del_ccl_rule(1);

Sample result:

   rds_del_ccl_rule   
----------------------
 -7851264404688445170
(1 row)

• A query_id is a special identifier for SQL statements in PostgreSQL. Similar SQL statements have the same query_id.

  Example:

  -- The following two SQL statements have the same query_id.
  SELECT * FROM tbl WHERE a = 1;
  SELECT * FROM tbl WHERE a = 2;

• A query_id contains information about the objects in the SQL statement, which is the oid information. Tables with the same name in different databases are not the same object. Tables with the same name in different schemas are also not the same object. Therefore, identical SQL statements have different query_id values if they contain different query objects.

• If the object accessed in an SQL statement is a global table or a global function, the query_id is the same regardless of the database.

  • Example 1: pg_database is a global table. The query_id is the same when this table is queried in different databases.

    • Execute the following statement in the ccl_test database:

      SELECT rds_get_query_id($$SELECT * FROM pg_database;$$);

      Sample result:

         rds_get_query_id   
      ----------------------
       -8733244708994363681
      (1 row)

    • Execute the following statement in the ccl_test2 database:

      SELECT rds_get_query_id($$SELECT * FROM pg_database;$$);

      Sample result:

         rds_get_query_id   
      ----------------------
       -8733244708994363681
      (1 row)

  • Example 2: pg_sleep is a global function. The query_id is the same when this function is called in different databases.

    • Execute the following statement in the ccl_test database:

      SELECT rds_get_query_id($$SELECT pg_sleep(1);$$);

      Sample result:

        rds_get_query_id  
      --------------------
       440101247839410938
      (1 row)

    • Execute the following statement in the ccl_test2 database:

      SELECT rds_get_query_id($$SELECT pg_sleep(1);$$);

      Sample result:

        rds_get_query_id  
      --------------------
       440101247839410938
      (1 row)

• When you call a function, the query_id changes if the parameter type changes or if a FROM clause is added or removed.

  For example, when you call the pg_sleep function, the returned query_id differs depending on the situation.

  • Without a FROM clause:

    SELECT rds_get_query_id($$SELECT pg_sleep(1);$$);

    Sample result:

      rds_get_query_id  
    --------------------
     440101247839410938
    (1 row)

  • With a FROM clause:

    SELECT rds_get_query_id($$SELECT * FROM pg_sleep(1);$$);

    Sample result:

       rds_get_query_id   
    ----------------------
     -3404018605099167039
    (1 row)

  • Parameter type change:

    select rds_get_query_id($$SELECT * FROM pg_sleep(1.0);$$);

    Sample result:

      rds_get_query_id   
    ---------------------
     3073869725037049158
    (1 row)