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

Tue Nov 14 23:50:21 JST 2017

Tracy, a test with -S is a read only test, without -S option, means you run
a test in read write mode. Because of this, the read write test should runs
a time in which there are presence of checkpoint executions, before evaluate
final results.

For instance: A checkpoint is executed every 5 minutes, runs the test 10
minutes.

Regards.

-----Mensaje original-----
De: pgpool-general-bounces at pgpool.net [mailto:pgpool-general-bounces at pgpool.
net] En nombre de Tatsuo Ishii
Enviado el: martes, 14 de noviembre de 2017 12:26 a. m.
Para: tstewart at vendwatchtelematics.com
CC: pgpool-general at pgpool.net
Asunto: [pgpool-general: 5817] Re: problem with processing time when using
pgpool

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
>>>>>
>>>
>
_______________________________________________
pgpool-general mailing list
pgpool-general at pgpool.net
http://www.pgpool.net/mailman/listinfo/pgpool-general