SQL Throttling

UDB Read/Write splitting middleware supports SQL throttling feature. SQL throttling can be used to control database request traffic and SQL concurrency, ensuring service availability.

Throttling Schemes

There are two forms of throttling in read/write splitting:

1、High Watermark Throttling

Set an upper limit for active connections for read/write splitting middleware. When concurrency exceeds this value, the execution of SQL is rejected to protect MySQL. This is referred to as high watermark throttling. This adds a throttling layer to the database, preventing it from being overwhelmed by sudden burst requests.

High Watermark Throttling Commands：

a、uset active_sess_hwm =XXX Sets the throttling high watermark for the read/write splitting middleware, with a range of values from 0~100000, where 0 means no throttling by default. This parameter corresponds to the newly added field active_sess_hwm in the table t_rwproxy_instance.

b、uset active_sess_ctl_mode="all[|select|insert|update|delete]" The SQL types subject to throttling, combination of possible values: [all|select|insert|update|delete]，default is select, use caution when setting to all.

c、ushow rw_proxy active_sess_hwm、active_sess_ctl_mode values can be obtained

d、ushow sql_limit_stat shows the current throttling information for each read/write splitting middleware.

Where,

rwId：Read/Write splitting id

active_sess_hwm：Throttling High Watermark

active_sess_ctl_mode：Throttling Type

curr_active_sess：Current Active Sessions

2、SQL Template Throttling

Set throttling SQL templates for the read/write splitting middleware through custom SQL. During the statistical period (5s), when the number of SQL requests executed exceeds the set frequency, the execution of template SQL is rejected, protecting MySQL.

The SQL Template Throttling Commands are as follows:

a、uinsert sql_limit(sql_stmt : freq_num) SQL template throttling, sql_stmt is a SQL template, a SQL statement with constants replaced with ?. freq_num is the throttling frequency unit (SQL execution count/s). SQL template throttling information will be recorded in mysql.sql_limit_rules. Upon startup, the middleware will attempt to create operations similar to sql_route_rules table.

b、ushow sql_limit_stmts queries all throttling templates

c、udelete sql_limit("rule_id" : id) Deletes the SQL throttling template. The id here is the rule_id value obtained through the ushow sql_limit_stmts query.

or using the command udelete sql_limit("sql_template" : sql_stmt) to delete the SQL throttling template

d、ushow sql_limit_stmts_stat displays the current template throttling information for each read/write splitting middleware.

Where,

mwId：Read/Write splitting id

stmt：Throttling SQL Template

limit_freq_num：Throttling Frequency (Records/s)

curr_freq_num：Current SQL Frequency (Records/s)

e、ushow mw_nodes queries all the IPs and ports of read/write splitting middleware

Throttling Rejected Scenarios:

a、Super user permissions

b、commit/rolback

c、Transactions

d、ushow、uinsert、udelete、uset and other middleware management commands count active connections, but do not throttle

Additional Throttling Notes

a、High watermark throttling pertains to the read/write splitting middleware. UDB read/write splitting has two middleware by default, so the high watermark of read/write splitting throttling equals the high watermark of middleware throttling times the number of middleware.

b、Throttling settings do not distinguish between users, i.e., the throttling rules set for user1 also apply to user2.

c、Read/write splitting in read_only mode also supports throttling.

d、When ignoring throttling conditions, template throttling is not effective.