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

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

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

メソッドの読み込み

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

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

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

一括データをアップロードする手順

Azure Database for PostgreSQL フレキシブル サーバーにデータを一括アップロードする手順は次のとおりです。

手順 1: データの準備

データがクリーンで、データベース用に適切に書式設定されていることを確認します。

手順 2: 読み込み方法の選択

データのサイズと複雑さに基づいて、適切な読み込み方法を選択します。

手順 3: 読み込みメソッドの実行

選択した読み込み方法を実行して、データをデータベースにアップロードします。

手順 4: データを確認する

アップロード後、データがデータベースに正しく読み込まれたことを確認します。

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

ここでは、初期データ読み込みのベスト プラクティスを示します。

インデックスを削除する

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

制約を削除する

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

• 一意のキー制約

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

• 外部キー制約

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

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

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

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

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

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

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

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

• 次の構文を使用して、新しいログ記録されないテーブルを作成します。

  CREATE UNLOGGED TABLE <tablename>;
  

• 次の構文を使用して、既存のログ記録されるテーブルをログ記録されないテーブルに変換します。

  ALTER TABLE <tablename> SET UNLOGGED;
  

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

• auto vacuum': It's best to turn off 初期データ読み込み中は自動バキュームをオフにすることをお勧めします。 初期読み込みが完了した後、データベース内のすべてのテーブルに対して手動 VACUUM ANALYZE を実行してから、auto vacuum をオンにすることをお勧めします。

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 フレキシブル サーバー インスタンスで初期データ読み込みを開始する前に、次のことをお勧めします。

• サーバーの高可用性を無効にします。 プライマリで初期読み込みが完了したら、これを有効にすることができます。
• 初期データ読み込みが完了した後に読み取りレプリカを作成します。
• 初期データ読み込み中には、ログを最小限にするか、まとめて無効にします (たとえば、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);

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

増分データ読み込みのベスト プラクティスについては、以下で説明します。

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

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

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

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

テーブル統計の監視と保守は、データベースのクエリ パフォーマンスにとって重要です。 これには、増分読み込みがあるシナリオも含まれます。 PostgreSQL では、自動バキューム デーモン プロセスを使用して、使用不能タプルをクリーンアップし、テーブルを分析して統計を更新し続けます。 詳細については、自動バキュームの監視とチューニングに関する記事を参照してください。

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

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

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

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

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

未使用のインデックスは、クエリ ストアとインデックス使用クエリの 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 running the second time','LSN value when run the first time')/1024/1024/1024,2) WAL_CHANGE_GB;

• wal_compression: 有効にすることができます。 このパラメーターを有効にすると、WAL ログ中の圧縮と WAL 再生中の展開に追加の CPU コストが発生する可能性があります。

関連するコンテンツ

• Azure Database for PostgreSQL の CPU 使用率が高い場合のトラブルシューティング - フレキシブル サーバー。
• Azure Database for PostgreSQL - フレキシブル サーバーの高いメモリ使用率のトラブルシューティング。
• Azure Database for PostgreSQL - フレキシブル サーバーで実行速度が遅いクエリをトラブルシューティングして特定する。
• Azure Database for PostgreSQL - フレキシブル サーバーのサーバー パラメーター。
• Azure Database for PostgreSQL - フレキシブル サーバーでの自動バキュームのチューニング。