[pgsql-jp: 37731] Re: INDEXを残したまま大量データを高速に挿入したい

[Tokuharu yuzawa]

始めまして湯沢と申します。

私も同じように、1日に1千万件程度のデータをＴＡＢＬＥに格納すると言うことを
行っています。
3ヶ月でおよそ6億件データ量になる想定です。
Postgresは、8.1.4を使用
Windows環境

複数セッションからINSERT文（PREPARE使用）で1件1件挿入していましたが、合計レ
コード件数が3億件くらいからか？
DISK IOがかなり頻繁になり、データ格納速度が半減してしまい、何か良い対応方法
がないかと頭を抱えています。

気になることが何点かあります。
コンピータの管理−＞記憶域−＞デフラグツール
でＤＢが存在するドライブを、分析ボタンを押し分析してみたら、不連続状態の領域
しか存在しないのですが、速度に大きく影響するのでしょうか？
ＤＢを停止した状態で、デフラグを行ってもＤＢへの影響はないのでしょうか？
ちなみにＤＢ以外のファイルは当ドライブには存在しません。
この不連続状態を回避する方法はありませんでしょうか？
たとえばOracelでTABLEを作成するときのように初期状態にデータ領域確保量、次回
領域確保量・・・と言うような感じで？
また一部のTABLEはかなりの頻度でUPDATEが行われるように現在なっているのです
が、
これも影響しますでしょうか？レコード数のほぼ100倍

vacuumログについて
Posugreのログファイルに、「autovacuum:processing databse "ＤＢ名"と出力され
ていますが、autovacuumの状態や状況を確認したいのですが方法が分かりません。
PsqlでVACUUM FULL VERBOSEを実行したときのように結果が出力されるようにする方
法はありますでしょうか？

どなたかアドバイスをいただければ幸いです。
よろしくお願いいたします。



> -----Original Message-----
> From: pgsql-jp-bounces ＠ ml.postgresql.jp
> [mailto:pgsql-jp-bounces ＠ ml.postgresql.jp] On Behalf Of 武田憲太郎
> Sent: Tuesday, November 21, 2006 8:39 PM
> To: 'PostgreSQL Japanese Mailing List'
> Subject: [pgsql-jp: 37707] Re:INDEXを残したまま大量データを高速に挿入した
> い
>
> 武田と申します。
>
> 処理する行数の分だけ、insert/updateを発行する形でしょうか？
> もしそうだとしたら、私ならそれを以下のように変更します。
>
> -- 準備
> create temp table 一時テーブル名 ..... ;
> copy 一時テーブル名 from 入力元 ;
>
> -- ここで必要に応じて一時テーブルにインデックスを張る。
>
> begin ;
>
> -- １．存在しない行へのinsert
> insert into 処理テーブル名
>  select (カラムリスト) from 一時テーブル名
>   except
>  select (カラムリスト) from 一時テーブル名
>   where (pkey制約に引っかかる行を除外するための条件) -- ※下でもう一度使う
> ;
>
> -- ２．存在する行のupdate
> update 処理テーブル名 set カラムカウント=カラムカウント+count
>  where
>   t.カラム = 処理テーブル名.カラム
> from
>  (
>   select count(*), カラム from 一時テーブル名
>    where not ※ group by カラム
>  ) as t
> -- 「何某」の部分を、count(*)とgroup byで集計する。
>
> commit;
>
> drop table 一時テーブル名 ;
>
> 本来であればcreate temp table/drop tableもトランザクションの中に置いたほう
> が
> 良いと思うのですが、１と２で獲得されたロックをなるべく早めに開放してあげた
> い
> ために外側に置いています。
>
> insertに関してはSQL一回で済むのでcopyと同等のパフォーマンスを発揮できる
> (?)、
> [pgsql-jp: 37704]の問題はexceptで回避する、updateに関してもSQLは一回だけ、
> 一
> 旦countで集計してから一気に更新するので内部的な更新回数も最小限で済む、み
> た
> いな考え方です。
>
> 細かい条件（元々の入力元に重複が存在するのか、pkeyの条件は何か）などが分ら
> な
> いので上の通りにはならないと思いますが、考え方的にいかがでしょうか？
>
> 既に試してたらごめんなさい。
>
>
>
> -----Original Message-----
> From: pgsql-jp-bounces ＠ ml.postgresql.jp
> [mailto:pgsql-jp-bounces ＠ ml.postgresql.jp] On Behalf Of 河本陽一
> Sent: Tuesday, November 21, 2006 5:03 PM
> To: pgsql-jp ＠ ml.postgresql.jp
> Subject: [pgsql-jp: 37702] INDEXを残したまま大量データを高速に挿入したい
>
> こうもとです。
>
> 　毎日1回、大量のデータ(1000万件)をひとつのトランザクションで投入し
> ようとしています。PostgreSQLは、7.4を使用しています。
> 　投入速度が遅いため、投入時は最低限のINDEX以外を削除することで性能
> が出るようになりました。
> 　しかし、データ投入の間(時間にして約8時間20分)は、INDEXがないために
> SELECTの性能が出なくなってしまいます。
> 　これでは使用する上で困るので、回避方法を探しています。
>
> 　データ挿入の処理速度を上げる方法を調べてみましたが、いずれも思うよ
> うな動作となりませんでした。
> ・COPYを使用する
> 　→既存データがないので使用不可
> ・INDEXの削除
> 　→上記問題でSELECTの性能が出ないので見送り
> ・DBチューニング
> 　→7.4なので maintenance_work_mem は使用不可
> 　→checkpoint_segments を60にし、topでIO待ちが3%程度でも性能出ず
> 　→sort_mem を65536にしても性能でず
>
> 　挿入のトランザクションが終わるまではそのデータは見えないので、表示
> にしか必要ないINDEXは更新しない方法がないか探してみましたが、見つか
> りませんでした。
>
> 　INDEXの削除や追加をトランザクションの中に入れてみましたが、こうす
> ると削除したINDEXを使用しているテーブルの問い合わせでロックがかかっ
> てしまい、トランザクションが終わるまで戻ってきませんでした。
>
> 環境
>   Redhat Linux ES4 (Kernel 2.6.9-42)
>   PostgreSQL 7.4.13
>
> ======================================================================
> 河本陽一(こうもとよういち)
> mailto:komoto.yoichi ＠ kcc.co.jp