すべてのプロダクト
Search

このプロダクト

    PolarDB:OSS外部テーブルの並列書き込みとクエリ

    ドキュメントセンター

    PolarDB:OSS外部テーブルの並列書き込みとクエリ

    最終更新日:Jun 04, 2024

    PolarDB for PostgreSQL は、Object Storage Service (OSS) 外部テーブルの並列書き込みおよびクエリ用のElastic Parallel Query (ePQ) 機能をサポートしています。

    前提条件

    次のいずれかのバージョンを実行するPolarDB for PostgreSQLクラスターが作成されます。

    • マイナーバージョンが14.9.13.0以降のPostgreSQL 14

    • マイナーバージョンが1.1.35以降のPostgreSQL 11

    説明

    次のいずれかのステートメントを実行して、PolarDB for PostgreSQLクラスターのマイナーバージョンを表示できます。

    • PostgreSQL 14

      select version();

    • PostgreSQL 11

      ショーpolar_version;

    背景情報

    PolarDB for PostgreSQL では、OSS_fdw拡張子を使用してoss外部テーブルを作成できます。 外部テーブルは物理的にOSSに格納され、外部テーブルのメタデータのみがデータベースに格納されます。 これにより、データベースで頻繁に使用されない履歴データまたはコールドデータを外部テーブルとしてOSSにアーカイブして、データベースのストレージコストを削減できます。 PolarDB for PostgreSQL で使用される外部データラッパー (FDW) 標準は、指定された条件下で標準SQLステートメントを実行することで、OSS外部テーブルの読み取りと書き込みを継続できるようにします。

    データアーカイブのためにローカルテーブルからOSS外部テーブルにデータを書き込むと、デフォルトでPolarDB for PostgreSQL がOSS外部テーブルにデータを書き込むプロセスを開始します。 この場合、単一のプロセスを使用して、ネットワーク経由でOSSにデータをアップロードします。 大量のデータをアーカイブする必要があるシナリオでは、ネットワーク帯域幅の使用率が低いため、単一プロセス書き込みモードは非効率的です。

    OSS外部テーブルの履歴アーカイブデータをクエリすると、デフォルトでPolarDB for PostgreSQL がOSS外部テーブルの全データをクエリするプロセスを開始します。 この場合、単一のプロセスを使用して、ネットワーク経由でOSSからデータをダウンロードします。 大量のアーカイブデータを照会する必要があるシナリオでは、ネットワーク帯域幅の使用率が低いため、単一プロセス照会モードは非効率的です。

    上記の問題を解決するために、PolarDB for PostgreSQL は、OSS外部テーブルの並列書き込みとクエリのePQをサポートしています。

    • ePQオプティマイザは、複数のプロセスのOSS外部テーブルへの並列データ書き込みの実行計画を生成できます。 ePQオプティマイザは、読み書きノードで複数のプロセスを開始して、OSS外部テーブルにデータを並列に書き込みます。

    • ePQオプティマイザは、複数のプロセスでOSS外部テーブルの並列データクエリの実行プランを生成できます。 ePQオプティマイザは、読み書きノードで複数のプロセスを開始して、OSS外部テーブルのデータを並列にクエリします。

    このように、単一プロセスのアップロードおよびダウンロードモードは、マルチプロセスのアップロードおよびダウンロードモードに最適化される。 これにより、OSSのネットワーク帯域幅リソースを最大限に活用し、コールドデータのアーカイブとクエリのパフォーマンスを向上させます。

    手順

    データの準備

    1. t_localという名前のローカルテーブルを作成し、特定の量のデータをテーブルに挿入します。 

      CREATE TABLE t_local (id INT、age INT、msg TEXT、detail TEXT);

INSERT INTO t_local
選択
    ランダム () * 1000000、
    ランダム () * 10000、
    md5 (ランダム ():: テキスト) 、
    md5 (ランダム ()::TEXT)
FROM generate_series(1, 2000000);

    2. ローカルテーブルt_localと同じスキーマを持つt_OSSという名前のoss外部テーブルを作成します。 

      拡張の作成oss_fdw;

CREATE SERVER ossserver外国データラッパーoss_fdw
オプション (ホスト 'oss -cn-xxx.aliyuncs.com '、バケット 'mybucket' 、id 'xxx' 、key 'xxx');

CREATE FOREIGN TABLE t_oss (id INT, age INT, msg TEXT, detail TEXT)
サーバーossserverオプション (dir 'archive/');

    OSS外部テーブルへのデータの並列書き込み

    1. 次のコマンドを実行して、ePQを無効にします。 

      polar_enable_pxをオフに設定します。

    2. 次のコマンドを実行して、ローカルテーブルt_localからOSS外部テーブルt_ossにデータをインポートします。 次のサンプルコードは、実行計画の例を示し、消費時間を示しています。 

      EXPLAIN (コストオフ) INSERT INTO t_oss SELECT * FROM t_local;
        クエリ計画
---------------------------
 t_ossに挿入
   -> Seqスキャンでt_local
(2行)

INSERT INTO t_oss SELECT * FROM t_local;
INSERT 0 2000000
時間: 8861.708 ms (00:08.862)

      前述の実行計画に従って、エグゼキュータはローカルテーブルをスキャンすると同時にOSS外部テーブルにデータを書き込むプロセスを開始します。 消費される合計時間は8861.708ミリ秒です。

    3. 次のコマンドを実行してePQと並列INSERT機能を有効にし、ローカルテーブルの並列クエリの並列度とOSS外部テーブルへの並列書き込みの並列度を16に設定します。 

      SET polar_enable_pxをオンにします。polar_px_enable_insert_selectをオンに設定します。polar_px_dop_per_nodeを16に設定します。SET polar_px_insert_dop_numに16;

    4. 次のコマンドを再度実行します。 次のサンプルコードは、実行計画の例を示し、消費時間を示しています。 

      EXPLAIN (コストオフ) INSERT INTO t_oss SELECT * FROM t_local;
                    クエリ計画
---------------------------------------------------
 t_ossに挿入
   -> 結果
         -> PXハッシュ32:16 (slice1; セグメント: 32)
               -> t_localの部分Seqスキャン
 Optimizer: PolarDB PX Optimizer
(5行)

INSERT INTO t_oss SELECT * FROM t_local;
INSERT 0 2000000
時間: 1321.212 ms (00:01.321)

      上記の実行計画では、segments: 32は、エグゼキュータがslice1を実行するために32のプロセスを開始することを示します。 Partial Seq Scanは、ローカルテーブルが並列にスキャンされることを示します。 PX Hash 32:16は、Motion演算子を使用して、OSS外部テーブルへの並列書き込みのために16のプロセスにデータを分散することを示します。 消費される合計時間は1321.212ミリ秒であり、シングルプロセスモードでのデータ書き込みと比較して大幅に短縮されます。

    OSS外部テーブルのデータを並行してクエリ

    1. 次のコマンドを実行して、ePQを無効にします。 

      polar_enable_pxをオフに設定します。

    2. 次のコマンドを実行して、OSS外部テーブルt_ossの完全なデータを照会します。 次のサンプルコードは、実行プランの例を示し、クエリ結果を示しています。 

      EXPLAIN SELECT COUNT(*) FROM t_oss;
                                  クエリ計画
-------------------------------------------------------------------------------
 集計 (コスト=1366687.96 .. 1366687.97行=1幅=8)
   -> 外部スキャンt_oss (コスト=0.00 .. 1334280.40行=12963024幅=0)
         OSS上のディレクトリ: archive /
         OSSファイルの数: 17
         OSSファイルの合計サイズ: 297 MB
(5行)

選択カウント (*) からt_oss;
  カウント
---------
 4000000
(1行)

時間: 36230.325 ms (00:36.230)

      前述の実行計画に従って、エグゼキュータはOSS外部テーブル内の17個のOSSオブジェクトをスキャンするプロセスを開始します。 OSSオブジェクトの合計サイズは297 MBです。 消費される合計時間は36230.325ミリ秒です。

    3. 次のコマンドを実行してePQを有効にし、並列クエリの並列度を8に設定します。 

      SET polar_enable_pxをオンにします。SET polar_px_dop_per_nodeに8;

    4. 次のコマンドを再度実行します。 次のサンプルコードは、実行プランの例を示し、クエリ結果を示しています。 

      EXPLAIN SELECT COUNT(*) FROM t_oss;
                                      クエリ計画
---------------------------------------------------------------------------------------
 集計 (コスト=0.00 .. 431.00行=1幅=8)
   -> PXコーディネーター16:1 (slice1; セグメント: 16) (コスト=0.00 .. 431.00行=1 width=1)
         -> t_ossの部分外部スキャン (コスト=0.00 .. 431.00行=1幅=1)
               OSS上のディレクトリ: archive /
               OSSファイルの数: 17
               OSSファイルの合計サイズ: 297 MB
 Optimizer: PolarDB PX Optimizer
(7行)

選択カウント (*) からt_oss;
  カウント
---------
 4000000
(1行)

時間: 18100.894 ms (00:18.101)

      上記の実行計画では、segments: 16は、エグゼキュータがslice1を実行するために16のプロセスを開始することを示します。 Partial Foreign Scanは、OSS外部テーブルが並行してスキャンされることを示します。 OSSオブジェクトは、並列クエリのために16のプロセスに分散されます。 PXコーディネーター16:1は、すべてのプロセスのクエリ結果が、データクエリを開始するプロセスに集約され、Motion演算子を使用して返されることを示します。 合計時間は18100.894ミリ秒で、シングルプロセスモードのデータクエリと比較して大幅に短縮されます。