数据库内核月报－ 2019/09 - AliSQL · 引擎特性 · Statement Queue - 《数据库内核月报》

背景
语法
- 变量
- Hint
- 接口
效果
配合 outline 在线修改
- 测试环境：
- 测试过程

背景

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语法：

1.ccl_queue_value

根据 value 的值进行 hash 分桶


1. 语法：
2. /*+ CCL_QUEUE_VALUE([INT | STRING)] */

4. 例子：
5. update /*+ ccl_queue_value(1) */ t set c=c+1 where id = 1;

7. update /*+ ccl_queue_value('xpchild') */ t set c=c+1
8. where name = 'xpchild';

2.ccl_queue_field

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


1. 语法：
2. /*+ CCL_QUEUE_FIELD(STRING) */

4. 例如：
5. update /*+ ccl_queue_field("id") */ t set c=c+1
6. where id = 1 and name = 'xpchild';

8. 在where条件中查找id字段指定的条件常量值来进行分桶

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

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

接口

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

1.dbms_ccl.show_ccl_queue()


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

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

2.dbms_ccl.flush_ccl_queue()

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


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

3. mysql> call dbms_ccl.show_ccl_queue();
4. +------+-------+-------------------+---------+---------+----------+
5. | ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITTING |
6. +------+-------+-------------------+---------+---------+----------+
7. |    1 | QUEUE |                64 |       0 |       0 |        0 |
8. |    2 | QUEUE |                64 |       0 |       0 |        0 |
9. |    3 | QUEUE |                64 |       0 |       0 |        0 |
10. |    4 | QUEUE |                64 |       0 |       0 |        0 |
11. +------+-------+-------------------+---------+---------+----------+
12. 4 rows in set (0.00 sec)

效果

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

配合 outline 在线修改

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

测试环境：

测试表结构：


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

测试语句：


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

测试脚本：


1. ./sysbench
2. --mysql-host= {$ip}
3. --mysql-port= {$port}
4. --mysql-db=test
5. --test=./sysbench/share/sysbench/update_non_index.lua
6. --oltp-tables-count=1
7. --oltp_table_size=1
8. --num-threads=128
9. --mysql-user=u0

测试过程

1. 在线增加 outline


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

2. 查看 outline 并验证


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

3. 验证 outline 生效


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

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

4. 查看 ccl queue 状态


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

5. 开启测试


1. sysbench
2. --mysql-host= {$ip}
3. --mysql-port= {$port}
4. --mysql-db=test
5. --test=./sysbench/share/sysbench/update_non_index.lua
6. --oltp-tables-count=1
7. --oltp_table_size=1
8. --num-threads=128
9. --mysql-user=u0

6. 验证测试效果


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

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


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

outline 显示命中了115795 次。

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

原文：http://mysql.taobao.org/monthly/2019/09/05/