幹貨 | 淺析RDS MySQL 8.0語句級并發控制

背景

為了應對突發的資料庫請求流量、資源消耗過載的語句通路、SQL 通路模型的變化，并保持 MySQL 執行個體持續穩定運作，阿裡雲RDS for MySQL 8.0所采用的AliSQL分支設計了基于語句規則的并發控制，Statement Concurrency Control，以下簡稱 CCL，有效控制比對某種規則的并發度，并提供了一組工具包（DBMS_CCL package) 友善快捷使用。

規則設計

CCL 規則一共定義了三個次元的特征：

1）SQL command

根據 statement 的類型，例如 ‘SELECT', 'UPDATE', 'INSERT', 'DELETE';

2) Object

根據 statement 操作的對象進行控制，例如 TABLE，VIEW;

3）keywords

根據 statement 語句的關鍵字進行控制;

CCL 根據規則的定義，設計了一個系統表，mysql.concurrency_control 持久化儲存 CCL rule：

Concurrency_control

CREATE TABLE `concurrency_control` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Type` enum('SELECT','UPDATE','INSERT','DELETE') NOT NULL DEFAULT 'SELECT',
  `Schema_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Table_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Concurrency_count` bigint(20) DEFAULT NULL,
  `Keywords` text COLLATE utf8_bin,
  `State` enum('N','Y') NOT NULL DEFAULT 'Y',
  `Ordered` enum('N','Y') NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Id`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB 
DEFAULT CHARSET=utf8 COLLATE=utf8_bin
STATS_PERSISTENT=0 COMMENT='Concurrency control'

COLUMNS

"Type"，用來定義 SQL command

"Schema_name" && "Table_name"，用來定義 Object

"Keywords"，用來定義關鍵字，可使用 ';' 分隔符多個關鍵字

"Concurrency_count"，用來定義并發度

"State"，表示這條規則是否 active

"Ordered"，表示keywords中多個關鍵字是否按順序比對

使用者可以直接操作這個表來定義規則，也可以使用 DBMS_CCL 工具包來操作 CCL rule。

管理接口

為了便捷的管理 CCL rule，AliSQL 在 DBMS_CCL package 中定義了四個 native procedure 來管理；

1）Add CCL rule

dbms_ccl.add_ccl_rule(type=>, schema=>, table=>, Concurrency_count=>, keywords=>);

增加規則（包括表和記憶體）例如：

1. 增加 SELECT 語句的并發度為 10；
    mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 10, '');
    Query OK, 0 rows affected (0.00 sec)

2. 增加 SELECT 語句，并在語句中出現關鍵字 key1 的并發度為 20
    mysql> call dbms_ccl.add_ccl_rule('SELECT', '', '', 20, 'key1');
    Query OK, 0 rows affected (0.00 sec)

3. 增加 test.t 表的 SELECT 語句的并發讀為 20；
    mysql> call dbms_ccl.add_ccl_rule('SELECT', 'test', 't', 30, '');
    Query OK, 0 rows affected (0.00 sec)

規則的比對按照 3 > 2 > 1 的優先級順序進行比對。

2）Delete CCL rule

dbms_ccl.del_ccl_rule(rule_id=> );

删除規則（包括記憶體和表中）例如：

1. 删除 rule id = 15 的 CCL rule
    mysql> call dbms_ccl.del_ccl_rule(15);
    Query OK, 0 rows affected (0.01 sec)

2. 如果删除的rule 不存在，語句報相應的 warning
    mysql> call dbms_ccl.del_ccl_rule(100);
    Query OK, 0 rows affected, 2 warnings (0.00 sec)

    mysql> show warnings;
+---------+------+----------------------------------------------------+
| Level   | Code | Message                                            |
+---------+------+----------------------------------------------------+
| Warning | 7514 | Concurrency control rule 100 is not found in table |
| Warning | 7514 | Concurrency control rule 100 is not found in cache |
+---------+------+----------------------------------------------------+

3) Show CCL rule

dbms_ccl.show_ccl_rule();

展示在記憶體中 active rule 的情況，例如：

mysql> 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 |          |
+------+--------+--------+-------+-------+-------+-------------------+---------+---------+----------+----------+

除了 rule 本身的屬性之外，增加了三個數字統計：

MATCHED
規則比對成功次數

RUNNING
在此規則下，正在 run 的線程數

WAITING
在此規則下，正在 wait的線程數

4）Flush CCL rule

dbms_ccl.flush_ccl_rule();

如果直接操作了concurrency_control table 修改規則，不能立即生效，可以調用 flush，重新生效。例如：

mysql> 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

mysql> call dbms_ccl.flush_ccl_rule();
Query OK, 0 rows affected (0.00 sec)

壓力測試

測試場景

1）設計三條規則

Rule 1：對 sbtest1 表應用 Object rule 控制

call dbms_ccl.add_ccl_rule('SELECT', 'test', 'sbtest1', 3, '');

Rule 2: 對sbtest2 表應用 keyword rule 控制

call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, 'sbtest2');

Rule 3: 對sbtest3 表應用 SQL command 控制

call dbms_ccl.add_ccl_rule('SELECT', '', '', 2, '');

2）使用 sysbench 進行測試

64 threads
4 tables
select.lua

檢視規則并發使用情況，可以到到 running 和 waiting 的數量：

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

檢視線程運作情況：大部分處在 Concurrency control waitting 狀态。

mysql> show processlist;
+-----+-----------------+-----------------+------+---------+------+------------------------------+--------------------------------------+
| Id  | User            | Host            | db   | Command | Time | State                        | Info                                 |
+-----+-----------------+-----------------+------+---------+------+------------------------------+--------------------------------------+
|  72 | root            | localhost:33601 | NULL | Query   |    0 | starting                     | show processlist                     |
| 171 | u1              | localhost:60120 | test | Query   |    2 | Concurrency control waitting | SELECT pad FROM sbtest3 WHERE id=51 |
| 172 | u1              | localhost:60128 | test | Query   |    5 | Concurrency control waitting | SELECT pad FROM sbtest4 WHERE id=35 |
| 174 | u1              | localhost:60385 | test | Query   |    4 | Concurrency control waitting | SELECT pad FROM sbtest3 WHERE id=54 |
| 178 | u1              | localhost:60136 | test | Query   |   12 | Concurrency control waitting | SELECT pad FROM sbtest4 WHERE id=51 |
| 179 | u1              | localhost:60149 | test | Query   |    5 | Concurrency control waitting | SELECT pad FROM sbtest2 WHERE id=51 |
| 182 | u1              | localhost:60124 | test | Query   |    1 | Concurrency control waitting | SELECT pad FROM sbtest4 WHERE id=51 |
| 183 | u1              | localhost:60371 | test | Query   |    5 | User sleep                   | SELECT pad FROM sbtest2 WHERE id=51 |
| 184 | u1              | localhost:60133 | test | Query   |    4 | Concurrency control waitting | SELECT pad FROM sbtest3 WHERE id=51 |
| 190 | u1              | localhost:60406 | test | Query   |    5 | Concurrency control waitting | SELECT pad FROM sbtest3 WHERE id=51 |
| 191 | u1              | localhost:60402 | test | Query   |    1 | Concurrency control waitting | SELECT pad FROM sbtest4 WHERE id=51 |
| 192 | u1              | localhost:60131 | test | Query   |    2 | User sleep                   | SELECT pad FROM sbtest1 WHERE id=51 |

......

使用規則和風險

Concurrency_control 被設計成不産生 BINLOG，是以對于 CCL 的操作隻影響目前執行個體。

對于 DML 的并發控制，可能存在事務鎖死鎖的情況，除了 CCL 提供了逾時機制，

同時等待中的線程也會響應事務逾時和線程 KILL 操作，以應對死鎖可能。

幹貨 | 淺析RDS MySQL 8.0語句級并發控制

背景

規則設計

壓力測試

使用規則和風險

繼續閱讀

SQL優化SQL語句優化的目的

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離