In high concurrency scenarios in MySQL, serial points in the service layer and engine layer (such as transaction lock conflicts) can lead to performance degradation. AliSQL provides the Statement Queue feature, which reduces conflict overhead through a bucket queue mechanism, improving instance performance. This feature assigns statements that might have the same conflicts (such as statements operating on the same row) to queue in the same bucket, optimizing concurrent execution efficiency.
Background information
In the MySQL service layer and engine layer, multiple serialization points exist when statements execute concurrently, which can easily cause conflicts. For example, transaction lock conflicts are common during DML statement execution. The finest granularity of transaction locks in the InnoDB engine is row-level locking. When multiple statements perform concurrent operations on the same row, serious conflicts occur, causing system throughput to decrease significantly as concurrency increases. To address this issue, AliSQL provides the Statement Queue feature, which effectively improves instance performance by reducing conflict overhead.
Prerequisites
RDS MySQL instance version must meet the following requirements:
RDS MySQL 8.0 Basic Edition or High-availability Edition (minor engine version 20191115 or later)
RDS MySQL 5.7 Basic Edition or High-availability Edition (minor engine version 20200630 or later)
Configuration variables
AliSQL provides two variables to define the bucket quantity and size of a statement queue. You can modify the variable values in the RDS console.
ccl_queue_bucket_count: the number of buckets.
Valid values: 1 to 64
Default value: 4
ccl_queue_bucket_size: the number of concurrent statements allowed per bucket.
Valid values: 1 to 4096
Default value: 64
Syntax
AliSQL supports two Hint syntaxes:
ccl_queue_value: uses a hash algorithm to determine the bucket based on the value.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: uses a hash algorithm to determine the bucket based on the field value 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';NoteBoth hints above are position-sensitive and must be placed after the update keyword.
ccl_queue_field can only specify one field at a time. The syntax
/*+ ccl_queue_field(id name) */is incorrect and the CCL queue will not take effect. For/*+ ccl_queue_field(id) ccl_queue_field(name) */, which is a duplicate hint, the field specified in the first hint will be used.The field specified in ccl_queue_field must appear in the WHERE clause.
In the ccl_queue_field syntax, the WHERE clause only supports binary operations on raw fields (without using any functions, calculations, etc. on the fields), and the right-side value of the binary operation must be a number or string.
Functions
AliSQL provides two functions to help you query the Statement Queue status:
dbms_ccl.show_ccl_queue(): queries the current Statement Queue status.call dbms_ccl.show_ccl_queue();Returns the following:
+------+-------+-------------------+---------+---------+----------+ | 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)Parameter description:
Parameter
Description
CONCURRENCY_COUNT
Maximum concurrency.
MATCHED
Total number of rule hits.
RUNNING
Current number of concurrent operations.
WAITTING
Current number of waiting operations.
dbms_ccl.flush_ccl_queue(): clears data in memory.call dbms_ccl.flush_ccl_queue(); call dbms_ccl.show_ccl_queue();Returns the following:
+------+-------+-------------------+---------+---------+----------+ | 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
Functional testing
To avoid lengthy application code modifications, Statement Queue can be used with Statement Outline for online business modifications, which is convenient and efficient. The following example demonstrates using the Sysbench update_non_index test case.
Test environment
Test 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;Test statement
UPDATE sbtest1 SET c='xyz' WHERE id=0;Test 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
Add Statement Outline online.
CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1, ' /*+ ccl_queue_field(id) */ ', "UPDATE sbtest1 SET c='xyz' WHERE id=0");Returns the following:
Query OK, 0 rows affected (0.01 sec)View Statement Outline.
call dbms_outln.show_outline();Returns the following:
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+ | 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 Statement Outline is effective.
explain UPDATE sbtest1 SET c='xyz' WHERE id=0;Returns the following:
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 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)show warnings;Returns the following:
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+ | 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 Statement Queue status.
call dbms_ccl.show_ccl_queue();Returns the following:
+------+-------+-------------------+---------+---------+----------+ | 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 test results.
call dbms_ccl.show_ccl_queue();Returns the following:
+------+-------+-------------------+---------+---------+----------+ | 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)call dbms_outln.show_outline();Returns the following:
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+ | 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)NoteThe query results show that Statement Outline hit 115,795 rules, Statement Queue status shows 10,996 queue hits, with 63 currently running concurrently and 4 waiting in queue.
Performance testing
Test environment
Application server: Alibaba Cloud ECS instance
RDS instance type: 8 cores, 16 GB memory, enterprise SSD (ESSD)
Instance type: High-availability Edition (with asynchronous replication)
Test case
Concurrent updates on records with id=1, using the following Sysbench test case:
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 results
With Statement Queue enabled, QPS increases significantly in high concurrency scenarios. The higher the concurrency, the more noticeable the improvement.
NoteWithout Statement Queue enabled, a primary/secondary failover occurred during the 4096-thread stress test, resulting in a QPS of 0.