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

Tracy Stewart tstewart at vendwatchtelematics.com
Tue Nov 14 13:00:07 JST 2017


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