RDS MySQL 8.0 Statement queue

2022-04-15 08:13:49

【7】Statement Queue

背景

MySQL 的 server 层和引擎层在 statement 并发执行过程中，有很多串行化的点，在 DML 语句中，事务锁冲突比较常见，InnoDB 中事务锁的最细粒度是行级锁，如果语句针对相同行进行并发操作，会导致冲突比较严重，系统吞吐量会随着并发的增加而递减。
AliSQL 设计了针对语句的排队机制，相同的行或者不同的语句进行分桶排队，尽可能的把具有相同冲突可能的在一个桶内排队，减少 conflict 的开销。

语法

变量

系统提供了两个变量来定义 CCL queue 的 bucket 数量和大小。

1. ccl_queue_bucket_count

表示：一共有多少个bucket，默认值：4，取值范围：[1, 64]

2. ccl_queue_bucket_size

表示：一个 bucket 允许并发数是多少，默认值：64，取值范围：[1, 4096]

Hint

系统支持两个 hint语法：

CCL_QUEUE_VALUE

根据 value 的值进行 HASH 分桶

 语法：
    /*+ CCL_QUEUE_VALUE([INT | STRING)] */
                
 例子：
  UPDATE /*+ ccl_queue_value(1) */ t 
    SET c=c+1
   WHERE id = 1;
  
  UPDATE /*+ ccl_queue_value('xpchild') */ t
    SET c=c+1 
   WHERE name = 'xpchild';

CCL_QUEUE_FIELD

根据 where 条件中的 field 指定的值进行 hash 分桶

语法：
    /*+ CCL_QUEUE_FIELD(STRING) */
  
例如：
  UPDATE /*+ ccl_queue_field("id") */ t
    SET c=c+1                                                                         
   WHERE id = 1 AND name = 'xpchild';
 
  在 WHERE 条件中查找 id 字段指定的条件常量值来进行分桶

注意： CCL_QUEUE_FIELD 填入的字段名字，在 WHERE 条件的查找过程中：

只支持对裸字段的二元运算符的条件
二元运算的右值必须是数字或者字符串

接口

系统支持两个接口进行查询当前的状态：

dbms_ccl.show_ccl_queue()

mysql> call dbms_ccl.show_ccl_queue();   
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |       1 |       0 |        0 |
|    2 | QUEUE |                64 |   40744 |      65 |        6 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.01 sec)

CONCURRENCY_COUNT: 最大并发数
MATCHED：命中规则的累积数量
RUNNING：当前并发的数量
WAITTING: 当前等待的数量

dbms_ccl.flush_ccl_queue()

清理内存中的状态，重新加载

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

mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |       0 |       0 |        0 |
|    2 | QUEUE |                64 |       0 |       0 |        0 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.00 sec)

效果

针对单行进行并发 update 的场景下，目前进行的测试，相比较原生的 MySQL， AliSQL 有接近 4 倍的提升。

配合 outline 在线修改

为了能够快速在线修改 SQL statement 的并发控制，而不介入冗长的应用业务代码的修改，这里可以使用 AliSQL 提供的 Outline 来配合，下面使用 sysbench 的 update_non_index 作为一个例子：

测试环境：

测试表结构：

CREATE TABLE `sbtest1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `k` int(10) unsigned NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 MAX_ROWS=1000000;

测试语句：

UPDATE sbtest1 SET c='xpchild' WHERE id=0;

测试脚本：

./sysbench 
--mysql-host= {$ip}
--mysql-port= {$port}
--mysql-db=test 
--test=./sysbench/share/sysbench/update_non_index.lua 
--oltp-tables-count=1 
--oltp_table_size=1 
--num-threads=128
--mysql-user=u0

测试过程

1. 在线增加 outline

mysql> CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1, 
                                             ' /*+ ccl_queue_field("id") */ ',
                          "UPDATE sbtest1 SET c='xpchild' WHERE id=0");
Query OK, 0 rows affected (0.01 sec)

2. 查看 outline 并验证

mysql> call dbms_outln.show_outline();
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
| ID   | SCHEMA | DIGEST                                                           | TYPE      | SCOPE | POS  | HINT                           | HIT  | OVERFLOW | DIGEST_TEXT                                 |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
|    1 | test   | 7b945614749e541e0600753367884acff5df7e7ee2f5fb0af5ea58897910f023 | OPTIMIZER |       |    1 |  /*+ ccl_queue_field("id") */  |    0 |        0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? |
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+
1 row in set (0.00 sec)

3. 验证 outline 生效

mysql> explain UPDATE sbtest1 SET c='xpchild' WHERE id=0;
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | sbtest1 | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                     |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update /*+ CCL_QUEUE_FIELD('id') */ `test`.`sbtest1` set `test`.`sbtest1`.`c` = 'xpchild' where (`test`.`sbtest1`.`id` = 0) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

4. 查看 ccl queue 初始状态

mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |       0 |       0 |        0 |
|    2 | QUEUE |                64 |       0 |       0 |        0 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.00 sec)

5. 开启测试

sysbench 
--mysql-host= {$ip}
--mysql-port= {$port}
--mysql-db=test 
--test=./sysbench/share/sysbench/update_non_index.lua 
--oltp-tables-count=1 
--oltp_table_size=1 
--num-threads=128
--mysql-user=u0

6. 验证测试效果

mysql> call dbms_ccl.show_ccl_queue();
+------+-------+-------------------+---------+---------+----------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
+------+-------+-------------------+---------+---------+----------+
|    1 | QUEUE |                64 |   10996 |      63 |        4 |
|    2 | QUEUE |                64 |       0 |       0 |        0 |
|    3 | QUEUE |                64 |       0 |       0 |        0 |
|    4 | QUEUE |                64 |       0 |       0 |        0 |
+------+-------+-------------------+---------+---------+----------+
4 rows in set (0.03 sec)

ccl queue 显示命中了10996 次排队，当前运行并发63个，排队等待4个。

mysql> call dbms_outln.show_outline();
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
| ID   | SCHEMA | DIGEST    | TYPE      | SCOPE | POS  | HINT                           | HIT    | OVERFLOW | DIGEST_TEXT                                 |
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
|    1 | test   | xxxxxxxxx | OPTIMIZER |       |    1 |  /*+ ccl_queue_field("id") */  | 115795 |        0 | UPDATE `sbtest1` SET `c` = ? WHERE `id` = ? |
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
1 row in set (0.00 sec)

outline 显示命中了115795 次。

Ccl queue 可以配合着 outline 进行在线修改业务，方便快捷。

码农公寓

【7】Statement Queue

背景

语法

变量

Hint

接口

效果

配合 outline 在线修改

测试环境：

测试过程

相关文章