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

Tatsuo Ishii ishii at sraoss.co.jp
Tue Nov 14 10:52:09 JST 2017


So where is the actual pgbench results for Pgpool-II 3.2.1 and
Pgpool-II 3.6.7?  You only showed me the results between port 5432 and
port 9999, which I guess the comparison betwenn PostgreSQL and via
Pgpool-II.

>>>>>>>>> using just this basic test for port 5432 and port 9999 and the version

From: Tracy Stewart <tstewart at vendwatchtelematics.com>
Subject: Re: [pgpool-general: 5803] problem with processing time when using pgpool
Date: Mon, 13 Nov 2017 19:45:19 -0600
Message-ID: <A526497F-8B90-401B-B503-A21302918F40 at vendwatchtelematics.com>

> I am using the exact same 9.6 clusters.  the only change i have made is to use pgpool-II 3.2.1 and 3.6.7.  Every setting is postgres 9.6 is the same.  
> 
> So, basically postgres 9.6 has been installed and working without fault;  then upgraded pgpool from 3.2.1 to 3.6.7 and then saw the big slow down.  
> 
> We have a pretty basic set up;  and I have only made changes, on pgpool…nothing at all has changed in postgres 9.6.  No config changes need to be checked.  
> 
> 
>> On Nov 13, 2017, at 7:10 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>> 
>> If you are not using the identical PostgreSQL 9.6 clusters with
>> Pgpool-II 3.2.1 and 3.6.7, you should check the PostgreSQL settings
>> for Pgpool-II 3.6.7.
>> 
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>> 
>>> I am using postgresql 9.6, not 10. I have rested today and I am still having the issue. We really need to get this resolved. 
>>> 
>>> Sent from my iPhone
>>> 
>>>> On Nov 13, 2017, at 6:44 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>>> 
>>>> 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
> 


More information about the pgpool-general mailing list