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

[Tatsuo Ishii]

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
>> though we are using postgres 9.6 - we process some of our normal day to day
>> processing and the speed is the same as if we go directly to postgres (port
>> 5432).  However when we upgraded pgpool to the latest version our
>> processing time takes 5 times as long.  This is horrible.  Has anyone else
>> seen this??  Trying to see how we can resolve this.
>>
>> To answer your question, we need:
>>
>> - Exact Pgpool-II full version
>> - pgpool.conf
>> - The query string taking 5 times long, or self contained test case
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
> 
> 
> 
> -- 
> Tracy A. Stewart
> tstewart at vendwatchtelematics.com