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.
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';NoteThe 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
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)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)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)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)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=u0Verify 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)NoteBased 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) endTest 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.
NoteIf 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.