【PostgreSQL】PostgreSQLのメモリについて勉強していろいろわかったこと

はじめに

PostgreSQLのメモリについて調べたときのメモです。

内容

PostgreSQLのメモリについてあまり知らない・・・

大別すると、PostgreSQLで使用するメモリは「共有メモリ」「バックエンドプロセスで確保されるプロセスメモリ」の二つらしい。

主に以下のページを参考にしました。
とても勉強になるのでぜひ！
- http://www.nminoru.jp/~nminoru/postgresql/pg-table-and-block-structure.html#block-and-page
- https://gihyo.jp/dev/feature/01/dex_postgresql/0002?page=2
- https://qiita.com/Brutus/items/972dcfb0abc3ef561ba0

共有バッファ(shared_buffers)

※厳密には、共有バッファメモリは4つに分かれているらしく詳しくは以下
https://gihyo.jp/dev/feature/01/dex_postgresql/0002?page=2

• 共有バッファ派、テーブルやインデックスのデータをページ単位でキャッシュ
• PostgreSQL インスタンス(=※データベースクラスタ)毎に確保されるバッファで、postgresql.conf の中の shared_buffers オプションでそのサイズが指定される
• 共有バッファは，shared_buffersの項目で値を設定でき、デフォルト値は32MB
• 共有メモリバッファを介してデータを読み取ることによってディスクに直接アクセスする回数を減らして読み取り性能を向上させることができる →データアクセス時にデータベースファイルをページ単位でメモリ上に展開しているから
• 共有バッファが大きければええやんって感じるが、メモリ領域を圧迫して、※ディスクスワップの発生によって性能を低下させる
• 目安としてメモリを1GB以上搭載したサーバの場合、搭載メモリ量の25％程度を設定するのが推奨 →　「見積りサイズ = サーバのメモリ容量 * 25 / 100」

プロセスメモリの作業メモリ(work_mem)

※厳密には、プロセスメモリは3つに分かれているらしく詳しくは以下
https://gihyo.jp/dev/feature/01/dex_postgresql/0002?page=2

• 作業メモリは，クエリの実行時にソートやハッシュテーブルの利用のために使われる領域
• サービス開始当初はwork_memの値が小さくても取り扱うデータも小さいため問題にならなかったものが，サービスの成長とともに取り扱うデータサイズが大きくなり，ソートの際にメモリからあふれることがある →このようなことを防ぐため，work_memの値は適切にチューニングする必要がありますし，スワップを監視する必要がある →「(OSの実メモリ - shared_buffers) / max_connections」を超えないように設定する。
• work_memはクライアント同時接続数に比例し、さらにその数倍の物理メモリを消費 → よって、メモリ不足からスワップが発生しかえって性能が悪くなるため、注意が必要

参考

・http://www.nminoru.jp/~nminoru/postgresql/pg-table-and-block-structure.html#block-and-page
・https://qiita.com/Brutus/items/972dcfb0abc3ef561ba0

以下用語知らなかったものを書きます。

恥ずかしいですが、上記の文章を読んでいて自分が知らなかったワードを列挙します。

データベース・クラスタって？

内容

データベース構築の基本は、1つのサーバーに1つのデータベースを構築し、運用すること。
しかし、1つのデータベースを複数のサーバー（仮想サーバーを含む）にまたがって構築するケースが増えてきた。
理由として、1つのサーバーだけでは実現がむずかしい「高可用性・並列処理・性能向上」があるから。性能向上だけ想像しにくいと思うので、以下。

<性能向上について>
データベースのユーザー数が膨大になることは、Web系のアプリケーションではよく見られます。大規模なWebアプリケーションの場合、1つのデータベースのコピーを作り、データベースの参照はこのコピーに対して行うようにすることで、ユーザー数の増大にも対応できるようになります。

ただし、データベース・クラスタに似たものとして、複数の異なるデータベース同士を連携させたシステムがあり、「DBLink」や「SQL/MED」との混同に注意する。

参考

ディスクスワップって？

「ディスクにスワップしていくんだろ。」程度の認識でもっと深く説明してくださいと言われたら、完全にアウトだったので調べました。

内容

コンピューターの処理はメモリ上で行われるが、対象のデータが大きい場合は、当面使用する予定の無い部分をHDDに退避し、空いた空間に必要なデータを配置して続行される。このメモリとHDDでファイルを交換することを、ディスクスワップとか単にスワップと言う。

参考

所感

スワップが発生しだしたら、メモリぶち上げるだけじゃなく、work_mem値やshared_buffers値を見直してみるのも手ですね。
あと、インデックステーブルを再生成するとか。（以下）

CREATE INDEX CONCURRENTLY new_idx ON table1(col1, col2, col3);