Re: Synchronization levels in SR

On Wed, 2010-09-08 at 12:04 +0200, Boszormenyi Zoltan wrote:
> >>
> >> I know why you want that, but I don't think its right.
> >>
> >
> > Agreed. That's why I put the wait before ProcArrayEndTransaction()
> > is called.
> >
>
> Then there is no use to implement individual sync/async
> replicated transactions, period. An async replicated transaction
> that waits for a sync replicated transaction because of locks
> will become implicitely sync. It just waits for another transactions'
> sync ack.

You aren't making any sense. You have made a general observation and
deduced something specific about replication from it. Most transactions
are not blocked by locks, especially in well designed applications, so
the argument is not relevant to replication.

If *any* two transactions wait upon each other then t2 will always wait
until t1 has completed.

If t1 is slow then any tuning you do on t2 will likely be wasted. If you
are concerned about performance you should first remove the dependency
between t1 and t2. The above observation isn't sufficient to conclude
that "tuning of t2 should not happen via the tuning feature Simon has
suggested". It's not sufficient to conclude much, if anything.

As it turns out, in the scenario you outline t2 *would* return faster
because you had marked it as "async". But it would wait behind t1, as
you say. So the performance gain will be clear and measurable. Even so,
it would be best to tune the problem (lock contention) not moan that the
tool you're choosing to use using (tuning replication) is at fault for
being inappropriate to the problem.

Mixing sync and async transactions is useful and it's a simple matter to
come up with real examples where it would benefit, as well as easily
testable workloads using pgbench. For example, customer table updates
(sync) alongside chat messages (async).

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services