文档

Statement Queue

更新时间:

如果您的MySQL的服务层和引擎层在语句并发执行过程中冲突较多,可以使用AliSQL提供的Statement Queue功能,该功能针对语句的排队机制,将语句进行分桶排队,尽量把可能具有相同冲突的语句(例如操作相同行)放在一个桶内排队,减少冲突的开销,有效提高实例性能。

背景信息

MySQL的服务层和引擎层在语句并发执行过程中,有很多串行的点容易导致冲突。例如在DML语句中,事务锁冲突比较常见,InnoDB中事务锁的最细粒度是行级锁,如果语句针对相同行进行并发操作,会导致冲突比较严重,系统吞吐量会随着并发的增加而递减。AliSQL提供Statement Queue功能,能够减少冲突开销,有效提高实例性能。

前提条件

实例版本满足以下要求:

  • RDS MySQL 8.0基础系列或高可用系列(内核小版本20191115及以上)

  • RDS MySQL 5.7基础系列或高可用系列(内核小版本20200630及以上)

变量

AliSQL提供了两个变量来定义语句队列的桶数量和大小:

  • ccl_queue_bucket_count:表示桶的数量。

    • 取值范围:1~64

    • 默认值:4

  • ccl_queue_bucket_size:表示一个桶允许的并发数。

    • 取值范围:1~4096

    • 默认值:64

说明

您可以在RDS控制台修改变量值,详情请参见设置实例参数

语法

AliSQL支持两种hint语法:

  • ccl_queue_value

    根据值进行hash分桶。

    语法:

    /*+ ccl_queue_value([int | string]) */

    示例:

    update /*+ ccl_queue_value(1) */ t set c=c+1 where id = 1;
    
    update /*+ ccl_queue_value('xyz') */ t set c=c+1 where name = 'xyz';
  • ccl_queue_field

    根据where条件中的字段值进行hash分桶。

    语法:

    /*+ ccl_queue_field(string) */

    示例:

    update /*+ ccl_queue_field(id) */ t set c=c+1 where id = 1 and name = 'xyz';
    说明
    • 上述两种hint都是位置敏感的,需要放在update之后。

    • ccl_queue_field每次只能指定一个field,/*+ ccl_queue_field(id name) */方式为语法错误,CCL queue不会生效。/*+ ccl_queue_field(id) ccl_queue_field(name) */为重复hint,以第一个hint指定的field为准。

    • ccl_queue_field中指定的field必须在where条件中出现。

    • 在ccl_queue_field语法中,where条件只支持原始字段(没有在字段上使用任何函数、计算等)的二元运算,并且二元运算的右侧值必须是数字或者字符串。

接口

AliSQL提供两个接口便于您查询Statement Queue状态:

  • dbms_ccl.show_ccl_queue()

    查询当前Statement 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)

实践

功能测试

为避免冗长的应用业务代码的修改,Statement Queue可以配合Statement 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='xyz' 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. 在线增加Statement Outline。

      mysql> CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1, 
                                                   ' /*+ ccl_queue_field(id) */ ',
                               "UPDATE sbtest1 SET c='xyz' WHERE id=0");
      Query OK, 0 rows affected (0.01 sec)
    2. 查看Statement 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. 验证Statement Outline生效。

      mysql> explain UPDATE sbtest1 SET c='xyz' 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` = 'xyz' where (`test`.`sbtest1`.`id` = 0) |
      +-------+------+-----------------------------------------------------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
    4. 查询Statement 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)
      
      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)
      说明

      查询结果显示Statement Outline命中了115,795次规则,Statement Queue状态显示命中了10,996次排队,当前运行并发63个,排队等待4个。

性能测试

  • 测试环境

    • 应用服务器:阿里云ECS实例

    • RDS实例规格: 8 核16 GB 内存、ESSD云盘

    • 实例类型:高可用系列(数据复制方式为异步复制)

  • 测试用例

    对id=1的记录进行并发更新,使用的Sysbench的测试用例如下:

    pathtest = string.match(test, "(.*/)")
    
    if pathtest then
     dofile(pathtest .. "oltp_common.lua")
    else
     require("oltp_common")
    end
    
    function thread_init()
     drv = sysbench.sql.driver()
     con = drv:connect()
    end
    
    function event()
     local val_name
     val_name = "'sdnjkmoklvnseajinvijsfdnvkjsnfjvn".. sb_rand_uniform(1, 4096) .. "'"
     query = "UPDATE sbtest1 SET c=" .. val_name .. " WHERE id=1"
     rs = db_query(query)
    end
  • 测试结果

    开启Statement Queue功能后,在高并发场景下QPS提升显著,并发数越高,提升越明显。

    image.png

    说明

    未开启Statement Queue功能,在4096线程压测下实例发生主备切换,因此QPS为0。

  • 本页导读 (1)