ステートメントキューを使用して、ステートメントをバケットにキューイングします - PolarDB

多数の Data Manipulation Language (DML) ステートメントが同時に同一行を対象とすると、InnoDB の行レベルロックによって直列化されます。同時実行が増えると、スレッドはロックの背後でキューに入り、スループットが低下します。ステートメントキューは、ハッシュアルゴリズムを使用して競合するステートメントを共有バケットにルーティングすることでこの問題に対処します。これにより、ステートメントはロック競合を発生させるのではなくキューで待機します。その結果、ロックの取得と解放を繰り返すサイクルによるオーバーヘッドが軽減されます。

単一行に対して 128 の同時実行 UPDATE ステートメントを実行するベンチマークテストでは、ステートメントキューを有効にした PolarDB のスループットは、標準の MySQL の約 4 倍になります。

ステートメントキューの使用タイミング

ステートメントキューは、大量の DML ステートメントが同一行を対象とする場合に最も効果的です。たとえば、リーダーボードのカウンター、在庫更新、または多数の同時書き込みが同一のプライマリキーに集中するチケット予約システムなどです。

ステートメントが異なる行を対象とするワークロードでは、低減できるロック競合がほとんどないため、効果は限定的です。

hotspot 変数を ON に設定してホット行の最適化を有効にしている場合、ステートメントキューは自動的にバイパスされます。両方を同時に使用せず、どちらか一方を使用してください。

前提条件

開始する前に、次の項目を満たしていることを確認してください：

次のいずれかのマイナーエンジンバージョンで稼働している PolarDB for MySQL クラスター：
- PolarDB for MySQL 8.0：マイナーエンジンバージョン 8.0.1.1.10 以降
- PolarDB for MySQL 5.7：マイナーエンジンバージョン 5.7.1.0.6 以降
- PolarDB for MySQL 5.6：マイナーエンジンバージョン 20200601 以降
ステートメントキュー機能を使用する前に、loose_rds_ic_reduce_hint_enable パラメーターを ON に設定します。デフォルト値は OFF です。変更はクラスターの再起動なしで即時に反映されます。

構文

PolarDB for MySQL 5.6

DML キーワード (SELECT、UPDATE、INSERT、DELETE) の直後に、POLARDB_STATEMENT_CONCURRENT_QUEUE キーワードを配置します。このキーワードは 4 種類の DML ステートメントすべてに適用されます。

値によるキューイング

明示的な整数値または文字列値に基づいて、ステートメントをバケットに割り当てます。同じ値を共有するステートメントは同一のバケットに振り分けられます。

構文：

POLARDB_STATEMENT_CONCURRENT_QUEUE [int | string]

例：

INSERT POLARDB_STATEMENT_CONCURRENT_QUEUE 1 INTO t VALUES(1, 1, 'xpchild');
UPDATE POLARDB_STATEMENT_CONCURRENT_QUEUE 1 t SET c=c+1 WHERE id = 1;
UPDATE POLARDB_STATEMENT_CONCURRENT_QUEUE "xpchild" t SET col1 = col1+1 WHERE id = 1;

WHERE 句のフィールドによるキューイング

WHERE 句内の列の実行時の値に基づいて、ステートメントをバケットに割り当てます。同じフィールド値を持つステートメントは同一のバケットに振り分けられます。

構文：

POLARDB_STATEMENT_CONCURRENT_QUEUE [field]

例：

SELECT POLARDB_STATEMENT_CONCURRENT_QUEUE id t.* FROM t WHERE 3 = id;
UPDATE POLARDB_STATEMENT_CONCURRENT_QUEUE id t SET c=c+1 WHERE id = 1 AND name = 'xpchild';

WHERE 句では、元の列に対する二項演算のみを使用する必要があります。各二項演算の右辺は、数値または文字列のリテラルである必要があります。元の列に対する関数や計算はサポートされていません。

PolarDB for MySQL 5.7 and 8.0

DML キーワード (SELECT、UPDATE、INSERT、DELETE) の直後に配置するヒント構文 /*+ ... */ を使用します。このヒントは 4 種類の DML ステートメントすべてに適用されます。

値によるキューイング

構文：

/*+ CCL_QUEUE_VALUE([INT | STRING]) */

例：

UPDATE /*+ ccl_queue_value(1) */ t SET c=c+1 WHERE id = 1;
UPDATE /*+ ccl_queue_value('xpchild') */ t SET c=c+1 WHERE name = 'xpchild';

WHERE 句のフィールドによるキューイング

構文：

/*+ CCL_QUEUE_FIELD(STRING) */

例：

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

WHERE 句では、元の列に対する二項演算のみを使用する必要があります。各二項演算の右辺は、数値または文字列のリテラルである必要があります。元の列に対する関数や計算はサポートされていません。

キューサイズの設定

キューの容量は 2 つの変数で制御されます。同時実行要件に基づいて設定してください。

変数	説明	有効な値	デフォルト
`ccl_queue_bucket_count`	バケット数	1～64	4
`ccl_queue_bucket_size`	バケットあたりの最大同時実行ステートメント数	1～4096	64

キュー状態の監視

次のストアドプロシージャを使用して、キューの状態を確認し、リセットできます。

現在の状態の確認

CALL dbms_ccl.show_ccl_queue();

出力例：

+------+-------+-------------------+---------+---------+---------+
| ID   | TYPE  | CONCURRENCY_COUNT | MATCHED | RUNNING | WAITING |
+------+-------+-------------------+---------+---------+---------+
|    1 | QUEUE |                64 |       1 |       0 |       0 |
|    2 | QUEUE |                64 |   40744 |      65 |       6 |
|    3 | QUEUE |                64 |       0 |       0 |       0 |
|    4 | QUEUE |                64 |       0 |       0 |       0 |
+------+-------+-------------------+---------+---------+---------+

出力フィールド：

フィールド	説明
`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 |
+------+-------+-------------------+---------+---------+---------+

アプリケーションコードを変更せずにキューヒントを適用

PolarDB for MySQL 5.7 および 8.0 では、ステートメントキューをステートメントアウトラインと組み合わせることで、サーバーレベルで既存の SQL ステートメントにキューヒントを注入できます。アプリケーションコードの変更は不要です。

この統合は、異なる構文を使用する PolarDB for MySQL 5.6 では利用できません。

次の例では、update_non_index.lua スクリプトを使用した Sysbench により、このワークフローを示します。

テストスキーマ：

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='xpchild' WHERE id=0;

手順：

オンラインでキューヒントを注入するステートメントアウトラインを作成します。

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

アウトラインが作成されたことを確認します。

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

対象ステートメントに対して EXPLAIN を実行し、ヒントが有効になっていることを確認します。

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

SHOW WARNINGS の出力に、注入されたヒントが表示されるはずです：

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

テスト開始前に、キューにアクティビティがないことを確認します。
```
CALL dbms_ccl.show_ccl_queue();
```

Sysbench テストを実行します。

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

アウトラインのヒット数とも突き合わせて確認します：

CALL dbms_outln.show_outline();

このテスト実行では、115,795 件のステートメントがアウトラインルールに一致し、そのうち 10,996 件がキューにヒットしました。63 件が同時に実行され、4 件が待機していました。

次のステップ

ステートメントアウトライン：アプリケーションコードを変更せずにオプティマイザヒントを注入