pg_reorg

オンラインVacumm処理コマンド

インストール方法

下記のURLから最新のソースファイルを入手します。 ​http://www.postgresql.org/ftp/projects/pgFoundry/reorg/

ファイルを解凍したら
$　mv pg_reorg /usr/local/postgresql-8.3.14/contrib/
へ移動
$　cd /usr/local/postgresql-8.3.14/contrib/pg_reorg
$ make
$ su
$ make install

実行サンプル

testという名前のデータベースのfooという1つのテーブルに対してオンライン VACUUM FULL を行うには、下記のコマンドを実行します。

$ pg_reorg --no-order --table foo -d test

名前
pg_reorg -- PostgreSQLデータベース内のテーブルに対して、参照/更新処理をブロックせずに再編成を行います。
概要

pg_reorg [OPTIONS]

オプション OPTIONS には以下を指定できます。詳細はオプションを参照してください。

    * 固有オプション
          o -o [--order-by] columns [,...]
          o -n [--no-order]
          o -t [--table] table
          o -T [--wait-timeout] seconds
          o -Z [--no-analyze]
    * 接続オプション
          o -a, --all : 全てのデータベースに対して実行します
          o -d, --dbname=DBNAME : 接続するデータベース
          o -h, --host=HOSTNAME : データベースサーバホスト、またはソケットディレクトリ
          o -p, --port=PORT : データベースサーバのポート
          o -U, --username=USERNAME : このユーザとして接続します
          o -w, --no-password : パスワードの入力を促しません
          o -W, --password : パスワード入力を強制します
    * 一般オプション
          o -e, --echo : サーバに送信するSQLを表示します
          o -E, --elevel=LEVEL : ログ出力レベルを設定します
          o --help : ヘルプを表示し、終了します
          o --version : バージョン情報を出力し、終了します

説明

pg_reorg は、PostgreSQLデータベース内のテーブルを再編成(行の並び替え)するユーティリティです。 clusterdb と異なり、参照/更新処理をブロックしません。再編成の方式として、以下のいずれか1つを選択できます。

    * オンライン CLUSTER (cluster index順に行を並び替える)
    * ユーザの指定した順に行を並び替える
    * オンライン VACUUM FULL (行の詰め合わせを行う)

このユーティリティを使用するためには、以下のことに注意をしてください。

    * このユーティリティは、スーパーユーザのみが実行することができます。
    * 対象のテーブルはPRIMARY KEYを持っている必要があります。

例

testというデータベースをオンライン CLUSTER するには、下記のコマンドを実行します。

$ pg_reorg test

testという名前のデータベースのfooという1つのテーブルに対してオンライン VACUUM FULL を行うには、下記のコマンドを実行します。

$ pg_reorg --no-order --table foo -d test

オプション

pg_reorg では、下記のコマンドライン引数を指定できます。

固有オプション

pg_reorg を実行する対象と並び替えの基準を指定するパラメータです。何も指定されていない場合は、cluster index順にオンライン CLUSTER を行います。この2つを同時に指定することはできません。

-n
--no-order
    オンライン VACUUM FULL の処理を行います。
-o columns [,...]
--order-by columns [,...]
    指定したカラムをキーにオンライン CLUSTER を行います。

-t table
--table=table
    オンライン CLUSTER 、または、オンライン VACUUM FULL を行うテーブルを指定します。このオプションが指定されていない場合は、対象となったデータベースに存在する全ての対象テーブルに対して処理を行います。 
-T seconds
--wait-timeout=seconds
    再編成完了直前に一瞬だけ排他ロックを取得しますが、この排他ロックが取得できるまで待機する秒数を指定します。この秒数が経過してもロックが取得できない場合には、対象のテーブルにアクセスしている他の全てのクエリを取り消します。また、サーバのバージョンが 8.4 またはそれ以降の場合には、指定した秒数の2倍経過してもロックを取得できない場合には、強制的に切断します。デフォルトは60秒です。 
-Z
--no-analyze
    再編成後に ANALYZE を行いません。このオプションが指定されていない場合は、再編成後に ANALYZE します。

接続オプション

PostgreSQLに接続するためのパラメータです。 --allと--dbnameまたは--tableを同時に指定することはできません。

-a
--all
    対象となる全てのデータベースに対してオンライン CLUSTER、または、オンラインVACUUM FULLを行います。
-d DBNAME
--dbname=DBNAME
    オンライン CLUSTER、または、オンライン VACUUM FULL を行うデータベース名を指定します。データベース名が指定されておらず、-a（または--all）も指定されていない場合、データベース名はPGDATABASE環境変数から読み取られます。この変数も設定されていない場合は、接続時に指定したユーザ名が使用されます。
-h HOSTNAME
--host=HOSTNAME
    サーバが稼働しているマシンのホスト名を指定します。ホスト名がスラッシュから始まる場合、Unixドメインソケット用のディレクトリとして使用されます。
-p PORT
--port=PORT
    サーバが接続を監視するTCPポートもしくはUnixドメインソケットファイルの拡張子を指定します。
-U USERNAME
--username=USERNAME
    接続するユーザ名を指定します。
-w
--no-password
    パスワードの入力を促しません。サーバがパスワード認証を必要とし、かつ、.pgpassファイルなどの他の方法が利用できない場合、接続試行は失敗します。バッチジョブやパスワードを入力するユーザが存在しない場合にこのオプションは有用かもしれません。 
-W
--password
    データベースに接続する前に、強制的にパスワード入力を促します。
    サーバがパスワード認証を要求する場合 自動的にパスワード入力を促しますので、これが重要になることはありません。しかし、サーバにパスワードが必要かどうかを判断するための接続試行を無駄に行います。こうした余計な接続試行を防ぐために -W の入力が有意となる場合もあります。 

一般オプション

-e
--echo
    サーバに送信するSQLを表示します。
-E
--elevel
    ログ出力レベルを設定します。 DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, PANIC から選択します。デフォルトは INFO です。
--help
    使用方法について表示します。
--version
    バージョン情報を表示します。

環境変数

PGDATABASE
PGHOST
PGPORT
PGUSER
    デフォルトの接続パラメータです。

また、このユーティリティは、他のほとんどの PostgreSQL ユーティリティと同様、libpq でサポートされる環境変数を使用します。詳細については、環境変数の項目を参照してください。
トラブルシューティング

pg_reorg の実行に失敗した場合にエラーが表示されます。想像されるエラー原因と対処を示します。

致命的なエラーで終了した場合、手動によるクリーンアップを行う必要があります。クリーンアップは、エラーが発生したデータベースに対して、$PGHOME/share/contrib/uninstall_pg_reorg.sql を実行し、その後、$PGHOME/share/contrib/pg_reorg.sql を実行します。

pg_reorg : reorg database "template1" ... skipped
    --allオプションを指定した際に、pg_reorg がインストールされていないデータベースに対して表示されます。
    pg_reorg スキーマのインストールを行ってください。
ERROR: pg_reorg is not installed
    --dbnameで指定したデータベースにpg_reorg がインストールされていません。
    pg_reorg のインストールを行ってください。
ERROR: relation "table" has no primary key
    指定したテーブルにPRIMARY KEYが存在していません。
    対象のテーブルにPRIMARY KEYの作成を行ってください。(ALTER TABLE ADD PRIMARY KEY)
ERROR: relation "table" has no cluster key
    指定したテーブルに CLUSTER KEYが存在していません。
    対象のテーブルに CLUSTER KEYの作成を行ってください。(ALTER TABLE CLUSTER)
pg_reorg : query failed: ERROR: column "col" does not exist
    --order-by で指定したカラムが対象のテーブルに存在していません。
    対象のテーブルに存在するカラムを指定してください。
ERROR: permission denied for schema reorg
    操作を行おうとした対象に権限がありません。
    スーパーユーザで操作を行ってください。
pg_reorg : query failed: ERROR: trigger "z_reorg_trigger" for relation "tbl" already exists
    操作を行おうとした対象にpg_reorg が処理のために作成するトリガと同名のものが存在しています。
    トリガの改名か削除を行ってください。
pg_reorg : trigger conflicted for tbl
    操作を行おうとした対象にpg_reorg が処理のために作成するトリガより後に実行されるトリガが存在しています。
    トリガの改名か削除を行ってください。

使用上の注意と制約

pg_reorg を使用する際には、以下の制約があります。以下の制約に関する操作を行った場合の動作は保証されません。注意してください。
一時テーブルへの操作

pg_reorg では、一時テーブルは操作の対象外です。
GiSTインデックスの使用

インデックス種別がGiSTとなっているインデックスがクラスタインデックスとなっているテーブルはpg_reorg コマンドを使用して操作を行うことはできません。これは、GiSTインデックスのソート順序は一意ではないため、ORDER BYによるソートが行えないためです。
DDLコマンドの発行

pg_reorg の実行中には、VACUUM と ANALYZE 以外 のDDL操作は行わないでください。多くの場合、pg_reorg は失敗しロールバックされます。しかし、以下の操作ではデータが破損するため、非常に危険です。

TRUNCATE
    削除した行が pg_reorg 実行後には復元しています。操作結果が消失します。
CREATE INDEX
    スワップされない索引が残る可能性があります。データの不整合が生じます。
ALTER TABLE ... ADD COLUMN
    追加された値が全てNULLに置換されてしまう可能性があります。データが消失します。
ALTER TABLE ... ALTER COLUMN TYPE
    実行するとスキーマで定義された型と実際の格納状態に齟齬をきたします。データの不整合が生じます。
ALTER TABLE ... SET TABLESPACE
    pg_reorg 実行後にrelfilenodeとの不整合が起こるため、対象のテーブルに対する参照/更新操作時にエラーが発生します。

詳細

pg_reorg は reorg スキーマに作業用テーブルを作成し、そこでデータの並び替えを行います。最後にシステムカタログを直接書き換えることで、元のテーブルと名前を交換しています。

REDHAT5.2以下では、pg_reorgのコンパイルでエラーとなる原因はULLIONG_MAXの定義が無いため。

/usr/local/postgresql-8.3.x/contrib/pg_reorg/bin/pgut/pgut.cに修正必要

/* old gcc doesn't have LLONG_MAX. */
#ifndef LLONG_MAX
#if defined(HAVE_LONG_INT_64) || !defined(HAVE_LONG_LONG_INT_64)
#define LLONG_MAX               LONG_MAX
#else
#define LLONG_MAX               INT64CONST(0x7FFFFFFFFFFFFFFF)
#endif
#endif

#define ULLONG_MAX      (LLONG_MAX * 2ULL + 1)    /* この行を追加 */

その後の設定

$ cd /usr/local/postgresql-8.3.x/contrib/pg_reorg/
$ make
$ make install
$ cd lib
$ make install
$ cd /usr/local/pgsql/bin/
$ /usr/local/pgsql/bin/psql -U postgres -f ../share/contrib/pg_reorg.sql -d topaz

/usr/local/pgsql-8.3.7/bin/pg_reorg -U postgres --no-order --table 連携ログ -d topaz

バキューム対象のテーブルの不要行を調査する（n_dead_tup)

SELECT
    relname, n_live_tup, n_dead_tup, round(n_dead_tup*100/n_live_tup,2) AS ratio
FROM
    pg_stat_user_tables WHERE relname = 'テーブル名';

 relname | n_live_tup | n_dead_tup | ratio
------------+------------+------------+-------
 テーブル名 |     454340 |      23699 |  5.00

Autovacuumと vacuum full の違いについて説明しますね。
Autovacuum(vacuum analyze)が行う処理は、「削除フラグのついた領域を書き込み可能にする」ことです。
一方、vacuumが行う処理は、「削除あるいは書き込み可能となっている領域を解放し前詰め（デフラグ）する」ことです。
そのため、大量データ挿入更新処理がある場合、Autovacuumだけですと、最大で入力したデータの２倍のファイルサイズが必要となります。
とはいえ、運用上それほど巨大なファイルへの入出力がないなら、あえてvacuum fullをかける必要はないでしょうし、おそらく今回の問題の原因ではないと思います。

今回、速度が遅くなった最大の理由は、インデックスの非最適化によるものと思われます。
実はautovacuumはもちろん、full vacuumでも、インデックスだけは最適化されません(ただし9,0からはvacuum fullでindex最適化まで行うようになりました）。

そのため、インデックスの非最適化によって、速度が目で見てわかるほど落ちることがあります。

VACUUM が適切に実行されずに性能が低下していることを前提として回答します。

更新頻度の高い巨大なテーブルが存在する場合には、自動バキュームの設定をテーブルごとに調整したほうがいいと思います。

デフォルトの設定のままでは、UPDATE や DELETE で不要になった行が 
50 行 (autovacuum_vacuum_threshold) + 全行数の 2 割 (autovacuum_vacuum_scale_factor) 

を超えた場合に VACUUM が実行されます。

例えば、テーブルの行数が 100,000 行の場合には不要な行が 20,050 行を超えると VACUUM が実行されますが、100,000,000 の場合には 20,050,000 行
を超えるまで実行されません。
同じ 2 割であっても 2 万行と 2 千万行では性能への影響が違います。

PostgreSQL 8.3 であれば、pg_autovacuum テーブルにエントリを追加すればテーブルごとに自動バキュームの設定を行うことができます (参考 URL を見てください)。

あと、その他に気になることとしては、デフォルトでは自動バキュームで同時に起動できる VACUUM は 3 つ (autovacuum_max_workers) までなので、
更新頻度の高いテーブルが大量に存在する場合には、自動バキュームが特定のテーブルへの VACUUM につきっきりになってしまい、VACUUM の実行されないテーブルが
発生して性能が低下してしまう場合があります。

http://www.postgresql.jp/document/8.3/html/catalog-pg-autovacuum.html

設定を解除する場合

$ /usr/local/pgsql/bin/psql -U postgres -f ../share/contrib/uninstall_pg_reorg.sql -d topaz