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

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


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