[pgsql-jp: 34126] Re: pgpoolのインストールに関して

[Junji Shiokawa]

塩川です。

さっそくお返事いただきましてありがとうございます!

From: Tatsuo Ishii <t-ishii ＠ sra.co.jp>
Subject: [pgsql-jp: 34125] Re: pgpoolのインストールに関して
Date: Sun, 17 Oct 2004 21:04:06 +0900 (JST)

> 石井です．
> 
> > ・psqlでpgpool(デバックモード)に接続し、SELECT文を発行したクエリのDB1
> > とDB2のログをみると明かにどっちにも同じSELECT文を投げている形跡があり
> > ます。これはこーいうものなのですか？
> 
> フロントエンド(PHP側)が7.3以前だからですね．フロントエンドも7.4以降に
> すればロードバランスも動きますし，もっと効率もあがるはずです．
> 

なるほどー。そんなところでしたか。。。
さっそくリコンパイルしてベンチマークしてみました。

...

これはすごい。。。3倍のスピードが出るようになしました。。。
すんなり解決してくださり、ありがとうございます!!

■ pgbench ---------------------------------------------------------------

単体テスト
==> db1.s1.txt <==
pghost: db1 pgport: (null) nclients: 100 nxacts: 10 dbName: pgpooltest
transaction type: SELECT only
scaling factor: 1
number of clients: 100
number of transactions per client: 10
number of transactions actually processed: 1000/1000
tps = 64.060444 (including connections establishing)
tps = 64.060448 (excluding connections establishing)

==> db1.s2.txt <==
pghost: db1 pgport: (null) nclients: 100 nxacts: 10 dbName: pgpooltest
transaction type: SELECT only
scaling factor: 1
number of clients: 100
number of transactions per client: 10
number of transactions actually processed: 1000/1000
tps = 93.393502 (including connections establishing)
tps = 93.393511 (excluding connections establishing)

==> db1.s3.txt <==
pghost: db1 pgport: (null) nclients: 100 nxacts: 10 dbName: pgpooltest
transaction type: SELECT only
scaling factor: 1
number of clients: 100
number of transactions per client: 10
number of transactions actually processed: 1000/1000
tps = 92.844795 (including connections establishing)
tps = 92.844804 (excluding connections establishing)

pgpoolテスト
==> pgpool.s1.txt <==
client 98 receiving
client 99 receiving
pghost:  pgport: (null) nclients: 100 nxacts: 10 dbName: pgpooltest
transaction type: SELECT only
scaling factor: 1
number of clients: 100
number of transactions per client: 10
number of transactions actually processed: 1000/1000
tps = 243.240765 (including connections establishing)
tps = 243.240824 (excluding connections establishing)

==> pgpool.s2.txt <==
client 98 receiving
client 99 receiving
pghost:  pgport: (null) nclients: 100 nxacts: 10 dbName: pgpooltest
transaction type: SELECT only
scaling factor: 1
number of clients: 100
number of transactions per client: 10
number of transactions actually processed: 1000/1000
tps = 247.145103 (including connections establishing)
tps = 247.145164 (excluding connections establishing)

==> pgpool.s3.txt <==
client 98 receiving
client 99 receiving
pghost:  pgport: (null) nclients: 100 nxacts: 10 dbName: pgpooltest
transaction type: SELECT only
scaling factor: 1
number of clients: 100
number of transactions per client: 10
number of transactions actually processed: 1000/1000
tps = 244.708126 (including connections establishing)
tps = 244.708126 (excluding connections establishing)

■ php script ------------------------------------------------------------
単体テスト
==> db1.d1.txt <==
Time taken for tests:   31.280113 seconds
Requests per second:    31.97 [#/sec] (mean)
Time per request:       3128.011 [ms] (mean)
Time per request:       31.280 [ms] (mean, across all concurrent requests)

==> db1.d2.txt <==
Time taken for tests:   32.749591 seconds
Requests per second:    30.53 [#/sec] (mean)
Time per request:       3274.959 [ms] (mean)
Time per request:       32.750 [ms] (mean, across all concurrent requests)

==> db1.d3.txt <==
Time taken for tests:   35.387703 seconds
Requests per second:    28.26 [#/sec] (mean)
Time per request:       3538.770 [ms] (mean)
Time per request:       35.388 [ms] (mean, across all concurrent requests)

pgpoolテスト
==> pgpool.d1.txt <==
Time taken for tests:   9.833230 seconds
Requests per second:    101.70 [#/sec] (mean)
Time per request:       983.323 [ms] (mean)
Time per request:       9.833 [ms] (mean, across all concurrent requests)

==> pgpool.d2.txt <==
Time taken for tests:   12.63502 seconds
Requests per second:    82.89 [#/sec] (mean)
Time per request:       1206.350 [ms] (mean)
Time per request:       12.064 [ms] (mean, across all concurrent requests)

==> pgpool.d3.txt <==
Time taken for tests:   10.392375 seconds
Requests per second:    96.22 [#/sec] (mean)
Time per request:       1039.237 [ms] (mean)
Time per request:       10.392 [ms] (mean, across all concurrent requests)

|fork corporation
+-----