This topic describes the Statement Queue feature of AliSQL. AliSQL uses this feature to enable statements that may request locks on the same resources to queue in the same bucket. This reduces overheads from possible conflicts. For example, AliSQL can place statements that are to manage the same row in a table into the same bucket.

Background information

During concurrent statement execution, the MySQL server and engine are likely to conflict with each other in a number of serial operations. Take transactional lock conflicts triggered by data manipulation language (DML) statements as an example. The InnoDB storage engine supports resource locking accurate to rows. If you execute multiple DML statements concurrently on the same row, serious conflicts may occur, which decreases the overall throughput of your database system. The Statement Queue feature reduces overheads from these conflicts to increase database performance.

Benefits

AliSQL executes UPDATE statements concurrently on a single row four times better than native MySQL.

Variables

AliSQL provides two variables to define the bucket quantity and size of a statement queue:

  • ccl_queue_bucket_count: the number of buckets allowed in a statement queue. Value range: 1 to 64. Default value: 4.
  • ccl_queue_bucket_size: the number of concurrent statements allowed in a statement bucket. Value range: 1 to 4096. Default value: 64.
Note You can reconfigure the variables in the ApsaraDB for RDS console. For more information, see Reconfigure parameters for an RDS for MySQL instance.

Syntax

AliSQL supports two hint syntaxes:

  • ccl_queue_value

    AliSQL places statements into buckets based on the field values calculated by using the hash algorithm.

    Syntax:

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

    Example:

    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';
  • ccl_queue_field

    AliSQL places statements into buckets based on the field values specified in the WHERE conditions by using the hash algorithm.

    Syntax:

    /*+ ccl_queue_field(string) */

    Example:

    update /*+ ccl_queue_field("id") */ t set c=c+1 where id = 1 and name = 'xpchild';
    Note In the ccl_queue_field syntax, the WHERE conditions only support binary operations on raw fields that have not been altered by using functions or computation operations. Additionally, the value in the part on the right of a binary operation must be a number or string.

API operations

AliSQL provides two API operations for you to query the status of a statement queue:

  • dbms_ccl.show_ccl_queue()

    Queries the status of the current 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)

    The following table describes the parameters of the API operation.

    Parameter Description
    CONCURRENCY_COUNT The maximum number of concurrent statements allowed.
    MATCHED The total number of matched rules.
    RUNNING The number of concurrent statements that are being executed.
    WAITTING The number of statements that are waiting for execution.
  • dbms_ccl.flush_ccl_queue()

    Deletes data about the current statement queue from the memory. Then, you can query the status of the statement 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)

Practices

Statement Queue can work with Statement outline to support online updates of your application code. The following example describes how to execute the update_non_index.lua script by using SysBench.

  • Test environment
    • Table structure
      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;
    • Statement
      UPDATE sbtest1 SET c='xpchild' WHERE id=0;
    • Script
      ./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
  • Test procedure
    1. Create a statement outline in online mode.
      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. View the statement outline you created.
      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. Verify that the statement outline takes effect.
      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. Query the status of the statement queue used.
      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. Start the test.
      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. Verify the test result.
      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)
      Note Based on the query results, the statement outline is hit 115,795 times, the statement queue is hit 10,996 times, a total of 63 statements are being executed concurrently, and four statements are waiting in queue.