全部產品
Search
文件中心

PolarDB:Concurrency Control

更新時間:Sep 13, 2024

為了應對突發的資料庫請求流量、資源消耗過高的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規則:

  1. 根據DIGEST值匹配。

  2. 根據TYPE、SCHEMA和TABLE匹配。

  3. 僅根據TYPE匹配。

參數說明

您可以在PolarDB控制台修改以下參數,具體操作步驟請參見設定叢集參數和節點參數

參數

說明

loose_ccl_mode

超出並發數量時,SQL語句的行為。取值如下:

  • WAIT(預設):排隊等待。等待其它SQL語句執行完成後執行該語句。

  • REFUSE:報錯。

說明

PolarDB MySQL版 8.0版本支援該參數。5.6和5.7版本直接進行排隊等待。

loose_ccl_max_waiting_count

loose_ccl_mode參數設定為WAIT時,符合單個CCL規則的SQL語句排隊等待的最大數量,超出該值時會報錯。

取值範圍: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

並發數量。

說明

您可以將Concurrency_count的值設定為0來實現SQL黑名單功能。以此來禁止執行此類query。

Keywords

關鍵字,多個關鍵字使用英文分號(;)分隔。

State

規則是否啟用,取值範圍如下:

  • Y(預設):啟用規則。

  • N:禁用規則。

Ordered

Keywords中配置多個關鍵字時,是否按順序匹配CCL規則。取值範圍如下:

  • N(預設):Keywords中配置多個關鍵字時,不需要按順序匹配CCL規則。

  • YKeywords中配置多個關鍵字時,需要按順序匹配CCL規則。

Digest

使用Digest_text進行hash計算時,得到的64個位元組的hash字串。詳情請參見STATEMENT_DIGEST()

Digest_text

SQL語句的特徵。

Extra

其它資訊。

管理CCL規則

為了便捷地管理CCL規則,PolarDBDBMS_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);

      其中,533c0a9cf0cf92d2c26e7fe8821735eb4a72c409aaca24f9f281d137427bfa9aSELECT * 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;命令來查看警告內容。樣本如下:

    1. 刪除規則ID為100的CCL規則。

      CALL dbms_ccl.del_ccl_rule(100);

      執行結果如下:

      Query OK, 0 rows affected, 2 warnings (0.00 sec)
    2. 執行以下命令,查看警示內容。

      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版本的Code7517PolarDB MySQL版 5.7版本的Code3267PolarDB MySQL版 5.6版本的Code3045

  • 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 |          |
    +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+​
    說明

    其中,MATCHEDRUNNINGWAITTING參數的說明如下:

    • MATCHED:規則匹配成功次數。

    • RUNNING:該規則下正在並發執行的線程數。

    • WAITTING:該規則下正在等待執行的線程數。

  • 您可以使用UPDATE語句來修改CCL規則ID值,以調整目標規則的優先順序。

    文法

    UPDATE mysql.concurrency_control SET ID = xx WHERE ID = xx;

    樣本

    1. 執行以下命令,查看記憶體中已啟用的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 |          |
      +------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+
    2. 執行以下命令,調整ID值為17的CCL規則的優先順序,即將ID值修改為20。

      UPDATE mysql.concurrency_control SET ID = 20 WHERE ID = 17;
    3. 執行以下命令,查看修改後的已啟用的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)​

功能測試

  1. 分別以三個維度來建立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。
  2. 使用Sysbench進行測試,具體情境如下:

    • 64 threads

    • 4 tables

    • select.lua

  3. 查看規則並發數量,如下:

    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列,符合預期的並行數量。