はじめに
PostgreSQLの設計を行う際の、メモリに対する考え方についてまとめました。
PostgreSQLで使用するメモリは大別すると、PostgreSQLサーバプロセス全体で共有される共有メモリと、バックエンドプロセスで確保されるプロセスメモリの二つになります。
PostgreSQLで使用するメモリ = 共有メモリ + プロセスメモリ
本記事では、特に重要な、共有メモリの共有バッファ(shared_buffers)と、プロセスメモリの作業メモリ(work_mem)について記載しています。
共有バッファ(shared_buffers)
共有バッファは、テーブルやインデクスのデータをページ単位でキャッシュします。データアクセス時にデータベースファイルをページ単位でメモリ上に展開し、繰り返しデータにアクセスするときの処理性能を向上させます。
しかし、共有バッファが大きいほど性能が良いというわけではなく、サイズが大きくなると、バッファ探索に時間がかかったり、チェックポイント処理の負担も大きくなります。
なお、PostgreSQLでは、目安としてメモリを1GB以上搭載したサーバの場合、搭載メモリ量の25％程度を設定するのが推奨になります。
見積りサイズ = サーバのメモリ容量 * 25 / 100
作業メモリ(work_mem)
作業メモリの「work_mem」を大きくすると、ソートやハッシュをメモリ上で行えるため、基本的には性能が向上しますが、複雑な問い合わせの場合には、ソートやハッシュが問い合わせの中で複数回実行されることがあります。この場合、work_memサイズの数倍のメモリが必要になります。またwork_memはクライアント同時接続数に比例し、さらにその数倍の物理メモリを消費します。よって、メモリ不足からスワップが発生しかえって性能が悪くなるため、注意が必要になります。
見積りサイズ = サーバのメモリ容量 - (shared_buffers + その他(※)) / max_connections
(※)OS及びMWで消費するメモリ等
その他
max_connectionsは、アプリケーションに対する同時接続数及びPostgreSQLのスーパユーザによる接続(※)を考慮して設計します。
(※)superuser_reserved_connectionsデータベースの障害対策として接続確立後のデータベース処理時に、アプリケーションが異常終了した場合、タイミングによってはデータベースがアプリケーションの異常を検知できず、不要なプロセスが残存する状態が発生します。本現象の発生により、不必要なメモリが獲得されたままになる可能性がある為、コネクションのタイムアウトとして、「tcp_keepalives_idle」及び「tcp_keepalives_interval」を設定するのが望ましいです。
さくっと設定値見積もるなら、PgTuneがおすすめです。