[pgpool-general: 1255] Re: The performance is not proportional to the number of backend hosts

Tatsuo Ishii ishii at postgresql.org
Wed Dec 12 21:09:53 JST 2012


Recently we did pgpool SELECT performance test and got very positive
result: we used 4 64GB mem/Xeon 2.6GHz with 8 cores x 2(total 16
cores) blade servers. On each server there was pgpool-II
3.2.1+PostgreSQL 9.2.1 combo. 4 pgbench instances run on 4 Xeon
3.06GHz with 6 cores x 2(total 12 cores).

pgbench parameters:
scale factore = 1000
pgbench -T 300 0c 100 -j 20, with following custom scenario:
¥set nbranches :scale
¥set ntellers 10 * :scale
¥set naccounts 100000 * :scale
¥set range 2000
¥set aidmax :naccounts - :range
¥setrandom aid 1 :aidmax
¥setrandom bid 1 :nbranches
¥setrandom tid 1 :ntellers
¥setrandom delta -5000 5000 SELECT count(abalance) FROM pgbench_accounts WHERE aid BETWEEN :aid and :aid + :range;

each PostgreSQL was assigned 32GB shared buffers.  The result was,
pgbench almost linearly scales up to 4 nodes(we have not tested beyond
4 nodes). The result graph is published here:

http://www.pgecons.org/wp-content/uploads/2012/12/20121207_WG1_PGECons.pdf

Unfortunately the material is written in Japanese, however you could
understand the graph part of page 29.

Here are some tips we learned:

- Run SELECT for at least 300 seconds, othewise you cannot get stable results.

- The standard scenario (i.e. -S option) is so trivial and you cannot
  satulate CPUs. That is why we used the custom scenario.

- Choose best balance for -c and -j.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> Hello,
> 
> I am using pgpool for load balancing. However, the results of pgbench are
> not as good as I thought.
> Even though the statements are all SELECT.
> 
> The command of pgbench I used is as follows:
> $ pgbench -p 9999 -h 192.168.16.222  -t 100 -c 1000 -j 1 -Sr -U admin testdb
> 
> Under observation, I found the session activities are weird by typing
> following command.
> $ ps | grep pgpool | grep -c SELECT
> 
> When pgpool only connects to ONE backend host. The number of SELECT
> sessions is about 100~130 when pgbench is running.
> But there are only 10~13 if THREE backend hosts are connected.
> Although the TPS(transactions per second) measured by pgbench is about 1.3
> times better than the one which only a backend host connected.
> The performance is still not satisfied, it should be THREE times better!
> 
> BTW, the CPU utilization in backend host is about 90% when one backend host
> connected; only 30% when three backend hosts connected.
> The memory and I/O seems fine.
> 
> How can I further improve the performance ?
> Thanks.


More information about the pgpool-general mailing list