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ユーザのパスワードのハッシュアルゴリズムを指定します。 デフォルトの pg_hba.conf 構成ファイルを使用して、特定のデータベースやユーザとして接続出来るIPアドレスを制限した方が良いでしょう。 |
メモリ・ストレージ・WAL
Postgresはインストール直後の設定ではメモリをあまり消費しないよう構成されています。 そのため、大容量のメモリを積んでなおかつデータベース専用機として使用する場合はメモリをより積極的に使用できるよう設定を行う必要があります。
また、オンライントランザクションのようなレスポンスを気にするシステムと、データウェアハウスのようなスループットを気にするシステムではチューニングが異なります。 そのため、どちらのワークロードがより実運用に近いかを意識する必要があります。
項目 | 説明 |
---|---|
shared_buffers | 読み込み及び書き込みに使用するための共有バッファのメモリ量を指定します。 Postgresで使用可能なメモリの25%が妥当と言われています。また、PostgresはOSのファイルキャッシュにも依存しているため、やみくもに大きくすると逆効果となります。 |
effective_cache_size | プランナがクエリプランを生成する際に参考とするOSのファイルキャッシュの大きさを指定します。 実際にこのサイズのメモリを消費するわけではありません。 Windowsであれば、SuperFetch君が使えるメモリ量を指定すれば大丈夫っぽいです。 |
work_mem | ソートなどのクエリ操作中に使用可能な最大メモリ量を指定します。 このサイズを超える場合は一時ファイルに書き出されるようになります。 ネット上の記事では1つのセッションでの最大メモリ量と解説されることがありますが、それは誤りです。 1つのセッションでクエリ操作がパラレル実行される場合、それぞれのクエリ操作で 極端に大きな値を設定した場合、同時コネクション数によっては使用できるメモリが枯渇する事になります。 逆に小さすぎる場合、小さなデータセットのソートでも一時ディスクにデータを書き込むようになるため動作が遅くなります。以下の式をベースに調整するのが良いでしょう。 (PostgreSQLで使用可能なメモリ - 共有バッファ)÷ 最大コネクション数 |
maintenance_work_mem | バキュームなどの保守操作で使用される最大メモリ量を指定します。 このメモリ量を増やすことでダンプからのリストアが高速に処理されるようになります。 データウェアハウスなどの大量のデータの入れ替えが発生するシステムでは多めに設定した方が良いでしょう。 |
min_wal_size | ディスク上に保持するWALの最小サイズを指定します。 WALファイルはデータ正常にディスクに書き込まれた後に再利用するため、常にこのパラメータで指定しているサイズは保持されます。 |
max_wal_size | WALがこのサイズを超えた場合、チェックポイント間隔以下でもチェックポイントが実行されます。 この値はソフトリミットで、ディスクの負荷状況によってはWALがこのサイズを上回る可能性があります。 チェックポイントは非常にI/Oコストが大きい処理です。 そのため、データウェアハウスのような大量のデータを一度に流し込むような用途ではこのパラメータを十分に大きくします。 |
checkpoint_timeout | 自動チェックポイントを発動させる間隔を指定します。この設定値が小さいと頻繁にチェックポイントが走るようになるため書き込みの性能が劣化します。 一般的には |
checkpoint_completion_target | あるチェックポイントまでに完了したトランザクションデータは次のチェックポイントまでの間で均等にならされてディスクに書き込まれます。 これは、チェックポイント直後のI/O処理のバーストを防ぐための措置です。 その書き込みの際に、チェックポイント間隔のどのくらいの割合でならしながら書き込むかを指定します。 |
wal_buffers | WALレコードをディスクに書き込む際のバッファ量を指定します。 |
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寄りの傾向を想定しています。
項目 | 値 |
---|---|
OS | Windows 10 2004(19041.264) |
Postgres | EDB社製ディストリビューション バージョン11.12 |
CPU | Intel(R) Core(TM) i3-6100U CPU @ 2.30GB |
メモリ | 8.0GB |
ストレージ | SSD 128GB |
ワークロード傾向 | DWH |
チューニングパラメータ一覧
今回は以下のチューニングを行っています。
項目 | 値 | 備考 |
---|---|---|
listen_addresses | * | |
port | 5432 | |
password_encryption | scram-sha-256 | |
shared_buffers | 2GB | 物理メモリの1/4。 |
work_mem | 31MB | |
maintenance_work_mem | 768MB | ワークロードがDWH寄りなので多めにする。 |
max_worker_processes | 2 | 2コアなので。 |
max_parallel_maintenance_workers | 1 | |
max_parallel_workers_per_gather | 1 | |
max_parallel_workers | 2 | |
wal_buffers | 16MB | shared_buffers を増やしているので合わせて増やす。 |
max_wal_size | 6GB | ワークロードがDWH寄りなので多めにする。 |
min_wal_size | 2GB | |
checkpoint_completion_target | 0.9 | |
random_page_cost | 1.1 | SSDなので。 |
effective_cache_size | 4GB | |
default_statistics_target | 500 | DWHなので、アナライズに時間を掛けてもトータルのクエリコストがペイ出来るため増やす。 |
ベンチマーク
ワークロードはDWH寄りなので、それに応じたベンチマーク規格としてTPC-Hを実施します。
条件 | 値 |
---|---|
スケールファクタ | 1 |
同時接続数 | 1 |
また、ベンチマークツールとしてHammerDBのバージョン4.2を使用しています。
条件を変えてテストした訳ではないため厳密性には欠けますが、デフォルト値とチューニング済みでは以下の差異が見られました。
デフォルト設定 | 2.1秒/クエリ(各クエリの平均値) |
チューニング済み | 1.1秒/クエリ(各クエリの平均値) |
おわり