はじめに
i-ReporterのデータベースにはPostgreSQLを利用していますが、運用を行う中でPostgreSQLの影響によりサーバーに高負荷がかかる事象が発生しました。
この問題を受け、PostgreSQLの設定値（パラメータ）におけるパフォーマンスへの影響について調べてみました。
PostgreSQLの設定値とは？
PostgreSQLの設定値とは、データベースの動作や性能を制御するためのパラメータのことで、「postgresql.conf」ファイルで管理されています。
【ファイル格納場所】
≪通常≫
C:\Program Files\PostgreSQL\[バージョン]\data\postgresql.conf
≪i-Reporter≫
C:\ConMas\postgreSQL\postgresql.conf
また、設定値はデフォルトで設定されていますが、「サーバーに無駄な負荷がかかる」や「メモリやCPUを十分に活用できない」場合があり、環境によってデフォルト値のままだとパフォーマンスが十分に発揮されないことがあります。
そのため、サーバー構成や導入しているソフトウェアによって設定値の見直しが必要になります。
主な設定値
PostgreSQLの設定値と一言で言っても、種類や役割は色々あります。
そのため、ここでは特に運用やパフォーマンスに大きく関わる代表的な設定項目と、その役割について整理します。
【代表的な設定項目・役割】
| 設定項目 | 役割 | 備考 |
| shared_buffers | PostgreSQLが利用するメインのキャッシュ領域 | 値が小さいとディスクI/Oが増加し、パフォーマンス低下の原因になる |
| effective_cache_size | OSが利用するキャッシュサイズの推定値 | クエリプランナーの判断に影響する |
| work_mem | ソートやハッシュ処理で使用するメモリ | クエリごとに使用されるため、大きくしすぎると、同時接続数次第でメモリ不足を引き起こす |
| random_page_cost | ランダムアクセスのコスト | SSD環境では低めに設定することでインデックス利用が促進される |
| default_statistics_target | テーブルやインデックスの統計情報の収集粒度を指定 | クエリの実行計画の精度に影響する設定値で、統計情報の細かさを決定する |
推奨値の検討
PostgreSQLの設定値はデフォルトのままではなく、環境に合わせて調整した方がよいとわかりました。
しかし、設定値には明確に正解は無く、サーバーのスペックや利用状況によって最適値が変わります。
そのため、各設定項目の推奨値を算出する方法を整理します。
【推奨値の算出方法】
※APサーバーとDBサーバーを同居させる場合、全メモリの半分程度を利用可能メモリとします
| 設定項目 | 算出方法 | 備考 |
| shared_buffers | 利用可能メモリ× メモリ割当係数 | 通常のメモリ割当係数：25% |
| effective_cache_size | 利用可能メモリ × 見込み利用率 | 通常の見込み利用率：60～65% |
| work_mem | (利用可能メモリ ÷ (同時実行最大クエリ数 × 1クエリあたりの最大ノード数)) ÷ 同時実行時のメモリ使用量増加に対する安全係数 ⇒最適値（2のべき乗）に調整 | ・同時実行最大クエリ数は、 PostgreSQLの同時実行数を運用の観点から検討 ・最大ノード数は、3ノード（JOIN + GROUP BY + ORDER BY）になることが多い ・安全係数はLinuxの場合：2.0、Windowsの場合：5.0にすることが多い |
| random_page_cost | ストレージがHDDの場合：2、 SSDの場合：1 | |
| default_statistics_target | 一般的なWebシステムの場合：100〜300、 複雑なクエリ・データ偏りが大きい場合：300〜1000 |
i-Reporterの推奨値紹介
i-Reporterの環境構築を行う場合には、メーカーの公式マニュアルに推奨設定値が記載されています。
【メーカー推奨値】
| 設定項目 | 算出方法 |
| shared_buffers | 利用可能メモリの25% |
| effective_cache_size | 利用可能メモリの1/4～1/2 |
| work_mem | 利用可能メモリ－ shared_buffers = work_mem × 同時接続数 |
| random_page_cost | 一般的にストレージがHDDの場合：2、SSDの場合：1 |
| default_statistics_target | より精度を向上させるため1000を設定 |
まとめ
PostgreSQLは設定値を適切に調整することでパフォーマンスを向上させることができますが、デフォルト値のままだとパフォーマンスが悪化することもあり、運用状況に応じてチューニングする必要があります。
ただし、設定値に正解があるわけではなく、サーバーのスペックや導入しているアプリケーションの運用を加味して継続的に見直すことが大切です。