Statement Queue

PolarDB设计了针对语句的排队(Statement Queue)机制,将语句进行分桶排队,尽量把可能具有相同冲突(例如操作相同行)的语句放在一个桶内排队,减少冲突的开销。

背景信息

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

使用限制

  • PolarDB集群版本需为以下版本之一:

    • PolarDB MySQL版8.0版本且内核小版本需为8.0.1.1.10及以上

    • PolarDB MySQL版5.7版本且内核小版本需为5.7.1.0.6及以上。

    • PolarDB MySQL版5.6版本且内核小版本需为20200601及以上。

  • PolarDB MySQL版5.6版本与PolarDB MySQL版5.7和8.0版本使用了不同的语法实现Statement Queue机制,因此仅PolarDB MySQL版5.7和8.0版本的集群支持与计划固化 (Statement Outline)配合使用完成在线修改SQL statement的并发控制。

  • 热点行性能优化总开关开启后(hotspot参数设置为ON后),将不能使用Statement Queue机制。

效果

在针对单行进行并发UPDATE的场景测试中,相比原生的MySQL,PolarDB有接近4倍的性能提升。

语法

  • 针对PolarDB MySQL版5.6版本,您可以通过以下方式进行hash分桶。

    说明

    POLARDB_STATEMENT_CONCURRENT_QUEUE语法支持SELECT、UPDATE、INSERT和DELETE等语句。

    • 根据int或string值进行hash分桶。

      • 语法:

        POLARDB_STATEMENT_CONCURRENT_QUEUE [int | string]
      • 示例:

        insert POLARDB_STATEMENT_CONCURRENT_QUEUE 1 into t values(1, 1, 'xpchild');
        update POLARDB_STATEMENT_CONCURRENT_QUEUE 1 t set c=c+1 where id = 1;
        update POLARDB_STATEMENT_CONCURRENT_QUEUE "xpchild" t set col1 = col1+1 where id =1;
    • 根据WHERE条件中的field值进行hash分桶。

      • 语法:

        POLARDB_STATEMENT_CONCURRENT_QUEUE [field]
      • 示例:

        select POLARDB_STATEMENT_CONCURRENT_QUEUE id * from t where 3 = id;
        update POLARDB_STATEMENT_CONCURRENT_QUEUE id t set c=c+1 where id = 1 and name = 'xpchild';
        说明

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

  • 针对PolarDB MySQL版5.7和8.0,您可以通过以下HINT语法进行hash分桶。

    说明

    ccl_queue_value语法支持SELECT、UPDATE、INSERT和DELETE等语句。

    • 根据int或string值进行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';
    • 根据WHERE条件中的field值进行hash分桶。

      • 语法:

        /*+ CCL_QUEUE_FIELD(STRING) */
      • 示例:

         update /*+ ccl_queue_field("id") */ t set c=c+1
                                              where id = 1 and name = 'xpchild';
        说明

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

变量

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

变量

说明

ccl_queue_bucket_count

表示桶的数量,取值范围为1~64,默认值为4。

ccl_queue_bucket_size

表示一个桶允许的并发数,取值范围为1~4096,默认值为64。

接口

PolarDB提供以下两个接口便于您查询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_COUNTMATCHEDRUNNINGWAITTING说明如下:

    • 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 Outline在线修改

针对PolarDB MySQL版5.7和8.0版本,Statement Queue还可以配合计划固化 (Statement Outline)使用实现快速在线修改SQL statement的并发控制,避免介入冗长的应用业务代码的修改。 下文使用Sysbench的update_non_index为例进行演示。

说明

由于语法不同,PolarDB MySQL版5.6版本不支持与计划固化 (Statement Outline)配合使用。

  • 测试环境

    • 测试表结构

      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. 在线增加Statement 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. 查看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='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. 查询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个。