[pgsql-jp: 28420] Re: updateの性能を向上

[Tamotsu Ebina]

海老名＠インフォテックです

Mashiki wrote:
> 　Mashikiです。
> 
> ・$tmp_dbのほうの定義はどうなっているのでしょうか？


そうですね、$daily_dbより$tmp_dbの件数が問題ですね？
このテーブルの件数は何件でしょうか？
最初から当然小さな表であると仮定してかかっていましたが。

データタイプTEXTは問題ありませんでした。
マニュアルにもパフォーマンス上の差は無いと書いてありました。

確認テストからの結論はUPDATE処理のパフォーマンスの問題ではない。
と言うことは Where 句のあたりがあやしい?

同じ条件でテーブルをJOINして検索したときのレスポンスはどうかですか。



以下確認テストの結果

今回はアプリケーションサーバは立てずにDBサーバだけで実行しました。
更新時の値はデータをローディング後にLINUXを再起動し、
その直後に採取した値です。
更新時間の値は手を抜いてtime コマンドですませています。

$tmp_dbは1,000件の表ですがキーとなる値は連続した値に
なっています。本当はランダムにしたかったのですが。


-------------------------------------------------------------------
1)
H/W CPU 2.4GHz Memory 1GB HDD SCSI 10,000RPM 72GB x3 Raid5
S/WはRedHat8.0+PostgreSQL7.2.3

1-1) 質問者と同じ条件でテーブル を作成して50万件で実行したところ、
Javaプログラムでのローディング時間 3分14秒 1件あたり  0.000388


1-2) psql での更新はあっという間でした。

[tgko ＠ webspt011 tgko]$ time psql -h $hostname -d $database_name
  -U postgres -c "update $daily_db  set status='$condition_name'
  where $tmp_db.ip_address=$daily_pm_db_stat.ip_address
  and $tmp_db.port_number = $daily_pm_db_stat.port_number";

UPDATE 1000

real    0m3.195s
user    0m0.000s
sys     0m0.002s

1件あたり 0.003195


-------------------------------------------------------------------
2)
試しに、CPU 133MHz Memory 48MB IDE 1.4GB 4200RPM という
ロースペックのマシンで実行してみました。
(DEC HiNote Ultra II 96年発売当時は50万円以上のしろものでした)

S/W
Red Hat Linux 7.3 i386
PostgreSQL 7.2.1 on i586-pc-linux-gnu, compiled by GCC 2.96

2-1) 件数は50万件で、Javaでプログラムでinsert文を
生成してデータをロードするのに１時間25分かかりました。
1件あたり 0.0102 (ハイスペックマシンの26倍)


2-2) psql での更新は、なんと

[tgko ＠ hayama104 local]$ time psql -h $hostname -d $database_name
 -U postgres -c "update $daily_db  set status='$condition_name'
 where $tmp_db.ip_address = $daily_pm_db_stat.ip_address and
 $tmp_db.port_number = $daily_pm_db_stat.port_number";

UPDATE 1000

real    0m6.267s
user    0m0.010s
sys     0m0.020s

1件あたり 0.006267 (ハイスペックマシンの約1.96倍)

この劣悪な(?)環境で50万件の内から1,000件を10秒以内で
処理するのですから大したものです。
(次回$tmp_db表の値の並びがランダムのケースをやってみます。)

以下実行結果の確認

[tgko ＠ hayama104 local]$ psql -h $hostname -d $database_name
 -U postgres -c "select count(*) from $daily_db where status='C'"

 count
-------
  1000
(1 row)


[tgko ＠ hayama104 local]$ psql -h $hostname -d $database_name
 -U postgres -c "select count(*) from $daily_db where status!='C'"

 count
--------
 499000
(1 row)


[tgko ＠ hayama104 local]$ psql -h $hostname -d $database_name
-U postgres -c "select count(*) from $tmp_db";

 count
-------
  1000
(1 row)



> ・また、$daily_pm_db_statの定義はどのようになっているでしょうか？
> ・更新対象500の時も3000の時も15分かかるのでしょうか？
> ・15分はこのpsqlを1回実行するのにかかる時間でしょうか？
> 　それともこのpsqlを500〜3000流すのでしょうか？
> ・テストしているマシンのスペック(特にCPUの種類/速さとメモリの量)は？
> 
> と不明なことが多いで、
> 
> 
>>最も最適な更新方法はあるのでしょうか？
>>それとも、これは妥当な性能なのでしょうか？
> 
> 
> は、なんともいえないでしょう。
> 
> 気がついたところで、
> 
> ・$tmp_dbにインデクスは張られていますか
> ・$daily_dbと$daily_pm_db_statが別のものであれば、全件更新に
> 　なっているような気がします。結果の更新件数は期待通りですか
> ・explainの結果は期待通りのものですか
> 
> が気になります。
> 


$tmp_db、$daily_dbと$daily_pm_db_statと中味が見えないところが
気になります、本当に記載された様な条件の対象テーブルでしょうか?


最後に、

VACUUMまたはVACUUMDBは頻繁に掛ける必要があるようです。
何回も更新していると索引部分の更新でなくてもレスポンスが落ちます。
(例えばテストケースの表のカラムstatus)
これはUPDATEが内部的には、論理的DELETE(update)とINSERTだからでしょう。
件数の多いテーブルだとその影響が看過できません。

7.3で索引部分の更新による性能劣化の問題は修整されたと、
聞きしましたが実機では未確認です。