变量
系统提供了两个变量来定义 ccl queue 的 bucket 数量和大小。
1.ccl_queue_bucket_count
表示:一共有多少个bucket, 默认值:4,取值范围:[1, 64]
2.ccl_queue_bucket_size
表示:一个bucket 允许并发数是多少, 默认值:64, 取值范围:[1, 4096]
Hint
系统支持两个hint语法:
1.ccl_queue_value
根据 value 的值进行 hash 分桶
2.ccl_queue_field
/*+ CCL_QUEUE_FIELD(STRING) */
例如:
update /*+ ccl_queue_field("id") */ t set c=c+1
where id = 1 and name = 'xpchild';
在where条件中查找id字段指定的条件常量值来进行分桶
注意: ccl_queue_field 填入的字段名字, 在 where 条件的查找过程中:
- 只支持对裸字段的二元运算符的条件
- 二元运算的右值必须是数字或者字符串
接口
系统支持两个接口进行查询当前的状态:
1.dbms_ccl.show_ccl_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)
CONCURRENCY_COUNT: 最大并发数
MATCHED: 命中规则的累积数量
RUNNING:当前并发的数量
WAITTING: 当前等待的数量
2.dbms_ccl.flush_ccl_queue()
清理内存中的状态, 重新加载
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)
针对单行进行并发 update 的场景下,目前进行的测试,相比较原生的 MySQL, AliSQL 有接近 4 倍的提升。
为了能够快速在线修改 SQL statement 的并发控制,而不介入冗长的应用业务代码的修改,这里可以使用AliSQL 提供的 Outline 来配合, 下面使用 sysbench 的 update_non_index 作为一个例子:
测试环境:
测试表结构:
UPDATE sbtest1 SET c='xpchild' 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
测试过程
1. 在线增加 outline
mysql> CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1,
' /*+ ccl_queue_field("id") */ ',
"UPDATE sbtest1 SET c='xpchild' WHERE id=0");
Query OK, 0 rows affected (0.01 sec)
2. 查看 outline 并验证
3. 验证 outline 生效
mysql> explain UPDATE sbtest1 SET c='xpchild' 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 |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
+-------+------+-----------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4. 查看 ccl queue 状态
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. 开启测试
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. 验证测试效果
ccl queue 显示命中了10996 次排队, 当前运行并发63个,排队等待4个。
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` = ? |
+------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+
outline 显示命中了115795 次。