運用開始後に、想定とは異なるシステム負荷やデータの偏りなど、様々な要因でSQLの実行に遅延が発生する可能性があります。運用に影響を与える遅延が発生していないかを監視することで、遅延の早期検出ができます。また、監視により原因究明や遅延の解消などのリカバリ作業に必要な時間を短縮することができます。
遅延SQLの発生原因の1つとして、統計情報の変化があります。統計情報を起因とした遅延の場合、遅延発生前の統計情報を復元することで解消するものがあります。このため、統計情報を定期的にバックアップして、遅延発生に備えてください。
運用に向けた作業の流れ
遅延SQLの監視と統計情報のバックアップを行うために、必要な作業の流れを以下に示します。
対応時期 | 作業項目 | 作業内容 |
|---|---|---|
環境構築時 | (1)pg_dbms_stats拡張機能の有効化 | pg_dbms_stats拡張機能をCreate Extension文で有効化します。 |
(2)遅延SQL監視のログ設定 | postgresql.confに、log_min_duration_statementパラメタなどを設定します。 | |
システム開発時 | (3)統計情報のバックアップ用スクリプトの作成と確認 (*1) | 統計情報やVacuumの運用に応じた、統計情報のバックアップを行うための、スクリプトを用意します。 |
運用中 | (4)統計情報の定期バックアップ | (3)で作成したスクリプトを実行し、統計情報をバックアップします。 |
(5)遅延SQLの監視と遅延許容時間の調整 | 遅延SQLの発生の監視、および、遅延許容時間の設定を調整し、運用に最適な設定に調整します。 | |
遅延SQL発生時 | (6)遅延SQLの発生原因の調査 | 遅延SQLが発生した際に、SQL遅延発生原因を調査し、対策を検討します。 |
(7)統計情報の復元 (*2) | バックアップしていた統計情報を復元し、固定化することで遅延SQLを解消します。 |
*1: 運用中でも可能ですが、運用への動作影響を加味して、運用開始前に対応してください。
*2: 原因が統計情報でないと判明した場合は、その原因に応じた対処を別途実施します。
以下に具体的な作業内容を示します。
(1) pg_dbms_stats拡張機能の有効化
統計情報のバックアップと復元を行うには、pg_dbms_stats拡張機能のエクスポート機能およびインポート機能が必要となりますので、pg_dbms_stats拡張機能を有効にしてください。
有効化する方法は、“導入ガイド”の“pg_dbms_statsのセットアップ”を参照してください。
(2) 遅延SQL監視のログ設定
遅延SQL監視にはデータベースのログを利用します。
postgresql.confに以下の設定を行ってください。なお、遅延SQLの原因究明を行う際に日時が重要になるので、形式やファイル名には日付や時刻を含む設定にしてください。
パラメータ | 指定値 |
|---|---|
logging_collector | on |
log_line_prefix | 以下のようなフォーマットを指定し、時刻や実行アプリケーションなど、調査に必要となる情報を付加します。 [%t]%u %d %p[%l] |
log_filename | 過去の実行実績の確認が必要になるため、日時がわかるように設定します。 postgresql.%Y-%m-%d |
log_min_duration_statement | 300000 |
ポイント
log_min_duration_statementパラメータの指定値が小さい場合は、遅延とみなす必要のないSQLでもログに出力され、大きすぎると遅延したSQLを検出できません。そのため、設定値が想定できない場合は、運用開始時は小さめに設定し、運用中に監視しつつ、大きな値に随時調整してください。
(3) 統計情報のバックアップ用スクリプトの作成と確認
統計情報バックアップには、pg_dbms_stats拡張機能のエクスポート機能を利用します。
バックアップは、復元だけでなく、遅延SQLの原因究明にも利用するため、バックアップの実行時刻が重要な意味を持ちます。そのため、必ずバックアップ時刻がわかるように以下のようなスクリプトを作成してください。
なお、エクスポート機能を利用してデータベース単位でエクスポートするサンプルスクリプトを提供しています。スキーマ単位への変更など、利用者自身での修正でより柔軟な対応をすることも可能になりますので、スクリプトは実行カレントにコピーしてから利用してください。
サンプルスクリプトは以下に格納されています。また、“<x>”は製品のバージョンを示します。
/opt/fsepv<x>server64/share/doc/extension/export_plain_stats-<x>.sql.sample
以下は、実行カレントに“export_plain_stats-<x>.sql”としてコピーした場合の作成例です。
expprt PATH=/opt/fsepv<x>server64/bin:$PATH export CURRENTDIR=/pg_dbms_stats/backup pushd "${CURRENTDIR}" # make file name EXECDATE=`date '+%Y%m%d%H%M%S'` FILENAME=pg_dbms_stats_back.${EXECDATE}.dmp # export psql -d database1 -f export_plain_stats-<x>.sql # rename dump file mv export_stats.dmp ${FILENAME} popd
(4) 統計情報の定期バックアップ
“(3) 統計情報のバックアップ用スクリプトの作成と確認”で作成したスクリプトを実行して統計情報を常時バックアップします。
統計情報の更新方法に合わせて、以下のタイミングでバックアップを実行してください。
統計情報はautovacuumが実行された際に更新されます。
そのため、OSのcronコマンドやタスクマネージャなどのスケジュール機能を用いて“(3) 統計情報のバックアップ用スクリプトの作成と確認”で作成したスクリプトを定期的に実行し、統計情報をファイルにバックアップしてください。
autovacuumの最短の実行間隔をログから取得し、取得したautovacuumの最短の実行間隔より短い間隔で統計情報のバックアップを取得してください。autovacuumのログは、log_autovacuum_min_durationパラメータに0を設定することで取得できます。
ANALYZEコマンドなどを使用して、利用者が統計情報の更新をコントロールしている場合には、統計情報の更新と同時に、統計情報のバックアップを行ってください。
バックアップした統計情報は、性能問題が発生した場合に遅延SQLの原因を究明する調査資料に使用します。そのため、運用でのトラブル発生から解決までに要すると想定される期間、保存しておいてください。
ポイント
バックアップに必要な容量は主にデータベースに含まれるオブジェクト(スキーマやテーブルなど)の数で変わります。スクリプトの動作確認の際のバックアップファイルから必要な容量を概算し、確保してください。
バックアップした統計情報を復元するには、バックアップ元のスキーマやテーブルなどのオブジェクトの名称が合致している必要があります。運用中にオブジェクトの名称などを変更する場合には、統計情報を復元する際にオブジェクト定義と合致しているかを確認できるようにバックアップファイル名や保存先を変更してください。
(5) 遅延SQLの監視と遅延許容時間の調整
log_min_duration_statementパラメータに設定した時間以上に遅延しているSQLを監視します。
本節の設定では以下のような形式で出力されます。
[2024-04-30 10:20:11 JST]user1 postgres 3414[1]LOG: duration: 301001.541 ms statement: SELECT pg_sleep(301) [2024-04-30 10:26:12 JST]user1 postgres 3414[1]LOG: duration: 302002.321 ms statement: SELECT pg_sleep(302)
“(6) 遅延SQLの発生原因の調査”を参照し、原因究明と対策を行ってください。
現在の設定では冗長なログが出力されるだけなので、log_min_duration_statementパラメータを大きく変更後、リロードして有効にしてください。
(6) 遅延SQLの発生原因の調査
SQLが遅延する原因には様々な要素があるため、まず原因を特定する必要があります。
ここでは統計情報の変化を起因としたSQLの遅延を基準にした調査方法を示します。
なお、以下の場合は、統計情報を起因とした遅延は考えにくいため、その場合は別の観点（I/Oやシステム負荷等）からの調査を推奨します。
SQL文に検索条件を含まない場合
pg_hint_plan拡張機能を利用したSQL文の場合
サーバログから遅延したSQLを確認し、そのSQLで使用しているスキーマを特定します。
運用環境とは別のデータベースクラスタ（開発環境など）に、問題が発生した環境と同じデータベース名、同じテーブル定義での環境（以下、再現環境と呼びます）を作成します。再現環境でもpg_dbms_stats拡張は必要ですので、pg_dbms_stats拡張機能を有効にしてください。
運用環境からpg_dumpコマンドにて、対象のテーブル定義を取得します。
この時にデータは不要なため、運用環境からpg_dumpコマンドの-sオプションを使用し、抽出したテーブル定義を利用します。
以下は、調査対象のスキーマが、データベースdatabase1のスキーマschema_1の場合に、ファイルddl_schema.dmpに出力した例です。
pg_dump -d database1 -s schema_1 > ddl_schema.dmp
ddl_schema.dmpを使って、再現環境のデータベースdatabase1に調査用のスキーマschema_1を作成します。
psql -d database1 -f ddl_schema.dmp
SQLが遅延した原因を特定するため、まず、統計情報に遅延の原因があるかをバックアップした統計情報を使って確認します。再現環境のスキーマに対し、pg_dbms_statsのインポート機能(dbms_stats.import_schema_stats)で統計情報をインポートします。
以下は、スキーマschema_1の統計情報をバックアップファイル“pg_dbms_stats_back.20240422011223.dmp”から復元した例です。
psql -d database1 -c
"SELECT dbms_stats.import_schema_stats('schema_1','pg_dbms_stats_back.20240422011223.dmp')"その後、遅延SQLに対するEXPLAINコマンドを実行し、実行結果(問い合わせ計画)を取得します。
上記以前の、遅延SQLが安定稼働していた時間帯にバックアップした統計情報を別途インポートし、統計情報を復元します。
例えば、対象のSQLが、定期バッチ処理の毎日で実行されるSQLであれば、前日の同時刻のものを使用します。オンライン処理であれば、同様の業務アプリが実行されると想定される、過去の時間帯のものを利用します。
以下は、バッチ処理で使用されるSQLと想定した場合の、1日前のバックアップファイル“pg_dbms_stats_back.20240421010001.dmp”から復元した例です。
psql -d database1 -c
"SELECT dbms_stats.import_schema_stats('schema_1','pg_dbms_stats_back.20240421010001.dmp')"こちらも同様に、遅延SQL に対するEXPLAINコマンドの実行結果(問い合わせ計画)を取得します。
4.と5.の実行結果(問い合わせ計画)を比較し、その結果から以下のように対応してください。
問い合わせ計画が同じ、または、5.の問い合わせ計画の実行想定時間の方が大きい場合
統計情報以外の問題が考えられます（システム負荷やインデックスの偏り、I/Oなど）ので、別の観点での調査を行ってください。
上記以外の場合
統計情報の更新により、最適な問い合わせ計画が選択されていないことが問題と考えられます。
“(7) 統計情報の復元”を参照して、運用環境に安定稼働していた時間帯の統計情報をインポートし、復旧します。
(7) 統計情報の復元
運用環境の統計情報を、遅延発生前のものに復元します。手順は検証時と同様に、以下で実施します。
psql -d database1 -c
"SELECT dbms_stats.import_schema_stats('schema_1','pg_dbms_stats_back.20240422000000')"