[pgpool-general: 1484] Re: [Benchmarks] Replication mode VS Master/Slave mode

[Tatsuo Ishii]

> Hey! It's me again.
> 
> I just realized (shame on me) that my pgbench with SELECT (only
> selects) have the same results: master/slave is mostly twice better
> than replication.
> As the select are load balanced with replication I don't why I have
> such results in that case.

Not reproduced here.

With steaming replication mode:

[t-ishii at localhost test]$pgbench -p 11002 -S -n -c 10 -T 30 test
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 30 s
number of transactions actually processed: 278484
tps = 9282.699128 (including connections establishing)
tps = 9284.413941 (excluding connections establishing)

With replication mode:

pgbench -p 11002 -S -n -c 10 -T 30 test
transaction type: SELECT only
scaling factor: 1
query mode: simple
number of clients: 10
number of threads: 1
duration: 30 s
number of transactions actually processed: 273558
tps = 9117.816172 (including connections establishing)
tps = 9149.884370 (excluding connections establishing)

How did you execute pgbench exactly?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

> 2013/3/13 Thomas Martin <tmartincpp at gmail.com>:
>> Thanks a lot for your time and your answers; they are explaining my
>> benchmarks results.
>>
>> Thomas
>>
>>
>> 2013/3/13 Tatsuo Ishii <ishii at sraoss.co.jp>:
>>>> Hi Tatsuo.
>>>>
>>>> Sorry for the delay.
>>>>
>>>> 2013/3/10 Tatsuo Ishii <ishii at postgresql.org>:
>>>>> Not sure how much numbers you got, but I would think the difference
>>>>> between those mode comes from the way database gets replicated.  (I
>>>>> assme you are using the Master/Slave mode with streaming replication).
>>>>
>>>> Good point, benchmarks was done with two nodes only.
>>>>
>>>>
>>>>> Streaming replication(SR) is essentially a synchrnonous replication
>>>>> system. That means when client is noticed that UPDATE command
>>>>> completes, it is possible that standbys are not silll get updated. So
>>>>> SR is fast. The price for this is, if you send SELECT to standbys, you
>>>>> may get older results.
>>>>
>>>> You are right, I will have to try with PostgreSQL 9.2 someday (which
>>>> seems to have a real synchronous method).
>>>
>>> Unfortunately no. It just confirms that standbys completes to write
>>> data to WAL. Data updates are done after WAL redo and you still have a
>>> chance to get older results.
>>>
>>>>> On the other hand with pgpool's replication mode, when client is
>>>>> noticed that UPDATE command completes, it is guaranteed that slaves
>>>>> are get updated. The price is speed, of course. If you have two DB
>>>>> nodes, because pgpool-II needs to write to the first node then the
>>>>> second node, the performance is expected 1/2 comparing with single
>>>>> node. If you have third and fource node, there are written in
>>>>> parallel. Included diagram(write-query-performance.png) shows the
>>>>> relationship between performance (Y axis) and number of nodes(X
>>>>> axis). Also I include anoter diagram(read-query-performance.png) which
>>>>> is similar one but for read query.
>>>>
>>>> I'm a bit confused about the differences between two or more nodes.
>>>>
>>>> With two nodes, is the Pgpool really waiting for the first node to
>>>> complete the UPDATE before sending it to the second node?
>>>
>>> Yes.
>>>
>>>> Before your explanations I was thinking that the Pgpool was starting
>>>> the UPDATE on both nodes at the same time (and next he had to wait
>>>> until he got the slower response before processing next requests).
>>>
>>> No, this will result in cross session dead lock situation very easily.
>>> (this is a well known classical problem).
>>>
>>> Note that this only happens when there are multiple concurrent
>>> sessions.  In other word, if there is only one session, pgpool can do
>>> UPDATE on both nodes at the same time (in fact pgpool does this way
>>> when num_init_children = 1).
>>>
>>>> Could you please confirm which is the actually method used and explain
>>>> why this is different with three of four node?
>>>
>>> Ok, I will explain four node case.
>>>
>>> 1) send update command to the first node and wait for its completion.
>>>
>>> 2) send update command to the second, the third and the fourth node at
>>>    the same time.
>>>
>>> 3) wait for completion of the update on all of the second, the third
>>>    and the fourth node.
>>>
>>> 4) reply back to client.
>>>
>>>>> If you issue read/write queries, total performance would be somewhat
>>>>> synthetic of those two diagrams.
>>>>>
>>>>> Hope this helps,
>>>>> --
>>>>> Tatsuo Ishii
>>>>> SRA OSS, Inc. Japan
>>>>> English: http://www.sraoss.co.jp/index_en.php
>>>>> Japanese: http://www.sraoss.co.jp
>>>>
>>>>
>>>> Thanks a lot!
>>>>
>>>> Thomas
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general