[pgsql-jp: 34767] Re: 最良のチューニング
ISHIDA Akio
iakio ＠ mono-space.net
2005年 1月 30日 (日) 23:27:49 JST
こんにちは。石田@苫小牧市です。
vacuum については、
田中 正幸 wrote:
> http://www.postgresql.jp/document/pg800doc/html/maintenance.html
> 定常的なデータベース保守作業
ここには、
| VACUUM FULLは、テーブル内のほとんどの行を削除したことが判明し
| ている場合に推奨します。（中略)
| 容量の復旧のための定常的なバキューム処理には、VACUUM FULL で
| はなく、普通の VACUUM を使用してください。
> http://www.postgresql.jp/document/pg800doc/html/sql-vacuum.html
> VACUUM
ここには、
| FULLオプションを定常的に使用することは推奨しませんが、これが
| 有用となる、特殊な場合もあります。例えば、テーブル内のほとんど
| 全ての行を削除し、そのテーブルによるディスクの使用量を物理的に
| 縮小させたいような場合です
とあることから考えると、
> データの分布がかわったり、データが頻繁に更新される場合にはVACUUM FULL
と REINDEX
> が必要と思います。
というのはちょっとニュアンスが違うのではないかと思います。
私は、データの分布がかわったら ANALYZE が必要、データが頻繁に更新
される場合には頻繁に VACUUM が必要と考えています。このタイミングが
適切であれば、ファイルのサイズが大きくなりすぎることは防げます。
但し、適切なタイミングで VACUUM を行なわなかった為にサイズが大きく
なりすきてしまテーブルに対して、そのサイズを縮小する為には
VACUUM FULL が必要です。
インデックスに関しては、
> http://www.postgresql.jp/document/pg800doc/html/sql-reindex.html
> REINDEX
| 対象とするインデックスが回収されることがない使用されない
| インデックスページを多く含む場合です。これは、特定のアクセスパターン
| 下のPostgreSQLのB-treeインデックスで起こり得ます。
http://www.postgresql.jp/document/pg800doc/html/routine-reindex.html
| 定期的にREINDEXコマンドを使用してインデックスを再構築すること
| が価値がある状況があります。 (中略)
| しかし、PostgreSQL 7.4では、以前のリリースに比べてこの動作の
| 必要性は減少しています。
と、実際のところ REINDEX が必要なのかどうかよくわからなかった
ので、ちょっと実験してみました。
psql から UPDATE と VACUUM を交互に実行しながら、
別端末から index のファイルサイズを見てみました。
ちょっと長いので先に結果だけ書くと、
o VACUUM コマンドでも インデックスのゴミ領域の再利用は可能。
（しかし、ドキュメントによると、VACUUM が有効でないケースが
あるように読める）
o 1度目の VACUUM では、削除された領域を再利用可能にしなかった。
2度目以降の VACUUM では、前回の VACUUM で削除された領域を
再利用可能にマークしているように見える。
いずれにしろ、VACUUM や REINDEX については、何日に何回とかいう
目安よりも実際のファイルサイズや VACUUM VERBOSE の出力等を
調べた方が良いと思います。
以下ログです。バージョンは 8.0.0 です。
# generate_series() が使いたかったので。
ishida=# CREATE TABLE tbl (i int unique);
NOTICE: CREATE TABLE / UNIQUE will create implicit index "tbl_i_key"
for table "tbl"
CREATE TABLE
ishida=# INSERT INTO tbl SELECT generate_series(0, 99999);
INSERT 0 100000
ishida=# SELECT relfilenode FROM pg_class WHERE relname = 'tbl_i_key';
relfilenode
-------------
157436
(1 行)
（別端末より）$ ls -l data/base/17231/157436
-rw------- 1 ishida ishida 2506752 1月 30 22:56 data/base/17231/15743
ishida=# UPDATE tbl SET i = i + 100000;
UPDATE 100000
（別端末より）$ ls -l data/base/17231/157436
-rw------- 1 ishida ishida 5013504 1月 30 22:56 data/base/17231/157436
-->> ファイルサイズが2506752増えた（倍になった）
ishida=# VACUUM VERBOSE tbl;
INFO: vacuuming "public.tbl"
INFO: index "tbl_i_key" now contains 100000 row versions in 612 pages
DETAIL: 100000 index row versions were removed.
303 index pages have been deleted, 0 are currently reusable.
-->> 303インデックスページが削除されているが、再利用可能は0
CPU 0.24s/1.27u sec elapsed 3.31 sec.
INFO: "tbl": removed 100000 row versions in 491 pages
DETAIL: CPU 0.05s/0.05u sec elapsed 0.19 sec.
INFO: "tbl": found 100000 removable, 100000 nonremovable row versions
in 981 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.34s/1.45u sec elapsed 3.72 sec.
VACUUM
（別端末より）$ ls -l data/base/17231/157436
-rw------- 1 ishida ishida 5013504 1月 30 22:57 data/base/17231/157436
-->> VACUUM 後、ファイルサイズは変らず
-->> もう一度やってみる。
ishida=# UPDATE tbl SET i = i + 100000;
UPDATE 100000
（別端末より）$ ls -l data/base/17231/157436
-rw------- 1 ishida ishida 7512064 1月 30 23:01 data/base/17231/157436
-->> ファイルサイズが 2498560 増えた
ishida=# VACUUM VERBOSE tbl;
INFO: vacuuming "public.tbl"
INFO: index "tbl_i_key" now contains 100000 row versions in 917 pages
DETAIL: 100000 index row versions were removed.
607 index pages have been deleted, 303 are currently reusable.
-->> 607インデックスページが削除され、再利用可能は303
CPU 0.09s/0.61u sec elapsed 2.01 sec.
INFO: "tbl": removed 100000 row versions in 491 pages
DETAIL: CPU 0.05s/0.03u sec elapsed 0.09 sec.
INFO: "tbl": found 100000 removable, 100000 nonremovable row versions
in 981 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 80 unused item pointers.
0 pages are entirely empty.
CPU 0.21s/0.75u sec elapsed 2.31 sec.
VACUUM
ishida=# UPDATE tbl SET i = i + 100000;
UPDATE 100000
（別端末より）$ ls -l data/base/17231/157436
-rw------- 1 ishida ishida 7528448 1月 30 23:03 data/base/17231/157436
-->> ファイルサイズが 16384 しか増えなかった
ishida=# VACUUM VERBOSE tbl;
INFO: vacuuming "public.tbl"
INFO: index "tbl_i_key" now contains 100000 row versions in 919 pages
DETAIL: 100000 index row versions were removed.
609 index pages have been deleted, 305 are currently reusable.
-->> 609インデックスページが削除され、再利用可能は305
CPU 0.12s/0.57u sec elapsed 0.76 sec.
INFO: "tbl": removed 100000 row versions in 492 pages
DETAIL: CPU 0.09s/0.12u sec elapsed 1.60 sec.
INFO: "tbl": found 100000 removable, 100000 nonremovable row versions
in 981 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 124 unused item pointers.
0 pages are entirely empty.
CPU 0.28s/0.80u sec elapsed 2.56 sec.
VACUUM
-->> 続けて VACUUM してみる
ishida=# VACUUM VERBOSE tbl;
INFO: vacuuming "public.tbl"
INFO: index "tbl_i_key" now contains 100000 row versions in 919 pages
DETAIL: 610 index pages have been deleted, 610 are currently reusable.
-->> 610 インデックスページが削除され、再利用可能は310
CPU 0.05s/0.01u sec elapsed 0.07 sec.
INFO: "tbl": found 0 removable, 100000 nonremovable row versions in 981
pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 100124 unused item pointers.
0 pages are entirely empty.
CPU 0.08s/0.05u sec elapsed 0.15 sec.
VACUUM
--
ISHIDA Akio <iakio ＠ mono-space.net / ishida ＠ cycleof5th.com>
pgsql-jp メーリングリストの案内