Use Statement Queue to improve performance under high concurrency - ApsaraDB RDS

Statement Queue reduces row-level lock contention in high-concurrency MySQL workloads by routing statements that operate on the same rows into shared buckets, where they queue and execute in controlled batches instead of competing for the same locks simultaneously.

How it works

In InnoDB, transaction locks are row-level. When many statements concurrently update the same row, each statement acquires and releases the row lock in sequence, creating a bottleneck: throughput drops sharply as concurrency rises. In severe cases, this cascade of lock conflicts can trigger a primary/secondary failover.

Statement Queue is an AliSQL feature that intercepts statements before they reach the engine layer and routes them into buckets using a hash algorithm. Statements hashed to the same bucket queue together and execute up to a configurable concurrency limit per bucket. This concentrates contention within each bucket and eliminates the uncontrolled stampede across all threads.

Two SQL Hint syntaxes control which bucket a statement goes into:

ccl_queue_value — hashes on an explicit value you supply in the Hint
ccl_queue_field — hashes on the actual value of a field in the WHERE clause

Prerequisites

Before you begin, ensure that you have:

RDS MySQL 8.0 Basic Edition or High-availability Edition with minor engine version 20191115 or later
RDS MySQL 5.7 Basic Edition or High-availability Edition with minor engine version 20200630 or later

Configure Statement Queue

Statement Queue uses two variables to define the bucket structure. Modify them in the RDS console. See Modify the parameters of an ApsaraDB RDS for MySQL instance.

Variable	Description	Valid values	Default
`ccl_queue_bucket_count`	Number of buckets	1–64	4
`ccl_queue_bucket_size`	Maximum concurrent statements per bucket	1–4096	64

Apply hints to statements

Add a Hint immediately after the UPDATE keyword to route a statement into the queue.

ccl_queue_value

Routes the statement to a bucket based on a value you specify — an integer or a 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';

Use ccl_queue_value when you know the specific value that identifies the hotspot row (for example, a fixed record ID or partition key).

ccl_queue_field

Routes the statement to a bucket based on the runtime value of a field in the WHERE clause.

UPDATE /*+ ccl_queue_field(id) */ t SET c = c + 1 WHERE id = 1 AND name = 'xyz';

Use ccl_queue_field when the hotspot value changes across requests but always appears in the WHERE clause.

Hint constraints

Both hints are position-sensitive and must be placed directly after UPDATE.
ccl_queue_field accepts only one field. /*+ ccl_queue_field(id name) */ is invalid and the queue does not take effect.
When duplicate hints appear (for example, /*+ ccl_queue_field(id) ccl_queue_field(name) */), the first hint applies.
The field specified in ccl_queue_field must appear in the WHERE clause.
The WHERE clause must use binary operations directly on raw fields — no functions or calculations applied to the field. The right-hand side of the operation must be a number or string.

Monitor queue status

Use these stored procedures to observe queue activity at runtime.

Check queue status

CALL dbms_ccl.show_ccl_queue();

Example output:

+------+-------+-------------------+---------+---------+----------+
| 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 |
+------+-------+-------------------+---------+---------+----------+

Column	Description
`CONCURRENCY_COUNT`	Maximum concurrent statements allowed in this bucket
`MATCHED`	Total number of rule hits
`RUNNING`	Statements currently executing
`WAITTING`	Statements currently queued and waiting

Reset queue counters

To clear in-memory statistics and reset MATCHED, RUNNING, and WAITTING to zero:

CALL dbms_ccl.flush_ccl_queue();
CALL dbms_ccl.show_ccl_queue();

Example output after flush:

+------+-------+-------------------+---------+---------+----------+
| 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 |
+------+-------+-------------------+---------+---------+----------+

Apply hints without modifying application code

To apply Statement Queue hints to live traffic without changing application code, use it together with Statement Outline. Statement Outline injects hints into matching statements at the server level, so the application sends plain SQL and the server applies the hint automatically.

The following example uses the Sysbench update_non_index test case to demonstrate this approach.

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 (128 threads):

./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 a Statement Outline to inject the ccl_queue_field hint online.

CALL DBMS_OUTLN.add_optimizer_outline('test', '', 1,
  ' /*+ ccl_queue_field(id) */ ',
  "UPDATE sbtest1 SET c='xyz' WHERE id=0");

Expected output:

Query OK, 0 rows affected (0.01 sec)

Confirm the outline was registered.

CALL dbms_outln.show_outline();

Expected output:

+------+--------+------------------------------------------------------------------+-----------+-------+------+------------------------------+------+----------+---------------------------------------------+
| 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` = ? |
+------+--------+------------------------------------------------------------------+-----------+-------+------+------------------------------+------+----------+---------------------------------------------+

Verify the hint takes effect by running EXPLAIN and checking the warning.

EXPLAIN UPDATE sbtest1 SET c = 'xyz' WHERE id = 0;
SHOW WARNINGS;

The SHOW WARNINGS output should include the injected hint:

+-------+------+------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                      |
+-------+------+------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | update /*+ ccl_queue_field(id) */ `test`.`sbtest1` set `test`.`sbtest1`.`c` = 'xyz' where (`test`.`sbtest1`.`id` = 0) |
+-------+------+------------------------------------------------------------------------------------------------------------------------------+

Check the baseline queue status before starting the load.
```
CALL dbms_ccl.show_ccl_queue();
```
All MATCHED counts should be zero at this point.

Start the Sysbench load.

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

Verify results while the load is running.

CALL dbms_ccl.show_ccl_queue();

Expected output:

+------+-------+-------------------+---------+---------+----------+
| 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 |
+------+-------+-------------------+---------+---------+----------+

CALL dbms_outln.show_outline();

Expected output:

+------+--------+-----------+-----------+-------+------+------------------------------+--------+----------+---------------------------------------------+
| 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` = ? |
+------+--------+-----------+-----------+-------+------+------------------------------+--------+----------+---------------------------------------------+

Statement Outline matched 115,795 statements. Of those, 10,996 were routed to bucket 1 of the Statement Queue, with 63 executing concurrently and 4 waiting — confirming the queue is absorbing contention as expected.

Performance testing

Test environment:

Application server: Alibaba Cloud ECS instance
RDS instance: 8 cores, 16 GB memory, enterprise SSD (ESSD), High-availability Edition with asynchronous replication

Test case: All 128–4096 concurrent threads update 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)
end

Results: With Statement Queue enabled, QPS increases significantly in high-concurrency scenarios. The higher the concurrency, the more pronounced the improvement.

Note

Without Statement Queue, a primary/secondary failover occurred during the 4096-thread stress test, bringing QPS to 0.

What's next

Statement Outline — inject hints into live traffic without modifying application code
Modify the parameters of an ApsaraDB RDS for MySQL instance — update ccl_queue_bucket_count and ccl_queue_bucket_size in the RDS console