35. 基本的な PostgreSQL チューニング

PostgreSQL は非常に多用途なデータベースシステムで、非常に少ないリソース環境下やリソースを様々なアプリケーションで共有するような時に効率的に実行できます。様々な環境下で確実に実行できるようにするために、デフォルトのコンフィギュレーションは非常に保守的で、高パフォーマンス運用データベースとしては適切とまでは言えません。空間データベースには様々な利用パターンがあり、また、非空間データベースと比べて個数は少なくサイズが大きい傾向があることを加えると、デフォルトコンフィギュレーションは、目的に対して、総合的には適切ではありません。

これらのコンフィギュレーションのパラメータは、データベースコンフィギュレーションファイルである postgresql.conf で編集できます。これは標準のテキストファイルで、どのテキストエディタでも編集できます。サーバを再起動させるまで変更は反映されません。

このセクションでは、より運用環境向けの空間データベースに調整できるコンフィギュレーションのパラメータの一部について説明します。

注釈

これらの値は推奨値でしかありません。環境によって異なり、最適な設定を決めるためにはテストが必要です。ただし、このセクションの情報で、良いスタートを切れるとは思います。

35.1. shared_buffers

データベースサーバが共有メモリバッファに利用するメモリ量を設定します。このメモリバッファは、名前が示す通り、バックエンド処理の中で共有されています。デフォルト値は通常、運用データベースとしては嘆かわしいぐらい不十分です。

デフォルト値: 通常 32MB

推奨値: データベースメモリの約75%で最大約2GBまで

35.2. effective_cache_size

PostgreSQL が shared_buffers のために設定するメモリに加えて、クエリプランナはOS が仮想ファイルシステムの一部としてキャッシュした可能性があるディスクブロック数の合計を考慮します。大量のメモリを持つシステムでは、この値を非常に大きくすることができます。effective_cache_size は、概ね、コンピュータ上のメモリの合計から、shared_buffers を引き、work_mem の想定される接続数倍を引き、コンピュータ上で動いている他のあらゆる処理で必要なメモリを引き、他の急に必要になりそうなメモリ 1GB を引いたものです。データベースは追加キャッシュを直接には**使いません**が、プランの計算では、オペレーティングシステムがファイルシステムデータを、ほぼそのサイズのメモリ内にキャッシュすることを想定します。

デフォルト値: 通常 4GB

推奨値: 通常の動作条件下で存在すると予想される「空き」メモリ量

35.3. work_mem

データベースがディスク上のファイルに切り替わる前に、内部のソート処理、インデックス処理とハッシュテーブルが使用できるメモリ量を定義します。この値は個々の処理で有効なメモリを定義します。複雑なクエリでは、いくつかのソート処理またはハッシュ処理が並行実行される可能性がありますし、個々の接続されたセッションがクエリを実行している可能性があります。

そのため、この値を増やす前に、接続数と、予期されるクエリの複雑さを考慮する必要があります。値を増やす 利点 は、ORDER BY節 や DISTINCT節、マージ、ハッシュ接続ハッシュベースの集約、ハッシュベースのサブクエリ処理などで、前よりもディスク書込みなしで実行できることが増えることです。**コスト**は、**接続ごと**に使われるメモリで、これは運用段階の接続では非常に高くなります。

デフォルト値: 1MB

推奨値: 32MB

35.4. maintenance_work_mem

メンテナンス処理のメモリの総量を定義します。VACUUM、インデックス、外部キー生成が当てはまります。これらの処理が非常に一般的というわけでもないので、高い値では、時折コストが発生するだけで、メンテナンス作業で大幅に速度向上する可能性があります。このパラメータは、次に示すように CREATE INDEX または VACUUM 呼出しを実行する前に、単一セッションについて交互に増やすことができます。

SET maintenance_work_mem TO '128MB';
VACUUM ANALYZE;
SET maintenance_work_mem TO '16MB';

デフォルト値: 16MB

推奨値: 128MB

35.5. wal_buffers

ログ先行書込み (WAL) データに使用するメモリ量を設定します。ログ先行書込みは、データの完全性を保証するための高パフォーマンスな機構です。個々の変更コマンドで、最初に WAL ファイルに書かれ、ディスクにフラッシュされます。WAL ファイルがフラッシュされた時の一回だけ変更がデータファイル自体に書き込まれます。これにより、データファイルに最適かつ非同期の手法でディスクに書くことができ、クラッシュ時に全てのデータの変更が WAL から回復できます。

このバッファのサイズは、一つの一般的なトランザクションで WAL データを保持するのに十分なサイズが必要なだけです。ほとんどのデータではデフォルト値で十分ですが、地理空間データはずっと大きくなる傾向があります。ゆえに、このパラメータを増加させることをお勧めします。

デフォルト値: 64kB

推奨値: 1MB

35.6. checkpoint_segments

ログファイルのセグメントの最大数を設定する値です (通常は16MB)。セグメントは自動 WAL チェックポイントの間を示します。WAL チェックポイントは一連の WAL トランザクションで、トランザクションごとに、チェックポイント前の全ての情報でデータファイルが更新されていることが保証されています。この時、全てのダーティなデータページがディスクにフレッシュされ、チェックポイントのレコードがログファイルに書き込まれます。これにより、クラッシュからの復旧の処理において、最も近いチェックポイントのレコードを見つけることができ、全ての続くログセグメントを適用してデータ復旧を完了させることができます。

チェックポイント処理は全てのダーティなデータページをディスクにフラッシュする必要があるため、かなりの I/O 負荷が発生します。前の議論と同じで、空間データは非空間データの最適化のバランスを崩すのに十分大きなものです。この値を大きくすると、過剰なチェックポイントを防ぐことができますが、クラッシュした際の再起動が遅くなるかも知れません。

デフォルト値: 3

推奨値: 6

35.7. random_page_cost

ディスクからのランダムページアクセスのコストを表現する、単位の無い値です。この値は、シーケンシャルページアクセスや CPU 処理コストなどといった他のコストパラメータとの相対値です。この値には魔法の弾丸はなく、デフォルトは一般的に保守的で、ハードディスクなどの回転するメディア上でのデータベース実行用です。SSD のランダムアクセスコストは、それより低く設定するべきです。

この値は SET random_page_cost TO 2.0 コマンドでセッションごとに設定できます。このコマンドは、クエリプランにどれぐらい影響を与えるかを試すのに使えます。

デフォルト値: 4.0

推奨値: ハードディスクでは 2.0、SSDでは 1.0

35.8. seq_page_cost

これは、順次ページアクセスのコストを制御するパラメータです。この値は一般的には調整する必要はありませんが、この値と random_page_cost との間の差は、クエリプランナの選択に大きな影響を与えます。この値はセッションごとに設定することもできます。

デフォルト値: 1.0

推奨値: 1.0

35.9. コンフィギュレーションのリロード

これらの変更を行った後に、変更を保存して、コンフィギュレーションを再読込して下さい。PostgreSQL サービスを再起動するのが最も簡単です。

  • まず pgAdmin で、サーバ PostGIS (localhost:5432) を右クリックして Disconnect from server を選択します。

  • Windows サービス (services.msc) では PostgreSQL を右クリックして「再起動」を選択します。

  • その後 pgAdmin に戻って、サーバをクリックして Connect Server を選択します。