為了應對突發的資料庫請求流量、資源消耗過高的SQL語句以及SQL語句訪問模型變化問題,保證PolarDB叢集持續穩定運行,阿里雲提供了基於SQL語句的並發控制Concurrency Control(簡稱CCL)規則,並提供了工具包DBMS_CCL
便於您快捷地使用該功能。
前提條件
PolarDB叢集版本需為以下版本之一:
PolarDB MySQL版8.0版本。
PolarDB MySQL版5.7版本且核心小版本需為5.7.1.0.6及以上。
說明當叢集版本為PolarDB MySQL版5.7版本且核心小版本為5.7.1.0.27及以上時,CCL與Thread Pool實現相容。
PolarDB MySQL版5.6版本。
注意事項
CCL規則的修改操作只能在主節點進行,修改完成後會自動同步到其他節點。
CCL提供了逾時機制以應對執行DML操作時導致事務鎖死結的問題,等待中的線程也會響應事務逾時和線程KILL操作以應對死結問題。
功能設計
維度特徵
CCL定義了如下五個維度特徵,SQL語句將根據這些維度特徵與CCL規則進行匹配:
維度 | 說明 |
TYPE | SQL語句類型(如SELECT、UPDATE、INSERT、DELETE和DDL語句等)。 |
SCHEMA | SQL操作的資料庫名稱。 |
TABLE | SQL操作的表名稱或視圖名稱。 |
KEYWORD | SQL語句中的關鍵字。您可以在CCL規則中配置多個關鍵字,多個關鍵字之間使用英文分號(;)分隔。 |
DIGEST | SQL語句進行hash計算得到的字串,詳情請參見STATEMENT_DIGEST()。 |
SQL語句與CCL規則匹配方式
當您設定的CCL規則中DIGEST值為空白時,匹配方式如下:
DIGEST值為空白,TYPE、SCHEMA和TABLE非空時,您的SQL語句中的TYPE、SCHEMA和TABLE需要與CCL規則中的TYPE、SCHEMA和TABLE同時匹配,CCL規則才會生效。
DIGEST值為空白,SCHEMA和TABLE為空白,TYPE非空時,您的SQL語句中的TYPE需要與CCL規則中的TYPE匹配,CCL規則才會生效。
說明若CCL規則中的KEYWORD非空,則還需要對KEYWORD進行校正:
當CCL規則中配置單個KEYWORD時,SQL語句中包含該KEYWORD,則表示匹配成功。
當CCL規則中配置多個KEYWORD時,SQL語句中必須包含CCL規則中配置的所有KEYWORD,才能匹配成功。且匹配CCL規則時,對於SQL語句中的KEYWORD無順序要求。
當您設定的CCL規則中DIGEST值非空時,您的SQL語句所在的SCHEMA和DIGEST值,需要與CCL規則中的SCHEMA和DIGEST值同時匹配,CCL規則才會生效。
當您設定的CCL規則中SCHEMA為空白時,您的SQL語句的DIGEST值與CCL規則中的DIGEST值匹配,CCL規則即可生效。
SQL語句與CCL規則匹配順序
單條SQL語句只能與單個CCL規則進行匹配,當單條SQL語句同時符合多個CCL規則時,則按照以下順序選擇優先順序最高的CCL規則進行匹配,且同等優先順序別下,優先匹配ID較小的CCL規則:
根據DIGEST值匹配。
根據TYPE、SCHEMA和TABLE匹配。
僅根據TYPE匹配。
參數說明
您可以在PolarDB控制台修改以下參數,具體操作步驟請參見設定叢集參數和節點參數。
參數 | 說明 |
loose_ccl_mode | 超出並發數量時,SQL語句的行為。取值如下:
說明 僅PolarDB MySQL版 8.0版本支援該參數。5.6和5.7版本直接進行排隊等待。 |
loose_ccl_max_waiting_count | 當 取值範圍:0~65536。預設值為0。 說明 僅PolarDB MySQL版 5.7和8.0版本支援該參數。 |
CCL規則表
PolarDB設計了一張系統資料表concurrency_control
用於儲存CCL規則,系統啟動時會自動建立該表,無需您手動建立。該系統資料表的建立語句如下:
CREATE TABLE concurrency_control (
Id bigint AUTO_INCREMENT NOT NULL,
Type varchar(64),
Schema_name varchar(64),
Table_name varchar(64),
Concurrency_count bigint NOT NULL,
Keywords text,
State enum('N','Y') COLLATE utf8_general_ci DEFAULT 'Y' NOT NULL,
Ordered enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
Digest varchar(64),
Digest_text longtext,
Extra mediumtext,
PRIMARY KEY Rule_id(id)
) Engine=InnoDB STATS_PERSISTENT=0 CHARACTER SET utf8 COLLATE utf8_bin
comment='Concurrency control' TABLESPACE=mysql;
參數說明請參見下表:
參數 | 說明 |
Id | CCL規則ID。 |
Type | SQL語句類型(如SELECT、UPDATE、INSERT、DELETE和DDL語句等)。 |
Schema_name | 資料庫名稱。 |
Table_name | 資料庫內的表名。 |
Concurrency_count | 並發數量。 說明 您可以將 |
Keywords | 關鍵字,多個關鍵字使用英文分號(;)分隔。 |
State | 規則是否啟用,取值範圍如下:
|
Ordered | Keywords中配置多個關鍵字時,是否按順序匹配CCL規則。取值範圍如下:
|
Digest | 使用 |
Digest_text | SQL語句的特徵。 |
Extra | 其它資訊。 |
管理CCL規則
為了便捷地管理CCL規則,PolarDB在DBMS_CCL
中定義了以下六個本機存放區過程。詳細說明如下:
add_ccl_rule:增加根據TYPE、SCHEMA、TABLE和KEYWORD匹配的CCL規則。
文法
dbms_ccl.add_ccl_rule('<Type>','<Schema_name>','<Table_name>',<Concurrency_count>,'<Keywords>');
樣本
增加TYPE為SELECT的CCL規則,當並發數量為10時進行排隊等待或者報錯。
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
增加TYPE為SELECT,且SELECT語句中出現關鍵字key1的CCL規則,當並發數量為20時進行排隊等待或者報錯。
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');
增加TYPE為SELECT,且SELECT語句中出現關鍵字key1、key2和key3的CCL規則,當並發數量為20時進行排隊等待或者報錯,關鍵字出現順序不限。
CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1;key2;key3');
增加TYPE為SELECT,SCHEMA為
test
,TABLE為t
的CCL規則,當SELECT語句的並發數量為10時進行排隊等待或者報錯。CALL dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 10, '');
add_ccl_digest_rule:增加根據DIGEST值匹配的CCL規則。
說明支援使用
add_ccl_digest_rule
預存程序的資料庫版本引擎為:PolarDB MySQL版為8.0.1版本且核心小版本為8.0.1.1.31及以上。
PolarDB MySQL版為8.0.2版本且核心小版本為8.0.2.2.12及以上。
文法
dbms_ccl.add_ccl_digest_rule('<Schema_name>', '<Query>', <Concurrency_count>);
樣本
增加SQL語句與
SELECT * FROM t1
匹配的CCL規則,當並發數量為10時進行排隊等待或者報錯。CALL dbms_ccl.add_ccl_digest_rule("", "SELECT * FROM t1", 10);
增加SCHEMA為
test
,且SQL語句與SELECT * FROM t1
匹配的CCL規則,當並發數量為10時進行排隊等待或者報錯。CALL dbms_ccl.add_ccl_digest_rule("test", "SELECT * FROM t1", 10);
增加SQL語句與
SELECT * FROM t1 WHERE col1=1
匹配的CCL規則,當並發數量為10時進行排隊等待或者報錯。CALL dbms_ccl.add_ccl_digest_rule("", "SELECT * FROM t1 WHERE col1 = 1", 10);
說明SQL語句中存在常量時,即使常量值不相同,也會進行匹配。如上述CCL規則也能與SQL語句
SELECT * FROM t1 WHERE col1 = 2
匹配。
add_ccl_digest_rule_by_hash:增加根據DIGEST值匹配的CCL規則,直接使用計算完成的DIGEST值而非SQL語句。
說明僅當PolarDB MySQL版 引擎為8.0.1版本且核心小版本為8.0.1.1.31及以上時,支援使用
add_ccl_digest_rule_by_hash
預存程序。文法
dbms_ccl.add_ccl_digest_rule_by_hash('<Schema_name>', '<Digest>', <Concurrency_count>);
樣本
增加SQL語句的DIGEST值與
533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a
匹配的CCL規則,當並發數量為10時進行排隊等待或者報錯。CALL dbms_ccl.add_ccl_digest_rule_by_hash('', '533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a', 10);
其中,
533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a
為SELECT * FROM t1
計算得到的DIGEST值。您可以通過SELECT statement_digest("SELECT * FROM t1")
命令計算或從其他模組中擷取該值。增加SCHEMA為
test
,且SQL語句的DIGEST值與533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a
匹配的CCL規則,當並發數量為10時進行排隊等待或者報錯。CALL dbms_ccl.add_ccl_digest_rule_by_hash('test', '533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9a', 10);
del_ccl_rule:刪除CCL規則。
文法
dbms_ccl.del_ccl_rule(<Id>);
樣本
刪除規則ID為15的CCL規則。
CALL dbms_ccl.del_ccl_rule(15);
如果刪除的規則不存在,系統會報相應的警告資訊,您可以使用
SHOW WARNINGS;
命令來查看警告內容。樣本如下:刪除規則ID為100的CCL規則。
CALL dbms_ccl.del_ccl_rule(100);
執行結果如下:
Query OK, 0 rows affected, 2 warnings (0.00 sec)
執行以下命令,查看警示內容。
SHOW WARNINGS;
執行結果如下:
+---------+------+----------------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------------+ | Warning | 7517 | Concurrency control rule 100 is not found in table | | Warning | 7517 | Concurrency control rule 100 is not found in cache | +---------+------+----------------------------------------------------+
說明上述樣本中PolarDB MySQL版 8.0版本的
Code
為7517
、PolarDB MySQL版 5.7版本的Code
為3267
、PolarDB MySQL版 5.6版本的Code
為3045
。show_ccl_rule:查看記憶體中已啟用的CCL規則。
文法
dbms_ccl.show_ccl_rule();
樣本
CALL dbms_ccl.show_ccl_rule();
執行結果如下:
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+ | ID | TYPE | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS | +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+ | 17 | SELECT | test | t | Y | N | 30 | 0 | 0 | 0 | | | 16 | SELECT | | | Y | N | 20 | 0 | 0 | 0 | key1 | | 18 | SELECT | | | Y | N | 10 | 0 | 0 | 0 | | +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
說明其中,MATCHED、RUNNING和WAITTING參數的說明如下:
MATCHED:規則匹配成功次數。
RUNNING:該規則下正在並發執行的線程數。
WAITTING:該規則下正在等待執行的線程數。
您可以使用UPDATE語句來修改CCL規則ID值,以調整目標規則的優先順序。
文法
UPDATE mysql.concurrency_control SET ID = xx WHERE ID = xx;
樣本
執行以下命令,查看記憶體中已啟用的CCL規則。
CALL dbms_ccl.show_ccl_rule();
執行結果如下:
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+ | ID | TYPE | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS | +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+ | 17 | SELECT | test | t | Y | N | 30 | 0 | 0 | 0 | | | 16 | SELECT | | | Y | N | 20 | 0 | 0 | 0 | key1 | | 18 | SELECT | | | Y | N | 10 | 0 | 0 | 0 | | +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
執行以下命令,調整ID值為17的CCL規則的優先順序,即將ID值修改為20。
UPDATE mysql.concurrency_control SET ID = 20 WHERE ID = 17;
執行以下命令,查看修改後的已啟用的CCL規則。
CALL dbms_ccl.show_ccl_rule();
執行結果如下:
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+ | ID | TYPE | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS | +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+ | 16 | SELECT | | | Y | N | 20 | 0 | 0 | 0 | key1 | | 18 | SELECT | | | Y | N | 10 | 0 | 0 | 0 | | | 20 | SELECT | test | t | Y | N | 30 | 0 | 0 | 0 | | +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
flush_ccl_rule: 如果您通過修改表
concurrency_control
中的內容來修改CCL規則,則您還需要使用如下命令使該規則生效。文法
dbms_ccl.flush_ccl_rule();
樣本
通過使用UPDATE語句修改CCL規則ID來調整目標規則的優先順序。
UPDATE mysql.concurrency_control SET CONCURRENCY_COUNT = 15 WHERE Id = 18;
執行結果如下:
Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
執行以下命令,使該設定生效。
CALL dbms_ccl.flush_ccl_rule();
執行結果如下:
Query OK, 0 rows affected (0.00 sec)
功能測試
分別以三個維度來建立CCL規則:
CALL dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, ''); //使用SELECT語句操作test資料庫下的表sbtest1,且並發數量為3。 CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2'); //SELECT語句中包含關鍵字sbtest2,且並發數量為2。 CALL dbms_ccl.add_ccl_rule('SELECT', '', '', 2, ''); //SELECT語句,且並發數量為2。
使用Sysbench進行測試,具體情境如下:
64 threads
4 tables
select.lua
查看規則並發數量,如下:
CALL dbms_ccl.show_ccl_rule();
執行結果如下:
+------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+ | ID | TYPE | SCHEMA | TABLE | STATE | ORDER | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING | KEYWORDS | +------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+ | 20 | SELECT | test | sbtest1 | Y | N | 3 | 389 | 3 | 9 | | | 21 | SELECT | | | Y | N | 2 | 375 | 2 | 14 | sbtest2 | | 22 | SELECT | | | Y | N | 2 | 519 | 2 | 34 | | +------+--------+--------+---------+-------+-------+-------------------+---------+---------+----------+----------+ 3 rows in set (0.00 sec)
查看RUNNING列,符合預期的並行數量。