Azure Database for PostgreSQL - フレキシブルサーバーでのデータの一括アップロードに関するベストプラクティス

[アーティクル]
08/15/2024

適用対象: Azure Database for PostgreSQL - フレキシブルサーバー

この記事では、Azure Database for PostgreSQL - フレキシブルサーバーでデータを一括で読み込むさまざまな方法と、空のデータベースでの初期データ読み込みと増分データ読み込みの両方のベストプラクティスについて説明します。

メソッドの読み込み

次のデータ読み込み方法は、最も時間のかかるものから最も時間のかからないものの順に並べ替えられます。

単一レコード INSERT コマンドを実行します。
コミットごとに 100 から 1000 行にバッチ処理します。トランザクションブロックを使用して、コミットごとに複数のレコードをラップできます。
複数行の値を使用して INSERT を実行します。
COPY コマンドを実行します。

データベースにデータを読み込むには、COPY コマンドを使用することをお勧めします。 COPY コマンドを使用できない場合は、次に最適な方法はバッチ INSERT を使用することです。 COPY コマンドを使用したマルチスレッド処理は、一括データ読み込みの最適な方法です。

初期データ読み込みのベストプラクティス

インデックスを削除する

初期データ読み込みを行う前に、テーブル内のすべてのインデックスを削除することをお勧めします。データの読み込み後にインデックスを作成する方が常に効率的です。

制約を削除する

主な削除制約を次に示します。

一意のキー制約

強力なパフォーマンスを実現するには、最初のデータ読み込み前に一意のキー制約を削除し、データ読み込みが完了した後に再作成することをお勧めします。ただし、一意のキー制約を削除すると、重複するデータに対する保護が取り消されます。
外部キー制約

最初のデータ読み込み前に外部キー制約を削除し、データ読み込みが完了した後に再作成することをお勧めします。

session_replication_role パラメーターを replica に変更すると、すべての外部キーチェックも無効になります。ただし、この変更を行うと、適切に使用されていない場合、データが不整合な状態のままになる可能性があることに注意してください。

ログ記録されないテーブル

最初のデータ読み込みで使用する前に、ログに記録されていないテーブルを使用することの長所と短所を考慮してください。

ログに記録されていないテーブルを使用すると、データの読み込みが高速になります。ログに記録されないテーブルに書き込まれたデータは、先書きログには書き込まれません。

ログに記録されないテーブルを使用する場合の欠点は次のとおりです。

これらはクラッシュ時に安全ではありません。ログ記録されないテーブルはクラッシュ後に自動的に切り捨てられるか、不完全にシャットダウンされます。
ログ記録されないテーブルのデータはスタンバイサーバーにレプリケートできません。

ログに記録されないテーブルを作成したり、既存のテーブルをログに記録されていないテーブルに変更したりするには、次のオプションを使用します。

次の構文を使用して、新しいログ記録されないテーブルを作成します。
```
CREATE UNLOGGED TABLE <tablename>;
```
次の構文を使用して、既存のログ記録されるテーブルをログ記録されないテーブルに変換します。
```
ALTER TABLE <tablename> SET UNLOGGED;
```

サーバーパラメーターのチューニング

autovacuum: 初期データ読み込み中に、autovacuum をオフにすることをお勧めします。初期読み込みが完了したら、データベース内のすべてのテーブルに対して手動の VACUUM ANALYZE を実行し、autovacuum を有効にすることをお勧めします。

Note

十分なメモリとディスク領域がある場合にのみ、こちらの推奨事項に従ってください。

maintenance_work_mem: Azure Database for PostgreSQL フレキシブルサーバーインスタンスでは、最大 2 ギガバイト (GB) に設定できます。 maintenance_work_mem は、自動バキューム、インデックス、外部キーの作成を高速化するのに役立ちます。
checkpoint_timeout: Azure Database for PostgreSQL フレキシブルサーバーインスタンスでは、既定値の 5 分から最大 24 時間まで checkpoint_timeout 値を増やすことができます。 Azure Database for PostgreSQL フレキシブルサーバーインスタンスにデータを最初に読み込む前に、値を 1 時間に増やすことをお勧めします。
checkpoint_completion_target: 0.9 の値をお勧めします。
max_wal_size: Azure Database for PostgreSQL フレキシブルサーバーインスタンスで許容される最大値 (初期データ読み込み中は 64 GB) に設定できます。
wal_compression: 有効にできます。このパラメーターを有効にすると、先書きログ (WAL) ログ記録中の圧縮と WAL 再生中の展開に費やされる CPU コストが増える可能性があります。

Azure Database for PostgreSQL フレキシブルサーバーの推奨事項

Azure Database for PostgreSQL フレキシブルサーバーインスタンスで初期データ読み込みを開始する前に、次のことをお勧めします。

サーバーの高可用性を無効にします。プライマリで初期読み込みが完了したら、これを有効にすることができます。
初期データ読み込みが完了した後に読み取りレプリカを作成します。
最初のデータ読み込み中に、ログ記録を最小限にするか、まとめて無効にします (たとえば、pgaudit、pg_stat_statements、クエリストアを無効にする)。

インデックスを再作成して制約を追加する

最初の読み込み前にインデックスと制約を削除したと仮定すると、インデックスの作成と制約の追加には、(前述のように) maintenance_work_mem の高い値を使用することをお勧めします。さらに、PostgreSQL バージョン 11 以降では、初期データ読み込み後に並列インデックスの作成を高速化するために、次のパラメーターを変更できます。

max_parallel_workers: システムで並列クエリをサポートできるワーカーの最大数を設定します。
max_parallel_maintenance_workers: CREATE INDEX に使用できるワーカープロセスの最大数を制御します。

セッションレベルで推奨設定を行うことで、インデックスを作成することもできます。これを行う方法の例を次に示します。

SET maintenance_work_mem = '2GB';
SET max_parallel_workers = 16;
SET max_parallel_maintenance_workers = 8;
CREATE INDEX test_index ON test_table (test_column);

増分データ読み込みのベストプラクティス

テーブルをパーティション分割する

常に大きなテーブルをパーティション分割することをお勧めします。パーティション分割の利点 (特に増分読み込み時) には、次のようなものがあります。

新しい差分に基づいて新しいパーティションを作成すると、新しいデータをテーブルに追加するのが効率的になります。
テーブルの保守が容易になります。データの増分読み込み中にパーティションを削除すると、大きなテーブルで時間のかかる削除を回避できます。
自動バキュームは、増分読み込み中に変更または追加されたパーティションでのみトリガーされるため、テーブルの統計の保守が容易になります。

外部キー制約のインデックスを作成する

子テーブルの外部キーにインデックスを作成すると、次のシナリオで役立ちます。

親テーブルのデータの更新または削除。親テーブルでデータが更新または削除されると、子テーブルで検索が実行されます。検索を高速化するには、子テーブルの外部キーのインデックスを作成できます。
クエリ。キー列の親および子テーブルの結合が表示されます。

未使用のインデックスを特定する

データベース内の未使用のインデックスを特定し、削除します。インデックスは、データ読み込み時のオーバーヘッドになります。テーブルのインデックスが少ないほど、データインジェスト時のパフォーマンスが向上します。

未使用のインデックスは、クエリストアとインデックス使用クエリの 2 つの方法で特定できます。

クエリストア

クエリストア機能は、データベース上のクエリ使用パターンに基づいて削除できるインデックスを特定するのに役立ちます。ステップバイステップガイダンスについては、「クエリデータストア」をご覧ください。

サーバーでクエリストアを有効にすると、次のクエリを使用して、azure_sys データベースに接続することで削除できるインデックスを特定できます。

SELECT * FROM IntelligentPerformance.DropIndexRecommendations;

インデックスの使用量

次のクエリを使用して、未使用のインデックスを特定することもできます。

SELECT 
    t.schemaname, 
    t.tablename, 
    c.reltuples::bigint                            AS num_rows, 
    pg_size_pretty(pg_relation_size(c.oid))        AS table_size, 
    psai.indexrelname                              AS index_name, 
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size, 
    CASE WHEN i.indisunique THEN 'Y' ELSE 'N' END AS "unique", 
    psai.idx_scan                                  AS number_of_scans, 
    psai.idx_tup_read                              AS tuples_read, 
    psai.idx_tup_fetch                             AS tuples_fetched 
FROM 
    pg_tables t 
    LEFT JOIN pg_class c ON t.tablename = c.relname 
    LEFT JOIN pg_index i ON c.oid = i.indrelid 
    LEFT JOIN pg_stat_all_indexes psai ON i.indexrelid = psai.indexrelid 
WHERE 
    t.schemaname NOT IN ('pg_catalog', 'information_schema') 
ORDER BY 1, 2;

number_of_scans、tuples_read、tuples_fetched の各列では、値が 0 のインデックス usage.number_of_scans 列を、使用されていないインデックスとして指しています。

サーバーパラメーターのチューニング

Note

十分なメモリとディスク領域がある場合にのみ、次のパラメーターの推奨事項に従ってください。

maintenance_work_mem: Azure Database for PostgreSQL フレキシブルサーバーインスタンスでは、このパラメーターを最大 2 GB に設定できます。 maintenance_work_mem は、インデックスの作成と外部キーの追加を高速化するのに役立ちます。
checkpoint_timeout: Azure Database for PostgreSQL フレキシブルサーバーインスタンスでは、既定値の 5 分から、10 分または 15 分に checkpoint_timeout 値を増やすことができます。 checkpoint_timeout を 15 分などの大きな値に増やすと I/O 負荷が減りますが、クラッシュが発生した場合の回復に時間がかかる欠点があります。変更を行う前に、慎重に検討することをお勧めします。
checkpoint_completion_target: 0.9 の値をお勧めします。
max_wal_size: この値は、SKU、ストレージ、ワークロードによって異なります。 max_wal_size を適切な値にする 1 つの方法を次の例に示します。

ピーク営業時間中に、次を行ってある値に達します。

a. 次のクエリを実行して、現在の WAL ログシーケンス番号 (LSN) を取得します。
```
SELECT pg_current_wal_lsn (); 
```
b. checkpoint_timeout の秒数だけ待ちます。次のクエリを実行して、現在の WAL LSN を取得します。
```
SELECT pg_current_wal_lsn (); 
```
c. 2 つの結果を使用して、GB の違いを確認します。
```
SELECT round (pg_wal_lsn_diff('LSN value when run second time','LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB; 
```
wal_compression: 有効にできます。このパラメーターを有効にすると、WAL ログ記録中の圧縮と WAL 再生中の展開に費やされる CPU コストが増える可能性があります。

次の方法で共有

Azure Database for PostgreSQL - フレキシブルサーバーでのデータの一括アップロードに関するベストプラクティス

メソッドの読み込み

初期データ読み込みのベストプラクティス

インデックスを削除する

制約を削除する

ログ記録されないテーブル

サーバーパラメーターのチューニング

Azure Database for PostgreSQL フレキシブルサーバーの推奨事項

インデックスを再作成して制約を追加する

増分データ読み込みのベストプラクティス

テーブルをパーティション分割する

最新のテーブル統計を維持する

外部キー制約のインデックスを作成する

未使用のインデックスを特定する

サーバーパラメーターのチューニング

次のステップ

フィードバック

その他のリソース

次の方法で共有

Azure Database for PostgreSQL - フレキシブル サーバーでのデータの一括アップロードに関するベスト プラクティス

メソッドの読み込み

初期データ読み込みのベスト プラクティス

インデックスを削除する

制約を削除する

ログ記録されないテーブル

サーバー パラメーターのチューニング

Azure Database for PostgreSQL フレキシブル サーバーの推奨事項

インデックスを再作成して制約を追加する

増分データ読み込みのベスト プラクティス

テーブルをパーティション分割する

最新のテーブル統計を維持する

外部キー制約のインデックスを作成する

未使用のインデックスを特定する

サーバー パラメーターのチューニング

次のステップ

フィードバック

その他のリソース

Azure Database for PostgreSQL - フレキシブルサーバーでのデータの一括アップロードに関するベストプラクティス

初期データ読み込みのベストプラクティス

サーバーパラメーターのチューニング

Azure Database for PostgreSQL フレキシブルサーバーの推奨事項

増分データ読み込みのベストプラクティス

サーバーパラメーターのチューニング