OSS外部テーブルの並列書き込みとクエリ - PolarDB - Alibaba Cloud ドキュメントセンター

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

背景情報

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

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

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

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

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

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

手順

データの準備

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

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

INSERT INTO t_local
SELECT
    random() * 1000000,
    random() * 10000,
    md5(random()::TEXT),
    md5(random()::TEXT)
FROM generate_series(1, 2000000);

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

CREATE EXTENSION oss_fdw;

CREATE SERVER ossserver FOREIGN DATA WRAPPER oss_fdw
OPTIONS (host 'oss-cn-xxx.aliyuncs.com', bucket 'mybucket', id 'xxx', key 'xxx');

CREATE FOREIGN TABLE t_oss (id INT, age INT, msg TEXT, detail TEXT)
SERVER ossserver OPTIONS (dir 'archive/');

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

次のコマンドを実行して、ePQを無効にします。
```
SET polar_enable_px TO OFF;
```
次のコマンドを実行して、ローカルテーブルt_localからOSS外部テーブルt_ossにデータをインポートします。次のサンプルコードは、実行計画の例を示し、消費時間を示しています。
```
EXPLAIN (COSTS OFF) INSERT INTO t_oss SELECT * FROM t_local;
        QUERY PLAN
---------------------------
 Insert on t_oss
   ->  Seq Scan on t_local
(2 rows)

INSERT INTO t_oss SELECT * FROM t_local;
INSERT 0 2000000
Time: 8861.708 ms (00:08.862)
```
前述の実行計画に従って、エグゼキュータはローカルテーブルをスキャンすると同時にOSS外部テーブルにデータを書き込むプロセスを開始します。消費される合計時間は8861.708ミリ秒です。
次のコマンドを実行してePQと並列INSERT機能を有効にし、ローカルテーブルの並列クエリの並列度とOSS外部テーブルへの並列書き込みの並列度を16に設定します。
```
SET polar_enable_px TO ON;
SET polar_px_enable_insert_select TO ON;
SET polar_px_dop_per_node TO 16;
SET polar_px_insert_dop_num TO 16;
```
次のコマンドを再度実行します。次のサンプルコードは、実行計画の例を示し、消費時間を示しています。
```
EXPLAIN (COSTS OFF) INSERT INTO t_oss SELECT * FROM t_local;
                    QUERY PLAN
---------------------------------------------------
 Insert on t_oss
   ->  Result
         ->  PX Hash 32:16  (slice1; segments: 32)
               ->  Partial Seq Scan on t_local
 Optimizer: PolarDB PX Optimizer
(5 rows)

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

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

次のコマンドを実行して、ePQを無効にします。
```
SET polar_enable_px TO OFF;e_pxをオフに設定します。
```

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

EXPLAIN SELECT COUNT(*) FROM t_oss;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Aggregate  (cost=1366687.96..1366687.97 rows=1 width=8)
   ->  Foreign Scan on t_oss  (cost=0.00..1334280.40 rows=12963024 width=0)
         Directory on OSS: archive/
         Number Of OSS file: 17
         Total size of OSS file: 297 MB
(5 rows)

SELECT COUNT(*) FROM t_oss;
  count
---------
 4000000
(1 row)

Time: 36230.325 ms (00:36.230)

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

次のコマンドを実行してePQを有効にし、並列クエリの並列度を8に設定します。
```
SET polar_enable_px TO ON;
SET polar_px_dop_per_node TO 8;
```
次のコマンドを再度実行します。次のサンプルコードは、実行プランの例を示し、クエリ結果を示しています。
```
EXPLAIN SELECT COUNT(*) FROM t_oss;
                                      QUERY PLAN
---------------------------------------------------------------------------------------
 Aggregate  (cost=0.00..431.00 rows=1 width=8)
   ->  PX Coordinator 16:1  (slice1; segments: 16)  (cost=0.00..431.00 rows=1 width=1)
         ->  Partial Foreign Scan on t_oss  (cost=0.00..431.00 rows=1 width=1)
               Directory on OSS: archive/
               Number Of OSS file: 17
               Total size of OSS file: 297 MB
 Optimizer: PolarDB PX Optimizer
(7 rows)

SELECT COUNT(*) FROM t_oss;
  count
---------
 4000000
(1 row)

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