MySQL の高い同時実行性のシナリオでは、サービスレイヤーとエンジンレイヤーのシリアルポイント (トランザクションロックの競合など) がパフォーマンスの低下につながる可能性があります。AliSQL は、バケットキューメカニズムを通じて競合のオーバーヘッドを削減し、インスタンスのパフォーマンスを向上させるステートメントキュー機能を提供します。この機能は、同じ競合を持つ可能性のあるステートメント (同じ行を操作するステートメントなど) を同じバケットでキューイングするように割り当て、同時実行効率を最適化します。
背景情報
MySQL サービスレイヤーとエンジンレイヤーでは、ステートメントが同時に実行されると複数のシリアル化ポイントが存在し、競合が発生しやすくなります。たとえば、トランザクションロックの競合は DML 文の実行中によく発生します。InnoDB エンジンのトランザクションロックの最も細かい粒度は行レベルロックです。複数のステートメントが同じ行に対して同時操作を実行すると、深刻な競合が発生し、同時実行性が高まるにつれてシステムのスループットが大幅に低下します。この問題に対処するため、AliSQL はステートメントキュー機能を提供し、競合のオーバーヘッドを削減することでインスタンスのパフォーマンスを効果的に向上させます。
前提条件
RDS MySQL インスタンスのバージョンは、次の要件を満たす必要があります:
RDS MySQL 8.0 Basic Edition または High-availability Edition (マイナーエンジンバージョン 20191115 以降)
RDS MySQL 5.7 Basic Edition または High-availability Edition (マイナーエンジンバージョン 20200630 以降)
設定変数
AliSQL は、ステートメントキューのバケット数とサイズを定義するための 2 つの変数を提供します。RDS コンソールで変数値を変更できます。
ccl_queue_bucket_count: バケットの数。
有効値: 1～64
デフォルト値: 4
ccl_queue_bucket_size: バケットごとに許可される同時実行ステートメントの数。
有効値: 1～4096
デフォルト値: 64
構文
AliSQL は 2 つのヒント構文をサポートしています:
ccl_queue_value: ハッシュアルゴリズムを使用して、値に基づいてバケットを決定します。構文:
/*+ 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 句のフィールド値に基づいてバケットを決定します。
構文:
/*+ ccl_queue_field(string) */例:
update /*+ ccl_queue_field(id) */ t set c=c+1 where id = 1 and name = 'xyz';説明上記のヒントはどちらも位置に敏感であり、update キーワードの後に配置する必要があります。
ccl_queue_field は一度に 1 つのフィールドしか指定できません。構文
/*+ ccl_queue_field(id name) */は正しくなく、CCL キューは有効になりません。/*+ ccl_queue_field(id) ccl_queue_field(name) */は重複したヒントであり、最初のヒントで指定されたフィールドが使用されます。ccl_queue_field で指定されたフィールドは WHERE 句に現れる必要があります。
ccl_queue_field 構文では、WHERE 句は生のフィールド (フィールドに関数や計算などを使用しない) に対する二項演算のみをサポートし、二項演算の右側の値は数値または文字列でなければなりません。
関数
AliSQL は、ステートメントキューのステータスをクエリするのに役立つ 2 つの関数を提供します:
dbms_ccl.show_ccl_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)
プラクティス
機能テスト
長時間のアプリケーションコードの変更を避けるために、ステートメントキューはステートメントアウトラインと併用してオンラインビジネスの変更を行うことができ、便利で効率的です。次の例では、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
テスト手順
ステートメントアウトラインをオンラインで追加します。
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)ステートメントアウトラインを表示します。
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)ステートメントアウトラインが有効であることを確認します。
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)ステートメントキューのステータスをクエリします。
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)説明クエリ結果は、ステートメントアウトラインが 115,795 ルールにヒットし、ステートメントキューのステータスは 10,996 のキューヒットを示し、現在 63 が同時に実行中で、4 がキューで待機していることを示しています。
パフォーマンス テスト
テスト環境
アプリケーションサーバー: Alibaba Cloud ECS インスタンス
RDS インスタンスタイプ: 8 コア、16 GB メモリ、エンタープライズ SSD (ESSD)
インスタンスタイプ: High-availability Edition (非同期レプリケーション付き)
テストケース
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テスト結果
ステートメントキューを有効にすると、高い同時実行性のシナリオで QPS が大幅に増加します。同時実行性が高いほど、改善はより顕著になります。
説明ステートメントキューを有効にしない場合、4096 スレッドのストレステスト中にプライマリ/セカンダリフェールオーバーが発生し、QPS が 0 になりました。