PostgreSQLのメモリ設定を微調整して最適なパフォーマンスを実現する

最近、PostgreSQLのメモリ設定について深く掘り下げて、微調整してパフォーマンスを向上させる方法を探っています。いくつかのリソースを調べてテストを行った結果、大きな違いを生むいくつかの重要な設定を見つけました。

PostgreSQLを運用していて、メモリの使用を最適化したい場合は、注目すべき重要な設定がいくつかあります。

調整すべき主要なメモリ設定

調整を検討すべき最も重要なPostgreSQLのメモリ設定を見ていきましょう。

shared_buffers

shared_buffersは、PostgreSQLが頻繁にアクセスするデータを保持するための個人的なメモリストレージのようなものです。目的は、できるだけ多くのデータをメモリに保存して、高価なディスク読み取りを減らすことです。

推奨設定:

ほとんどの専門家は、これをシステムの総RAMの約25%に設定することを推奨しています。

例えば、16GBのRAMがある場合、約4GBに設定するのが良い出発点です。

work_mem

PostgreSQLがデータのソート、結合、またはグループ化を含むクエリを処理する際に、work_memを使用します。この設定は、各操作がディスクに一時ファイルを書き始める前にどれだけのメモリを取得するかを制御します。

例:

もしwork_mem = 50MBで、クエリに5つのソートタスクがある場合、50MB x 5 = 250MBのメモリを使用する可能性があります。
今、10人のユーザーが同様のクエリを実行していると想像してみてください – それは250MB x 10 = 2.5GBのメモリを使用していることになります。

ヒント:

低すぎると、クエリがディスク使用のために遅くなる可能性があります。
高すぎると、特に多くのユーザーがいる場合にメモリがすぐに不足するかもしれません。

時には、大きなwork_memの値が実際に物事を遅くすることがあります。なぜなら、PostgreSQLが大きなメモリチャンクを管理しなければならないからです。

effective_cache_size

この設定は少し異なります – 実際にメモリを割り当てるのではなく、PostgreSQLにOSキャッシュからどれだけのメモリを使えると思っているかを伝えます。PostgreSQLはこの値を使用して、インデックスを使用するべきか、シーケンシャルスキャンを行うべきかを決定します。

設定を間違えると:

低すぎる? PostgreSQLはメモリが不足していると思い、インデックスを避けるかもしれません。
高すぎる? PostgreSQLはあまりにも楽観的になり、悪い決定を下すかもしれません。

良い目安: これを総RAMの50-75%に設定してください。特にPostgreSQLがマシンの主要なワークロードである場合は。

maintenance_work_mem

この設定は、VACUUM、CREATE INDEX、その他のメンテナンスタスクにPostgreSQLがどれだけのメモリを使用できるかに関するものです。この値を高く設定することで、これらの操作を高速化し、通常のデータベースアクティビティを遅くすることなく行うことができます。

推奨設定:

余裕があれば、メンテナンスタスクをスムーズに行うために1GB以上に引き上げてください。

メモリ設定を変更する方法

PostgreSQLでは、要件やリスク許容度に応じて、メモリ設定を複数の方法で変更できます。以下は最も効果的なアプローチです:

メモリ設定をグローバルに変更する

すべてのセッションとクエリに対して永続的な変更を行うには、PostgreSQLの設定ファイル（postgresql.conf）を変更するか、ALTER SYSTEMを使用します:


ALTER SYSTEM SET work_mem = '500MB';
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '20GB';
SELECT pg_reload_conf();  -- 再起動せずに変更を適用

セッションごとに設定を変更する

現在の接続にのみ影響を与える一時的な調整を行うには:

SET work_mem = '100MB';
SET effective_cache_size = '16GB';

PostgreSQLメモリ調整の実際の例

シナリオ ##o

ある30GB RAMサーバー上で稼働しているPostgreSQL 10データベースは、金融取引を処理する際にCSVエクスポートクエリのパフォーマンスが遅いという問題を抱えていました。4つの大きなテーブルを結合する複雑なクエリは、結果を出すのに33秒かかっていました:


SELECT * FROM finance_transaction ft
INNER JOIN billing_record br ON br.id = ft.billing_record
INNER JOIN transaction_log tl ON ft.transaction_log = tl.id
INNER JOIN transaction_audit_history tah ON tah.finance_transaction = ft.id;

テーブルのサイズ:

finance_transaction (~100万行)
transaction_log (~100万行)
transaction_audit_history (~400万行)
billing_record (~100万行)

デフォルトのメモリ設定:

設定	デフォルト値
shared_buffers	128MB
work_mem	4MB
maintenance_work_mem	64MB
effective_cache_size	4GB

クエリ実行計画 (EXPLAIN ANALYZE)

Gather  (cost=1071962.33..3055242.09 rows=6022483 width=4782) (actual time=26603.645..33465.648 rows=4847517 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Hash Join  (cost=1070962.33..2451993.79 rows=1505621 width=4782) (actual time=26817.703..29038.177 rows=969503 loops=5)
        Hash Cond: (ft.id = tah.finance_transaction)
        ->  Hash Join  (cost=716707.62..1254298.44 rows=403544 width=4591) (actual time=18471.362..19081.998 rows=259931 loops=5)
              Hash Cond: (br.id = ft.billing_record)
              ->  Parallel Seq Scan on billing_record br  (cost=0.00..39488.92 rows=335492 width=3598) (actual time=0.005..42.297 rows=268393 loops=5)
              ->  Hash  (cost=501594.96..501594.96 rows=1564453 width=993) (actual time=18468.587..18468.589 rows=1299656 loops=5)
                    Buckets: 4096  Batches: 512  Memory Usage: 708kB
                    ->  Hash Join  (cost=103452.20..501594.96 rows=1564453 width=993) (actual time=9094.933..15042.241 rows=1300948 loops=5)
                          Hash Cond: (tl.id = ft.transaction_log)
                          ->  Seq Scan on transaction_log tl  (cost=0.00..33484.53 rows=1564453 width=766) (actual time=0.019..133.507 rows=1564453 loops=5)
                          ->  Hash  (cost=46549.31..46549.31 rows=1300631 width=227) (actual time=9092.767..9092.768 rows=1300948 loops=5)
                                Buckets: 16384  Batches: 128  Memory Usage: 2199kB
                                ->  Seq Scan on finance_transaction ft  (cost=0.00..46549.31 rows=1300631 width=227) (actual time=0.005..143.568 rows=1300948 loops=5)
        ->  Hash  (cost=165645.54..165645.54 rows=4852654 width=191) (actual time=8267.360..8267.360 rows=4852654 loops=5)
              Buckets: 32768  Batches: 512  Memory Usage: 2059kB
              ->  Seq Scan on transaction_audit_history tah  (cost=0.00..165645.54 rows=4852654 width=191) (actual time=0.020..789.242 rows=4852654 loops=5)
Planning time: 0.346 ms
Execution time: 33586.541 ms

クエリ実行計画を分析する:

問題	EXPLAIN ANALYZEからの観察	考えられる原因	解決策
ハッシュ結合でのディスクスピル	Buckets: 4096 → Batches: 512 Memory Usage: 708kB	work_memが低すぎる（デフォルト4MB）	work_memを300MBに増やす ALTER SYSTEM SET work_mem = '300MB';
シーケンシャルスキャンからの高いI/O	billing_record、finance_transaction、transaction_logでのParallel Seq Scans	shared_buffersが低すぎる（デフォルト128MB）	shared_buffersを8GBに増やす ALTER SYSTEM SET shared_buffers = '8GB';
クエリプランナーが利用可能なRAMを過小評価	インデックススキャンの代わりにシーケンシャルスキャンを多く使用	effective_cache_sizeが低すぎる（デフォルト4GB）	effective_cache_sizeを20GBに増やす ALTER SYSTEM SET effective_cache_size = '20GB';

メモリ設定を増やした後、これがEXPLAIN ANALYZEの結果です:

Gather  (cost=1071962.33..3055242.09 rows=6022483 width=4782) (actual time=16228.246..23884.145 rows=4847517 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Hash Join  (cost=1070962.33..2451993.79 rows=1505621 width=4782) (actual time=17226.338..20072.376 rows=969503 loops=5)
        Hash Cond: (ft.id = tah.finance_transaction)
        ->  Hash Join  (cost=716707.62..1254298.44 rows=403544 width=4591) (actual time=14289.482..15561.150 rows=259931 loops=5)
              Hash Cond: (br.id = ft.billing_record)
              ->  Parallel Seq Scan on billing_record br  (cost=0.00..39488.92 rows=335492 width=3598) (actual time=0.005..37.096 rows=268393 loops=5)
              ->  Hash  (cost=501594.96..501594.96 rows=1564453 width=993) (actual time=14287.210..14287.212 rows=1299656 loops=5)
                    Buckets: 524288  Batches: 8  Memory Usage: 46914kB
                    ->  Hash Join  (cost=103452.20..501594.96 rows=1564453 width=993) (actual time=3728.706..6986.704 rows=1300948 loops=5)
                          Hash Cond: (tl.id = ft.transaction_log)
                          ->  Seq Scan on transaction_log tl  (cost=0.00..33484.53 rows=1564453 width=766) (actual time=0.018..123.814 rows=1564453 loops=5)
                          ->  Hash  (cost=46549.31..46549.31 rows=1300631 width=227) (actual time=3704.309..3704.309 rows=1300948 loops=5)
                                Buckets: 2097152  Batches: 2  Memory Usage: 151889kB
                                ->  Seq Scan on finance_transaction ft  (cost=0.00..46549.31 rows=1300631 width=227) (actual time=0.018..139.211 rows=1300948 loops=5)
        ->  Hash  (cost=165645.54..165645.54 rows=4852654 width=191) (actual time=2927.466..2927.466 rows=4852654 loops=5)
              Buckets: 2097152  Batches: 4  Memory Usage: 246002kB
              ->  Seq Scan on transaction_audit_history tah  (cost=0.00..165645.54 rows=4852654 width=191) (actual time=0.025..630.852 rows=4852654 loops=5)
Planning time: 0.341 ms
Execution time: 24013.565 ms

パフォーマンス比較: メモリ設定を増やす前と後

メトリック	以前（デフォルト設定）	後（最適化されたメモリ設定）	改善
総実行時間	33.58s	24.01s	⬇ 約28.5%速く（約9.5s節約）
ハッシュ結合 (ft.id = tah.finance_transaction)	29.03s	20.07s	⬇ 約9s速く
ハッシュ結合 (br.id = ft.billing_record)	19.08s	15.56s	⬇ 約3.5s速く
ハッシュバッチ (ft.transaction_log)	128バッチ	2バッチ	✅ ディスクスピルの大幅削減
ハッシュバッチ (transaction_audit_history)	512バッチ	4バッチ	✅ ディスク使用量の削減

適切なメモリ設定の選択: ケースバイケースのアプローチ

PostgreSQLのメモリ設定の調整は、一律の解決策ではありません。データベースのサイズ、同時ユーザー、クエリパターンによって異なります。work_mem、shared_buffers、effective_cache_sizeの適切な値を見つけることが重要です。

サーバーに十分な空きRAMがある場合、PostgreSQLにより多くのメモリを割り当てることができます。
多くの同時ユーザーがいる場合、クエリごとにメモリを割り当てすぎるとシステムの不安定性を引き起こす可能性があります。
遅いクエリに対してEXPLAIN ANALYZEを実行して、ディスクスピルやハッシュ結合の非効率性を特定します。

全体のシステムに影響を与えずに安全に変更をテストしたい場合は、グローバルではなくクエリごとに設定を適用します:

✅ 単一の重いクエリのために設定を変更:

SET LOCAL work_mem = '300MB';
SELECT * FROM finance_transaction ft
INNER JOIN billing_record br ON br.id = ft.billing_record
INNER JOIN transaction_log tl ON ft.transaction_log = tl.id
INNER JOIN transaction_audit_history tah ON tah.finance_transaction = ft.id;

結論

PostgreSQLのメモリ設定を調整することで、パフォーマンスに大きな違いをもたらし、クエリをより速く実行し、不要なディスクI/Oを減らすことができます。重要なのは、データベースの負荷、利用可能なRAM、およびクエリの動作に基づいて適切なバランスを見つけることです。

重要なポイント:

ワークロードを理解する – メモリ設定を盲目的に増やすのではなく、まずクエリの実行状況を分析してください。
使用する EXPLAIN ANALYZE – これにより、ディスクスピルや非効率な結合の問題を特定できます。
慎重に work_mem を調整 – 高い値は結合やソートを速くすることができますが、設定が高すぎるとRAMをすべて消費してしまう可能性があります。
適切なレベルで設定を変更 – 全体的な改善にはグローバル設定を使用し、一時的な調整にはセッション設定を使用し、本当に重い操作にはクエリごとの設定を使用します。
影響を監視 – ログを確認し、パフォーマンスを追跡し、必要に応じて調整します。

結局のところ、PostgreSQLの調整は科学と実験の一部です。小さく始めて、結果を追跡し、進めながら調整してください。適切な設定を行えば、データベースはよりスムーズに、速く、そしてはるかに効率的に動作します。🚀

PostgreSQLのメモリ設定を微調整して最適なパフォーマンスを実現する

返信 (0)

目次

調整すべき主要なメモリ設定

メモリ設定を変更する方法

PostgreSQLメモリ調整の実際の例

結論