During the execution of concurrent statements, the MySQL server and engine may conflict with each other. To resolve this issue, PolarDB-X Standard Edition provides the statement queue feature to allow statements that require the same resources to queue in the same bucket. For example, the statements that require the same row of a table queue in the same bucket. This feature helps reduce overheads that are caused by potential conflicts and improve the performance of your instance.
Background information
During the execution of concurrent statements, the MySQL server and engine may conflict with each other in some serial operations. For example, transactional lock conflicts are common during the execution of DML statements. The InnoDB storage engine supports resource locking accurate to rows. If multiple DML statements are concurrently executed on a row, serious conflicts may occur. The overall throughput of your database system decreases in proportion with the number of concurrent DML statements. PolarDB-X Standard Edition provides the statement queue feature to reduce conflict overheads and improve instance performance.
Prerequisites
The edition of your PolarDB-X instance is Standard Edition, and the engine of the instance is MySQL 8.0.
Variables
PolarDB-X Standard Edition provides the following variables that are used to define the number of buckets and size of a statement queue:
ccl_queue_bucket_count: the number of buckets that are allowed in the statement queue.
Valid values: 1 to 64.
Default value: 4.
ccl_queue_bucket_size: the number of concurrent statements that are allowed per bucket.
Valid values: 1 to 4096.
Default value: 64.
Syntax
PolarDB-X Standard Edition supports the following hints:
ccl_queue_value
PolarDB-X uses a hash algorithm to determine the bucket into which each statement is placed based on the value of a specified field. Syntax:
/*+ ccl_queue_value([int | string]) */Sample statement:
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
PolarDB-X uses a hash algorithm to determine the bucket into which each statement is placed based on the field value that is specified in the WHERE clause.
Syntax:
/*+ ccl_queue_field(string) */Sample statement:
update /*+ ccl_queue_field(id) */ t set c=c+1 where id = 1 and name = 'xyz';
The preceding hints must be placed after update.
The ccl_queue_field hint specifies only one field at a time. If the /*+ ccl_queue_field(id name) */ hint contains a syntax error, the concurrency control (CCL) queue does not take effect. If the /*+ ccl_queue_field(id) ccl_queue_field(name) */ hint is duplicate, the field that is specified in the first hint prevails.
The field that is specified in the ccl_queue_field hint must be used in the WHERE clause.
In the ccl_queue_field hint, the WHERE clause supports binary operators only on raw fields. These raw fields have not been altered by using functions or computation operations. In addition, the right operand of such a binary operator must be a number or a string.
Operations
PolarDB-X Standard Edition supports the following hints:
dbms_ccl.show_ccl_queue()
This function is used to query the status of the current statement 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)The following table describes the parameters in the statement.
Parameter
Description
Parameter
Description
CONCURRENCY_COUNT
The maximum number of concurrent statements that are allowed.
MATCHED
The total number of statements that hit the specified rules.
RUNNING
The number of statements that are being concurrently executed.
WAITTING
The number of statements that are waiting in the queue.
dbms_ccl.flush_ccl_queue()
This function is used to delete the data of a statement queue from the memory.
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)
Feature testing
Statement queue can work with the statement outline feature to support online updates of your application code. In the following example, Sysbench is used to execute the update_non_index script.
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;Statement
UPDATE sbtest1 SET c='xyz' WHERE id=0;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=u0Test procedure
Create a statement outline in online mode.
mysql> 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)View the statement outline that you created.
mysql> 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)Verify whether the statement outline takes effect.
mysql> 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) mysql> 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)Query the status of the statement queue that is used.
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)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 result.
mysql> 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) 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` = ? | +------+--------+-----------+-----------+-------+------+--------------------------------+--------+----------+---------------------------------------------+ 1 row in set (0.00 sec)Based on the query result, a total of 115,795 statements hit the rules for the statement outline, a total of 10,996 statements hit the rules for the statement queue, a total of 63 statements are being concurrently executed, and four statements are waiting in the queue.
