RDS MySQL 8.0 Statement queue-阿里云开发者社区

开发者社区> 数据库> 正文

RDS MySQL 8.0 Statement queue

简介: 【7】Statement Queue 背景 MySQL 的 server 层和引擎层在 statement 并发执行过程中,有很多串行化的点,在 DML 语句中,事务锁冲突比较常见,InnoDB 中事务锁的最细粒度是行级锁,如果语句针对相同行进行并发操作,会导致冲突比较严重,系统吞吐量会随着并发的增加而递减。

【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语法:

  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';
  1. 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. 只支持对裸字段的二元运算符的条件
  2. 二元运算的右值必须是数字或者字符串

接口

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

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

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

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章