tencent cloud

PrevNext

Feedback

search by keyword

Recent Pages

Documentation
TencentDB for PostgreSQL
    Documentation
    Download PDF

    SQL Traffic Throttling

    Last updated: 2024-10-28 17:41:54
    Download PDF

      Use Limits

      Note:
      After a restart, HA, or major version upgrade, the rules are no longer in memory and will become invalid. You need to reload them.
      Directly configuring the SQL throttling rules of the Extended Query Protocol is not supported. For more information about the Extended Query Protocol, refer to the official documentation.
      The SQL throttling feature currently supports only TencentDB for PostgreSQL instances with kernel versions v14.11_r1.23, v15.6_r1.12, v16.2_r1.6, and above. If necessary, upgrade the minor version or upgrade the major version.
      Throttling rules take effect only when they are loaded into memory. When new rules are added, they are persisted to the tencentdb_sql_throttling.persistent_rules_table by default. You can reload the rules into memory after a restart or HA.

      Creating a Plugin

      Note:
      If you need to use the SQL throttling feature, submit a ticket to contact us for adding the shared_preload_libraries parameter. Modifying the shared_preload_libraries parameter will restart the instance, so make sure that your services have a reconnection mechanism.
      Check that tencentdb_sql_throttling has been added to the shared_preload_libraries parameter:
      postgres=> show shared_preload_libraries;
                                                                    shared_preload_libraries                 
                                                    
      -------------------------------------------------------------------------------------------------------
      ----------------------------------------------
       pg_stat_statements,pg_stat_log,wal2json,decoderbufs,decoder_raw,pg_hint_plan,rds_server_handler,tencen
      tdb_pwdcheck,pgaudit,tencentdb_sql_throttling
      (1 row)
      Create the tencentdb_sql_throttling plugin and verify its creation:
      postgres=> create extension tencentdb_sql_throttling;
CREATE EXTENSION
postgres=> select * from pg_extension where extname='tencentdb_sql_throttling';
        oid  |         extname          | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition 
      -------+--------------------------+----------+--------------+----------------+------------+-----------+--------------
       16415 | tencentdb_sql_throttling |    16385 |         2200 | t              | 1.0        |           | 
      (1 row)

      Adding New Throttling Rules

      TencentDB for PostgreSQL supports adding new throttling rules via the query_id and SQL. The query_id is a 64-bit integer obtained by hashing the normalized query statement in the kernel; it serves as a unique identifier for queries. For more details on the query_id, refer to the official documentation.

      Adding New Throttling Rules via query_id

      If you need to obtain the query_id of an SQL statement, you can get it by querying pg_stat_statements or pg_stat_activity. In this document, it is obtained by querying pg_stat_activity.
      postgres=> select datname,usename,client_addr,state,query_id,query  from pg_stat_activity where usename='dbadmin' and client_addr='172.16.76.48' limit 10;
 datname  | usename |  client_addr  | state  |      query_id      |             query              
----------+---------+---------------+--------+--------------------+--------------------------------
 postgres | dbadmin | 172.16.76.48 | active | 497939862935121343 | SELECT id FROM t WHERE id = 1
 postgres | dbadmin | 172.16.76.48 | active | 497939862935121343 | SELECT id FROM t WHERE id = 53
 postgres | dbadmin | 172.16.76.48 | active | 497939862935121343 | SELECT id FROM t WHERE id = 54
 postgres | dbadmin | 172.16.76.48 | active | 497939862935121343 | SELECT id FROM t WHERE id = 4
 postgres | dbadmin | 172.16.76.48 | active | 497939862935121343 | SELECT id FROM t WHERE id = 52
 postgres | dbadmin | 172.16.76.48 | active | 497939862935121343 | SELECT id FROM t WHERE id = 51
 postgres | dbadmin | 172.16.76.48 | active | 497939862935121343 | SELECT id FROM t WHERE id = 7
 postgres | dbadmin | 172.16.76.48 | active | 497939862935121343 | SELECT id FROM t WHERE id = 10
 postgres | dbadmin | 172.16.76.48 | active | 497939862935121343 | SELECT id FROM t WHERE id = 11
 postgres | dbadmin | 172.16.76.48 | active | 497939862935121343 | SELECT id FROM t WHERE id = 8
(10 rows)
      After obtaining the query_id, we can add new throttling rules based on the query_id. TencentDB for PostgreSQL provides a function for you. The function definition is as follows:
      tencentdb_sql_throttling.add_rule_with_queryid(
          queryid bigint,
          work_node int,
          max_concurrency int,
          is_enabled boolean,
          memory_only boolean,  
          query_string text 
      );
      The parameters of the add_rule_with_queryid function are explained below:
      Note:
      queryid: A 64-bit integer obtained by hashing the normalized query statement, which can be obtained by querying pg_stat_statements or pg_stat_activity;
      work_node: The node where the throttling rule takes effect. 0 indicates it takes effect on both the master instance and read-only instances; 1 indicates it takes effect only on the master instance; 2 indicates it takes effect only on read-only instances;
      max_concurrency: The maximum concurrency limit allowed by the throttling rule;
      is_enabled: Whether the current rule is effective, with values of true and false;
      memory_only: Optional. Whether the rule needs to be persisted to the table. After a restart or HA, the rule can be reloaded into memory using a command to take effect;
      query_string: Optional. The query statement can be added to the rule. In this case, the queryid of the query_string will not be calculated for validation, and the throttling rule will be based on the queryid. If this parameter is not provided, the kernel will automatically search for the SQL statement corresponding to the queryid in the pg_stat_statements view and record the result in the rule.
      work_node: The node where the throttling rule takes effect. 0 indicates it takes effect on both the master instance and read-only instances; 1 indicates it takes effect only on the master instance; 2 indicates it takes effect only on read-only instances;
      max_concurrency: The maximum concurrency limit allowed by the throttling rule;
      is_enabled: Whether the current rule is effective;
      memory_only: Optional. Whether the rule needs to be persisted to the table. After a restart or HA, the rule can be reloaded into memory using a command to take effect;
      query_string: Optional. The query statement can be added to the rule. In this case, the queryid of the query_string will not be calculated for validation, and the throttling rule will be based on the queryid. If this parameter is not provided, the kernel will automatically search for the SQL statement corresponding to the queryid in the pg_stat_statements view and record the result in the rule. A SQL throttling example is as follows:
      postgres=> SELECT tencentdb_sql_throttling.add_rule_with_queryid(497939862935121343,0,10,true);
 add_rule_with_queryid 
-----------------------
 
(1 row)

      Adding Throttling Rules through SQL Statements

      Note:
      Throttling for SQL statements using the Extended Query Protocol is currently not supported.
      If a throttling rule already exists for a database object in an SQL statement, new rules cannot be created.
      TencentDB for PostgreSQL provides a throttling function for you. The function definition is as follows:
      tencentdb_sql_throttling.add_rule_with_query(
              query_string text,
              work_node int,
              max_concurrency int,
              is_enabled boolean,
              memory_only boolean
      )
      The parameters of the add_rule_with_query function are explained below. The function returns the query_id of the throttled SQL statement:
      Note:
      query_string: The SQL statement that needs throttling;
      work_node: The node where the throttling rule takes effect. 0 indicates it takes effect on both the master instance and read-only instances; 1 indicates it takes effect only on the master instance; 2 indicates it takes effect only on read-only instances;
      max_concurrency: The maximum concurrency limit allowed by the throttling rule;
      is_enabled: Whether the current rule is effective, with values of true and false;
      memory_only: Optional. Whether the rule needs to be persisted to the table. After a restart or HA, the rule can be reloaded into memory using a command;
      A SQL throttling example is as follows:
      postgres=# select tencentdb_sql_throttling.add_rule_with_query('select pg_sleep(1);',0,1,true);
       add_rule_with_query  
      ----------------------
       -3416356442043621232
      (1 row)
      After the throttling rule is set, you can view it. Refer to viewing throttling rules.

      Deleting Throttling Rules

      Deleting Rules via query_id

      After obtaining the query_id, we can delete the throttling rule based on it. TencentDB for PostgreSQL provides a function for you. The function definition is as follows:
      tencentdb_sql_throttling.drop_rule(
              queryid bigint,
              memory_only boolean
      );
      The parameters of the drop_rule function are explained below:
      Note:
      queryid: A 64-bit integer obtained by hashing the normalized query statement, which can be obtained by querying pg_stat_statements or pg_stat_activity;
      memory_only
      When the memory_only is true, only rules in memory are deleted, and rules in the persistent table are unaffected. This can be seen as unloading throttling rules from memory;
      When the memory_only is false, rules in both memory and the table are deleted. If the deletion fails, a WARNING log will be printed. For read-only instances, only rules in memory are deleted.
      A deletion example is as follows:
      postgres=> SELECT tencentdb_sql_throttling.drop_rule(497939862935121343,true);
-[ RECORD 1 ]
drop_rule |
      When you need to delete certain SQL throttling rules that are not loaded into memory, you can directly delete the corresponding SQL throttling rule data from the persistent table

      Deleting All Rules

      When you need to delete all SQL throttling rules, TencentDB for PostgreSQL provides a function for you. The function definition is as follows:
      tencentdb_sql_throttling.drop_all_rules(
              memory_only boolean
      );
      The parameters of the drop_all_rules function are explained below:
      Note:
      memory_only
      When the memory_only is true, only rules in memory are deleted, and rules in the persistent table are unaffected. This can be seen as unloading throttling rules from memory;
      When the memory_only is false, rules in both memory and the table are deleted. If the deletion fails, a WARNING log will be printed. For read-only instances only rules in memory are deleted.
      A deletion example is as follows:
      postgres=> select tencentdb_sql_throttling.drop_all_rules(true);
       drop_all_rules 
      ----------------
       
      (1 row)

      Loading Throttling Rules

      Loading throttling rules refers to loading SQL throttling rules stored on the disk into memory, and making them effective.

      Loading Throttling Rules via query_id

      After obtaining the query_id, we can load the throttling rule based on it. TencentDB for PostgreSQL provides a function for you. The function definition is as follows:
      tencentdb_sql_throttling.load_rule(queryid bigint);
      The parameters of the load_rule function are explained below:
      Note:
      queryid: A 64-bit integer obtained by hashing the normalized query statement, which can be obtained by querying pg_stat_statements or pg_stat_activity;
      A loading example is as follows:
      postgres=> SELECT tencentdb_sql_throttling.load_rule(497939862935121343);
-[ RECORD 1 ]
load_rule | 

postgres=> SELECT * FROM tencentdb_sql_throttling.rules;
-[ RECORD 1 ]-------+-----------------------------------
queryid             | 497939862935121343
work_node           | 0
max_concurrency     | 10
is_enabled          | t
query_string        | SELECT id FROM t WHERE id = 594017
current_concurrency | 10
total_hit_count     | 4760
reject_count        | 4713

postgres=>

      Loading All Throttling Rules

      When you need to load all throttling rules, TencentDB for PostgreSQL provides a function for you. The function definition is as follows:
      tencentdb_sql_throttling.load_all_rules();
      A loading example is as follows:
      postgres=> select tencentdb_sql_throttling.load_all_rules();
       load_all_rules 
      ----------------
       
      (1 row)

      Persisting Throttling Rules

      Persistence refers to storing SQL throttling rules from memory into the table so they can be directly loaded and take effect without needing to be reset.

      Persisting Throttling Rules via query_id

      After obtaining the query_id, we can persist the throttling rule based on it. TencentDB for PostgreSQL provides a function for you. The function definition is as follows:
      tencentdb_sql_throttling.dump_rule(queryid bigint);
      The parameters of the dump_rule function are explained below:
      Note:
      queryid: A 64-bit integer obtained by hashing the normalized query statement, which can be obtained by querying pg_stat_statements or pg_stat_activity;
      Persistence example:
      postgres=> select tencentdb_sql_throttling.dump_rule(440101247839410938);
       dump_rule 
      -----------
       
      (1 row)

      Persisting All Throttling Rules

      If you need to persist all throttling rules, TencentDB for PostgreSQL provides a function for you. The function definition is as follows:
      tencentdb_sql_throttling.dump_all_rules();
      Example to persist all SQL throttling rules:
      postgres=> select tencentdb_sql_throttling.dump_all_rules();;
       dump_all_rules 
      ----------------
       
      (1 row)
      Be aware that if a rule that has already been persisted is persisted again, an error will occur. The specific error message is shown in the figure below.
      postgres=> SELECT tencentdb_sql_throttling.dump_all_rules();
NOTICE:  duplicate key value violates unique constraint "persistent_rules_table_pkey"
NOTICE:  duplicate key value violates unique constraint "persistent_rules_table_pkey"
 dump_all_rules 
----------------
 
(1 row)

      Viewing Throttling Rules

      You can use the SQL statement SELECT * FROM tencentdb_sql_throttling.rules; to view current throttling rules in memory. The output fields are explained below:
      Note:
      queryid: The queryid of the throttled SQL statement, which is a 64-bit integer obtained by hashing the normalized query statement;
      work_node: The node where the rule takes effect. 0 indicates it takes effect on both the master instance and read-only instances; 1 indicates it takes effect only on the master instance; 2 indicates it takes effect only on read-only instances;
      max_concurrency: The maximum concurrency limit allowed;
      is_enabled: Whether the throttling rule is effective, with values of true and false;
      query_string: The SQL statement corresponding to the throttling rule;
      current_concurrency: The current concurrency quantity of the SQL statement;
      total_hit_count: The number of times the throttling rule has been hit. This value is cumulative since the SQL throttling rule was established and can be emptied only by deleting and reloading the rule;
      reject_count: The total number of times the rule has limited traffic. This value is cumulative since the SQL throttling rule was established and can be emptied only by deleting and reloading the rule;
      Example to view throttling rules in memory:
      postgres=> SELECT * FROM tencentdb_sql_throttling.rules;
-[ RECORD 1 ]-------+--------------------------------
queryid             | 497939862935121343
work_node           | 0
max_concurrency     | 10
is_enabled          | t
query_string        | SELECT id FROM t WHERE id = 553
current_concurrency | 0
total_hit_count     | 91
reject_count        | 81

postgres=>
      You can use the SQL statement SELECT * FROM tencentdb_sql_throttling.persistent_rules_table; to view current throttling rules in storage. The output fields are explained below:
      Note:
      queryid: The queryid of the throttled SQL statement, which is a 64-bit integer obtained by hashing the normalized query statement;
      work_node: The node where the rule takes effect. 0 indicates it takes effect on both the master instance and read-only instances; 1 indicates it takes effect only on the master instance; 2 indicates it takes effect only on read-only instances;
      max_concurrency: The maximum concurrency limit allowed;
      is_enabled: Whether the throttling rule is effective, with values of true and false;
      query_string: The SQL statement corresponding to the throttling rule.
      Example of viewing rules persisted to the table:
      postgres=> SELECT * FROM tencentdb_sql_throttling.persistent_rules_table;
-[ RECORD 1 ]---+-----------------------------------
queryid         | 497939862935121343
work_node       | 0
max_concurrency | 10
is_enabled      | t
query_string    | SELECT id FROM t WHERE id = 594017

      Modifying Throttling Rules

      TencentDB for PostgreSQL provides the change_rule_status function to modify throttling rules. The function definition is as follows:
      tencentdb_sql_throttling.change_rule_status(
              queryid bigint,
              is_enabled boolean,
              max_concurrency int
      );
      The parameters of the function are explained below:
      Note:
      queryid: The queryid of the throttled SQL statement, which is a 64-bit integer obtained by hashing the normalized query statement;
      is_enabled: Whether the throttling rule is effective, with values of true and false;
      max_concurrency: The maximum concurrency limit allowed. The default value is -1, which means no modification.
      Modification example:
      postgres=> select tencentdb_sql_throttling.change_rule_status(440101247839410938,true,300);
       change_rule_status 
      --------------------
       
      (1 row)
      When the system encounters an exception, you can call the change_rule_current_concurrency function to manually modify the current concurrency quantity of a rule, ensuring the rule continues to run. The function definition is as follows:
      tencentdb_sql_throttling.change_rule_current_concurrency(
      	queryid bigint,
      	new_currenct_concurrency int
      );
      The change_rule_current_concurrency parameters are explained below:
      Note:
      queryid: The queryid of the throttled SQL statement, which is a 64-bit integer obtained by hashing the normalized query statement;
      new_currenct_concurrency: The new concurrency quantity for the rule.
      A call example is as follows:
      postgres=> SELECT tencentdb_sql_throttling.change_rule_current_concurrency(497939862935121343,20);
-[ RECORD 1 ]-------------------+-
change_rule_current_concurrency | 

postgres=> SELECT * FROM tencentdb_sql_throttling.rules;
-[ RECORD 1 ]-------+--------------------------------
queryid             | 497939862935121343
work_node           | 0
max_concurrency     | 10
is_enabled          | t
query_string        | SELECT id FROM t WHERE id = 553
current_concurrency | 20
total_hit_count     | 286028
reject_count        | 283788

postgres=>
      Modify the effective status of the rule, increase the concurrency quantity, and make it effective:
      postgres=> SELECT * FROM tencentdb_sql_throttling.rules;
-[ RECORD 1 ]-------+--------------------------------
queryid             | 497939862935121343
work_node           | 0
max_concurrency     | 10
is_enabled          | t
query_string        | SELECT id FROM t WHERE id = 553
current_concurrency | 20
total_hit_count     | 286028
reject_count        | 283788

postgres=> SELECT tencentdb_sql_throttling.change_rule_status(497939862935121343,false,20);
-[ RECORD 1 ]------+-
change_rule_status | 

postgres=> SELECT * FROM tencentdb_sql_throttling.rules;
-[ RECORD 1 ]-------+--------------------------------
queryid             | 497939862935121343
work_node           | 0
max_concurrency     | 20
is_enabled          | f
query_string        | SELECT id FROM t WHERE id = 553
current_concurrency | 20
total_hit_count     | 358931
reject_count        | 356691
      Contact Us

      Contact our sales team or business advisors to help your business.

      Contact Us
      Technical Support

      Open a ticket if you're looking for further assistance. Our Ticket is 7x24 avaliable.

      Submit a Ticket
      7x24 Phone Support
      Copyright © 2013-2024 Tencent Cloud. All Rights Reserved.
      Privacy PolicyLegalCookie Policy