パフォーマンスチューニング9つの技 ～「基盤」について～
PostgreSQLインサイド

今回は、実際の業務において押さえておきたいパフォーマンスチューニング手法の「基盤」について解説します。ここでの「基盤」とは、PostgreSQL自体のアーキテクチャーを考慮の上、特にデータベース内部の機構が効率良く動作するよう調整することであり、データベースの設定ファイルpostgresql.confのパラメーターの見直しや資源の再構成を行いながら最適な状態にすることがテーマです。以下に、「パフォーマンスチューニング9つの技」の全体概要を示します。

今回の記事は、パフォーマンスチューニングの観点と仕組みを理解することに主眼を置いています。具体的な対処方法についてはシステムによって異なるため、マニュアルの確認や、各種チューニングサービスのご利用をご検討ください。なお、この記事で対象にしているPostgreSQLのバージョンは9.5以降です。

本記事の構成

本記事「パフォーマンスチューニング9つの技」は以下4つの記事から構成されています。他の記事も併せてご覧ください。

• パフォーマンスチューニング9つの技 ～はじめに～
• パフォーマンスチューニング9つの技 ～「書き」について～
• パフォーマンスチューニング9つの技 ～「探し」について～
• パフォーマンスチューニング9つの技 ～「基盤」について～（本記事）

1. パフォーマンスチューニングの「基盤」とは

PostgreSQLでは、追記型アーキテクチャーが採用されており、様々なシステム要件や状況に応じて、この機構が効果的に動作し、想定どおりのパフォーマンスを発揮できるよう、設定ファイルpostgresql.confのパラメーターで調整が行えるよう設計されています。また、資源を分散する機能などもあります。このような、PostgreSQLの「基盤」部分に対するパフォーマンスチューニングには、図1に示すような、いくつかの対処すべきポイントがあります。これらのポイントは、実際の運用時と同様の状況でPostgreSQLを動作させないと、正しく対処ができないものもあります。これらのチューニングポイントについて、順に説明していきます。

図1 PostgreSQL内部のパフォーマンスチューニングのポイント（概要）

PostgreSQL全体の構成についての説明は「PostgreSQLのアーキテクチャー概要」を参照してください。

2. パフォーマンスチューニングの「基盤」の技

PostgreSQLの内部で扱うデータが効率よく処理され、データベースのパフォーマンスを向上させることを目的とした、設定ファイルpostgresql.confのパラメーターの調整方法になどについて説明します。

2.1 データを整理し、統計情報を最新化する

PostgreSQLに格納されるテーブルやインデックスに対して更新を繰り返していくと、不要領域が増えてデータが肥大化していくため、パフォーマンスに影響を与えるようになります。また、PostgreSQL内部で作成される統計情報が常に最新状態になっていないと、この情報を基に作成する実行計画が最適な結果にならず、SQLを高速に実行することができません。
ここでは、パフォーマンスを維持するために、PostgreSQL内部のデータ整理と、統計情報を最新に保つ方法について、以下の内容で説明します。また、関連して、トランザクションIDについても、パフォーマンスに影響する課題があるため説明します。

• VACUUMで不要領域を再利用可能な状態にする
• REINDEXでインデックスの不要領域を削除する
• ANALYZEで統計情報を最新化する
• VACUUM FREEZEでトランザクションIDを凍結状態にする

VACUUMで不要領域を再利用可能な状態する

PostgreSQLは、追記型アーキテクチャーを採用しているため、テーブルやインデックスの共有バッファー内のページにおいて、挿入や更新時にはレコードを次々に追記していき、削除や更新時に不要になったレコードは削除マークを付けたまま残しておきます。そのため、何もしないと今後参照されることのない不要レコード（不要タプル）が徐々に増加していくことでデータファイル容量が肥大化し、その結果、データファイルにアクセスする際のディスクI/Oが増加してパフォーマンス劣化につながります。この課題を解決する機能として、VACUUMがあります。VACUUMの処理は、不要レコードの領域を回収して再利用可能な状態にします。PostgreSQLには、このVACUUMを自動で実行するためのautovacuumという機構があり、定期的にVACUUM処理が実行できます。

図2 VACUUM処理

autovacuumは、テーブルデータ内の不要レコードの割合を定期的にチェックし、その割合を超えたときにのみVACUUMを実行します。注意点として、データ量の多いテーブルなどでは、VACUUMを実行するために必要となる不要レコードの数が多くなるため、VACUUMが実施されにくくなります。また、VACUUM対象のテーブル数が多い場合、VACUUMが実施される順番が回ってくるのに時間がかかり、不要レコードがたまっていくことがあります。
データベースのレスポンス改善のため、autovacuumの実行頻度を低くしたり、実行されないようにしたりすることもできます。すると、VACUUMが適宜実行されなくなるので、不要データが増えていきテーブルサイズが肥大化し易くなります。その結果、ディスクを圧迫するとともに、共有メモリー上に必要なデータ量も増えます。これにより、共有メモリー上に載らないデータが増えていき、ディスクI/Oを発生させることでパフォーマンス劣化につながる可能性があります。一方で、VACUUMの効果を十分得るためにVACUUMの実行頻度を高くすると、VACUUM処理自体がデータベース資源やサーバー資源などを多く使用することで、データベース全体のパフォーマンス劣化につながる可能性があります。そのため、データベースの利用状況に応じて適切なバランスになるよう、autovacuumに関する設定を調整する必要があります。例えば、業務中に、autovacuumプロセス（Linuxのpsコマンドなどで確認するとautovacuum workerプロセスとして見えます）を監視し、常時autovacuumプロセスが動作しているようであれば、処理が追い付いていないので、autovacuum_max_workersパラメーターの値を上げることを検討します。また、大きなテーブルの場合には、より少ないディスクI/O、かつ、より短い時間でVACUUM処理を終わらせた方がシステムへの負荷を減らせるため、autovacuum_vacuum_scale_factorやautovacuum_vacuum_insert_scale_factorパラメーターの設定値を下げることを検討します。さらに、業務の繁忙時期にautovacuumが実施されるとレスポンスに影響がある場合などには、手動のVACUUMを追加する検討も必要になることがあります。

autovacuumに関する設定ファイルpostgresql.confのパラメーターとしては、以下のものがあります。

上記はPostgreSQL全体に影響するパラメーターですが、テーブルごとにVACUUMの条件を変更することができます。CREATE TABLEやALTER TABLEコマンドの格納パラメーターが準備されています。

参考

統計情報ビューpg_stat_user_tablesを参照することで、テーブルごとの不要レコード数（n_dead_tup）とその割合（dead_ratio）、テーブルサイズ（table_size）などを確認することができます。
以下の例では、pgbenchを実行した直後の結果を示します。

postgres=# SELECT schemaname, relname, n_live_tup, n_dead_tup,
postgres-#        round(n_dead_tup*100/(n_dead_tup+n_live_tup), 1) AS dead_ratio,
postgres-#        pg_size_pretty(pg_relation_size(relid)) AS table_size
postgres-# FROM pg_stat_user_tables
postgres-# WHERE (n_dead_tup+n_live_tup) > 0;
 schemaname |     relname      | n_live_tup | n_dead_tup | dead_ratio | table_size
------------+------------------+------------+------------+------------+------------
 public     | pgbench_history  |      99809 |          0 |        0.0 | 5208 kB
 public     | pgbench_accounts |     999973 |      33310 |        3.0 | 130 MB
 public     | pgbench_branches |         10 |        837 |       98.0 | 136 kB
 public     | pgbench_tellers  |        100 |        669 |       86.0 | 168 kB
(4 行)

その後、autovacuumが動作した後の結果を示します。不要レコードの割合が「50レコード ＋ 0.2 × レコード数」を超えているテーブルpgbench_branches、pgbench_tellersの不要レコードがVACUUMによって回収されていることが分かります。

postgres=# SELECT schemaname, relname, n_live_tup, n_dead_tup,
postgres-#        round(n_dead_tup*100/(n_dead_tup+n_live_tup), 1) AS dead_ratio,
postgres-#        pg_size_pretty(pg_relation_size(relid)) AS table_size
postgres-# FROM pg_stat_user_tables
postgres-# WHERE (n_dead_tup+n_live_tup) > 0;
 schemaname |     relname      | n_live_tup | n_dead_tup | dead_ratio | table_size
------------+------------------+------------+------------+------------+------------
 public     | pgbench_history  |     100000 |          0 |        0.0 | 5208 kB
 public     | pgbench_accounts |     999973 |      33310 |        3.0 | 130 MB
 public     | pgbench_branches |         10 |          0 |        0.0 | 136 kB
 public     | pgbench_tellers  |        100 |          0 |        0.0 | 168 kB
(4 行)

REINDEXでインデックスの不要領域を削除する

テーブルやインデックスは、ページの単位でデータが順次格納されていきます。しかし、データの更新を繰り返すことで、ページの内部には空きスペースが増えていきます。テーブルの場合は、空きの多いページがあっても、データのサイズが収まればそのページ内にデータの追加が可能です。しかし、インデックスの場合は、ソート順にデータを入れていく必要があるため、空いているページがあっても、ソート順で入れる場所が決まってしまいます。例えば図3の例において、「50」というデータのインデックスを追加する場合は、ルートページ、および、インターナルページの条件をたどって、2つ目のリーフページに作成されることになります。

図3 B-Treeインデックスの例

そのため、インデックスには1個から2個のデータしかないページも存在することがあります。このようなページを整理し、インデックスに使用されるページ数を減らすためには、定期的にREINDEXコマンドを実行します。その結果、共有メモリーの使用量を減らすことができ、パフォーマンス改善が見込めます。
なお注意点としては、REINDEXコマンドはACCESS EXCLUSIVEのロックをとり、テーブルへのすべてのアクセスを防いでしまうため、業務中の実行は現実的ではありません。保守時に、REINDEXコマンドを実行するよう運用計画を立てることをお勧めします。
PostgreSQL 12では、B-treeインデックスの改善が行われ、空きスペースを減らす対処や、ロック強度の改善が行われています。新しく追加されたREINDEX CONCURRENTLYコマンドは、インデックスの再構築時間が長くなりますが、ロックの強度がSHARE UPDATE EXCLUSIVEレベルに下げられ、業務中にも実行できます。

参考

PostgreSQLの拡張ツールの1つであるpg_repackを利用すれば、PostgreSQL 11以前でも、ロック強度がSHARE UPDATE EXCLUSIVEレベルで済むため、業務中でもインデックスの再編成が可能です。なお、pg_repackはLinuxのみ対応しており、利用時には以下の手順で処理されます。

1. 元のインデックス定義に添って、新しいインデックスをCONCURRENTLYオプションを利用して作成します。
2. システムカタログを更新し、元のインデックスと新しいインデックスを入れ替えます。
3. 元のインデックスを削除します。

Windowsの場合、上記の1.から3.の手順を手動で行うことで同様の対応は可能です。

ANALYZEで統計情報を最新化する

SQLによる検索を実行した際、PostgreSQLのプランナーはテーブルデータの特性を保持している統計情報を参照することで、最適な実行計画を生成します。そのため、統計情報は常に最新化されている必要があります。PostgreSQLでは、autovacuumが実行されると、VACUUM処理を行った後にANALYZE処理も行われるため、常に統計情報は最新化されることになります。このANALYZE処理は、更新の多いテーブルを検出した際に起動されますが、その条件が妥当でない場合には、設定ファイルpostgresql.confのパラメーターで調整が可能です。具体的には、パフォーマンスチューニング手法の「探し」で説明しているEXPLAINコマンドにより実行計画を評価することで統計情報の最新化に問題がないかを判断し、必要に応じて、上述の表にあるautovacuum、autovacuum_max_workers、autovacuum_naptimeを含め、以下のパラメーターの設定値について妥当性を検討してください。

上記は、PostgreSQL全体に影響するパラメーターですが、テーブルごとにANALYZEの条件を変更することができます。CREATE TABLEやALTER TABLEコマンドの格納パラメーターが準備されています。なお、これらのパラメーターを設定する際、autovacuum_enabled（toast.autovacuum_enabled）パラメーターも有効になっている必要があります。

その他に考慮すべき点として、大量のデータ挿入や大量のデータ更新を行った直後は、一時的に統計情報が実際のデータと大きくかけ離れてしまうことに注意が必要です。このタイミングでSQLによる検索を実行すると、正確でない統計情報が使用されりことにより、時間のかかる実行計画が生成され、パフォーマンス劣化につながることがあります。このようなタイミングにおいては、手動でANALYZEコマンドを実行して、統計情報を最新化することも検討してください。
さらに、一時テーブルや外部テーブルはautovacuumの対象ではないため、テーブル更新後は手動でANALYZEコマンドを実行する必要があります。

統計情報の最新化とは別に、統計情報の質を上げてプランナーの予測品質を向上させる方法もあります。統計情報は、ある程度のデータをサンプリングして分析した結果ですが、このサンプリング量を変更することができます。このサンプリング量は、以下のいずれかの方法で変更できます。

• 設定ファイルpostgresql.confのdefault_statistics_targetパラメーター
  デフォルトは100です。SET STATISTICS文で指定されていないカラムやインデックス列が対象です。
• テーブルの各カラムに対して実施するALTER TABLEコマンドのSET STATISTICS文
• 式として定義されたインデックス列に対するALTER INDEXコマンドのSET STATISTICS文

テーブルサイズが大きく、適切な実行計画が作成されない場合などは、実行計画のサンプリング数を増やす検討も行ってください。ただし、サンプリング数を増やすとVACUUM処理やANALYZE処理に時間がかかるようになるため、注意が必要です。具体的には、ANALYZE処理が実行計画を作成する際、サンプリング数を1つ増やす度に、読み出されるテーブルデータは300ページ追加される可能性があります。そのため、実際にANALYZE処理にかかる時間を確認しながらサンプリング数を増やすようにしてください。

VACUUM FREEZEでトランザクションIDを凍結状態にする

始めに、トランザクションIDの凍結処理について説明します。PostgreSQLで更新トランザクションが実行されると、個々のトランザクションを一意に識別できるようトランザクションID（XID）が順次割り振られます。このトランザクションIDは、テーブルにデータを格納する際、レコード（タプル）のシステム列xminに格納しておくことで、現在実行中のトランザクションから見て、過去のトランザクションが格納したレコードは可視状態であり、未来に実行されるトランザクションが格納するレコードは不可視となるよう制御し、多版型同時実行制御（MVCC）に利用されます。

図4 トランザクションID（XID）によるレコードの可視制御

このトランザクションIDには32ビットの符号なし整数を使用しているため、約40億個（2の32乗個）のトランザクションが稼働すると、周回して0から割り振られます。また、現在のトランザクションIDの値を基準にして、それ以前の約20億個を過去のトランザクションID（可視の状態）とし、約20億個先までを未来のトランザクションID（未来のXIDであり、不可視の状態）として識別されます。そして、autovacuumの処理の中で、現在のトランザクションIDより5千万（vacuum_freeze_min_ageパラメーターのデフォルト値）より前の、既にトランザクション処理が終了しているトランザクションIDを凍結状態にして、すべてのトランザクションから可視の状態にします。凍結状態とは、レコード内のトランザクションIDを、無限の過去を意味するFrozenTransactionId（数値は2）に書き換えることです。

図5 トランザクションID（XID）の扱いと凍結範囲

このトランザクションIDの凍結処理には以下の2つの機構が存在し、これらはパフォーマンス劣化につながることがあります。

• autovacuumの強制実行
  データベース内で一番古いトランザクションIDと現在のトランザクションIDとの差（ageと言います）が2億個（autovacuum_freeze_max_ageパラメーターのデフォルト値）を超えるとautovacuumが強制的に実行され、想定外にデータベースの負荷が高くなることがあります。なお、利用できる残りのトランザクションIDの数が少なくなると、新たなトランザクションIDの割り振りが抑止され、インスタンスがエラーで操作できなくなることもあります。
• テーブル内の全ブロックスキャンによる凍結処理
  autovacuumは、処理を効果的に早く完了させるために、可視性マップを参照し、不要レコードがある程度の割合で存在するテーブルのみを対象に処理します。そのため、対象外のテーブル内に古いトランザクションIDが隠れていると、トランザクションIDを完全に凍結状態にすることができません。そこで、テーブルごとに一番古いトランザクションIDと現在のトランザクションIDとの差が1.5億（vacuum_freeze_table_ageパラメーターのデフォルト値）を超えると、テーブル内の全ブロックをスキャンしながら凍結処理が行われます。この処理は、データベースに非常に高い負荷をかけることが想定されます。

これらパフォーマンス劣化への対策として、定期的に、手動でVACUUM FREEZEコマンドを実行することをお勧めします。なお、手動でコマンドを実行する際には、すべてのテーブルの、すべてのトランザクションIDを対象にすることから、非常に時間かかることが想定されます。対象テーブル数の多い場合には特に注意してください。そのため、手動のVACUUM FREEZEコマンドの実行は、保守が可能な時間帯に実施することを検討してください。

手動でVACUUM FREEZEコマンドを実行するタイミングについては、実運用時において、トランザクションIDの消費速度とageの値が大きくなるテーブル数がどれだけあるかを確認し、ageの値が1億5千万を超える前に行うなどの計画を立ててください。テーブルごとにageの値を確認するためには、データベースに接続して以下のSQLを実行します。

postgres=# SELECT c.oid::regclass as table_name,
postgres-#        greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
postgres-# FROM pg_class c
postgres-# LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
postgres-# WHERE c.relkind IN ('r', 'm');
                 table_name                 |   age
--------------------------------------------+---------
 t1                                         | 2571836
 table_info                                 |    4874
 time_data                                  |     687
 pg_statistic                               | 2571843
 tbl                                        |     619
 ・・・

また、データベースクラスタ作成時に作られるデフォルトのデータベースtemplate0、template1、postgresについても同様に、更新のないテーブルに対してのトランザクションIDの凍結処理が必要になります。
データベースごとにageの値を確認するためには、以下のSQLを実行します。

postgres=# SELECT datname, age(datfrozenxid) FROM pg_database;
     datname     |   age
-----------------+---------
 postgres        | 2571843
 db              | 2571843
 template1       | 2571843
 template0       | 2571843
 testdb          | 2571843
(5 rows)

参考

PostgreSQLは、テーブル内の各レコードにシステム列（oid, tableoid, xmin, cmin, xmax, cmax, ctid）を持っており、その情報はレコードデータのヘッダー部分などに格納されています。

図6 レコード（ヒープタプル）の構造

このうち、xminには挿入時のトランザクションID、xmaxには削除時のトランザクションIDが格納されます。トランザクションIDの凍結時には、xmin、xmaxにFrozenTransactionIdが設定されるか、あるいは、レコードデータ内に含まれるフラグ領域（t_infomask）に凍結状態のフラグが立ちます（通常は後者です）。

2.2 パラメーター調整で高速化する

設定ファイルpostgresql.confに記述できるパラメーターの中で、PostgreSQLの内部処理をコントロールすることでパフォーマンス改善が可能なパラメーターについて説明します。

• work_memパラメーターの値を大きくし、メモリーで処理させる
• 遺伝的問い合わせ最適化（GEQO）により、遅くなっていないか確認する
• インデックスが利用されやすくするために、プランナーが使用する推定コストを調整する
• 並列処理のパラメーターを変更し高速化する（パラレルクエリ）
• スタンバイで参照処理を行う場合、WALの適用を遅らせることでコンフリクトを軽減する

work_memパラメーターの値を大きくし、メモリーで処理させる

SQLの実行において、ソートやハッシュなどメモリーを利用する処理に関しては、設定ファイルpostgresql.confのwork_memパラメーターで指定したサイズを超えないように実行計画が作成されます。そのため、work_memの値が必要量より小さいと、例えばソート処理の場合には、メモリー上で処理できるハッシュが使われず、ディスクを利用する方式で実行計画を作成してしまうため、実行に時間がかかる結果となってしまいます。したがって、メモリーに余裕がある限りはwork_memを大きくし、最適な実行計画が作成されるようにします。
注意点として、work_memパラメーターを大きくし過ぎると、すべてのサーバープロセスで大量のメモリーを確保することになり、問題となることがあります。そのような場合は、必要な時にだけ、SET文によりwork_memの値を変更し、終わったら元の値に戻すような対応を検討してください。

遺伝的問い合わせ最適化（GEQO）により、遅くなっていないか確認する

多くのテーブルにアクセスするSQLの場合、実行計画を作成する際に結合順番などパターンが多過ぎるため、全パターンを試すのではなく遺伝的問い合わせ最適化（GEQO）（注1）を用いて実行計画を決定します。GEQOを用いた場合、実行計画の全パターンを試している訳ではないため、作成された実行計画が最適なパターンにならない場合があります。そのため、設定ファイルpostgresql.confのgeqoパラメーターを無効にしたり、geqo_thresholdパラメーターを調整したりするなどして、作成される実行計画を比較しながら、最適な実行計画が選ばれるよう検討していく必要があります。

• 注1
  ヒューリスティック（発見的）検索法を用いて実行計画を選択する演算手法であり、多くのテーブルを結合するような複雑な問い合わせに対して、計画時間を軽減します。

参考

実行計画に遺伝的問い合わせ最適化が使用される場合、pg_hint_planによる結合順番の制御ができません。そのため、SQL文単位でヒント句を有効にしたい場合は、SET文でgeqo_thresholdパラメーターの値を一時的に大きくして、遺伝的問合せを効かせないようにするなどの対処が必要になります。

インデックスが利用されやすくするために、プランナーが使用する推定コストを調整する

テーブルのデータ量が多い場合、テーブルのほとんどすべてのデータにアクセスするようなSQLでなければ、基本的にインデックスを利用して検索したほうが速く処理できます。そのため、seq_page_costパラメーターの値を高くする、または、random_page_costパラメーターの値を低くして、インデックス検索のコストを下げることでインデックスが利用されやすくなり、パフォーマンス改善が見込めます。また、effective_cache_sizeパラメーターをより高い値を設定することで、よりインデックスが利用されやすくなるため、こちらもパフォーマンス改善が見込めます。

並列処理のパラメーターを変更し高速化する（パラレルクエリ）

大量のデータを検索する時には、大量のデータにアクセスするため時間がかかってしまいます。その際には、並列処理を用いて並列にデータにアクセスすることで、全体の処理時間を短縮することができます。並列処理は、スキャンや結合、集約などで利用できます。また、CREATE INDEXコマンドでB-treeインデックスを作成する際にも並列処理による高速化が期待できます。
並列処理が行われる場合は、以下のように、実行計画のノードにGatherまたはGather Mergeが存在します。

postgres=# EXPLAIN SELECT t1.id, t1.data, t2.data FROM tbl1 t1, tbl2 t2
postgres-# WHERE t1.foreign_id = t2.id AND t1.id BETWEEN 10 AND 20;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Nested Loop  (cost=1000.29..16604.41 rows=1 width=70)
   ->  Gather  (cost=1000.00..16596.10 rows=1 width=41)
         Workers Planned: 2
         ->  Parallel Seq Scan on tbl1 t1  (cost=0.00..15596.00 rows=1 width=41)
               Filter: ((id >= 10) AND (id <= 20))
   ->  Index Scan using tbl2_pkey on tbl2 t2  (cost=0.29..8.31 rows=1 width=37)
         Index Cond: (id = t1.foreign_id)
(7 rows)

なお、並列処理については、デフォルトで有効になっており、以下のパラメーターで設定値の変更が可能です。ただし、パラメーター値を大きくし過ぎると、CPU資源を使い切ってしまい他の業務に影響する可能性があるので、注意が必要です。

参考

パラレルクエリの実行時には、利用可能なCPUが不足する場合があります。また、クエリの開始時にワーカーの不足やワーカーが存在しないなどの理由で、ワーカーを新たに追加できずに、別のクエリで利用中のCPUを割り当てることで過負荷が発生する可能性があります。
「FUJITSU Software Enterprise Postgres」では、パラレルクエリのワーカー数を決定する際に現時点のCPU負荷を考慮して、ワーカー数を決定します。また、クエリ実行中でも、利用可能なワーカーに空きができた場合には追加のワーカーを割り当てることができます。これにより、クエリのパフォーマンスをより向上させることができます。

スタンバイで参照処理を行う場合、WALの適用を遅らせることでコンフリクトを軽減する

ストリーミングレプリケーション構成時にスタンバイ側で参照処理を行うと、WALの適用処理が同一資源にアクセスすることでコンフリクト（競合）が発生することがあります。WALの適用処理は、コンフリクトを検出後に一定時間が経過すると、参照処理をキャンセルさせてWALの適用処理を続行します。すると、参照処理はアプリケーション側から再実行させる必要があるため、結果的にパフォーマンスに影響を与えます。

図7 スタンバイの参照処理でコンフリクトが発生するしくみ

この対策として、まずは、プライマリー側での排他ロックや各種オブジェクトの更新 / 削除の必要性の検証や、スタンバイ側での参照処理（トランザクション）の処理時間を短くする検討が必要です。それでも解決できない場合は、プライマリー側で行っている処理を確認の上、それに応じて以下のパラメーターを設定することでコンフリクトを軽減させることができます。

• プライマリー側で、テーブル空間、データベース、テーブル、インデックスが削除される場合

  max_standby_archive_delayパラメーター、max_standby_streaming_delayパラメーターを調整することで、コンフリクトが発生して処理がキャンセルされるまでの時間を延ばします。ただし、プライマリー側とスタンバイ側でデータの乖離が大きくなる、ディスクを圧迫する、および、昇格までの時間がかかるようになるため、システムの要件に合わせて設定する必要があります。

  パラメーター	デフォルト値	説明
  max_standby_archive_delay	30秒	WALアーカイブからWALデータを読み込んで適用する処理が待たされた際、スタンバイサーバーの参照処理をキャンセルするまでの待機時間を設定。-1を指定すると、コンフリクトする参照処理が完了するまで待ち続ける。
  max_standby_streaming_delay	30秒	ストリーミングレプリケーションからWALデータを受け取って適用する処理が待たされた際、スタンバイサーバーの参照処理をキャンセルするまでの待機時間を設定。-1を指定すると、コンフリクトする参照処理が完了するまで待ち続ける。

• プライマリー側で、VACUUMまたはHOTによる行データの回収処理が実行される場合（頻繁な更新がある場合）

  vacuum_defer_cleanup_ageパラメーターを調整することで、指定したトランザクション数だけ行データの削除を遅らせます。hot_standby_feedbackパラメーターをonに設定することで、スタンバイが必要とするデータの削除を遅らせることができます。ただし、プライマリー側のVACUUM処理において回収できない行データが増えることで、テーブルサイズが大きくなる可能性があるため、注意が必要です。また、上記のmax_standby_archive_delayパラメーター、max_standby_streaming_delayパラメーターも併せて調整することで効果がでることもあります。

  パラメーター	デフォルト値	説明
  vacuum_defer_cleanup_age	0	プライマリーサーバーにおいて、VACUUMおよびHOT更新が不要になった行データを回収する際、指定されたトランザクションの数だけ遅延させる。
  hot_standby_feedback	off	onに設定することで、ホットスタンバイがスタンバイサーバー上で現在処理を行っている参照処理について、プライマリーまたは上位サーバーにフィードバックを送る。

なお、コンフリクトを完全に無くすことは非常に難しいため、これらの対策と併せて、アプリケーション側の参照処理には、再実行処理を入れておく必要があります。

2.3 資源分割とロック回避で高速化する

テーブルスペースやパーティショニングを利用することで高速に処理する方法、また、PostgreSQL内部処理に必要なロックレベルを意識した設計が必要な点について説明します。

• テーブルスペースを活用する
• テーブルのパーティショニングを活用する
• VACUUMとSQLとのロック競合を軽減する

テーブルスペースを活用する

テーブルスペースを利用することで、頻繁にアクセスされるテーブルをI/O性能の良いディスクに格納されるよう配置したり、テーブルやインデックスごとに複数のディスクにデータを配置したりすることができます。その結果、ディスクI/Oが分散することによるパフォーマンス向上が見込めます。例えば、同時に複数人でアクセスするテーブルがある場合には、テーブルとインデックスを別のテーブルスペースに分けるなどの検討を行ってください。

参考

ディスクI/Oの分散については、使用するディスクにRAID構成を組んでおくことで、さらに効果が見込めます。参照主体ならRAID 5、更新主体ならRAID 1＋0 の構成が一般的です。

テーブルのパーティショニングを活用する

テーブルのパーティショニングを利用することにより、テーブル内のデータを分散して格納できます。パーティションに分割された個々のパーティションテーブルは、別々のテーブルスペースに配置し、I/O負荷を分散させることでパフォーマンス向上が見込めます。
また、テーブルの検索時には、パーティションキーによる条件を追加することで、必要なパーティションのみを検索するパーティション・プルーニングが利用でき、アクセスするデータ量を絞り込めることで処理の高速化が見込めます。なお、パーティション・プルーニングを利用するためには、SQL全体を見てパーティションキーを正しく選択するよう設計する必要があります。パーティショニングの詳細は、以下を参照してください。

• パーティショニングの概要
• パーティショニングにおける性能向上のしくみ

VACUUMとSQLとのロック競合を軽減する

VACUUMやANALYZEなどのようなPostgreSQL内部の機構により発生するロックは、テーブル単位のロックに加えてページレベルのロックも必要になります。そのため、クライアントからのトランザクション処理で使用するロックレベルによっては、VACUUMやANALYZEと競合が発生し、ロック待ちによるパフォーマンス劣化につながることがあるため、注意が必要です。その対策として、負荷が低い時にVACUUMコマンドやANALYZEコマンドを手動で実行しておき、データベースの負荷が高い時にそれらが動かないようにするなどの対応が必要になります。また、可能であれば、長いトランザクション処理については、長時間テーブルをロックし続ける可能性があるので、処理を分割して短いトランザクションになるよう対処することも検討してください。
なお、VACUUM FULLコマンドやREINDEXコマンド、あるいは、業務中にDDLなどの定義変更を行う場合は、特にテーブルに対するロックレベルが強く、他のSQLの実行ができなくなるなどの弊害が生じます。このようなSQLを実行する場合は、保守時間中に実施するなどの検討が必要です。

PostgreSQLのパフォーマンスチューニングの重要なポイントである「基盤」についての考え方と手法について解説しました。このシリーズはここまでとなります。本シリーズを通して、PostgreSQLのパフォーマンスチューニングの一助となると幸いです。

2021年8月27日更新

こちらもおすすめ

• チューニング ～ データベースチューニング ～
• 【豆知識】SQLチューニングアプローチの考え方
• 技術者に聞く：パーティショニングにおける性能改善への取り組み

本コンテンツに関するお問い合わせ