読者です 読者をやめる 読者になる 読者になる

アルパカDiary Pro

はてなブログProではありません

コネクションをプーリングしている/していない時の比較ベンチマーク

だいぶ前に「postgresのコネクションはforkするので負荷大きい」
というアドバイスいただいていて、検証しようと思ってたのですが
いままで手つかずで…。
手が空いたのでベンチマーク取ってみました。

環境

postgresql 8.4.4
pg-pool2 2.3.3

pg-poolはコネクションプーリングモード。
パラメータは基本的に変えてない。
「num_init_children」だけ128に増やした。

ベンチマーク取り方

pgbench使う。
初期データ投入は以下のコマンド。

pgbench -i -s 25 test

ベンチマークは以下のコマンド。多分「-C」オプションが重要。*1

pgbench -S -C -c 30 -t 300 test

ベンチマークに使ったスクリプト

$ cat pgbench.sh
i=0
echo "< pgbench nopool start  >"
while [ $i -lt 5 ]; do
 /usr/local/pgsql/bin/pgbench -h 192.168.126.137 -U postgres -S -C -c 30 -t 300 test
 i=`expr $i + 1`
done
echo "< pgbench nopool end  >"
echo ""

i=0
echo "< pgbench pool start  >"
while [ $i -lt 5 ]; do
 /usr/local/pgsql/bin/pgbench -h 192.168.126.137 -U postgres -p 9999 -S -C -c 30 -t 300 test
 i=`expr $i + 1`
done
echo "< pgbench pool end  >"

ベンチマーク結果(5回繰り返し)

プーリングなし

< pgbench nopool start  >
starting vacuum...end.
transaction type: SELECT only
scaling factor: 25
query mode: simple
number of clients: 30
number of transactions per client: 300
number of transactions actually processed: 9000/9000
tps = 70.868983 (including connections establishing)
tps = 5119.721851 (excluding connections establishing)
starting vacuum...end.
transaction type: SELECT only
scaling factor: 25
query mode: simple
number of clients: 30
number of transactions per client: 300
number of transactions actually processed: 9000/9000
tps = 72.145497 (including connections establishing)
tps = 5442.660663 (excluding connections establishing)
starting vacuum...end.
transaction type: SELECT only
scaling factor: 25
query mode: simple
number of clients: 30
number of transactions per client: 300
number of transactions actually processed: 9000/9000
tps = 72.216336 (including connections establishing)
tps = 5403.273303 (excluding connections establishing)
starting vacuum...end.
transaction type: SELECT only
scaling factor: 25
query mode: simple
number of clients: 30
number of transactions per client: 300
number of transactions actually processed: 9000/9000
tps = 72.140168 (including connections establishing)
tps = 5409.990088 (excluding connections establishing)
starting vacuum...end.
transaction type: SELECT only
scaling factor: 25
query mode: simple
number of clients: 30
number of transactions per client: 300
number of transactions actually processed: 9000/9000
tps = 71.793557 (including connections establishing)
tps = 5365.280199 (excluding connections establishing)
< pgbench nopool end  >


プーリングあり

< pgbench pool start  >
starting vacuum...end.
transaction type: SELECT only
scaling factor: 25
query mode: simple
number of clients: 30
number of transactions per client: 300
number of transactions actually processed: 9000/9000
tps = 265.589115 (including connections establishing)
tps = 6013.646970 (excluding connections establishing)
starting vacuum...end.
transaction type: SELECT only
scaling factor: 25
query mode: simple
number of clients: 30
number of transactions per client: 300
number of transactions actually processed: 9000/9000
tps = 290.995381 (including connections establishing)
tps = 6040.544132 (excluding connections establishing)
starting vacuum...end.
transaction type: SELECT only
scaling factor: 25
query mode: simple
number of clients: 30
number of transactions per client: 300
number of transactions actually processed: 9000/9000
tps = 281.898755 (including connections establishing)
tps = 5764.109580 (excluding connections establishing)
starting vacuum...end.
transaction type: SELECT only
scaling factor: 25
query mode: simple
number of clients: 30
number of transactions per client: 300
number of transactions actually processed: 9000/9000
tps = 476.570018 (including connections establishing)
tps = 7621.970584 (excluding connections establishing)
starting vacuum...end.
transaction type: SELECT only
scaling factor: 25
query mode: simple
number of clients: 30
number of transactions per client: 300
number of transactions actually processed: 9000/9000
tps = 478.271565 (including connections establishing)
tps = 7703.487626 (excluding connections establishing)
< pgbench pool end  >

考察

includingに注目してみると、コネクションプーリングをしている方が
およそ4〜7倍の性能向上がみられます。

これが他のDBMSに比べてどのくらいの差異なのかはわかりませんが、
プーリングの効力は大きそうですね