Changes between Initial Version and Version 1 of Postgresql無停止オンラインVacumm処理コマンド


Ignore:
Timestamp:
Nov 7, 2016, 1:56:37 PM (7 years ago)
Author:
admin
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Postgresql無停止オンラインVacumm処理コマンド

    v1 v1  
     1= pg_reorg =
     2
     3オンラインVacumm処理コマンド
     4
     5== インストール方法 ==
     6
     7下記のURLから最新のソースファイルを入手します。
     8http://www.postgresql.org/ftp/projects/pgFoundry/reorg/
     9{{{
     10ファイルを解凍したら
     11$　mv pg_reorg /usr/local/postgresql-8.3.14/contrib/
     12へ移動
     13$　cd /usr/local/postgresql-8.3.14/contrib/pg_reorg
     14$ make
     15$ su
     16$ make install
     17}}}
     18
     19== 実行サンプル ==
     20testという名前のデータベースのfooという1つのテーブルに対してオンライン VACUUM FULL を行うには、下記のコマンドを実行します。
     21{{{
     22$ pg_reorg --no-order --table foo -d test
     23}}}
     24
     25
     26{{{
     27名前
     28pg_reorg -- PostgreSQLデータベース内のテーブルに対して、参照/更新処理をブロックせずに再編成を行います。
     29概要
     30
     31pg_reorg [OPTIONS]
     32
     33オプション OPTIONS には以下を指定できます。詳細はオプションを参照してください。
     34
     35    * 固有オプション
     36          o -o [--order-by] columns [,...]
     37          o -n [--no-order]
     38          o -t [--table] table
     39          o -T [--wait-timeout] seconds
     40          o -Z [--no-analyze]
     41    * 接続オプション
     42          o -a, --all : 全てのデータベースに対して実行します
     43          o -d, --dbname=DBNAME : 接続するデータベース
     44          o -h, --host=HOSTNAME : データベースサーバホスト、またはソケットディレクトリ
     45          o -p, --port=PORT : データベースサーバのポート
     46          o -U, --username=USERNAME : このユーザとして接続します
     47          o -w, --no-password : パスワードの入力を促しません
     48          o -W, --password : パスワード入力を強制します
     49    * 一般オプション
     50          o -e, --echo : サーバに送信するSQLを表示します
     51          o -E, --elevel=LEVEL : ログ出力レベルを設定します
     52          o --help : ヘルプを表示し、終了します
     53          o --version : バージョン情報を出力し、終了します
     54
     55説明
     56
     57pg_reorg は、PostgreSQLデータベース内のテーブルを再編成(行の並び替え)するユーティリティです。 clusterdb と異なり、参照/更新処理をブロックしません。再編成の方式として、以下のいずれか1つを選択できます。
     58
     59    * オンライン CLUSTER (cluster index順に行を並び替える)
     60    * ユーザの指定した順に行を並び替える
     61    * オンライン VACUUM FULL (行の詰め合わせを行う)
     62
     63このユーティリティを使用するためには、以下のことに注意をしてください。
     64
     65    * このユーティリティは、スーパーユーザのみが実行することができます。
     66    * 対象のテーブルはPRIMARY KEYを持っている必要があります。
     67
     68例
     69
     70testというデータベースをオンライン CLUSTER するには、下記のコマンドを実行します。
     71
     72$ pg_reorg test
     73
     74testという名前のデータベースのfooという1つのテーブルに対してオンライン VACUUM FULL を行うには、下記のコマンドを実行します。
     75
     76$ pg_reorg --no-order --table foo -d test
     77
     78オプション
     79
     80pg_reorg では、下記のコマンドライン引数を指定できます。
     81
     82固有オプション
     83
     84pg_reorg を実行する対象と並び替えの基準を指定するパラメータです。何も指定されていない場合は、cluster index順にオンライン CLUSTER を行います。この2つを同時に指定することはできません。
     85
     86-n
     87--no-order
     88    オンライン VACUUM FULL の処理を行います。
     89-o columns [,...]
     90--order-by columns [,...]
     91    指定したカラムをキーにオンライン CLUSTER を行います。
     92
     93-t table
     94--table=table
     95    オンライン CLUSTER 、または、オンライン VACUUM FULL を行うテーブルを指定します。このオプションが指定されていない場合は、対象となったデータベースに存在する全ての対象テーブルに対して処理を行います。
     96-T seconds
     97--wait-timeout=seconds
     98    再編成完了直前に一瞬だけ排他ロックを取得しますが、この排他ロックが取得できるまで待機する秒数を指定します。この秒数が経過してもロックが取得できない場合には、対象のテーブルにアクセスしている他の全てのクエリを取り消します。また、サーバのバージョンが 8.4 またはそれ以降の場合には、指定した秒数の2倍経過してもロックを取得できない場合には、強制的に切断します。デフォルトは60秒です。
     99-Z
     100--no-analyze
     101    再編成後に ANALYZE を行いません。このオプションが指定されていない場合は、再編成後に ANALYZE します。
     102
     103接続オプション
     104
     105PostgreSQLに接続するためのパラメータです。 --allと--dbnameまたは--tableを同時に指定することはできません。
     106
     107-a
     108--all
     109    対象となる全てのデータベースに対してオンライン CLUSTER、または、オンラインVACUUM FULLを行います。
     110-d DBNAME
     111--dbname=DBNAME
     112    オンライン CLUSTER、または、オンライン VACUUM FULL を行うデータベース名を指定します。データベース名が指定されておらず、-a（または--all）も指定されていない場合、データベース名はPGDATABASE環境変数から読み取られます。この変数も設定されていない場合は、接続時に指定したユーザ名が使用されます。
     113-h HOSTNAME
     114--host=HOSTNAME
     115    サーバが稼働しているマシンのホスト名を指定します。ホスト名がスラッシュから始まる場合、Unixドメインソケット用のディレクトリとして使用されます。
     116-p PORT
     117--port=PORT
     118    サーバが接続を監視するTCPポートもしくはUnixドメインソケットファイルの拡張子を指定します。
     119-U USERNAME
     120--username=USERNAME
     121    接続するユーザ名を指定します。
     122-w
     123--no-password
     124    パスワードの入力を促しません。サーバがパスワード認証を必要とし、かつ、.pgpassファイルなどの他の方法が利用できない場合、接続試行は失敗します。バッチジョブやパスワードを入力するユーザが存在しない場合にこのオプションは有用かもしれません。
     125-W
     126--password
     127    データベースに接続する前に、強制的にパスワード入力を促します。
     128    サーバがパスワード認証を要求する場合 自動的にパスワード入力を促しますので、これが重要になることはありません。しかし、サーバにパスワードが必要かどうかを判断するための接続試行を無駄に行います。こうした余計な接続試行を防ぐために -W の入力が有意となる場合もあります。
     129
     130一般オプション
     131
     132-e
     133--echo
     134    サーバに送信するSQLを表示します。
     135-E
     136--elevel
     137    ログ出力レベルを設定します。 DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, PANIC から選択します。デフォルトは INFO です。
     138--help
     139    使用方法について表示します。
     140--version
     141    バージョン情報を表示します。
     142
     143環境変数
     144
     145PGDATABASE
     146PGHOST
     147PGPORT
     148PGUSER
     149    デフォルトの接続パラメータです。
     150
     151また、このユーティリティは、他のほとんどの PostgreSQL ユーティリティと同様、libpq でサポートされる環境変数を使用します。詳細については、環境変数の項目を参照してください。
     152トラブルシューティング
     153
     154pg_reorg の実行に失敗した場合にエラーが表示されます。想像されるエラー原因と対処を示します。
     155
     156致命的なエラーで終了した場合、手動によるクリーンアップを行う必要があります。クリーンアップは、エラーが発生したデータベースに対して、$PGHOME/share/contrib/uninstall_pg_reorg.sql を実行し、その後、$PGHOME/share/contrib/pg_reorg.sql を実行します。
     157
     158pg_reorg : reorg database "template1" ... skipped
     159    --allオプションを指定した際に、pg_reorg がインストールされていないデータベースに対して表示されます。
     160    pg_reorg スキーマのインストールを行ってください。
     161ERROR: pg_reorg is not installed
     162    --dbnameで指定したデータベースにpg_reorg がインストールされていません。
     163    pg_reorg のインストールを行ってください。
     164ERROR: relation "table" has no primary key
     165    指定したテーブルにPRIMARY KEYが存在していません。
     166    対象のテーブルにPRIMARY KEYの作成を行ってください。(ALTER TABLE ADD PRIMARY KEY)
     167ERROR: relation "table" has no cluster key
     168    指定したテーブルに CLUSTER KEYが存在していません。
     169    対象のテーブルに CLUSTER KEYの作成を行ってください。(ALTER TABLE CLUSTER)
     170pg_reorg : query failed: ERROR: column "col" does not exist
     171    --order-by で指定したカラムが対象のテーブルに存在していません。
     172    対象のテーブルに存在するカラムを指定してください。
     173ERROR: permission denied for schema reorg
     174    操作を行おうとした対象に権限がありません。
     175    スーパーユーザで操作を行ってください。
     176pg_reorg : query failed: ERROR: trigger "z_reorg_trigger" for relation "tbl" already exists
     177    操作を行おうとした対象にpg_reorg が処理のために作成するトリガと同名のものが存在しています。
     178    トリガの改名か削除を行ってください。
     179pg_reorg : trigger conflicted for tbl
     180    操作を行おうとした対象にpg_reorg が処理のために作成するトリガより後に実行されるトリガが存在しています。
     181    トリガの改名か削除を行ってください。
     182
     183使用上の注意と制約
     184
     185pg_reorg を使用する際には、以下の制約があります。以下の制約に関する操作を行った場合の動作は保証されません。注意してください。
     186一時テーブルへの操作
     187
     188pg_reorg では、一時テーブルは操作の対象外です。
     189GiSTインデックスの使用
     190
     191インデックス種別がGiSTとなっているインデックスがクラスタインデックスとなっているテーブルはpg_reorg コマンドを使用して操作を行うことはできません。これは、GiSTインデックスのソート順序は一意ではないため、ORDER BYによるソートが行えないためです。
     192DDLコマンドの発行
     193
     194pg_reorg の実行中には、VACUUM と ANALYZE 以外 のDDL操作は行わないでください。多くの場合、pg_reorg は失敗しロールバックされます。しかし、以下の操作ではデータが破損するため、非常に危険です。
     195
     196TRUNCATE
     197    削除した行が pg_reorg 実行後には復元しています。操作結果が消失します。
     198CREATE INDEX
     199    スワップされない索引が残る可能性があります。データの不整合が生じます。
     200ALTER TABLE ... ADD COLUMN
     201    追加された値が全てNULLに置換されてしまう可能性があります。データが消失します。
     202ALTER TABLE ... ALTER COLUMN TYPE
     203    実行するとスキーマで定義された型と実際の格納状態に齟齬をきたします。データの不整合が生じます。
     204ALTER TABLE ... SET TABLESPACE
     205    pg_reorg 実行後にrelfilenodeとの不整合が起こるため、対象のテーブルに対する参照/更新操作時にエラーが発生します。
     206
     207詳細
     208
     209pg_reorg は reorg スキーマに作業用テーブルを作成し、そこでデータの並び替えを行います。最後にシステムカタログを直接書き換えることで、元のテーブルと名前を交換しています。
     210
     211}}}
     212
     213REDHAT5.2以下では、pg_reorgのコンパイルでエラーとなる原因はULLIONG_MAXの定義が無いため。
     214
     215/usr/local/postgresql-8.3.x/contrib/pg_reorg/bin/pgut/pgut.cに修正必要
     216{{{
     217/* old gcc doesn't have LLONG_MAX. */
     218#ifndef LLONG_MAX
     219#if defined(HAVE_LONG_INT_64) || !defined(HAVE_LONG_LONG_INT_64)
     220#define LLONG_MAX               LONG_MAX
     221#else
     222#define LLONG_MAX               INT64CONST(0x7FFFFFFFFFFFFFFF)
     223#endif
     224#endif
     225
     226#define ULLONG_MAX      (LLONG_MAX * 2ULL + 1)    /* この行を追加 */
     227}}}
     228
     229その後の設定
     230{{{
     231$ cd /usr/local/postgresql-8.3.x/contrib/pg_reorg/
     232$ make
     233$ make install
     234$ cd lib
     235$ make install
     236$ cd /usr/local/pgsql/bin/
     237$ /usr/local/pgsql/bin/psql -U postgres -f ../share/contrib/pg_reorg.sql -d topaz
     238}}}
     239
     240{{{
     241/usr/local/pgsql-8.3.7/bin/pg_reorg -U postgres --no-order --table 連携ログ -d topaz
     242}}}
     243バキューム対象のテーブルの不要行を調査する（n_dead_tup)
     244{{{
     245
     246SELECT
     247    relname, n_live_tup, n_dead_tup, round(n_dead_tup*100/n_live_tup,2) AS ratio
     248FROM
     249    pg_stat_user_tables WHERE relname = 'テーブル名';
     250
     251 relname | n_live_tup | n_dead_tup | ratio
     252------------+------------+------------+-------
     253 テーブル名 |     454340 |      23699 |  5.00
     254
     255}}}
     256{{{
     257Autovacuumと vacuum full の違いについて説明しますね。
     258Autovacuum(vacuum analyze)が行う処理は、「削除フラグのついた領域を書き込み可能にする」ことです。
     259一方、vacuumが行う処理は、「削除あるいは書き込み可能となっている領域を解放し前詰め（デフラグ）する」ことです。
     260そのため、大量データ挿入更新処理がある場合、Autovacuumだけですと、最大で入力したデータの２倍のファイルサイズが必要となります。
     261とはいえ、運用上それほど巨大なファイルへの入出力がないなら、あえてvacuum fullをかける必要はないでしょうし、おそらく今回の問題の原因ではないと思います。
     262
     263今回、速度が遅くなった最大の理由は、インデックスの非最適化によるものと思われます。
     264実はautovacuumはもちろん、full vacuumでも、インデックスだけは最適化されません(ただし9,0からはvacuum fullでindex最適化まで行うようになりました）。
     265
     266そのため、インデックスの非最適化によって、速度が目で見てわかるほど落ちることがあります。
     267
     268VACUUM が適切に実行されずに性能が低下していることを前提として回答します。
     269
     270更新頻度の高い巨大なテーブルが存在する場合には、自動バキュームの設定をテーブルごとに調整したほうがいいと思います。
     271
     272デフォルトの設定のままでは、UPDATE や DELETE で不要になった行が
     27350 行 (autovacuum_vacuum_threshold) + 全行数の 2 割 (autovacuum_vacuum_scale_factor)
     274
     275を超えた場合に VACUUM が実行されます。
     276
     277例えば、テーブルの行数が 100,000 行の場合には不要な行が 20,050 行を超えると VACUUM が実行されますが、100,000,000 の場合には 20,050,000 行
     278を超えるまで実行されません。
     279同じ 2 割であっても 2 万行と 2 千万行では性能への影響が違います。
     280
     281PostgreSQL 8.3 であれば、pg_autovacuum テーブルにエントリを追加すればテーブルごとに自動バキュームの設定を行うことができます (参考 URL を見てください)。
     282
     283あと、その他に気になることとしては、デフォルトでは自動バキュームで同時に起動できる VACUUM は 3 つ (autovacuum_max_workers) までなので、
     284更新頻度の高いテーブルが大量に存在する場合には、自動バキュームが特定のテーブルへの VACUUM につきっきりになってしまい、VACUUM の実行されないテーブルが
     285発生して性能が低下してしまう場合があります。
     286
     287http://www.postgresql.jp/document/8.3/html/catalog-pg-autovacuum.html
     288}}}
     289
     290設定を解除する場合
     291{{{
     292$ /usr/local/pgsql/bin/psql -U postgres -f ../share/contrib/uninstall_pg_reorg.sql -d topaz
     293}}}