PostgreSQLのパフォーマンスについて質問です。

質問

PostgreSQLのパフォーマンスについて質問です。
INSERTを大量に行う処理の速度が遅いので、高速化をはかるため、INSERTを２０個ずつまとめる修正を行いました。
基本的に速度は速くなったのですが、あるデータだけ逆に遅くなってしまいました。
その遅くなったデータはレコード数が膨大なのですが、まとめてINSERTをおこなうSQLを連続で発行したら、処理速度が遅くなることはあるのでしょうか？
なお、INSERTは下記のように行っています。
INSERT INTO テーブル名 （列名群） VALUES (挿入データ群),(挿入データ群),・・・,(挿入データ群);

また、すべてのテーブルにインデックスはついておらず、プライマリキーと外部キーが設定されています。
マシンスペックはCore2 Duo 2.53GHz、メモリ2GB、OSはWindows XPです。

よろしくお願いします。

回答No.2

YkazubonY · ベストアンサー

物理メモリーは何メガバイト積んでいるのでしょうか.１GB未満は問題がありますね。
postgres.confの設定はどうなっていますか?

＃ディスクアクセスの高速化設定
fsync = off
wal_sync_method = open_sync
の設定はしていますか.

＃物理メモリー使用量の設定
shared_buffers = 2048
sort_mem = 1345
vacuum_mem = 8192
※上記のサイズは、128KByte単位です。

この設定だと、Postgresの物理シェアードメモリーバッファサイズが256MBになります。
でも、8万行程度のインサートは10秒ぐらいで終わると思います。

Postgresは、メモリーをいっぱい積んで、設定を正しく行えば、高速に動作しますよ.
但し、Ver 8.3以降ですが。

お礼

YkazubonYさん回答ありがとうございます。

PostgreSQL8.3を使用しており、物理メモリは２GB積んでいます。（実運用環境のサーバは４GBです）

postgres.confの設定はshared_buffersを800MB、work_memを32MB、checkpoint_segmentsを64に変更しています。
checkpoint_segmentsはネットで最高で64と書いてあったので64に設定したのですが、ログファイルにもっと増すようにと注意が書かれるのですが、弊害がおこりそうで怖くて増やせません。

ディスクアクセスの高速化設定早速明日試してみたいと思います。
sort_mem、vacuum_memも増やしてみたいと思うのですが、今のshared_buffers=800MBという設定は大きすぎでしょうか？
ちなみに実運用環境では最大１５０コネクションになることがあります。

よろしくお願いします。

回答No.1

chukenkenkou

まず、PostgreSQLのバージョンは何ですか？
例えば、PostgreSQL 8.3と8.4でも、大きな機能追加などかなり違ったりします。

>あるデータだけ逆に遅くなってしまいました。
>その遅くなったデータはレコード数が膨大

もっと具体的に提示するようにしてください。
再現性、つまり一旦、表の削除、再定義してそのinsertを実行すると、同じような結果になりますか？

SQLをどこからどういう方法で投げているのか不明ですが、通信とかどこがボトルネックになっているのか絞り込まないと。

お礼

chukenkenkouさん回答ありがとうございます。

説明不足で申し訳ありません。
PostgreSQLのバージョンは8.3です。
また、表の再定義を行ったあとも同じ結果になりました。

なお、SQLはC＋＋で作成したプログラムから発行しています。
表は全部で２４個あり、修正前はそのうち３個の表のINSERTを２０個ずつまとめてINSERTしていたのですが、修正後はほとんどのテーブルで２０個ずつまとめてINSERTするようにしました。

PostgreSQLのconfの設定でSQLのログを出すようにして検証を行ったところ、INSERTを１件ずつ行った場合は１秒間に約４００個のINSERTが行えており、修正前にINSERTを２０件ずつ行った場合は１秒間に約２００個のINSERTが行えていました。
修正後ははじめは修正前と同様に１秒間に約２００個のINSERTが行えていたのですが、徐々に数が減り、最終的には１秒間に２５個のINSERTしか行えていませんでした。

また、ローカルのマシンにPostgreSQLを入れて行ったテストでは修正後は修正前の約３倍の速度になりました。スペックの高いサーバにPostgreSQLを入れてネットワーク越しに行ったテストでは約１．３倍の速度になりました。
ちなみに修正前は約３００００件、修正後は約５０００件のSQLを発行しています。

何か思い当たる点がありましたらよろしくお願いします。