ApsaraDB RDS:Use the rds_ccl extension to perform SQL throttling

Scenarios

The function in the following syntax can be called only on the primary RDS instance to create an SQL throttling rule.

Syntax

Syntax 1: Perform throttling on the 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 the query_id parameter.

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
);

Parameters

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

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

Usage notes

• In Syntax 1, you can set comment and search_path to a pair of quotation marks (''). If you set the parameters to NULL, the function does not perform any operations and directly returns a value. A pair of quotation marks indicate an empty string.

• In Syntax 2, take note of the following points:

  • You can set query_string and comment to a pair of quotation marks (''). If you set the parameters to NULL, the function does not perform any operations and directly returns a value. A pair of quotation marks indicate an empty string.

  • If you configure other values for query_id and query_string, AliPG performs SQL throttling based on query_id that you specified.

• This function returns ccl_id. ccl_id is the unique identifier of an SQL throttling rule in the current database.

Examples

The ccl_tbl table is created in the ccl_test database on the primary RDS instance. You can create an SQL throttling rule that meets the following requirements for the RDS instance:

1. All SELECT * FROM ccl_tbl statements are throttled. In this example, query_id is set to 1.

2. Up to three SQL statements of this type can be concurrently executed.

3. Up to two SQL statements of this type can stay in the waiting state.

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

• Syntax 1

  SELECT rds_add_ccl_rule(
    $$SELECT * FROM ccl_tbl;$$, -- The SQL statement that needs to be throttled and is wrapped by using $$.
    1,					-- SQL throttling is performed only on the primary RDS instance.
    3,					-- Up to three SQL statements of this type can be concurrently executed.
    2,					-- Up to two SQL statements of this type can stay in the waiting state.
    true,					-- The rule immediately takes effect after it is created.
    'limit constant select',		-- The description of the rule.
    ''					-- The default value of search_path is used.
  );

• Syntax 2

  SELECT rds_add_ccl_rule_with_query_id(
    1,					-- query_id of the SQL statement that you want to throttle.
    1,					-- SQL throttling is performed only on the primary RDS instance.
    3,					-- Up to three SQL statements of this type can be concurrently executed.
    2,					-- Up to two SQL statements of this type can stay in the waiting state.
    true,					-- The rule immediately takes effect after it is created.
    '' -- The text of the SQL statement. In this example, this parameter is not specified.	
    'limit constant select',		-- The description of the rule.
  );

Scenarios

The statement in the following syntax can be executed on the primary RDS instance or read-only RDS instances to query the SQL throttling rules that are created.

Syntax

• Query the SQL throttling rules that are created for all databases on the RDS instance.

  SELECT * FROM rds_enabled_ccl_rule;

  Note

  In the query result, the values of query_string and comment are truncated due to display limits, and only the first 200 characters of each value are displayed. To view the complete values, execute the SELECT * FROM rds_show_current_db_ccl_rule(); statement.

• Query the SQL throttling rules that are created for the current database on the RDS instance.

  SELECT * FROM rds_show_current_db_ccl_rule();

Scenarios

The function in the following syntax can be called only on the primary RDS instance and can be used in the following operations:

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

• You can call this function to enable a disabled SQL throttling rule.

Syntax

SELECT rds_enable_ccl_rule(ccl_id int);

Parameters

Usage notes

None

Examples

SELECT rds_enable_ccl_rule(1);

Scenarios

The function in the following syntax can be called on the primary RDS instance or read-only RDS instances to load an SQL throttling rule. Only the loaded rule can be used to perform SQL throttling.

This function can be used in the following scenarios:

• Primary RDS instance:

  • If is_enabled is set to true when you create an SQL throttling rule, the rule is automatically loaded to the primary RDS instance. No human intervention is required.

  • If is_enabled is set to false when you create an SQL throttling rule and the rds_enable_ccl_rule function is called to enable the rule, the rule is automatically loaded to the primary RDS instance. No human intervention is required.

  • If the primary RDS instance is restarted, you must call this function to manually load an SQL throttling rule.

• Read-only RDS instance:

  If you want to enable an SQL throttling rule on a read-only RDS instance, you must set node_tag to 2 or 3 when you create an SQL throttling rule and then call the function on the read-only RDS instance to load the rule.

Syntax

SELECT rds_load_ccl_rule(ccl_id int);

Parameters

Usage notes

• You can load an SQL throttling rule to a read-only RDS instance only after you call the rds_add_ccl_rule function on the primary RDS instance and set node_tag to 2 or 3 to create the rule.

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

Examples

SELECT rds_enable_ccl_rule(1);

Scenarios

The function in the following syntax can be called only on the primary RDS instance to change the values of max_concurrency and max_waiting 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 modify an SQL throttling rule, the modification immediately takes effect.

• You can change the maximum number of concurrent SQL statements and the maximum number of waiting SQL statements in an SQL throttling rule.

Examples

SELECT rds_update_ccl_rule(
 2,     -- ccl_id
 4,     -- The new maximum number of concurrent SQL statements is 4.
 5      -- The new maximum number of waiting SQL statements is 5.
);

Scenarios

The function in the following syntax can be called only on the primary RDS instance to disable an SQL throttling rule. A disabled rule can no longer be used for SQL throttling.

Syntax

• Disable an SQL throttling rule.

  SELECT rds_disable_ccl_rule(ccl_id int);

• Disable all SQL throttling rules for a database.

  SELECT rds_disable_all();

Parameters

Usage notes

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

• When you disable an SQL throttling rule, the rule is automatically unloaded from the primary RDS instance, and the rule can no longer be used for SQL throttling.

Examples

SELECT rds_disable_ccl_rule(1);

Scenarios

The function in the following syntax can be called on the primary RDS instance or read-only RDS instances to unload an SQL throttling rule. An unloaded rule can no longer be used to perform SQL throttling.

This function can be used in the following scenarios:

• Primary RDS instance:

  • If the rds_disable_ccl_rule or rds_disable_all function is called on the primary RDS instance to disable an SQL throttling rule, the rule is automatically unloaded from the primary RDS instance. No human intervention is required.

  • If the rds_del_ccl_rule function is called on the primary RDS instance to delete an SQL throttling rule, the rule is automatically unloaded from the primary RDS instance. No human intervention is required.

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

• Read-only RDS instance:

  If you want to disable an SQL throttling rule for a read-only RDS instance, you can call this function on the read-only RDS instance.

Syntax

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

Parameters

Usage notes

After an SQL throttling rule is unloaded, the rule can no longer be used to perform SQL throttling. If you want to reuse the rule, you must reload the rule. For more information, see Load an SQL throttling rule.

Examples

SELECT rds_unload_ccl_rule(1,'');

Scenarios

The function in the following syntax can be called only on the primary RDS instance to delete an SQL throttling rule. After the rule is deleted, it is automatically unloaded from the primary RDS instance.

Syntax

SELECT rds_del_ccl_rule(ccl_id int);

Parameters

Usage notes

None

Examples

SELECT rds_del_ccl_rule(1);

The following results can be returned:

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

• query_id is a special identifier for SQL statements in PostgreSQL. The same type of SQL statement has the same query_id value.

  Examples:

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

• query_id contains the oid information about the object in an SQL statement. Tables whose names are the same in different databases are not the same object. Tables whose names are the same in different schemas are not the same object. If an SQL statement is used to query different objects in different databases, the SQL statement has different query_id values in the databases.

• If an SQL statement is used to query a global table or global function, the SQL statement has the same query_id value in different databases.

  • Example 1: pg_database is a global table. If you execute the same statement in different databases, the statement uses the same query_id value in different databases.

    • Execute the following statement in the ccl_test database:

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

      The following results can be returned:

         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;$$);

      The following results can be returned:

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

  • Example 2: pg_sleep is a global function. If you execute the same statement in different databases, the statement uses the same query_id value in different databases.

    • Execute the following statement in the ccl_test database:

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

      The following results can be returned:

        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);$$);

      The following results can be returned:

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

• In function call scenarios, if the data type of a parameter is changed or the existence of the FROM clause is changed, query_id is changed.

  For example, if you call the pg_sleep function, the value of query_id varies in different situations.

  • Without the FROM clause

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

    The following results can be returned:

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

  • With the FROM clause

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

    The following results can be returned:

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

  • Change of the data type of a parameter

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

    The following results can be returned:

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