[pgpool-general: 5811] Re: problem with processing time when using pgpool

[Tatsuo Ishii]

I ran pgbench against 1) pgpool-II 3.2.1 + PostgreSQL 10.1, 2)
pgpool-II 3.6.7 + PostgreSQL 10.1. In summary I see only slight
performance difference between them (0.42%). Pgbench, Pgpool-II and
PostgreSQL are running on my laptop (mem 16GB, CORE i7 x 2, Ubuntu
16.04).

1) pgpool-II 3.2.1 + PostgreSQL 10.1

$ pgbench -T 120 -j 6 -S -c 4 test
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
duration: 120 s
number of transactions actually processed: 1668067
latency average = 0.288 ms
tps = 13900.522648 (including connections establishing)
tps = 13901.712404 (excluding connections establishing)

2) pgpool-II 3.6.7 + PostgreSQL 10.1

$ pgbench -T 120 -j 6 -S -c 4 test
starting vacuum...end.
transaction type: <builtin: select only>
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 4
duration: 120 s
number of transactions actually processed: 1668067
latency average = 0.288 ms
tps = 13900.522648 (including connections establishing)
tps = 13901.712404 (excluding connections establishing)

> After further investigation, currently we are running 3.2.1
> 
> Thank you!
> Tracy Stewart
> 
> On Sun, Nov 12, 2017 at 6:00 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
> 
>> No, I am asking the version of old pgpool.
>>
>> >>>> Not sure if anyone else has had this issue, we have an old version of
>> >>> pgpool (for postgres 9,1) and when we use this we have NO issues, even
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>> > Date: Sun, 12 Nov 2017 17:25:49 -0600
>> > X-Mailer: iPhone Mail (15B150)
>> >
>> > 3.6.7. It was listed in the message. Thanks!  Tracy
>> >
>> > Sent from my iPhone
>> >
>> >> On Nov 12, 2017, at 5:09 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> >>
>> >> So what is the current version of Pgpool-II are you are using?
>> >> I am asking because you don't tell us that.
>> >>
>> >> Best regards,
>> >> --
>> >> Tatsuo Ishii
>> >> SRA OSS, Inc. Japan
>> >> English: http://www.sraoss.co.jp/index_en.php
>> >> Japanese:http://www.sraoss.co.jp
>> >>
>> >>> Thank you for your quick response.  To show an example, I ran a load
>> test
>> >>> using pgbench and here are the results.  Granted, this is over 2x as
>> slow
>> >>> using just this basic test for port 5432 and port 9999 and the version
>> is
>> >>> 3.6.7:
>> >>>
>> >>> 2017-11-10 12:40:31: pid 20066: LOG:  pgpool-II successfully started.
>> >>> version 3.6.7 (subaruboshi)
>> >>> [root at backup log]# sudo su - postgres
>> >>> -bash-4.1$  pgbench -T 120 -j 6 -S -c 4 -p 5432 pgbench
>> >>> starting vacuum...end.
>> >>> transaction type: <builtin: select only>
>> >>> scaling factor: 1
>> >>> query mode: simple
>> >>> number of clients: 4
>> >>> number of threads: 4
>> >>> duration: 120 s
>> >>> number of transactions actually processed: 2341361
>> >>> latency average = 0.205 ms
>> >>> tps = 19511.281019 (including connections establishing)
>> >>> tps = 19512.862613 (excluding connections establishing)
>> >>> -bash-4.1$  pgbench -T 120 -j 6 -S -c 4 -p 9999 pgbench
>> >>> starting vacuum...end.
>> >>> transaction type: <builtin: select only>
>> >>> scaling factor: 1
>> >>> query mode: simple
>> >>> number of clients: 4
>> >>> number of threads: 4
>> >>> duration: 120 s
>> >>> number of transactions actually processed: 1064267
>> >>> latency average = 0.451 ms
>> >>> tps = 8868.845623 (including connections establishing)
>> >>> tps = 8870.858650 (excluding connections establishing)
>> >>> -bash-4.1$
>> >>>
>> >>> --------------
>> >>> Attaching my pgpool.conf;  note, we basically are just using pgpool to
>> deal
>> >>> with connection issues, so we don't load balance, and we only have one
>> db
>> >>> server configured (the master).  The reason that we are addressing this
>> >>> problem is that we currently tried upgrading our production boxes to
>> use
>> >>> newer pgpool, and we noticed a huge issue with processing customer
>> data and
>> >>> the latency (5 times slower) as you can see from the log:
>> >>>
>> >>> not using pgpool:
>> >>> Nov 10 11:52:45.889945 [catalogAPI.import             ] : <I> Import of
>> >>> VmProducts -  Total Items Imported: 2018, Total Time: 00:00:27 Average
>> Save
>> >>> Time: 0.013707411773614
>> >>>
>> >>> using pgpool:
>> >>> Nov 10 11:31:56.243473 [catalogAPI.import             ] : <I> Import of
>> >>> VmProducts -  Total Items Imported: 2018, Total Time: 00:01:53 Average
>> Save
>> >>> Time: 0.056271944457169
>> >>>
>> >>> we went back to using a EOL version to get the processing time back on
>> >>> track but we really would like to use a newer version without this
>> issue.
>> >>>
>> >>> attached is my  pgpool.conf file also.
>> >>>
>> >>> Thank you in advance for your help.
>> >>>
>> >>> Tracy Stewart
>> >>>
>> >>> On Thu, Nov 9, 2017 at 5:56 PM, Tatsuo Ishii <ishii at sraoss.co.jp>
>> wrote:
>> >>>
>> >>>>> Not sure if anyone else has had this issue, we have an old version of
>> >>>> pgpool (for postgres 9,1) and when we use this we have NO issues, even
>>
> 
> 
> 
> -- 
> Tracy A. Stewart
> tstewart at vendwatchtelematics.com