Re: Could synchronous streaming replication really degrade the performance of the primary?

On Wed, May 9, 2012 at 3:58 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Wed, May 9, 2012 at 8:06 AM, MauMau <maumau307(at)gmail(dot)com> wrote:
>> I've heard from some people that synchronous streaming replication has
>> severe performance impact on the primary. They said that the transaction
>> throughput of TPC-C like benchmark (perhaps DBT-2) decreased by 50%. I'm
>> sorry I haven't asked them about their testing environment, because they
>> just gave me their experience. They think that this result is much worse
>> than some commercial database.
>
> I can't speak for other databases, but it's only natural to assume
> that tps must drop.  At minimum, you have to add the latency of
> communication and remote sync operation to your transaction time.  For
> very short transactions this adds up to a lot of extra work relative
> to the transaction itself.

Actually I would expect 50% degradation if both databases run on
identical hardware: the second instance needs to do the same work
(i.e. write WAL AND ensure it reached the disk) before it can
acknowledge.

"When requesting synchronous replication, each commit of a write
transaction will wait until confirmation is received that the commit
has been written to the transaction log on disk of both the primary
and standby server."
http://www.postgresql.org/docs/9.1/static/warm-standby.html#SYNCHRONOUS-REPLICATION

I am not sure whether the replicant can be triggered to commit to disk
before the commit to disk on the master has succeeded; if that was the
case there would be true serialization => 50%.

This sounds like it could actually be the case (note the "after it commits"):
"When synchronous replication is requested the transaction will wait
after it commits until it receives confirmation that the transfer has
been successful."
http://wiki.postgresql.org/wiki/Synchronous_replication

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/