WindowsでもPostgreSQLのチューニングをしたい

はじめに

PostgreSQLをWindowsにインストールする際にどのパラメータをチューニングするのか良く忘れるのでそれについてです。

特にチューニング系のパラメータはそこそこ数が多く、どのパラメータがどこに影響するのかよく忘れるのでその辺ですね。

検証に使用したプラットフォーム

ここで述べる内容については、以下のプラットフォームで検証を行っています。 使用するOSのバージョンもしくはPostgresのバージョンによっては使用できないパラメータがあるかもしれません。

  • Windows
    • Windows 10 2004(19041.264)
  • Postgres
    • EDB社製ディストリビューション バージョン11.12

インストール時に注意すべきパラメータ

デフォルトロケール

デフォルトでは、デフォルトロケールはインストーラを実行しているOSに設定されている値が使用されます。

しかし、ロケールとしてC以外を使用するとCを設定している場合と比べて文字列のソートが遅くなる傾向があります。 これは、Cの場合ではソート等に使用する照合順序として文字コードが使用されるのに対して、C以外ではそれぞれのロケールの言語の辞書が使用されるためです。 また、C以外のロケールでは使用できない機能等も存在します。

そのため、デフォルトロケールとしてCを使用し、特定の言語向けの照合順序を使用する必要がある場合にはデータベース毎もしくはテーブル毎にロケールを設定するようにした方が良いでしょう。

データディレクトリ

インストーラのデフォルトでは、データディレクトリはC:\Program Files\PostgreSQL\data以下に作成されます。

しかし、Windows Vista・Server 2008以降のWindows OSではC:\Program Filesなどの特定のディレクトリのセキュリティが強化され、編集する場合は管理者ユーザでもUACによる特権昇格が要求されるようになりました。 また、C:\Program Filesの用途としてこのディレクトリにはアプリケーションのみが配置される前提となっています。そのため、データディレクトリは別のディレクトリに配置する事をお勧めします。

個人的には<ドライブレター>:\pgdata\<メジャーバージョン>(例:C:\pgdata\11)のようなディレクトリパスに作成することをお勧めしています。 Postgresはメジャーバージョン間で内部データの互換性が失われることがあります。そのため、新しいメジャーバージョンをインストールした際に同じルールでデータディレクトリを作成出来るようパスにメジャーバージョンを含めています。

インストール後に設定すべきパラメータ

セキュリティ

項目説明
listen_addressesインストール直後の構成では、Postgresはローカルループバックに対してのみ接続を待ち受けます。 外部からの接続を受け付ける場合はこのパラメータを適切なIPアドレスもしくは*に設定する必要があります。
password_encryption

PostgreSQLユーザのパスワードのハッシュアルゴリズムを指定します。 デフォルトのmd5は現在では安全な暗号学的ハッシュ関数とみなされていません。 そのため、クライアントがサポートしていないなどの場合を除いてscram-sha-256を指定した方がより安全です。

また、可能であればpg_hba.conf構成ファイルを使用して、特定のデータベースやユーザとして接続出来るIPアドレスを制限した方が良いでしょう。

メモリ・ストレージ・WAL

Postgresはインストール直後の設定ではメモリをあまり消費しないよう構成されています。 そのため、大容量のメモリを積んでなおかつデータベース専用機として使用する場合はメモリをより積極的に使用できるよう設定を行う必要があります。

また、オンライントランザクションのようなレスポンスを気にするシステムと、データウェアハウスのようなスループットを気にするシステムではチューニングが異なります。 そのため、どちらのワークロードがより実運用に近いかを意識する必要があります。

項目説明
shared_buffers読み込み及び書き込みに使用するための共有バッファのメモリ量を指定します。 Postgresで使用可能なメモリの25%が妥当と言われています。また、PostgresはOSのファイルキャッシュにも依存しているため、やみくもに大きくすると逆効果となります。
effective_cache_sizeプランナがクエリプランを生成する際に参考とするOSのファイルキャッシュの大きさを指定します。 実際にこのサイズのメモリを消費するわけではありません。 Windowsであれば、SuperFetch君が使えるメモリ量を指定すれば大丈夫っぽいです。
work_mem

ソートなどのクエリ操作中に使用可能な最大メモリ量を指定します。 このサイズを超える場合は一時ファイルに書き出されるようになります。

ネット上の記事では1つのセッションでの最大メモリ量と解説されることがありますが、それは誤りです。 1つのセッションでクエリ操作がパラレル実行される場合、それぞれのクエリ操作でwork_memだけ使用されます。 そのため使用されるメモリ量は同時実行数に乗じられます。

極端に大きな値を設定した場合、同時コネクション数によっては使用できるメモリが枯渇する事になります。 逆に小さすぎる場合、小さなデータセットのソートでも一時ディスクにデータを書き込むようになるため動作が遅くなります。以下の式をベースに調整するのが良いでしょう。

(PostgreSQLで使用可能なメモリ - 共有バッファ)÷ 最大コネクション数
maintenance_work_memバキュームなどの保守操作で使用される最大メモリ量を指定します。 このメモリ量を増やすことでダンプからのリストアが高速に処理されるようになります。 データウェアハウスなどの大量のデータの入れ替えが発生するシステムでは多めに設定した方が良いでしょう。
min_wal_sizeディスク上に保持するWALの最小サイズを指定します。 WALファイルはデータ正常にディスクに書き込まれた後に再利用するため、常にこのパラメータで指定しているサイズは保持されます。
max_wal_size

WALがこのサイズを超えた場合、チェックポイント間隔以下でもチェックポイントが実行されます。 この値はソフトリミットで、ディスクの負荷状況によってはWALがこのサイズを上回る可能性があります。

チェックポイントは非常にI/Oコストが大きい処理です。 そのため、データウェアハウスのような大量のデータを一度に流し込むような用途ではこのパラメータを十分に大きくします。
checkpoint_timeout

自動チェックポイントを発動させる間隔を指定します。この設定値が小さいと頻繁にチェックポイントが走るようになるため書き込みの性能が劣化します。

一般的には30min以上を設定します。

checkpoint_completion_target

あるチェックポイントまでに完了したトランザクションデータは次のチェックポイントまでの間で均等にならされてディスクに書き込まれます。 これは、チェックポイント直後のI/O処理のバーストを防ぐための措置です。

その書き込みの際に、チェックポイント間隔のどのくらいの割合でならしながら書き込むかを指定します。
wal_buffersWALレコードをディスクに書き込む際のバッファ量を指定します。
random_page_costプランナに対して使用しているディスク装置のランダムにアクセスする際のコストを指定するものです。 HDDであれば4.0、SSDであれば1.1を指定します。 根拠はよく分かりません。
max_worker_processesバックグラウンドワーカープロセスの最大数を指定します。 データベース専用機であれば一般的にコア当たりのスレッド数×コア数×ソケット数を指定します。
max_parallel_workers_per_gather結合処理の際に並列で稼働させる最大のワーカー数を指定します。 ワーカープロセスはmax_worker_processesで指定したワーカープールから取り出されて使用されるため、max_worker_processes以上に指定しても意味がありません。
max_parallel_workersパラレルクエリ操作用に使用されるワーカーの最大数を指定します。 大体はmax_worker_processesと同じです。
max_parallel_maintenance_workersユーティリティコマンドで使用できる最大のワーカー数を設定します。 が、パラレルワーカーを使えるユーティリティコマンドはCREATE INDEX位なので、まぁ、その
default_statistics_target

デフォルトの統計対象を指定します。 大きな値を設定すると、ANALYZEに時間が掛かるようになりますが、よりプランナの予測品質が向上します。

DWHのような読み取り主体のワークロードであれば、デフォルト値よりも大きくしても良いかもしれません。

設定例

このセクションでは、以下のハードウェアとワークロード傾向におけるチューニング例と、ベンチマーク結果について提示します。

ハードウェアとワークロード傾向

今回想定するハードウェアは以下の通りとなります。 また、今回のワークロードは読み取りが主体かつ、複雑なクエリを実行する事が予想されるためDWH寄りの傾向を想定しています。

項目
OSWindows 10 2004(19041.264)
PostgresEDB社製ディストリビューション バージョン11.12
CPUIntel(R) Core(TM) i3-6100U CPU @ 2.30GB
メモリ8.0GB
ストレージSSD 128GB
ワークロード傾向DWH

チューニングパラメータ一覧

今回は以下のチューニングを行っています。

項目備考
listen_addresses*
port5432
password_encryptionscram-sha-256
shared_buffers2GB物理メモリの1/4。
work_mem31MB
maintenance_work_mem768MBワークロードがDWH寄りなので多めにする。
max_worker_processes22コアなので。
max_parallel_maintenance_workers1
max_parallel_workers_per_gather1
max_parallel_workers2
wal_buffers16MBshared_buffersを増やしているので合わせて増やす。
max_wal_size6GBワークロードがDWH寄りなので多めにする。
min_wal_size2GB
checkpoint_completion_target0.9
random_page_cost1.1SSDなので。
effective_cache_size4GB
default_statistics_target500DWHなので、アナライズに時間を掛けてもトータルのクエリコストがペイ出来るため増やす。

ベンチマーク

ワークロードはDWH寄りなので、それに応じたベンチマーク規格としてTPC-Hを実施します。

条件
スケールファクタ1
同時接続数1

また、ベンチマークツールとしてHammerDBのバージョン4.2を使用しています。

条件を変えてテストした訳ではないため厳密性には欠けますが、デフォルト値とチューニング済みでは以下の差異が見られました。

デフォルト設定2.1秒/クエリ(各クエリの平均値)
チューニング済み1.1秒/クエリ(各クエリの平均値)

おわり