Help us understand the problem. What is going on with this article?

PostgreSQL 12がやってくる(7) - CREATE INDEX/REINDEX/CLUSTER/VACUUM FULL command progress monitor

More than 1 year has passed since last update.

はじめに

今回はPostgreSQL 12の監視に関連する新機能をちょっと試してみた。

時間のかかるメンテナンスコマンドの状況をみたい!

PostgreSQLのメンテナンス系のコマンドだけど、巨大なテーブルに対して実行すると時間が結構かかるものが多い。

これらの時間がかかるメンテナンス系のコマンドの進捗状況を監視する機構はPostgreSQL 9.6から入っていたが、これまではVACUUMのみに対応していた。PostgreSQl 12では更に4種類のコマンドに対応するようになった。

コマンド名 内容 対応バージョン
VACUUM データベースの不要領域の回収とデータベースの解析(オプション)を行う PostgreSQl 9.6
VACUUM FULL1 「完全な」バキュームを実行。容量を削減する場合に使う。 PostgreSQl 12
CLUSTER インデックスに従ってテーブルをクラスタ化する PostgreSQl 12
CREATE INDEX 新しいインデックスを定義する PostgreSQL 12
REINDEX インデックスを再構築する PostgreSQL 12
ANALYZE データベースに関する統計を収集する 未対応

(注:マニュアルはPostgreSQL 11日本語版をリンクしている)

先日もとある案件(PostgreSQL 10)で、数百GBのテーブルに対するCREATE INDEXを実行することがあって、「いつ終わるんだよー!」と思いながら作業していたんですが、この機能が入ることによって、途中経過が見られるようになり、多少は苛つきが収まるかもしれない・・・ということで試してみました。

基本的な仕組み

こういった実行に長い時間がかかるメンテナンス用のコマンドの進捗状況を参照するために、PostgreSQL 10から、進捗情報のビューが提供されるようになった。
VACUUM(通常のVACUUM用)のビューはPostgreSQL 10から提供されていたが、PostgreSQL 12ではこれに加えて、2つのビューが追加され3つの進捗情報参照用のビューが使えるようになった。

ビュー名 内容 サポートバージョン
pg_stat_progress_vacuum 通常のVACUUMコマンドの進捗状況を表示する PostgreSQL 9.6
pg_stat_progress_cluster CLUSTERコマンドおよびVACUUM FULLコマンドの進捗状況を報告する。
VACUUM FULLコマンドはpg_stat_progress_vacuumではなく、こっちに進捗が表示されるので要注意。
PostgreSQL 12
pg_stat_progress_create_index CREATE INDEXコマンドおよびREINDEXコマンドの進捗状況を表示する。 PostgreSQL 12

これらのビューの内容を定期的にSELECTすることで進捗状況をアドホックに参照することができる。
また、こうしたビューの検索結果をINSERT文の結果として挿入することで、こうしたメンテナンスコマンドの進捗情報を蓄積することもできる。

pg_stat_progress_cluster

このビューには、CLUSTERコマンドとVACUUM FULLコマンドの進捗状況が格納される。PostgreSQL 9.xのどこかのバージョンで、CLUSTERコマンドとVACUUM FULLコマンドは「だいたい同じ」ような動作(テーブルを物理的に再生成する)になったので、CLSTERもVACUUM FULLもpg_stat_progress_clusterの報告内容は同じだろうと思ったら、実は微妙に違っていたりする。

列名 VACUUM FULL CLUSTER
command VACUUM FULL CLUSTER
phase "seq scanning heap"と表示される。
僅かなタイミングによっては他のステータスが表示されるのかもしれないが、だいたいはこれ。
"index scanning heap"と表示される。
僅かなタイミングによっては他のステータスが表示されるのかもしれないが、だいたいはこれ。
heap_tuples_scanned スキャンしたタプル数。 同左
heap_tuples_written 書き込んだタプル数。
だいたいheap_tuples_scannedと同じ値になってる。
同左
heap_blks_total 最初から最後まで不変。 0のまま。変化なし。
heap_blks_scanned 0からheap_blks_totalまで増加 0のまま。変化なし。

CLUSTERは論理的なタプル数はカウントするが、VACUUM FULLとは違って、物理的なブロック数はカウントしない、ということなんだろうか。

あと、CLUSTER/VACUUM FULLに共通するが、進捗状況の報告はスキャンまでである。その後、テーブル再生成のための書き込み処理を行っているはずだが、その部分については、進捗ビューには報告されない(pg_stat_progress_cluster を検索しても行がでてこなくなる)。
大きいテーブルの再生成だと、このファイル書き込みの時間もそれなりにかかるんだけど、残念ながら今の仕組みだと、その部分の報告はされない。
なので、pg_stat_progress_cluster の検索結果が0件になっても、コマンド自体は終了していない、という状況が発生する。
ここが少々イケてない気がするが・・・。

pg_stat_progress_create_index

このビューはCREATE INDEX/REINDEXの進捗状況を表示しれくれるビュー。
一応、両方のコマンドを試してみましたが、だいたい同じような情報を表示してくれます。違いはcommand列くらい。

列名 CREATE INDEX REINDEX
command CREATE INDEX REINDEX
phase 各インデックス毎に、以下のように遷移するようだ。
building index: scanning table
building index: loading tuples in tree
同左
blocks_total "scanning table" phaseの最初の頃にこの値がセットされるっぽい。
"loading tuples in tree" phaseになると0になる。
同左
blocks_done "scanning table" phaseの進捗を示すのがこの値か。
blocks_totalの値になるまで増加していく。
"loading tuples in tree" phaseになると0になる。
同左
tuples_total "loading tuples in tree" phaseに入るとこの値がセットされる。 同左
tuples_done "loading tuples in tree" phaseの進捗を示すのがこの値。0からtuples_totalの数値まで増加していく。
この値がtuples_totalに到達すると、そのインデックスに関する処理はだいたい終わり。
同左

pg_stat_progress_create_index の内容を別テーブルに挿入して、そこから上記の表に関する列のみ抜き出すと、こんな感じになる。
(pciというのは、pg_stat_progress_create_indexの内容+タイムスタンプを付与した情報をINSERTしていく自作のテーブル)

testdb=# SELECT command, phase, relid, blocks_total, blocks_done, tuples_total, tuples_done FROM pci;
   command    |                 phase                  | relid | blocks_total | blocks_done | tuples_total | tuples_done
--------------+----------------------------------------+-------+--------------+-------------+--------------+-------------
 REINDEX      | building index: scanning table         | 16442 |       163935 |       56837 |            0 |           0
 REINDEX      | building index: scanning table         | 16442 |       163935 |      163935 |            0 |           0
 REINDEX      | building index: loading tuples in tree | 16442 |            0 |           0 |     10000000 |     4334900
 REINDEX      | building index: loading tuples in tree | 16442 |            0 |           0 |     10000000 |     7440215
 REINDEX      | building index: loading tuples in tree | 16442 |            0 |           0 |     10000000 |     9282493
 REINDEX      | building index: loading tuples in tree | 16442 |            0 |           0 |     10000000 |    10000000
 REINDEX      | building index: loading tuples in tree | 16442 |            0 |           0 |     10000000 |    10000000

VACUUM FULLやCLUSTERと違って、pg_stat_progress_create_index ビューから検索結果が得られない(0件になる)状態から、CREATE INDEX/REINDEXコマンド自体が終了するまでの時間差はそれほどない気がする。

おわりに

PostgreSQLのメンテナンス系コマンドの進捗を確認するためのシステムビューの機能を軽く試してみました。
とにかく巨大なテーブルに対する、こうしたコマンドは時間がかかるので、「あとどのくらいで終わるんだろう」のヒントが得られるのは、運用を行うオペレータにとっては嬉しい機能かもしれません。

pg_stat_progress_create_index に関しては、パーティション固有の情報も出力するようですが、そこは今回は未検証です(単に、パーティション構成の検証環境を組むのが面倒だったので・・・すみません)。


  1. 個人的には、もうSQLコマンドとしてもVACUUMとは別の項でいいんじゃないかという気もしている・・・(だいたいCLUSTERと同様のことをするので)。 

nuko_yokohama
ぬこ@横浜です/ にゃーん / 趣味でポスグレをやってる者だ/ 名もなく 貧しく 太ましく
https://supleks.jp/u/8999.html
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away