DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ DevOpsツールとベストプラクティスの習得 - DevOpsナレッジハブ

読み取りと書き込みのパフォーマンスを最適化するための`postgresql.conf`パラメータのチューニング

主要な`postgresql.conf`パラメータを習得して、PostgreSQLの最適なパフォーマンスを引き出しましょう。この包括的なガイドでは、`shared_buffers`、`work_mem`、`checkpoint_timeout`について詳しく説明し、クエリ速度、トランザクションスループット、データベース全体の効率への影響を解説します。実践的なチューニング戦略を学び、ハードウェアやワークロードとの相互作用を理解し、その効果を監視する方法を発見してください。読み取り操作と書き込み操作の両方に対応した、実用的な設定例とベストプラクティスでPostgreSQLインスタンスを強化しましょう。

読み取りと書き込みのパフォーマンスを最適化するためのpostgresql.confパラメータのチューニング

PostgreSQLは通常、パッケージのデフォルト設定で許容範囲内で動作しますが、実際のトラフィックが発生すると、「許容範囲」が読み取りの遅延、書き込みのスパイク、またはランダムなレイテンシに変わる可能性があります。postgresql.confファイルは、PostgreSQLが共有キャッシュに使用できるメモリ量、各クエリ操作がディスクに溢れる前に使用できるメモリ量、チェックポイントがダーティページを書き込む頻度、およびプランナーが基盤となるマシンについて受け取るヒントなど、基本的なリソース予算を設定する場所です。

私が最もよく目にする間違いは、PostgreSQLのチューニングを魔法の数字のリストのように扱うことです。誰かがshared_buffers = RAMの25%をコピーし、work_memを大きな値に設定し、max_connectionsを2倍にして、データベースが高速化することを期待します。時にはうまくいきます。時には、レポートジョブ中にスワップが発生したり、チェックポイント中に壁にぶつかったりします。

より安全な方法は、症状からチューニングすることです。ワーキングセットがキャッシュされていないために読み取りが遅いのですか？レポートがソートをディスクに溢れさせていますか？書き込みがチェックポイント中に集中していますか？アプリケーションの接続が多すぎてメモリを競合していますか？このガイドでは、通常最初に重要となるパラメータについて、盲目的にコピーするのではなく、適応できる例を交えて説明します。

コアメモリパラメータの理解

効率的なメモリ管理は、高性能データベースシステムにとって最も重要です。PostgreSQLはさまざまなメモリ領域を利用しますが、その中で最も重要な2つは、頻繁にアクセスされるデータをキャッシュするためのshared_buffersと、内部クエリ操作用のwork_memです。

shared_buffers

shared_buffersは、間違いなくチューニングすべき最も重要なメモリパラメータの1つです。これは、PostgreSQLがデータブロックをキャッシュするために使用する専用メモリの量を定義します。これらのブロックには、テーブルデータ、インデックスデータ、システムカタログが含まれます。クエリがデータを要求すると、PostgreSQLは最初にshared_buffersをチェックします。データがそこにある場合（キャッシュヒット）、ディスクから読み取る必要がある場合よりもはるかに高速に取得されます。

パフォーマンスへの影響

  • 読み取りパフォーマンス: shared_buffersの値を大きくすると、キャッシュヒットの可能性が高まり、読み取り負荷の高いワークロードでのディスクI/Oが大幅に削減されます。これにより、クエリ応答が高速化されます。
  • 書き込みパフォーマンス: shared_buffersは「ダーティ」ページ（変更されたがまだディスクに書き込まれていないデータブロック）も保持します。バッファを大きくすると、より多くの書き込みを吸収できるため、システムはそれらをより少ない、より大きな書き込みにバッチ処理でき、書き込みスループットが向上します。ただし、大きすぎると、チェックポイント時間が長くなり、チェックポイント中のI/Oスパイクが増加する可能性があります。

チューニングガイドライン

  • 開始点: 一般的な推奨事項は、shared_buffersを**物理RAMの合計の25%**に設定することです。たとえば、16GBのRAMを搭載したサーバーの場合、shared_buffersは4GBになります。
  • 大容量RAMシステム: 64GB以上のRAMを搭載したサーバーでは、25%を割り当てると過剰になる可能性があります。PostgreSQLは、オペレーティングシステムのファイルシステムキャacheにも依存しています。ある時点を超えると、shared_buffersを増やしても、OSキャッシュが残りのキャッシュの多くを効果的に処理できるため、効果が薄れる可能性があります。そのような場合、15〜20％で十分であり、OSキャッシュやwork_memにより多くのRAMを割り当てることができます。
  • 監視: pg_stat_databaseのキャッシュヒット率に注目してください。ただし、1つのパーセンテージだけですべてが正常であると判断しないでください。ヒット率が高いと、非常に高価なクエリがいくつか隠れている可能性があり、比率が低いことは、大きなテーブルを1回スキャンするバッチジョブでは正常な場合があります。また、チェックポイントの動作とディスクレイテンシも監視してください。

設定例

postgresql.confでshared_buffersを4GBに設定するには：

shared_buffers = 4GB

ヒント: shared_buffersを変更した後、変更を有効にするにはPostgreSQLサービスを再起動する必要があります。

変更後の実用的な確認方法：

SELECT
    datname,
    blks_hit,
    blks_read,
    round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

shared_buffersを増やしてもアプリケーションがディスク読み取りを待機し続ける場合、問題はクエリの形状、インデックスの欠如、テーブルの肥大化、またはメモリよりも大きいワーキングセットにある可能性があります。より多くのキャッシュは、より良い実行計画の代わりにはなりません。

work_mem

work_memは、一時データをディスクに書き込む前に、クエリ操作（ソートやハッシュテーブルなど）で使用されるメモリの最大量を指定します。このメモリは、セッションごと、操作ごとに割り当てられます。複雑なクエリに複数のソート操作やハッシュ操作が含まれる場合、1つのセッション内でwork_memを複数回消費する可能性があります。

パフォーマンスへの影響

  • 複雑なクエリ: work_memは、ORDER BY、GROUP BY、DISTINCT、ハッシュ結合、マテリアライズを含むクエリに大きな影響を与えます。ソート操作やハッシュ操作がwork_mem制限を超えると、PostgreSQLは超過データを一時ディスクファイルに溢れさせ、実行速度が大幅に低下します。
  • 同時実行性: work_memは操作ごと、セッションごとに割り当てられるため、グローバルなwork_mem値が高く、同時に実行される複雑なクエリが多いと、利用可能なRAMをすぐに使い果たし、スワッピングや深刻なパフォーマンス低下を引き起こす可能性があります。

チューニングガイドライン

  • 過剰なグローバル値を避ける: 盲目的にwork_memを非常に大きな値にグローバル設定しないでください。代わりに、アプリケーションの一般的な同時実行性と、最もリソースを消費するクエリのメモリフットプリントを考慮してください。
  • ディスクスピルの監視: 問題のあるクエリに対してEXPLAIN ANALYZEを使用します。Sort Method: external merge Disk: NkBやHashAggregate batches: N (disk)のような行を探します。これはwork_memが不十分でデータがディスクに溢れたことを示します。
  • ターゲットを絞ったチューニング: 特定の長時間実行レポートやバッチジョブについては、グローバルに設定するのではなく、クエリを実行する前にセッションレベルでwork_memを設定することを検討してください。これにより、他の同時セッションに影響を与えることなく、その特定のクエリにより多くのメモリを使用できます。

設定例

postgresql.confでwork_memをグローバルに16MBに設定するには：

work_mem = 16MB

特定のセッション（例：psqlやアプリケーション接続内）でwork_memを設定するには：

SET work_mem = '256MB';
SELECT * FROM large_table ORDER BY some_column;

警告: work_memを増やす場合は注意してください。100の同時クエリがそれぞれ1GBのwork_memを必要とする場合、100GBのRAMが必要になります！必ずステージング環境で変更をテストし、システムのメモリ使用量を監視してください。

work_memを使用するより現実的な方法は、グローバル値を控えめに保ち、既知のレポートセッションでのみ値を上げることです：

BEGIN;
SET LOCAL work_mem = '256MB';

SELECT customer_id, sum(total_amount)
FROM orders
WHERE created_at >= current_date - interval '90 days'
GROUP BY customer_id
ORDER BY sum(total_amount) DESC;

COMMIT;

このパターンは、すべてのWebリクエストに対してグローバル値を上げるよりも安全です。短いクエリが多いWebアプリケーションは、予測可能なメモリ使用量を必要とします。夜間のレポートは、より大きなクエリごとの予算を賄うことができます。

チェックポイントによる書き込みパフォーマンスと耐久性の管理

チェックポイントは、データの耐久性を確保し、トランザクションログ（WAL - 先行書き込みログ）を管理するためのPostgreSQLの重要なメカニズムです。これらは、shared_buffersから変更されたデータブロックを定期的にディスクに同期し、以前のすべての変更が永続ストレージに書き込まれた時点を示します。

checkpoint_timeout

checkpoint_timeoutは、自動WALチェックポイント間の最大時間を定義します。また、最後のチェックポイント以降に生成されたWALセグメントの量がmax_wal_sizeを超えた場合にもチェックポイントが発生します。

パフォーマンスへの影響

  • 頻繁なチェックポイント（短いcheckpoint_timeout）： ダーティページがディスクにフラッシュされる際に、より頻繁なI/Oスパイクが発生します。これにより、クラッシュ後のリカバリ時間は短縮されますが（再生するWALが少ない）、書き込みアクティビティが集中するため、アクティブなワークロードのパフォーマンスに悪影響を与える可能性があります。
  • まれなチェックポイント（長いcheckpoint_timeout）： I/Oスパイクの頻度が減り、通常の動作中のパフォーマンスがスムーズになります。ただし、クラッシュが発生した場合、WALからより多くのデータを再生する必要がある可能性があり、データベースのリカバリ時間が長くなります。また、蓄積されたWALセグメントを格納するために、より大きなmax_wal_sizeが必要になります。

チューニングガイドライン

  • バランス: 目標は、スムーズな継続的パフォーマンスと許容可能なリカバリ時間のバランスを見つけることです。多くの本番システムは5〜15分程度から開始し、WALボリュームとリカバリ目標に基づいて調整します。
  • max_wal_sizeとの相互作用: これらの2つのパラメータは連携して動作します。checkpoint_timeoutが長くてもmax_wal_sizeが小さすぎる場合、チェックポイントはcheckpoint_timeoutよりもmax_wal_sizeによってより頻繁にトリガーされます。max_wal_sizeを、checkpoint_timeoutが主要なトリガーとなるように十分な大きさに調整します。
  • 監視: pg_stat_bgwriterを使用して、checkpoints_timedカウンターとcheckpoints_reqカウンターを観察します。checkpoint_timeoutが主要なトリガーである場合、checkpoints_timedはcheckpoints_req（WALサイズ制限により要求されたチェックポイント）よりも大幅に高い必要があります。

設定例

postgresql.confでcheckpoint_timeoutを10分に設定するには：

checkpoint_timeout = 10min
# また、それに応じてmax_wal_sizeを調整することを検討してください
max_wal_size = 4GB # 例、ワークロードに基づいて調整

ベストプラクティス: チェックポイントがmax_wal_sizeではなく、主にcheckpoint_timeoutによってトリガーされるようにします。これにより、より予測可能なI/Oパターンが得られます。max_wal_sizeが頻繁にチェックポイントをトリガーしている場合は、その値を増やします。

次のコマンドでパターンを確認します：

SELECT
    checkpoints_timed,
    checkpoints_req,
    checkpoint_write_time,
    checkpoint_sync_time,
    buffers_checkpoint
FROM pg_stat_bgwriter;

checkpoints_reqが急速に増加する場合、PostgreSQLはタイマーが切れたためではなく、WALがmax_wal_sizeを超えたためにチェックポイントを実行しています。これは、書き込みI/Oのバーストとして現れることがよくあります。max_wal_sizeを増やすとワークロードがスムーズになりますが、再生が必要なWALが増える可能性があるため、クラッシュリカバリ時間が長くなる可能性があります。

確認する価値のあるプランナーとWALの設定

大きなメモリとチェックポイントのパラメータの隣に、3つの設定がよくあります。

effective_cache_sizeは、PostgreSQLが割り当てるメモリではありません。これは、PostgreSQLの共有バッファとオペレーティングシステムのファイルキャッシュ全体で利用可能なキャッシュの量に関するプランナーの推定値です。低く設定しすぎると、プランナーは読み取りにコストがかかると想定してインデックススキャンを回避する可能性があります。専用のデータベースサーバーでは、一般的な開始点はRAMの大部分ですが、適切な値はホストで他に何が実行されているかによって異なります。

effective_cache_size = 12GB

maintenance_work_memは、CREATE INDEX、ALTER TABLE ADD FOREIGN KEY、VACUUMなどのメンテナンス操作に影響します。通常のクエリソートではwork_memと同じようには使用されません。メンテナンスウィンドウ中にインデックス構築が非常に遅い場合は、セッションのこの値を上げると役立つ場合があります：

SET maintenance_work_mem = '1GB';
CREATE INDEX CONCURRENTLY idx_orders_created_at ON orders (created_at);

wal_buffersは、WALレコードが書き出される前に使用されるメモリを制御します。PostgreSQLは自動的にサイズを設定できるため、デフォルトで問題ないことがよくありますが、書き込み負荷の高いワークロードで大規模なトランザクションがある場合は、変更する前にWAL書き込みがボトルネックになっていないか確認するとよいでしょう。チェックリストに載っているからといって、チューニングしないでください。

ワークロードの種類に応じた異なる開始点

OLTP Webアプリケーションの場合、優先事項は同時実行性の下での安定したレイテンシです。work_memは控えめに保ち、数千の直接接続を許可する代わりにコネクションプーラーを使用し、shared_buffersを非難する前にロック待機と不良な計画を監視します。典型的な問題は次のようになります。リリースによって数百万行にわたるORDER BY created_at DESCを使用したダッシュボードクエリが追加され、クエリがディスクに溢れ、データベースが一時ファイルI/Oを実行しているため、すべてのリクエストが突然遅くなります。修正方法は、インデックスまたはより狭いクエリであり、グローバルなwork_memを大きくすることではありません。

分析またはレポートデータベースの場合、大規模なソートとハッシュ集計は正常です。レポートロールのwork_memを上げ、一括インデックス作業のためにmaintenance_work_memを増やし、より長時間実行されるクエリを受け入れることができます。リスクは同時実行性です。10人のアナリストが同時にメモリを大量に消費するレポートを実行すると、1つの成功したテストクエリが示唆したよりもはるかに多くのメモリを消費する可能性があります。

書き込み負荷の高いシステムの場合、チェックポイントとWALがより重要です。アプリケーションに定期的な書き込みの停止がある場合は、それらがチェックポイントと一致するかどうかを確認してください。また、ストレージのレイテンシ、WALディスクの飽和、autovacuumアクティビティ、および長時間のトランザクションがクリーンアップを妨げていないかどうかも確認してください。checkpoint_timeoutを増やすだけでは、平均書き込みボリュームに追いつけないディスクを修正できません。

シンプルなチューニングワークフロー

現在の設定を記録することから始めます：

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
    'shared_buffers',
    'work_mem',
    'maintenance_work_mem',
    'effective_cache_size',
    'checkpoint_timeout',
    'max_wal_size',
    'wal_buffers',
    'max_connections'
)
ORDER BY name;

次に、何も変更する前に症状をキャプチャします。EXPLAIN (ANALYZE, BUFFERS)を使用して、1つまたは2つの遅いクエリ計画を保存します。スピルが疑われる場合は、一時ファイルのログを確認します：

log_temp_files = 0

この設定はすべての一時ファイルをログに記録するため、ビジーなシステムでは注意して使用するか、64MBなどのしきい値を設定します。同じクエリ形状から多数の大きな一時ファイルが表示される場合は、クエリをチューニングするか、インデックスを追加するか、そのワークロードのwork_memを上げます。

一度に1つのことを変更します。一部の設定は再起動が必要で、一部はリロードのみが必要で、一部はセッションごとに設定できます。PostgreSQLはどれがどれかを教えてくれます：

SELECT name, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'work_mem', 'checkpoint_timeout', 'max_wal_size');

postmasterコンテキストは再起動を意味します。sighupはリロードを意味します。userはセッションレベルの変更が可能であることを意味します。

一般的なチューニングのヒントとベストプラクティス

  • 反復チューニング: 小さな段階的な変更から始めます。一度に1つのパラメータを変更し、影響を観察してから、必要に応じてさらに調整します。チューニングは1回限りのタスクではなく、継続的なプロセスです。
  • すべてを監視する: PostgreSQLの組み込み統計ビュー（pg_stat_database、pg_stat_bgwriter、pg_stat_activity）、OSレベルの監視ツール（例：iostat、vmstat、top）、および外部の監視ソリューションを利用して、CPU、メモリ、ディスクI/O、クエリパフォーマンスに関するデータを収集します。
  • ワークロードを理解する: アプリケーションは読み取り主体ですか、それとも書き込み主体ですか？複雑な分析クエリを実行しますか、それとも単純なトランザクション操作を実行しますか？特定のワークロード特性に合わせて設定を調整します。
  • 他のパラメータを考慮する: shared_buffers、work_mem、checkpoint_timeoutは重要ですが、他にも多くのパラメータがパフォーマンスに影響を与える可能性があります。たとえば、effective_cache_size（利用可能なOSキャッシュに関するクエリプランナーへのヒント）やwal_buffers（フラッシュ前のWALレコード用メモリ）は、これらと一緒にチューニングされることがよくあります。
  • EXPLAIN ANALYZEを使用する: この貴重なツールは、PostgreSQLがクエリをどのように実行するかを理解し、ボトルネックを特定し、work_memが不十分かどうかを明らかにするのに役立ちます。

最高のPostgreSQLチューニング作業は、良い意味で退屈です。測定し、1つの設定を変更し、再び測定し、ロールバックパスを維持します。shared_buffers、work_mem、チェックポイント設定は実際に違いを生み出す可能性がありますが、それらはクエリ計画、インデックス、autovacuum、接続数、ストレージと連携して機能します。これらの部分が不健全な場合、設定だけではデータベースを救えません。