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

[Thomas Martin]

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).


> 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?
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).

Could you please confirm which is the actually method used and explain
why this is different with three of four node?


>
> 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