数据库内核月报

背景

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 分桶

语法：
/*+ 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';

2.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 条件的查找过程中：

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

接口

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

1.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: 当前等待的数量

2.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 进行在线修改业务，方便快捷。

数据库内核月报－ 2019 / 09

AliSQL · 引擎特性 · Statement Queue

背景

语法

变量

Hint

接口

效果

配合 outline 在线修改

测试环境：

测试过程