In high-concurrency MySQL workloads, serialization points in the service layer and engine layer, such as transactional lock contention, can degrade performance. AliSQL provides the statement queue feature, which uses a bucket-based queuing mechanism to reduce contention overhead and improve instance performance. This feature assigns statements that are likely to contend, such as statements that operate on the same row, to the same bucket to optimize concurrent execution.
Background information
In the MySQL service layer and engine layer, the concurrent execution of statements involves multiple serialization points that can easily cause contention. For example, transactional lock contention is common during the execution of DML statements. In the InnoDB engine, the finest granularity of a transactional lock is a row-level lock. When multiple statements concurrently operate on the same row, severe contention occurs, which sharply reduces system throughput as concurrency increases. To address this issue, AliSQL provides the statement queue feature to improve instance performance by reducing contention overhead.
Prerequisites
This feature is available for ApsaraDB RDS for MySQL instances with the following versions:
MySQL 8.4 on Basic Edition or High-availability Edition
MySQL 8.0 on Basic Edition or High-availability Edition (minor engine version 20191115 or later)
MySQL 5.7 on Basic Edition or High-availability Edition (minor engine version 20200630 or later)
Configuration variables
AliSQL provides two variables to define the number and size of buckets in the statement queue. You can modify these variables in the ApsaraDB RDS console.
ccl_queue_bucket_count: Specifies the number of buckets.
Valid values: 1 to 64
Default value: 4
ccl_queue_bucket_size: Specifies the number of concurrent statements allowed per bucket.
Valid values: 1 to 4096
Default value: 64
Syntax
AliSQL supports two types of hints:
ccl_queue_value: Hashes a specified value to assign the statement to a bucket.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: Hashes the value of a specified field from the WHERE clause to assign a bucket.
Syntax:
/*+ ccl_queue_field(string) */Example:
update /*+ ccl_queue_field(id) */ t set c=c+1 where id = 1 and name = 'xyz';NoteBoth hints are position-sensitive and must be placed immediately after the UPDATE keyword.
The
ccl_queue_fieldhint can specify only one field at a time. The format/*+ ccl_queue_field(id name) */is a syntax error and prevents the statement queue from taking effect. If you use duplicate hints such as/*+ ccl_queue_field(id) ccl_queue_field(name) */, only the first hint is used.The field specified in the
ccl_queue_fieldhint must appear in the WHERE clause.For the
ccl_queue_fieldhint, the WHERE clause supports binary operations only on raw fields (no functions or calculations applied to the field). The value on the right side of the binary operation must be a number or a string.
API
AliSQL provides two functions to monitor the statement queue:
dbms_ccl.show_ccl_queue(): Queries the current status of the statement queue.call dbms_ccl.show_ccl_queue();The following output is returned:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 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 allowed.
MATCHED
The total number of statements that have matched the rule.
RUNNING
The number of statements that are currently running.
WAITING
The number of statements that are currently waiting.
dbms_ccl.flush_ccl_queue(): Clears the in-memory data.call dbms_ccl.flush_ccl_queue(); call dbms_ccl.show_ccl_queue();The following output is returned:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 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
To avoid making lengthy changes to your application code, you can use the statement queue in conjunction with a statement outline to quickly and easily modify your application online. The following example uses the update_non_index test case from SysBench to demonstrate this process.
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;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
Procedure
Add a statement outline online.
CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1, ' /*+ ccl_queue_field(id) */ ', "UPDATE sbtest1 SET c='xyz' WHERE id=0");The following output is returned:
Query OK, 0 rows affected (0.01 sec)View the statement outline.
call dbms_outln.show_outline();The following output is returned:
+------+--------+------------------------------------------------------------------+-----------+-------+------+--------------------------------+------+----------+---------------------------------------------+ | 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.
explain UPDATE sbtest1 SET c='xyz' WHERE id=0;The following output is returned:
+----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 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;The following output is returned:
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+ | 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)Check the status of the statement queue.
call dbms_ccl.show_ccl_queue();The following output is returned:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 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 results.
call dbms_ccl.show_ccl_queue();The following output is returned:
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 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();The following output is returned:
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+ | 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 results show that the statement outline matched the rule 115,795 times. The statement queue status shows 10,996 matches, with 63 statements running and 4 waiting.
Performance test
Test environment
Application server: An ECS instance
RDS instance specifications: 8 vCPUs, 16 GB of memory, and an ESSD
Instance edition: High-availability Edition, which uses asynchronous replication.
Test case
The following SysBench test case performs concurrent updates on the record with id=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 results
Enabling the statement queue feature significantly increases QPS in high-concurrency workloads. The higher the concurrency, the greater the improvement.
NoteWithout the statement queue feature, a stress test with 4,096 threads causes a primary/secondary switchover, resulting in 0 QPS.