Oracle でも一つの DB をずっと使ってると行移行とかハイウォーターマークの問題とかいろいろあるわけで、データファイルの定期的なメンテナンスはいずれにせよ必須なのではないかしら。
インデックスを含むテーブルへの高速 import 手段は確かに欲しい！
８はまだ見てませんけど 7.4.6 までは大量投入時は drop index してからやる、というのが常識化しているくらい遅いですからね。drop index→copy→create index ならば常識的な時間で処理できるんだから、内部でそれと同じことをやればいいだけだと思うんだけど…。
あと、Oracle Developer DaysでのOracleとPostgreSQLとの比較 [atmarkit.co.jp]でPostgreSQLの問題点がいろいろ指摘されましたが、8.0になってTablespaceの導入によるI/Oの分散とパフォーマンスの改善が可能になり、SavepointとPoint In Time Recoveryによりバックアップ・リカバリも大分改善されましたから、今度このような講演をする際は材料探しに苦労しそうです:-)
=> \copy test_table from test_table.dump
\.
=> select * from test_table;
id
----
1
1
(2 rows)
// わざと unique 制約違反なデータを copy
=> create unique index test_table_id_key on test_table(id);
ERROR: could not create unique index
DETAIL: Table contains duplicated values.
=> commit;
COMMIT
// あれ？「WARNING: there is no transaction in progress」
// が出ないけど…
=> select * from test_table;
id
----
1
(1 row)
=> \d test_table
Table "public.test_table"
Column | Type | Modifiers
ーーーーーーーーーーーーーーーー
id | integer |
Indexes:
"test_table_id_key" unique, btree (id)
個人的には (スコア:2, 興味深い)
あとインデックスを含んだpg_restoreの高速化とか…無理か。
#面倒なので、OSごとHDDのミラーリング+イメージで対応してます。
Re:個人的には (スコア:3, 興味深い)
Oracle でも一つの DB をずっと使ってると行移行とかハイウォーターマークの問題とかいろいろあるわけで、データファイルの定期的なメンテナンスはいずれにせよ必須なのではないかしら。
インデックスを含むテーブルへの高速 import 手段は確かに欲しい！
８はまだ見てませんけど 7.4.6 までは大量投入時は drop index してからやる、というのが常識化しているくらい遅いですからね。drop index→copy→create index ならば常識的な時間で処理できるんだから、内部でそれと同じことをやればいいだけだと思うんだけど…。
Re:個人的には (スコア:2, 興味深い)
追記型の宿命のVACUUMに関しては、普段はコンカレントVACUUMで誤魔化しておき、定期的にフルVACUUMするしかないんでしょうねぇ。フルVACUUM中でもテーブルロックがかかるだけで参照はできるわけですから、運用次第でカバーできなくもないわけですし。
あと、Oracle Developer DaysでのOracleとPostgreSQLとの比較 [atmarkit.co.jp]でPostgreSQLの問題点がいろいろ指摘されましたが、8.0になってTablespaceの導入によるI/Oの分散とパフォーマンスの改善が可能になり、SavepointとPoint In Time Recoveryによりバックアップ・リカバリも大分改善されましたから、今度このような講演をする際は材料探しに苦労しそうです:-)
Re:個人的には (スコア:1)
# 接続が単数だけからしかこないとは限らないのが頭の痛いところ
人間が判断するから・・・だと、若干面倒だとは思いますが、別にcreate->dropでもいいですよね。
Re:個人的には (スコア:1)
確かにおっしゃる通り。
でも実は、PostgreSQL は DDL もトランザクションに含めることが出来ちゃったりするので「drop index→copy→create index」を１つのトランザクションとして実装すれば、(最初の drop の時点でロックがかかり) 大丈夫だったりします(笑。これはほとんど裏技だなぁ。
Re:個人的には (スコア:1)
もう一つ上げておきましょう。DROP->COPY->CREATEとした場合としない場合、どっちが早いかはCOPYの量に依存しますが、当然そのシーケンスの中で、ロックを取る前にインデックス再作成した方が早いかどうかを知ることはできません。極論、レコードを１つも挿入しないのに事実上のREINDEX相当を行うというばかげた事態になります。
ちなみに、pg_dumpもpg_restoreも、今のバージョンは全てのデータが投入されるまでindexを作成する処理は行いませんから、同等の処理は成されているはずなんですが・・・
Re:個人的には (スコア:2, 参考になる)
> いいえ、それでも元々データが１件以上入っていた場合破綻します。
そうでしょうか？ちょっとテストしてみました。
=> \d test_table
Table "public.test_table"
Column | Type | Modifiers
ーーーーーーーーーーーーーーーー
id | integer |
Indexes:
"test_table_id_key" unique, btree (id)
// unique 制約だとちと面倒なので単なる unique index として用意。
=> select * from test_table;
id
----
1
(1 row)
=> begin;
BEGIN
=> drop index test_table_id_key;
DROP INDEX
// ここで他の接続から同じように begin、drop index しても
// lock されているため待たされる。ちなみに select も待た
// される＜ダメじゃん！(笑。
=> \copy test_table from test_table.dump
\.
=> select * from test_table;
id
----
1
1
(2 rows)
// わざと unique 制約違反なデータを copy
=> create unique index test_table_id_key on test_table(id);
ERROR: could not create unique index
DETAIL: Table contains duplicated values.
=> commit;
COMMIT
// あれ？「WARNING: there is no transaction in progress」
// が出ないけど…
=> select * from test_table;
id
----
1
(1 row)
=> \d test_table
Table "public.test_table"
Column | Type | Modifiers
ーーーーーーーーーーーーーーーー
id | integer |
Indexes:
"test_table_id_key" unique, btree (id)
// ちゃんとロールバックされてる。
なんとなく僕にはちゃんと動いているように思えるんですが (まぁ select も lock してしまっているのは置いておくとして)、どのような場合に破綻してしまうのか教えていただけますか？
> DROP->COPY->CREATEとした場合としない場合、どっちが早いかはCOPYの量に依存します
それはそうですね。ごく単純に上記処理を行ってしまうといろいろもったいないことが起きてしまうことは分かります。本当にシステムに組み込む場合はもう少し凝ったことをしてあげた方が良いでしょう。
例えば、僕は上記の「drop→copy→create」が単一の import 用コマンドで行われていることを想定していますが、それならば drop 前に copy されるデータのサイズ等を知ることも可能なはずですから、それによって処理を分けるとか、
あるいはさらに、DB 自体を工夫して「index 更新なしの copy」と「新規追加された部分のみ index 追加」を行えるようにするといったことを想像していました。
> ちなみに、pg_dumpもpg_restoreも、今のバージョンは全てのデータが投入されるまでindexを作成する処理は行いませんから、同等の処理は成されているはずなんですが・・・
フルダンプ、フルリストア時はさすがに考慮されているのですね。そこは未検証でした。どうもありがとうございます。
僕の元々の書き込みは「インデックスを含むテーブルへの高速 import 手段は確かに欲しい！」ということでしたので、主に copy from が使われるような局面での高速化についての話でした。
Re:個人的には (スコア:1)
ただ、UNIQUE制約のチェックが、全件insert終了後にしかかけられないため、あまりにもコストがかかりすぎているようですね。結局のところ、絶対に重複がないと確信できる状態でない限りやらない方が良さそうです。
データ量を計測したりするコストが馬鹿にならないのでは。 たぶん、これらはやったところで今のCOPYの方が早いでしょう。なんとなれば、従来のindex付きcopyが「タプル挿入コスト＋インデックスタプル挿入コスト」なのに、インデックス作成を遅らせたところで後で読むコストが増えるだけです。まあでも、この種のトランザクションを自動で発行してくれるツールがあればそれはそれで選択肢として良いのかもしれません。高速にコピーできることがあるとはいえ、その間テーブルを完全にロックしてしまうのではっきり言って使い勝手は良くないですが・・・
Re:個人的には (スコア:1)
ですです。このあたりが「裏技」っぽいなぁと(^^;
> 結局のところ、絶対に重複がないと確信できる状態でない限りやらない方が良さそうです。
> データ量を計測したりするコストが馬鹿にならないのでは。
いや、おっしゃるとおりです。たぶんいろいろちゃんと考えてみると、「デフォルトの挙動を drop→copy→create に変更する」ことは難しい、という結論になるのでしょうね。
> なんとなれば、従来のindex付きcopyが「タプル挿入コスト＋インデックスタプル挿入コスト」なのに、インデックス作成を遅らせたところで後で読むコストが増えるだけです。
ええ、ふつうに考えるとそのはずなんですけど、今の PostgreSQL の index 付き copy は常軌を逸して遅いです。大量のデータを import する場合などに、index を付けたままの copy と drop→copy→create との間で、100倍くらいの処理時間の差が生じることも普通にありますよね。理論的には読むコストが増えているはずの後者の方が遅くなってもいいはずなのに…(一件ずつインデックスを挿入する、という処理のオーバーヘッドが死ぬほど大きい、ってことかなぁ…という想像をして、「新規追加された部分のみ index 追加」ならそのオーバーヘッドを回避できたりしないかしら、と考えたわけです)。
> まあでも、この種のトランザクションを自動で発行してくれるツールがあればそれはそれで選択肢として良いのかもしれません。高速にコピーできることがあるとはいえ、その間テーブルを完全にロックしてしまうのではっきり言って使い勝手は良くないですが・・・
大量のデータを bulk load する場合って、たいてい一般ユーザからのアクセスは遮断して行うことが多いんじゃないでしょうか。逆にそういう状況を仮定できない場合は確かに非常に使いにくいと思います。
大量のデータを保持しつつも更新は truncate→copy しかないような場合 (参照用 DB と更新用 DB を分けているような場合の参照側) に限れば、同じ構造を持つテーブルを２つ用意して、
1. 裏テーブルを drop index
2. 裏テーブルを truncate
3. 裏テーブルに copy
4. 裏テーブルに create index
5. 裏と表を swap (ALTER TABLE ～ RENAME TO)
までを一つのトランザクションとなるように処理すれば、表テーブルがロックされる時間は 5. から commit までのごくわずかな時間に抑えられます。ALTER TABLE すらトランザクションに含められる PostgreSQL ならではの裏技ですね。
// ストレージは２倍必要になりますけどね(^^;。
Re:個人的には (スコア:1)
そんななので、途中からやるなら単に作成をまとめたところで状況は良くなりません。create indexの場合は最初から作るのが前提なので、作成はもっと効率よくできます。うろ覚えですが、いったんソートしてからやっていたような。
むしろこれはトランザクションを含めながらほとんどのDDLがロールバックできないと言う他DBがふがいないと思っています。# もちろん、あらゆるものをロールバックできる、というのは決して簡単な問題ではないので、それはトレードオフ何じゃないかな、と思わなくもないですが。
Re:個人的には (スコア:1)
なるほど～。そんな経緯があるのですね。非常に勉強になりました。
> むしろこれはトランザクションを含めながらほとんどのDDLがロールバックできないと言う他DBがふがいないと思っています。
例外の少なさや機能の直行性などに関していえば PostgreSQL は商用 DB を超える部分もありますよね。商用 DB や MySQL などと比べると進化が遅くていらいらする人もいるかもしれませんが、そういった部分こそまさにトレードオフなのかもしれないなぁ、なんてふと思いました。
いろいろ貴重な情報ありがとうございました。
Re:個人的には (スコア:0)
で、FirebirdにはVACUUMがないのですね。
Re:個人的には (スコア:1)
ただしその実装アーキテクチャが異なる、というところがこの場合の肝で、追記型の PostgreSQL 以外はほとんどの場合専用のロールバック領域 (UNDO セグメントと言ったりいろいろ) を持つのではないでしょうか。少なくとも Oracle と MySQL(InnoDB) はそうです。それらの DB では VACUUM がいらない代わりに、その専用領域の管理が必要となります。最近でこそ Oracle も自動 UNDO 管理が出来るようになりましたが、昔はちょっと大きな更新を走らせるとしょっちゅう「ロールバックセグメントがあふれた！」とか「スナップショットが古すぎます、って何だ？」というようなことが巻き起こったものです (遠い目)。
というわけで、VACUUM は面倒だけど、そのおかげで MVCC も実現されてるしよけいなロールバック領域について悩まなくてすむしまぁいいのかな、という話でした。
ガベージコレクション (スコア:1, 参考になる)
PostgreSQLのVACUUMを自動でやっている感じでしょうか?
2. Garbage Collection [ibexpert.info]
Re:ガベージコレクション (スコア:1)
ただ PostgreSQL が手動 vacuum がデフォルトなのに対して、Firebird は自動 vacuum がデフォルトらしい、という点が違うのかな。PostgreSQL も今では、データファイルのゴミ領域の割合や CPU 利用率などを鑑みながらの自動 vacuum ができるようになっていますし (もちろんちゃんと設定すれば、ですが…)、Firebird も自動 garbage collection を off にすることができるみたいですので、運用時検討項目としては同じようなものなのかも。