[pgpool-general: 5817] Re: problem with processing time when using pgpool
Tatsuo Ishii
ishii at sraoss.co.jp
Tue Nov 14 14:25:54 JST 2017
Ok, I don't see too much difference between 3.6.7 vs. 3.2.1
(1516.578890 vs 1567.877385 = 1.0 vs 1.0338)。Probably this is just a
noise because PostgreSQL 9.6 shows subtle difference (1760.877729
vs. 1797.735445 = 1.0 vs 1.0209, which are should be identical because
they are identical DB cluster).
So I would say Pgpool-II 3.6.7 does not degrade from 3.2.1.
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
> Ok, here are the results for both without -S:
>
> pgpool 3.6.7
> -bash-4.1$ pgbench -T 90 -j 6 -c 4 -p 9999 pgbench
> 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: 90 s
> number of transactions actually processed: 136488
> latency average = 2.638 ms
> tps = 1516.488395 (including connections establishing)
> tps = 1516.578890 (excluding connections establishing)
> -bash-4.1$ pgbench -T 90 -j 6 -c 4 -p 5432 pgbench
> 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: 90 s
> number of transactions actually processed: 158496
> latency average = 2.272 ms
> tps = 1760.771620 (including connections establishing)
> tps = 1760.877729 (excluding connections establishing)
>
> pgpool 3.2.1
> pgbench -T 90 -j 6 -c 4 -p 9999 pgbench
> 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: 90 s
> number of transactions actually processed: 141108
> latency average = 2.551 ms
> tps = 1567.799687 (including connections establishing)
> tps = 1567.877385 (excluding connections establishing)
> -bash-4.1$ pgbench -T 90 -j 6 -c 4 -p 5432 pgbench
> 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: 90 s
> number of transactions actually processed: 161789
> latency average = 2.225 ms
> tps = 1797.632346 (including connections establishing)
> tps = 1797.735445 (excluding connections establishing)
>
>
>> On Nov 13, 2017, at 9:04 PM, Tatsuo Ishii <ishii at sraoss.co.jp> wrote:
>>
>> 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