PolarDB:インメモリ列指向インデックスに関するよくある質問

PolarDB for MySQL のインメモリ列指向インデックス機能の使用方法

列ストアインデックスのステータスの確認方法

クエリが列ストアインデックスを使用したことの確認方法

*************************** 1. row ***************************
IMCI Execution Plan (max_dop = 8, max_query_mem = 3435134976):
Project | Exprs: temp_table3.lineitem.L_ORDERKEY, temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT), temp_table3.orders.O_ORDERDATE, temp_table3.orders.O_SHIPPRIORITY
  Sort | Exprs: temp_table3.SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT) DESC,temp_table3.orders.O_ORDERDATE ASC
    HashGroupby | OutputTable(3): temp_table3 | Grouping: lineitem.L_ORDERKEY orders.O_ORDERDATE orders.O_SHIPPRIORITY | Output Grouping: lineitem.L_ORDERKEY, orders.O_ORDERDATE, orders.O_SHIPPRIORITY | Aggrs: SUM(lineitem.L_EXTENDEDPRICE * 1.00 - lineitem.L_DISCOUNT)
      HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_ORDERKEY = lineitem.L_ORDERKEY
        HashJoin | HashMode: DYNAMIC | JoinMode: INNER | JoinPred: orders.O_CUSTKEY = customer.C_CUSTKEY
          CTableScan | InputTable(0): orders | Pred: (orders.O_ORDERDATE < 03/24/1995 00:00:00.000000)
          CTableScan | InputTable(1): customer | Pred: (customer.C_MKTSEGMENT = "BUILDING")
        CTableScan | InputTable(2): lineitem | Pred: (lineitem.L_SHIPDATE > 03/24/1995 00:00:00.000000)
1 row in set (0.04 sec)

トラブルシューティング：クエリが列ストアインデックスを使用しない理由

列ストアノードを追加した後、SQL 文がクエリするテーブルに列ストアインデックスを追加する必要があります。また、SQL 文のクエリコストが特定のしきい値を超える必要もあります。これらの条件が満たされて初めて、SQL 文は列ストアインデックスを使用します。さらに、クエリを高速化するために IMCI を使用するには、SQL 文が列ストアノードに転送される必要があります。SQL 文がクエリに列ストアインデックスを使用できない場合は、次の手順に従って問題をトラブルシューティングしてください。

1. SQL 文が列ストアノードに転送されたことを確認します。

   SQL Explorer 機能を使用して、SQL 文が列ストアノードに転送されたかどうかを確認します。

   クラスターエンドポイントを使用し、行ストアノードと列ストアノード間の自動リクエスト分散を有効にすると、データベースプロキシは、推定クエリコストが imci_ap_threshold の値を超える SQL 文を自動的に列ストアノードに転送します。また、SQL 文の SELECT キーワードの前に /*FORCE_IMCI_NODES*/ を追加して、文を強制的に列ストアノードに転送することもできます。以下に例を示します。

   /*FORCE_IMCI_NODES*/EXPLAIN SELECT COUNT(*) FROM t1 WHERE t1.a > 1;

   詳細については、「行ストアと列ストア間の自動リクエスト分散の設定」をご参照ください。

   説明

   列ストアノードに直接接続する新しいエンドポイントを作成します。これにより、SQL 文が常に列ストアノードに転送されて実行されるようになります。

2. クエリコストがしきい値より高いかどうかの確認

   列ストアノードでは、オプティマイザーが SQL 文のコストを推定します。推定コストが設定されたしきい値 cost_threshold_for_imci よりも高い場合、クエリは列ストアインデックスを使用します。それ以外の場合は、元の行ストアインデックスを使用します。

   SQL 文が列ストアノードに転送されたことを確認した後、EXPLAIN からの実行計画にまだ列ストアインデックスの使用が表示されない場合は、推定実行コストを事前設定されたしきい値と比較します。これにより、推定コストが低すぎて列ストアインデックスを使用できなかったかどうかを判断できます。Last_query_cost 変数をクエリして、前の SQL 文の推定実行コストを取得できます。

   EXPLAIN SELECT * FROM t1;
   SHOW STATUS LIKE 'Last_query_cost';

   SQL 文の推定実行コストが事前設定された cost_threshold_for_imci よりも低い場合は、cost_threshold_for_imci の調整を検討してください。たとえば、ヒントを使用して単一の SQL 文の事前設定されたしきい値を調整します。

   /*FORCE_IMCI_NODES*/EXPLAIN SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

3. 必要なすべての列が列ストアインデックスで完全にカバーされているかどうかの確認

   組み込みのストアドプロシージャ dbms_imci.check_columnar_index() を使用して、SQL 文内のテーブルに対して列ストアインデックスが作成されているかどうかを確認します。以下に例を示します。

   CALL dbms_imci.check_columnar_index('SELECT COUNT(*) FROM t1 WHERE t1.a > 1');

   SQL 文が列ストアインデックスで完全にカバーされていない場合、このストアドプロシージャを呼び出すと、カバーされていないテーブルと列が返されます。完全にカバーされている場合、ストアドプロシージャは空の結果セットを返します。

4. サポートされていない SQL 機能を確認します。

   制限事項のリストを確認して、特定の SQL 機能が IMCI でサポートされているかどうかを確認します。

これらのチェックがすべて問題なければ、通常、SQL 文はクエリに列ストアインデックスを使用するはずです。

列ストアノードは行ストアインデックスを使用できるか

はい。列ストア読み取り専用ノードは、IMCI の機能が追加された標準の読み取り専用ノードと見なすことができます。したがって、列ストアノードは、標準の読み取り専用ノードと同様に行ストアインデックスを使用でき、さらに列ストアインデックスも使用できます。オプティマイザーは、cost_threshold_for_imci のしきい値に基づいてどちらのインデックスを使用するかを選択します。

SELECT /*+ SET_VAR(cost_threshold_for_imci=0) */ COUNT(*) FROM t1 WHERE t1.a > 1;

SELECT /*+ SET_VAR(USE_IMCI_ENGINE=OFF) */ COUNT(*) FROM t1 WHERE t1.a > 1;

SQL 文に適した列ストアインデックスの追加方法

SQL 文がクエリに IMCI を使用するためには、その文で使用されるすべての列が列ストアインデックスでカバーされている必要があります。SQL 文に含まれる列が列ストアインデックスでカバーされていない場合は、CREATE TABLE 文または ALTER TABLE 文を使用して追加できます。PolarDB for MySQL は、この操作を支援するための一連の組み込みストアドプロシージャを提供します。

dbms_imci.columnar_advise('<query_string>');

列ストアノードの高い CPU またはメモリ使用量のトラブルシューティング

• IMCI のデフォルト設定では、単一の SQL 文が同時に実行され、利用可能なすべての CPU を使用できます。複数の SQL 文が同時に実行されると、データベース内の内部スケジューラが SQL 文をスケジュールし、各 SQL 文の CPU とメモリの制限を動的に削減します。その結果、列ストアノードの平均 CPU およびメモリ使用量は、他のノードと比較して比較的高くなります。imci_max_dop パラメーターを調整して、単一の SQL 文の最大並列度、つまり単一の SQL 文が使用できる CPU コアの最大数を制御できます。

• CPU 使用率の監視しきい値は 70%、メモリ使用量の監視しきい値は 90% に設定することを推奨します。

• PolarDB for MySQL は、同一クラスター内のノードに対して異なる仕様をサポートしています。列ストアノードの構成を個別にスペックアップまたはスペックダウンできます。列ストアノードには、少なくとも 8 コアと 16 GB のメモリを搭載することを推奨します。

PolarDB for MySQL 5.6 および 5.7 でのインメモリ列指向インデックス機能のサポート

PolarDB for MySQL バージョン 5.6 および 5.7 は IMCI 機能をサポートしていません。PolarDB for MySQL 8.0 は IMCI 機能をサポートしています。

IMCI の制限事項、全文検索インデックスのサポート、MySQL との互換性

IMCI の使用は MySQL と完全に互換性があります。ただし、特定の時空間式、全文検索インデックス、一部の形式の相関サブクエリなど、一部の一般的でないクエリ機能はまだ完全にはサポートされていません。これらの機能を使用する SQL 文は列ストアインデックスを使用できず、デフォルトで行ストアインデックスを使用してクエリを実行します。IMCI の詳細な制限事項については、「制限事項」をご参照ください。

INSERT INTO SELECT 文および CREATE TABLE AS SELECT 文での列ストアインデックスの使用

IMCI は読み取り専用ノードでのクエリにのみ使用できます。INSERT 文および CREATE 文は、プライマリ (RW) ノードでのみ実行できます。INSERT INTO SELECT または CREATE TABLE AS SELECT クエリで列ストアインデックスを使用するには、IMCI の ETL (抽出、変換、ロード) 機能を使用する必要があります。詳細については、「列ストアインデックスを使用した ETL の高速化」をご参照ください。

IMCI 機能の料金と標準読み取り専用ノードでのサポート

IMCI 機能自体は無料です。ただし、IMCI を使用するには、別の読み取り専用ノードを追加し、その上で IMCI を有効にする必要があります。この新しい読み取り専用ノードと、列ストアインデックスが消費する追加ストレージに対して課金されます。

標準の読み取り専用ノードは IMCI をサポートしていません。

列ストアインデックスが使用する追加ストレージ容量

列ストアデータは列ごとに整理されているため、より高い圧縮率を実現できます。行ストアと比較して、圧縮率は通常 3 倍から 10 倍向上します。これにより、ストレージの追加増加量は約 10% から 30% になります。

列ストアインデックスが使用するストレージ容量の確認方法

• PolarDB for MySQL 8.0.1.1.32 以前のクラスターバージョンでは、information_schema の imci_columns システムテーブルを使用して、列ストアインデックスを持つテーブルのストレージ容量と列の圧縮率を表示できます。たとえば、test データベース内の列ストアインデックスを持つ test テーブルのストレージ容量と圧縮率を表示するには、次の SQL 文を実行します。

  SELECT
    SCHEMA_NAME, TABLE_NAME,
    SUM(EXTENT_SIZE * TOTAL_EXTENT_COUNT) AS TOTAL_SIZE,
    SUM(EXTENT_SIZE * USED_EXTENT_COUNT) AS USED_SIZE,
    SUM(EXTENT_SIZE * FREE_EXTENT_COUNT) AS FREE_SIZE,
    SUM(RAW_DATA_SIZE) / SUM(FILE_SIZE) AS COMPRESS
  FROM
    information_schema.imci_columns
  WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

• PolarDB for MySQL 8.0.1.1.33 以降のクラスターバージョンでは、information_schema の imci_data_files システムテーブルを使用してストレージ容量を表示し、imci_columns システムテーブルを使用して列の圧縮率を表示できます。たとえば、test データベース内の列ストアインデックスを持つ test テーブルのストレージ容量と列の圧縮率を表示するには、次の SQL 文を実行します。

  • 列ストアインデックスを持つ test テーブルが使用するストレージ容量を表示するには、次の SQL 文を実行します。

    SELECT
        SCHEMA_NAME, TABLE_NAME,
        SUM(EXTENT_SIZE * TOTAL_EXTENT_COUNT) AS TOTAL_SIZE,
        SUM(EXTENT_SIZE * USED_EXTENT_COUNT) AS USED_SIZE,
        SUM(EXTENT_SIZE * FREE_EXTENT_COUNT) AS FREE_SIZE
    FROM
        INFORMATION_SCHEMA.IMCI_DATA_FILES
    WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

  • 列の圧縮率を表示するには、次の SQL 文を実行します。

    SELECT
        SCHEMA_NAME, TABLE_NAME,
         SUM(RAW_DATA_SIZE) / SUM(CMP_DATA_SIZE) AS COMPRESS_RATIO
    FROM
        INFORMATION_SCHEMA.IMCI_COLUMNS
    WHERE SCHEMA_NAME = 'test' AND TABLE_NAME = 'test';

次の表に、上記の SQL 文のパラメーターを説明します。

列ストアインデックス追加後に INSTANT DDL が機能しない理由

• 8.0.1.1.42 および 8.0.2.2.23 より前のバージョンの PolarDB for MySQL では、テーブルレベルの列ストアインデックスを持つテーブルに列を追加しても、インスタント列追加ロジックは使用されません。これは、操作に列ストアインデックス構造の変更とインデックスデータの再構築が含まれるためです。

• PolarDB for MySQL 8.0.1.1.42 以降および 8.0.2.2.23 以降では、テーブルレベルの列ストアインデックスを持つテーブルで INSTANT DDL がサポートされています。この機能は、古い再構築モードと互換性がありません。imci_enable_add_column_instant_ddl パラメーターを OFF に設定する必要があります。また、テーブルにはプライマリキーが必要です。

Autoindex によって追加された列ストアインデックスの表示または削除方法

SELECT * FROM  information_schema.imci_autoindex_executed;

Autoindex によって自動的に追加された列ストアインデックスを削除する方法は、手動で追加された列ストアインデックスを削除する方法と同じです。

ALTER TABLE t1 comment 'columnar=0';

列ストアインデックス追加後に ALTER TABLE での列の追加または削除に時間がかかる理由

列を追加または削除すると、通常、テーブルデータが再構築されます。元のテーブルに列ストアインデックスがある場合、列ストアインデックスデータも再構築する必要があります。列ストアインデックスデータを再構築するプロセスでは、Redo ログへの書き込みが必要です。列ストアインデックスは多くの列をカバーすることが多いため、再構築中に生成される Redo ログデータの量は元のテーブルデータのサイズに比例します。これにより、列ストアインデックスのないテーブルを再構築する場合と比較して I/O ボリュームが増加し、操作に時間がかかります。

列ストアインデックスの追加による書き込みパフォーマンスへの影響

列ストアインデックスの追加が書き込みパフォーマンスに与える影響は、一般的に 5% 以内です。Sysbench oltp_insert workload テストデータセットを使用したテストでは、列ストアインデックスを追加した後の書き込みパフォーマンスの低下は約 3% でした。

IMCI がサポートするトランザクション分離レベル

IMCI は、READ_COMMITTED および REPEATABLE_READ のトランザクション分離レベルをサポートしています。

IMCI によるあいまい検索シナリオの高速化

はい。IMCI は、あいまい検索シナリオの高速化において非常に優れたパフォーマンスを発揮します。LIKE PRUNER、NGRAM LIKE、SMID LIKE などの機能をサポートしています。列ストア全文検索インデックス機能も、あいまい検索シナリオの高速化を提供します。