だいぶ前に「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 >"
ベンチマーク結果(５回繰り返し)
プーリングなし
< 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に比べてどのくらいの差異なのかはわかりませんが、
プーリングの効力は大きそうですね
