All Products
Search
Document Center

ApsaraDB RDS:Statement queue

Last Updated:Jan 30, 2024

During the execution of concurrent statements, the MySQL server and engine may conflict with each other. To resolve this issue, AliSQL provides the statement queue feature to allow statements that require the same resources to queue in the same bucket. For example, the statements that require the same row of a table queue in the same bucket. This feature helps reduce overheads that are caused by potential conflicts and improve the performance of your ApsaraDB RDS for MySQL instance.

Background information

During the execution of concurrent statements, the MySQL server and engine may conflict with each other in some serial operations. For example, transactional lock conflicts are common during the execution of DML statements. The InnoDB storage engine supports resource locking accurate to rows. If multiple DML statements are concurrently executed on a row, serious conflicts may occur. The overall throughput of your database system decreases in proportion with the number of concurrent DML statements. AliSQL provides the statement queue feature to reduce conflict overheads and improve instance performance.

Prerequisites

The RDS instance runs one of the following MySQL versions and RDS editions:

  • MySQL 8.0 with a minor engine version of 20191115 or later on RDS Basic Edition or RDS High-availability Edition

  • MySQL 5.7 with a minor engine version of 20200630 or later on RDS Basic Edition or RDS High-availability Edition

Variables

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

  • ccl_queue_bucket_count: the number of buckets that are allowed in the statement queue.

    • Valid values: 1 to 64.

    • Default value: 4.

  • ccl_queue_bucket_size: the number of concurrent statements that are allowed per bucket.

    • Valid values: 1 to 4096.

    • Default value: 64.

Note

You can modify the variables in the ApsaraDB RDS console. For more information, see Modify instance parameters.

Syntax

AliSQL supports two hints:

  • ccl_queue_value

    AliSQL uses a hash algorithm to determine the bucket into which each statement is placed based on the value of a specified field.

    Syntax:

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

    Example:

    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

    AliSQL uses a hash algorithm to determine the bucket into which each statement is placed based on the value of the field that is specified in the WHERE clause.

    Syntax:

    /*+ ccl_queue_field(string) */

    Example:

    update /*+ ccl_queue_field(id) */ t set c=c+1 where id = 1 and name = 'xyz';
    Note
    • The preceding hints must be placed after update.

    • The ccl_queue_field hint specifies only one field at a time. If the /*+ ccl_queue_field(id name) */ hint contains a syntax error, the concurrency control (CCL) queue does not take effect. If the /*+ ccl_queue_field(id) ccl_queue_field(name) */ hint is duplicate, the field that is specified in the first hint prevails.

    • The field that is specified in the ccl_queue_field hint must be used in the WHERE clause.

    • In the ccl_queue_field hint, the WHERE clause supports binary operators only on raw fields. These raw fields have not been altered by using functions or computation operations. In addition, the right operand of such a binary operator must be a number or a string.

Functions

AliSQL provides two functions that are used to query the status of a statement queue:

  • dbms_ccl.show_ccl_queue()

    This function is used to query 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.

    Parameter

    Description

    CONCURRENCY_COUNT

    The maximum number of concurrent statements that are allowed.

    MATCHED

    The total number of statements that hit the specified rules.

    RUNNING

    The number of statements that are being concurrently executed.

    WAITTING

    The number of statements that are waiting in queue.

  • dbms_ccl.flush_ccl_queue()

    This function is used to delete the data about a statement queue from the memory.

    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

Feature testing

Statement queue can work with the statement outline feature to support online updates of your application code. For more information, see Statement outline. In the following example, Sysbench is used to execute the update_non_index script.

  • Test environment

    • Table schema

      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;
    • Statements

      UPDATE sbtest1 SET c='xyz' 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='xyz' WHERE id=0");
      Query OK, 0 rows affected (0.01 sec)
    2. View the statement outline that 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 has taken effect.

      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. Query the status of the statement queue that is 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, a total of 115,795 statements hit the rules for the statement outline, a total of 10,996 statements hit the rules for the statement queue, a total of 63 statements are being concurrently executed, and four statements are waiting in queue.

Performance test

  • Test environment

    • The application is deployed on an Alibaba Cloud Elastic Compute Service (ECS) instance.

    • The RDS instance provides 8 CPU cores and 16 GB of memory and uses enhanced SSDs (ESSDs).

    • The RDS instance runs RDS High-availability Edition and uses asynchronous data replication.

  • Test case

    The following test case is used to perform concurrent updates on records whose IDs are set to 1:

    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
  • Test result

    If the statement queue feature is enabled, the queries per second (QPS) significantly increases and the increase is notable when the number of concurrent queries is large.

    Note

    If the statement queue feature is disabled, a primary/secondary switchover occurs when the number of threads in the stress test is 4,096. As a result, the QPS is 0.