在MySQL的高并发场景下,服务层和引擎层的串行点(如事务锁冲突)可能导致性能下降。AliSQL提供了Statement Queue功能,通过分桶排队机制减少冲突开销,从而提高实例性能。该功能将可能具有相同冲突的语句(如操作相同行的语句)分配到同一个桶内排队,优化并发执行效率。
背景信息
在MySQL的服务层和引擎层中,语句并发执行时存在多个串行化点,容易引发冲突。例如,在DML语句执行过程中,事务锁冲突较为常见。InnoDB引擎中事务锁的最细粒度为行级锁,当多个语句针对相同行进行并发操作时,会导致严重的冲突,进而使系统吞吐量随着并发量的增加而显著下降。为此,AliSQL提供了Statement Queue功能,通过减少冲突开销,有效提升实例性能。
前提条件
RDS MySQL实例版本需满足以下要求:
RDS MySQL 8.0基础系列或高可用系列(内核小版本20191115及以上)
RDS MySQL 5.7基础系列或高可用系列(内核小版本20200630及以上)
配置变量
AliSQL提供了两个变量来定义语句队列的桶数量和大小,您可以在RDS控制台修改变量取值。
ccl_queue_bucket_count:表示桶的数量。
取值范围:1~64
默认值:4
ccl_queue_bucket_size:表示一个桶允许的并发数。
取值范围:1~4096
默认值:64
语法
AliSQL支持两种Hint语法:
ccl_queue_value
:根据值进行hash分桶。语法:
/*+ ccl_queue_value([int | string]) */
示例:
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:根据where条件中的字段值进行hash分桶。
语法:
/*+ ccl_queue_field(string) */
示例:
update /*+ ccl_queue_field(id) */ t set c=c+1 where id = 1 and name = 'xyz';
说明上述两种hint都是位置敏感的,需要放在update关键字之后。
ccl_queue_field每次只能指定一个field,
/*+ ccl_queue_field(id name) */
方式为语法错误,CCL queue不会生效。/*+ ccl_queue_field(id) ccl_queue_field(name) */
为重复hint,以第一个hint指定的field为准。ccl_queue_field中指定的field必须在where条件中出现。
在ccl_queue_field语法中,where条件只支持原始字段(没有在字段上使用任何函数、计算等)的二元运算,且二元运算的右侧值必须是数字或字符串。
接口
AliSQL提供两个接口便于您查询Statement Queue状态:
dbms_ccl.show_ccl_queue()
:查询当前Statement Queue状态。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)
参数说明如下:
参数
说明
CONCURRENCY_COUNT
最大并发数。
MATCHED
命中规则的总数。
RUNNING
当前并发的数量。
WAITTING
当前等待的数量。
dbms_ccl.flush_ccl_queue()
:清理内存中的数据。call dbms_ccl.flush_ccl_queue(); 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)
实践
功能测试
为避免冗长的应用业务代码修改,Statement Queue可以配合Statement Outline进行在线业务修改,方便快捷。下文使用SysBench的update_non_index测试用例进行演示。
测试环境
测试表结构
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;
测试语句
UPDATE sbtest1 SET c='xyz' WHERE id=0;
测试脚本
./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
测试过程
在线增加Statement Outline。
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)
查看Statement Outline。
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)
验证Statement Outline生效。
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)
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)
查询Statement Queue状态。
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)
开启测试。
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
验证测试效果。
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)
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)
说明查询结果显示Statement Outline命中了115,795次规则,Statement Queue状态显示命中了10,996次排队,当前运行并发63个,排队等待4个。
性能测试
测试环境
应用服务器:阿里云ECS实例
RDS实例规格: 8 核16 GB 内存、ESSD云盘
实例类型:高可用系列(数据复制方式为异步复制)
测试用例
对id=1的记录进行并发更新,使用的Sysbench的测试用例如下:
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
测试结果
开启Statement Queue功能后,在高并发场景下QPS提升显著,并发数越高,提升越明显。
说明未开启Statement Queue功能,在4096线程压测下实例发生主备切换,因此QPS为0。