はじめに
巨大なPostgreSQLデータベースクラスタをpg_basebackupを使ってバックアップを取得するとき、大量データロード後のANALYZE・・・。
こういう結構時間がかかる処理って、どこまで処理が進んでいるのか、やっぱり気になりますよね。
今回は、PostgreSQL 13の新機能、pg_stat_progress_basebackupとpg_stat_progress_analyzeを使って、pg_basebackup処理やANALYZE処理の進捗確認がどんな感じでできるのか試してみました。
注意
まだ、β版にもなっていない時期なので、ビューの列などは今後、追加/削除/変更があるかもしれません。brata1がリリースされたら、列の変更等がないか再度確認する予定。
pg_stat_progress_basebackup
pg_basebackupは、データベースクラスタ全体をバックアップファイル(あるいはレプリケーションのベースとして)取得するユーティリティですが、データベースクラスタのサイズが大きいときには当然ながら時間がかかります。
このpg_stat_progress_basebackupは、pg_basebackupなどのアプリケーション（ストリーミングレプリケーションプロコロルを使うなら、pg_basebackupに限らないかもしれないけど）などのアプリケーションの実行中の進捗を占めるビューです。
なので、以前のpg_start_basebackup() → OSコマンドによるバックアップ処理 → pg_stop_backup()という手順の場合には、このビューによる進捗状況の監視はできません。
pg_stat_progress_basebackupの内容
バックアップ処理のの進捗状況を見るにはpg_stat_progress_basebackupというビューを検索します。このビューの内容はBase Backup Progress Reportingに説明があります。
| 列名 | 型 | 内容 |
|---|---|---|
| pid | integer | walsenderのプロセスID |
| phase | text | 現在の処理フェーズ。フェーズの詳細については後述。 |
| backup_total | bigint | ストリーミングされるデータの合計量。 これは、データベースファイルのストリーミングフェーズの開始時に推定および報告されます。 ストリーミングデータベースファイルの段階でデータベースが変更され、WALログが後でバックアップに含まれる可能性があるため、これは概算にすぎないことに注意してください。 ストリーミングされるデータの量が推定合計サイズを超えると、これは常にbackup_streamedと同じ値になります。 pg_basebackupで見積もりが無効になっている場合（つまり、-no-estimate-sizeオプションが指定されている場合）、ここはNULLになります。 |
| backup_streamed | bigint | ストリーミングされたデータの量。 このカウンタは、フェーズがデータベースファイルをストリーミングしているとき、またはwalファイルを転送しているときにのみ進みます。 |
| tablespaces_total | bigint | ストリーミングされるテーブルスペースの総数。 |
| tablespaces_streamed | bigint | ストリーミングされたテーブルスペースの数。 このカウンタは、データベースファイルをストリーミングしているフェーズがのときにのみ進みます。 |
(pg_basebackupユーティリティの)-no-estimate-sizeオプション！
そういうのもあるのか。
バックアップフェーズ
phase列に設定される値は以下の値域を持つ。
| フェーズ | 意味 |
|---|---|
| initializing | walsenderプロセスがバックアップを開始する準備をしています。 このフェーズは非常に短いと予想されます。 |
| waiting for checkpoint to finish | walsenderプロセスはpg_start_backupを実行してベースバックアップを取得するためのセットアップを行い、バックアップ開始チェックポイントが完了するのを待っています。--checkpointがデフォルトの場合、チェックポイント契機が来るまではこの状態になるっぽい。 |
| estimating backup size | ベースバックアップとしてストリーミングされるデータベースファイルの総量を推定するフェーズ |
| streaming database files | データベースファイルをベースバックアップとしてストリーミングしているフェーズ。 |
| waiting for wal archiving to finish | pg_stop_backup)を実行してバックアップを完了し、ベースバックアップに必要なすべてのWALファイルが正常にアーカイブされるのを待機しています。 --wal-method = noneまたは--wal-method = streamのいずれかがpg_basebackupで指定されている場合、このフェーズが完了するとバックアップが終了します。 |
| transferring wal files | WAL送信者プロセスは現在、バックアップ中に生成されたすべてのWALログを転送しています。 このフェーズは、-wal-method = fetchがpg_basebackupで指定されている場合、walアーカイブが完了するフェーズを待機した後に発生します。 このフェーズが完了すると、バックアップが終了します。 |
試してみた
以下のようなテーブルを定義しておく。
postgres=# \d backup_progress
Table "public.backup_progress"
Column | Type | Collation | Nullable | Default
----------------------+--------------------------+-----------+----------+---------
ts | timestamp with time zone | | |
pid | integer | | |
phase | text | | |
backup_total | bigint | | |
backup_streamed | bigint | | |
tablespaces_total | bigint | | |
tablespaces_streamed | bigint | | |
postgres=#
このテーブルに対して以下のようなINSERT文を定期的に発行し、別端末からpg_basebackupユーティリティを起動する。
INSERT INTO backup_progress
SELECT clock_timestamp() ts, * FROM pg_stat_progress_basebackup ;
で、pg_basebackupユーティリティが終了した後で、backup_progressテーブルに対して、以下のようなSELECT文を発行する。
postgres=# SELECT * FROM backup_progress ORDER by ts;
ts | pid | phase | backup_total | backup_streamed | tablespaces_total | tablespaces_streamed
-------------------------------+------+-------------------------------------+--------------+-----------------+-------------------+----------------------
2020-03-28 12:08:16.138108+00 | 2311 | waiting for checkpoint to finish | 0 | 0 | 0 | 0
（略）
2020-03-28 12:08:16.978108+00 | 2311 | waiting for checkpoint to finish | 0 | 0 | 0 | 0
2020-03-28 12:08:17.07976+00 | 2311 | streaming database files | 3292090368 | 5392896 | 1 | 0
2020-03-28 12:08:17.181573+00 | 2311 | streaming database files | 3292090368 | 10215424 | 1 | 0
（略）
2020-03-28 12:09:08.735793+00 | 2311 | streaming database files | 3292090368 | 3292052992 | 1 | 0
2020-03-28 12:09:09.127488+00 | 2311 | waiting for wal archiving to finish | 3292100096 | 3292100096 | 1 | 1
(317 rows)
検索結果から進捗状況を見てみる。
今回の検証モデルでは、デフォルトテーブルスペースしかないデータベースクラスタを対象にしているので、結構シンプルな結果になる。
- 最初に、
waiting for checkpoint to finishのフェーズが始まる。これはCHECKPOINT処理が終わるまで継続する。 - 次に。
streaming database filesフェーズになる。このときに、注目すべきは、backup_totalの数値とbackup_streamedの数値。この比率がデータベースファイルのバックアップの進捗状況となる。- バックアップ中にリレーションのサイズが増えることがあれば、
backup_totalの数値よりもbackup_streamedの数値が上回ることになるのだろう（未検証）
- バックアップ中にリレーションのサイズが増えることがあれば、
- 最後に、
waiting for wal archiving to finishフェーズになる。それが終わるとバックアップが完了する。
pg_stat_progress_analyze
ANALYZEの処理も、巨大なテーブルが大量にある場合には、そこそこ時間がかかるので、そういう時に、進捗情報が見れるとありがたいときもあると思います。
pg_stat_progress_analyzeの内容
ANALZYE実行の進捗状況を見るにはpg_stat_progress_analyzeというビューを検索します。このビューの内容はANALYZE Progress Reportingに説明があります。
| 列名 | 型 | 内容 |
|---|---|---|
| pid | integer | ANALYZE処理が実行されているバックエンドプロセス。 |
| datid | oid | ANALYZE対象となるデータベースのoid。 |
| datname | name | ANALYZE対象となるデータベース名。 |
| relid | oid | ANALYZE処理中のテーブルのoid。データベース全体へのANALYZEの実行時には、ここがくるくる変わるのだろう。 |
| phase | text | 処理フェーズを示す文字列。→詳細 個人的には略称と正式名称の2つが欲しいなあ・・・。(そういう要望も既に出ているかも。あとでcommitfest内容を確認しよ) |
| sample_blks_total | bigint | サンプリングされるヒープブロックの総数。 |
| sample_blks_scanned | bigint | スキャンされたヒープブロックの数。 |
| ext_stats_total | bigint | 拡張統計の数。 |
| ext_stats_computed | bigint | 計算された拡張統計の数。 このカウンターは、拡張統計を計算しているフェーズのときにのみ進みます。 |
| child_tables_total | bigint | 子テーブルの数 |
| child_tables_done | bigint | スキャンされた子テーブルの数。 このカウンターは、継承されたサンプル行を取得しているフェーズがのときにのみ進みます。 |
| current_child_table_relid | oid | 現在スキャンされている子テーブルのOID。 このフィールドは、継承されたサンプル行を取得しているフェーズのときにのみ有効です。 |
注目すべきは拡張統計情報に関する列(ext_stats_total, ext_stats_computed)や、パーティションテーブルに関する列(child_tables_total, child_tables_done, current_child_table_relid)があることか。
phase
すごーく雑にいうと、ANALYZE処理は対象となるリレーションに対して、順々に以下のフェーズの処理を行っているのだろう。→PostgreSQL Document ANALYZE phases
| フェーズ | 意味 |
|---|---|
| initializing | スキャン開始準備のフェーズ。これを表示させるのは結構難しそう。 |
| acquiring sample rows | サンプル行を取得するためのテーブルスキャン中のフェーズ。ここのフェーズがテーブルが大きいと時間がかかる部分。 |
| acquiring inherited sample rows | 子テーブルをスキャン中。 child_tables_total、child_tables_done、およびcurrent_child_table_relidには、このフェーズの進捗情報が含まれている。 |
| computing statistics | テーブルスキャン中に取得されたサンプル行から統計を計算するフェーズ。 |
| computing extended statistics | テーブルスキャン中に取得されたサンプル行から統計を計算するフェーズ。 拡張統計はPostgreSQL 10から入った機能。以前調べたPostgreSQL 10の拡張統計情報の記事→multi-column optimizer statistics |
| finalizing analyze | 計算した統計情報内容をpg_classに反映するフェーズ。 このフェーズが完了すると、（そのテーブル対する）ANALYZEは終了する。 |
試してみた
以下のようなテーブル構成のデータベースに対して、ANALZYE処理を実行したときの進捗状況を監視するときの例を示す。
testdb=# \d
List of relations
Schema | Name | Type | Owner
--------+------------------+-------------------+----------
public | test | partitioned table | postgres
public | test2 | table | postgres
public | test_p0 | table | postgres
public | test_p1 | table | postgres
public | test_p2 | table | postgres
(6 rows)
test_p0, test_p1, test_p2 はtestテーブルのパーティションテーブルとなる。
検証手順
このデータベースに対してANALYZEをかけつつ、別の端末から、pg_stat_progress_analyzeテーブルを定期的に検索する。で、検索結果をそのまま垂れ流すと後で見るのが大変になるから、その検索結果を以下のようなテーブルに格納する。
testdb=# \d analyze_progress
Table "public.analyze_progress"
Column | Type | Collation | Nullable | Default
---------------------+-----------------------------+-----------+----------+---------
relname | name | | |
datname | name | | |
phase | text | | |
sample_blks_total | bigint | | |
sample_blks_scanned | bigint | | |
child_tables_total | bigint | | |
child_tables_done | bigint | | |
ts | timestamp without time zone | | |
testdb=#
（上記のテーブルは拡張統計情報に関する検索結果は含んでいない）
で、以下のようなINSERT文を定期的に実行しつつ(こういうときには、psqlの\watchメタコマンドが便利）、別端末からデータベース全体に対するANALYZEを実行する。
INSERT INTO analyze_progress
SELECT c.relname, pa.datname, phase, sample_blks_total, sample_blks_scanned, child_tables_total, child_tables_done, clock_timestamp()
FROM pg_stat_progress_analyze pa JOIN pg_class c ON (pa.relid = c.oid);
で、ANALYZE処理を終わった後に、このanalyze_progressテーブルの内容を以下のSELECT文で確認する。
testdb=# SELECT relname, phase, sample_blks_total, sample_blks_scanned, child_tables_total, child_tables_done FROM analyze_progress ORDER BY ts;
relname | phase | sample_blks_total | sample_blks_scanned | child_tables_total | child_tables_done
------------------+---------------------------------+-------------------+---------------------+--------------------+-------------------
test | acquiring inherited sample rows | 34102 | 2878 | 3 | 0
test | acquiring inherited sample rows | 34102 | 7478 | 3 | 0
test | acquiring inherited sample rows | 34102 | 10893 | 3 | 0
（略）
test | acquiring inherited sample rows | 34084 | 34084 | 3 | 1
test | acquiring inherited sample rows | 34078 | 2038 | 3 | 2
test | acquiring inherited sample rows | 34078 | 6771 | 3 | 2
（略）
test | acquiring inherited sample rows | 34078 | 34078 | 3 | 2
test | computing statistics | 34078 | 34078 | 3 | 3
（略）
test | computing statistics | 34078 | 34078 | 3 | 3
test | finalizing analyze | 34078 | 34078 | 3 | 3
test | finalizing analyze | 34078 | 34078 | 3 | 3
test_p0 | acquiring sample rows | 34102 | 493 | 0 | 0
test_p0 | acquiring sample rows | 34102 | 5095 | 0 | 0
（略）
test_p2 | computing statistics | 34078 | 34078 | 0 | 0
test_p2 | finalizing analyze | 34078 | 34078 | 0 | 0
test_p2 | finalizing analyze | 34078 | 34078 | 0 | 0
test2 | acquiring sample rows | 102263 | 1736 | 0 | 0
test2 | acquiring sample rows | 102263 | 6359 | 0 | 0
（略）
test2 | computing statistics | 102263 | 102263 | 0 | 0
test2 | finalizing analyze | 102263 | 102263 | 0 | 0
test2 | finalizing analyze | 102263 | 102263 | 0 | 0
(1337 rows)
検索結果から進捗状況を見てみる。
- まず、
testテーブルに対する処理が行われる。acquiring inherited sample rowsフェーズがしばらく続く。-
sample_blks_scannedの数が増加していく。 - が、一旦、この数が減ってまた増加している。(子テーブル毎にリセットするのかな？）
-
- その後、
testテーブルに対するcomputing statisticsフェーズが始まる。 - その後、
testテーブルに対するfinalizing analyzeフェーズになる。これが終わると、testテーブルに対する一連の処理が終わる。 - 次に、
testテーブルの子テーブルにあたるtest_p0テーブルに対するacquiring sample rowsフェーズが始まる。 - その後に
computing statisticsフェーズになる。 - そして
finalizing analyzeフェーズを経て、test_p0テーブルに対する処理が終わる。 - 以下、
test_p1,test_p2も同様に処理される。 - パーティションテーブルの場合、全パーティションテーブル数が最初に
child_tables_totalにセットされ、パーティションテーブルが処理される度に、child_tables_doneの数がカウントアップされていく。 - その後に、別テーブル
test2に対しても同様に、acquiring sample rows→computing statistics→finalizing analyzeのフェーズを経る。 - 各テーブルごとの処理に繊維するごとに、
sample_blks_totalの値はリセットされ、sample_blks_scannedの値がカウントアップしていく。
わかったこと
さて、このANALYZE検証で自分なりに理解したこと。
- データベース内にいくつのテーブルがあるかどうかの総数/総ブロック数を算出するわけではない。
- なので、データベース内に何百もテーブルがある場合、残り何テーブルの処理が残っているのか、という進捗状況の確認はできない。
- パーティションテーブルについては、子テーブル総数を最初に算出するので、
child_tables_totalとchild_tables_doneの数から、おおよその処理状況は推測できるようになった。
おわりに
今回は、PostgreSQL 13から利用可能なバックアップ/ANALYZE処理の進捗状況を監視するビューを紹介しました。
PostgreSQLの適用領域が広がり、巨大なデータを扱うようになるとバックアップやANALYZE処理にも時間がかかるようになってきています。そういうときに、こういうビューを使って進捗状況を監視できるようになると、待ち時間のストレスが軽減されるかもしれません。
ただ、ANALYZEに関しては、パーティションテーブルでない、多数のテーブルが存在するケースでの進捗状況確認に課題があるな、という印象を持ちました。