[pgpool-general: 5815] Re: problem with processing time when using pgpool
Tatsuo Ishii
ishii at sraoss.co.jp
Tue Nov 14 12:04:39 JST 2017
Your tests seems an "apple and orange" comparison. You ran pgbench -S
against Pgpool-II 3.6.7, while you ran pgbench (without -S) against
Pgpool-II 3.2.1. They are not the same tests.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
From: Tracy Stewart <tstewart at vendwatchtelematics.com>
Subject: Re: [pgpool-general: 5803] problem with processing time when using pgpool
Date: Mon, 13 Nov 2017 20:52:15 -0600
Message-ID: <C6F36143-3C27-4EB2-89CA-00423ED1CA70 at vendwatchtelematics.com>
> Here hopefully this is what you need:
>
> This is pgpool 3.6.7 - the only way I can think to test is by using the ports...since pgpool uses port 9999 and postgres uses port 5432.
> Does this make sense?? Postgres 9.6 for both. These are results from the exact same development box we use.
>
> pgbench -T 90 -j 6 -c 4 -p 9999 pgbench
> starting vacuum...end.
> transaction type: <builtin: select only>
> scaling factor: 10
> query mode: simple
> number of clients: 4
> number of threads: 4
> duration: 90 s
> number of transactions actually processed: 1179779
> latency average = 0.305 ms
> tps = 13108.597149 (including connections establishing)
> tps = 13109.777024 (excluding connections establishing)
>
>
> pgbench -T 90 -j 6 -S -c 4 -p 5432 pgbench
> starting vacuum...end.
> transaction type: <builtin: select only>
> scaling factor: 10
> query mode: simple
> number of clients: 4
> number of threads: 4
> duration: 90 s
> number of transactions actually processed: 2132587
> latency average = 0.169 ms
> tps = 23695.330284 (including connections establishing)
> tps = 23696.573296 (excluding connections establishing)
>
>
> Version pgpool-II 3.2.1 - here are the results - which seem to be the same thing we are seeing with our software, the times seem to be be very much the same - so no degrading time - basically our problems started when we try and use a newer version of pgpool-II
>
> pgbench -T 30 -j 6 -c 4 pgbench -p 9999
> starting vacuum...end.
> transaction type: <builtin: TPC-B (sort of)>
> scaling factor: 10
> query mode: simple
> number of clients: 4
> number of threads: 4
> duration: 30 s
> number of transactions actually processed: 46826
> latency average = 2.572 ms
> tps = 1555.235625 (including connections establishing)
> tps = 1555.493590 (excluding connections establishing)
> pgbench -T 30 -j 6 -c 4 pgbench -p 5432
> starting vacuum...end.
> transaction type: <builtin: TPC-B (sort of)>
> scaling factor: 10
> query mode: simple
> number of clients: 4
> number of threads: 4
> duration: 30 s
> number of transactions actually processed: 49537
> latency average = 2.423 ms
> tps = 1650.836032 (including connections establishing)
> tps = 1651.088051 (excluding connections establishing)
>
> Hopefully this makes sense. This is the basically what we are seeing in our production environment. So, we continue to use the version pgpool-II 3.2.1 with postgres 9.6 currently.
>
> Since pgpool-II 3.2.1 is EOL - we really just want to have the same experience with a newer version.
>
> Tracy
>
>> On Nov 13, 2017, at 7:52 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>
>> 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