[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