高同時実行の MySQL ワークロードでは、サービスレイヤーやエンジンレイヤーのシリアル化ポイント (トランザクションロックの競合など) が原因で、パフォーマンスが低下することがあります。AliSQL は、バケットベースのキューイングメカニズムを使用して競合のオーバーヘッドを低減し、インスタンスのパフォーマンスを向上させるステートメントキュー機能を提供します。この機能は、同一行を操作するステートメントなど、競合が発生しやすいステートメントを同じバケットに割り当てて、同時実行を最適化します。
背景情報
MySQL のサービスレイヤーやエンジンレイヤーでは、ステートメントの同時実行には複数のシリアル化ポイントがあり、競合が発生しやすくなります。たとえば、DML ステートメントの実行中にはトランザクションロックの競合がよく発生します。InnoDB ストレージエンジンでは、トランザクションロックの最小粒度は行レベルロックです。複数のステートメントが同時に同一行を操作すると深刻な競合が発生し、同時実行が増えるにつれてシステムのスループットが急激に低下します。この問題に対処するため、AliSQL は競合のオーバーヘッドを低減してインスタンスのパフォーマンスを向上させるステートメントキュー機能を提供します。
前提条件
この機能は、次のバージョンの ApsaraDB RDS for MySQL インスタンスで利用できます：
Basic Edition または High-availability Edition の MySQL 8.4
Basic Edition または High-availability Edition の MySQL 8.0 (マイナーエンジンバージョン 20191115 以降)
Basic Edition または High-availability Edition の MySQL 5.7 (マイナーエンジンバージョン 20200630 以降)
設定変数
AliSQL は、ステートメントキュー内のバケットの数とサイズを定義する 2 つの変数を提供します。ApsaraDB 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_queue_field(id) ccl_queue_field(name) */のように重複したヒントを使用した場合、最初のヒントのみが使用されます。ccl_queue_fieldヒントで指定したフィールドは WHERE 句に含まれている必要があります。ccl_queue_fieldヒントでは、WHERE 句は生フィールドに対する二項演算のみをサポートします (フィールドに関数や計算を適用することはできません)。二項演算の右辺の値は数値または文字列である必要があります。
API
AliSQL は、ステートメントキューをモニタリングする 2 つの関数を提供します：
dbms_ccl.show_ccl_queue(): ステートメントキューの現在のステータスを照会します。call dbms_ccl.show_ccl_queue();次の出力が返されます：
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 1 | QUEUE | 64 | 1 | 0 | 0 | | 2 | QUEUE | 64 | 40744 | 64 | 6 | | 3 | QUEUE | 64 | 0 | 0 | 0 | | 4 | QUEUE | 64 | 0 | 0 | 0 | +------+-------+-------------------+---------+---------+---------+ 4 rows in set (0.01 sec)次の表でパラメータを説明します。
パラメータ
説明
CONCURRENCY_COUNT
許可される同時実行ステートメント数の上限です。
MATCHED
ルールに一致したステートメントの累計数です。
RUNNING
現在実行中のステートメント数です。
WAITING
現在待機中のステートメント数です。
dbms_ccl.flush_ccl_queue(): インメモリデータをクリアします。call dbms_ccl.flush_ccl_queue(); call dbms_ccl.show_ccl_queue();次の出力が返されます：
+------+-------+-------------------+---------+---------+---------+ | ID | TYPE | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING | +------+-------+-------------------+---------+---------+---------+ | 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 | WAITING | +------+-------+-------------------+---------+---------+---------+ | 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 | WAITING | +------+-------+-------------------+---------+---------+---------+ | 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 であることが確認できます。
パフォーマンステスト
テスト環境
アプリケーションサーバー：ECS インスタンス
RDS インスタンスの仕様：8 vCPU、16 GB メモリ、ESSD
インスタンスエディション：High-availability Edition (非同期レプリケーション)
テストケース
次の SysBench テストケースは、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テスト結果
ステートメントキュー機能を有効にすると、高同時実行ワークロードでの QPS が大幅に向上します。同時実行が高いほど、改善効果も大きくなります。
説明ステートメントキュー機能がない場合、4,096 スレッドでの負荷テストによりプライマリ/セカンダリ スイッチオーバーが発生し、QPS が 0 になります。