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

[Thomas Martin]

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