- Postgresデータベースは大量のRAMを使用する。結果セットを作成する際、インデックス照合、テーブルからの関連行の取得、タプルの結合・フィルタリング・集計・ソートといった段階を経るが、これらすべての段階でメモリに依存する
- Postgresのメモリ使用量を最適化するには、利用可能なRAMを最大限に活用しつつ、さまざまな種類のメモリ割り当てを効率よく調整し、OSが過剰なメモリ使用によってプロセスを終了させるのを防ぐ必要がある
Sharing is Caring
- Postgres関連のRAMのうち最大の部分は
shared_buffersと呼ばれ、最も頻繁に参照されるテーブルやインデックスのすべての行を表す。利用頻度に応じてスコアを付けるヒューリスティックによって支えられている
shared_buffersはPostgres起動時に割り当てられる固定値であり、予期しないメモリ問題の原因にはならない- デフォルト値は128MB
- ただしOSはこれを事前確保済みメモリと見なさない場合があるため、インスタンスのRAM容量に近い非常に高い値を指定するのは危険なことがある
- 本番システムにおける
shared_buffersの最も一般的な推奨値は、利用可能RAMの25%である。これはハードウェアに合わせて調整しやすく、多くのシステムにとって適切な出発点となる
- ベンチマーク結果では、25%という助言は概ね十分だが、データベースの使われ方によって変わり得る
- たとえばレポーティングシステムでは、複雑なアドホッククエリによりキャッシュヒット率が低く、むしろ少し低めの設定のほうがわずかに良い性能を示すことがある
pg_buffercache拡張を使うと、共有バッファに割り当てられているテーブルとインデックスを正確に把握できる。バッファ内で使用されているページ数を確認し、shared_buffersの値を調整できる
- バッファキャッシュが100%活用されていないなら、設定が高すぎる可能性があるため、インスタンスサイズや
shared_buffersの値を下げられる
- 100%に達しており、多くのテーブルの一部しかキャッシュされていない場合は、効果が逓減するまで段階的に高い値を試すのが有利なことがある
- Postgres 16の新しい
pg_stat_ioビューもshared_buffersの調整に役立つ。ヒット率やクライアントバックエンドの読み書きを確認できる
- 読み取り対書き込みの比率が1に近い場合、Postgresが同じページを
shared_buffers内で繰り返し循環させている可能性がある。こうしたスラッシングを減らすためにshared_buffersを増やすとよい
- システムRAMの50%を超え始めるなら、インスタンスサイズの拡大を検討すべきである。Postgresはユーザーセッションや関連クエリのためのメモリも引き続き必要とするためだ
Working Memory
- Postgresが実際に処理を行うために使うメモリのもう半分は、
work_memというパラメータで制御される作業メモリに相当する
- デフォルト値は4MBで、クエリ実行速度を上げるためにユーザーが最初に調整しがちな値の一つである
- しかしOSが「メモリ不足」メッセージによりPostgresを終了している場合、
work_memを増やしたくなるかもしれないが、これは問題を悪化させるだけである。Postgresが使用するRAM量を増やし、このような終了に直面する可能性を高めてしまう
- 多くの人は「作業メモリ」を、Postgresがクエリ処理中に行うすべての作業に割り当てられる単一の割り当てだと解釈するが、実際にはそれ以上になることがある
- 各ステップ（ノード）には
work_memの別個のインスタンスが割り当てられる。たとえばデフォルトの4MBを使う場合、4つのノードが必要なクエリは最大16MBのRAMを消費し得る
- 一般に多忙なサーバーでこのようなクエリが100本同時実行されると、結果計算だけで最大1.6GBのRAMを使う可能性がある。より複雑なクエリでは、実行に必要なノード数に応じてさらに多くのRAMが必要になることもある
EXPLAINコマンドでクエリの実行計画を確認すると、Postgresがどのようにクエリを実行し、出力生成にどのノードが必要かを把握できる
pg_stat_statements拡張と組み合わせれば、最も活発なクエリを切り分け、work_memによる総メモリ使用量を見積もることができる
work_memが低すぎると、RAMに収まらない行や中間結果はディスクにあふれ出し、はるかに遅くなる
pg_stat_databaseビューを確認すると、ディスクに書き込まれた一時ファイルの累積サイズと件数を確認でき、平均サイズが妥当ならwork_memをその量まで増やせる
- セッションごとに利用可能なRAM量のおおよその目安は、次の式で求められる:
(総RAMの80% - shared_buffers) / (max_connections)
- たとえば16GB RAM、4GBの共有バッファ、最大接続数100なら、セッションあたり約88MBを使える
- この値をクエリ計画ノードの平均数で割ることで、
work_memの良い設定値が得られる
Ongoing Maintenance
- PostgresのRAM使用量で最後に調整可能な部分は、作業メモリに似ているが保守専用のもので、
maintenance_work_memという似た名前のパラメータを持つ
- デフォルト値は64MBで、
VACUUM、CREATE INDEX、ALTER TABLE ADD FOREIGN KEYのような操作に専用で使われるRAM量を指定する
- これはセッションごとに1つの作業に制限され、多数の同時作業が発生する可能性も低いため、より高い値を使っても十分安全と見なされる
- こうした保守作業は大量のメモリを使うことがあり、RAM内だけで処理できればはるかに速く完了するため、1GBや2GBに設定するのは非常に一般的である
- 重要な注意点は、後で再利用するためにデッドタプルをマークするPostgresの自動バキューム（autovacuum）プロセスである
- autovacuumは
autovacuum_max_workersの上限までバックグラウンドワーカーを起動し、それぞれがmaintenance_work_memの完全なインスタンスを使える
- 十分な空きRAMがあるサーバーなら、1GBの保守作業メモリでも安全なことが多いが、RAMが逼迫しているならより慎重になるべきである
- 特にautovacuumワーカーを制限したい場合は、
autovacuum_work_memという別パラメータがある
- Postgresのautovacuumワーカーは1GBを超えて使えないため、
autovacuum_work_memをそれ以上に設定しても効果はない
Session Pooling
- メモリ消費を減らす最も簡単な方法は、潜在的な割り当てに論理的な上限を設けることである
- Postgresは現在プロセスベースのエンジンであり、各ユーザーセッションにはスレッドではなく物理プロセスが割り当てられる
- そのため、すべての接続には一定のRAMオーバーヘッドが伴い、コンテキストスイッチにも寄与する
- その結果、一般的な推奨として
max_connectionsは利用可能なCPUスレッド数の4倍以下に設定するのがよい。これによりCPU間でアクティブセッションを切り替える時間を最小化し、セッションが全体として消費し得るRAM量も自然に抑えられる
- すべてのセッションがクエリを実行中で、各ノードが
work_memの1つの割り当てを表す場合、理論上の最大作業メモリ使用量はconnections * nodes * work_memである
- クエリの複雑さを下げるのが常に可能とは限らないが、通常は接続数を減らすことはできる
- アプリケーションが常にある程度多めのセッションを開いていたり、複数の個別マイクロサービスがPostgresに依存していたりすると、これは言うほど簡単ではないかもしれない
work_mem * max_connections * 5という式は、すべての接続がアクティブだと仮定した場合に、基本的なクエリ処理のためPostgresインスタンスがユーザーセッションへ割り当て得る最大RAM量のおおまかな見積もりである
- サーバーにこの値を支える十分なRAMがないなら、いずれかの要素を下げるか、RAMを増やすことを検討すべきである
- クエリあたり平均5ノードという見積もりはアプリケーションに合わない可能性があるため、クエリ実行計画をよりよく把握したうえで必要に応じて調整する必要がある
- 次の段階は、PgBouncerのようなコネクションプーラーを導入することである
- これによりクライアント接続をデータベースから切り離し、高コストなPostgresセッションをクライアント間で再利用できる
- 適切に構成すれば、何百ものクライアントがアプリケーションに影響を与えることなく数十本のPostgres接続を共有できる
- 実際にPgBouncerが1000超の接続を40〜50本へ多重化し、プロセスオーバーヘッドによる総メモリ消費を大幅に削減する例が確認されている
Reducing Bloat
- メモリ使用量を追跡するうえで最も難しい側面は、おそらくテーブル膨張（bloat）である
- Postgresはストレージシステム上でデータを表現するために多版同時実行制御（MVCC）を使う
- つまりテーブル行が変更されるたびに、Postgresはテーブル内のどこかにその行の別コピーを作成し、新しいバージョン番号を付ける
- Postgresの
VACUUMプロセスは、古い行バージョンを「未使用」領域としてマークし、新しい行バージョンを配置できるようにする
- Postgresには、こうした再利用可能な割り当てを継続的に見つけ、テーブルが無制限に増えないようにする自動バキュームのバックグラウンドプロセスがある
- ただし、特に大規模システムでは、そのためのデフォルト構成では不十分なことがあり、この保守が追いつかなくなる可能性がある
- その結果、生きている行よりもデッド行のほうが多く詰まった、古いデータで「膨れ上がった」テーブルになることがある
- テーブルが極端に膨張している場合は、共有バッファへの影響も考慮すべきである
- 各ページに有効な行が1つと複数のデッド行しか含まれていない場合、あるクエリに10行必要なら10ページを共有バッファへ読み込まねばならず、他用途に使えたはずの大量のメモリを無駄にしてしまう
- こうした行への需要が特に高いと、使用回数の多さによって共有バッファに居座り、キャッシュ効率を大きく悪化させる
- テーブル膨張を推定するクエリはネット上に多く出回っているが、テーブルのページが実際にどう見えるかを具体的に確認する唯一の方法は
pgstattuple拡張を使うことである
free_percentが30%を超えるなら、自動バキュームをより積極的に調整する必要があるかもしれない。30%を大きく上回る場合は、膨張そのものを完全に除去するのが望ましい
- 現時点でそのための唯一のサポートされた方法は、
VACUUM FULLコマンドでテーブルを本質的に再構築することだ。これはすべての有効な行を新しい場所へ再配置し、以前の膨張したコピーを破棄する
- このプロセスは実行中に排他的アクセスロックを割り当てるため、ほぼすべてのケースで何らかのダウンタイムが必要になる
- その代替として、Temboがサポートする
pg_repack拡張がある
- このコマンドラインツールは、排他的ロックなしで完全オンラインのまま膨張を除去するためにテーブルを再編成できる
- このツールはPostgresコアの外部に存在し、テーブルおよびインデックスのストレージを変更するため、しばしば高度な利用と見なされる
- 使用前には本番以外の環境で十分にテストすることが推奨される
- さらに進んで、列順を再編成してページあたりの行数を最大化する、いわば列のテトリスを行うこともできる
- これはおそらく極端な最適化だが、このようにテーブルを再構築する自由がある環境では有効な戦略になり得る
The Balancing Act
- これらすべてのパラメータとリソースを適切に構成するのは、技術であると同時に芸術でもある
- 共有バッファの実際の使用量を測る方法や、作業メモリが低すぎるかどうかを確認する方法を見てきた
- しかし、多くの場合と同じように、使えるハードウェアや予算に制約があるとしたらどうだろうか。ここが「芸術」が必要になる部分である
- メモリが不足しがちな状況では、より多くの
work_memの余地を確保するためにshared_buffersを少し減らす必要があるかもしれない。あるいは両方を減らす必要があるかもしれない
- アプリケーションで多数のセッションが必要な場合、同時セッションが広範なRAM割り当てを積み上げるのを防ぐため、
work_memを下げるかコネクションプールを導入するほうが理にかなっていることがある
- 十分なRAMがある前提で過去に
maintenance_work_memを増やしていたなら、それを下げるほうが合理的かもしれない。考慮すべき点は多い
- 低メモリのインスタンスでは、上記の推奨だけでは不十分なこともある。そのような状況では、メモリ使用量を最大化しつつリソース枯渇を避けるために、次の作業順序に従うのが望ましい
- コネクションプーラーを追加し、
max_connectionsを下げる。最大リソース消費を減らす最も速く簡単な方法である
pg_stat_statementsで報告された最頻出クエリに対してEXPLAINを使い、平均ではなくクエリの最大ノード数を見つける。次にwork_memを(総RAMの80% - shared_buffers) / (max_connections * 最大計画ノード数)以下に設定するmaintenance_work_memとautovacuum_work_memをデフォルトの64MBへ戻す。保守作業が遅すぎてより多くのRAMを使えるなら、8MB単位で増やすことを検討するpg_buffercache拡張を使ってshared_buffersに保存されているテーブル量を確認する。各テーブルとインデックスを注意深く調べ、データのアーカイブや、取得情報量を減らすようなクエリ修正などにより削減できないか確認する。アクティブな膨張テーブルで使用されるページを圧縮するためにVACUUM FULLやpg_repackを含めることもできるpg_buffercacheが、shared_buffersが満杯でアクティブなページを追い出さずにはこれ以上減らせないことを示している場合、usagecount列を使って最も活発なページの優先度を判断する。この列の値は1〜5なので、3〜5回使用されるページに注目すれば、性能に大きな影響を与えずにshared_buffersを減らせる可能性がある- 最後に、より強力なハードウェアをプロビジョニングする。現在のワークロードに対してデータベースがより多くのRAMを必要としており、上記パラメータを下げるとシステム性能に悪影響が大きすぎる場合は、通常アップグレードするほうが合理的である
まだコメントはありません。