Re: Synchronization levels in SR

Simon Riggs írta:
> On Mon, 2010-09-06 at 21:45 +0200, Boszormenyi Zoltan wrote:
>   
>> Dimitri Fontaine írta:
>>     
>>> Boszormenyi Zoltan <zb@cybertec.at> writes:
>>>   
>>>       
>>>> Sorry for answering such an old mail, but what is the purpose of
>>>> a transaction level synchronous behaviour if async transactions
>>>> can be held back by a sync transaction?
>>>>     
>>>>         
>>> I don't understand why it would be the case (sync holding back async
>>> transactions) — it's been proposed that walsender could periodically
>>> feed back to the master the current WAL position received, synced and
>>> applied. 
>>>
>>> So you can register your sync transaction to wait (and block) until
>>> walsender sees a synced WAL position after your own (including it) and
>>> another transaction can wait until walsender sees a received WAL
>>> position after its own, for example. Of course, meanwhile, any async
>>> transaction would just commit without caring about slaves.
>>>   
>>>       
>> The locks held by a transaction are released after
>> RecordTransactionCommit(), and waiting for the sync ack
>> happens in this function. Now what happens when a sync
>> transaction hold a lock that an async one is waiting for?
>>     
>
> It seems your glass in half-empty. Mine is half-full.

This is good, we can meet halfway. :-)

>  My perspective
> would be that if there is contention between async and sync transactions
> then we will get better throughout than if all transactions were sync.
> Though perhaps the main issue in that case would be application lock
> contention, not the speed of synchronous replication.
>   

The difference we are talking about is:

xact1                    xact2
begin                           begin
lock something                           lock same

(in commit)
write wal record
wait for sync ack
release locks/etc   <xact2 can proceed from here

vs.

xact1                    xact2
begin                           begin
lock something                           lock same

(in commit)
write wal record
release locks/etc   <xact2 can proceed from here
wait for sync ack

In the first case, the contention is obviously increased.
With this, we are creating more idle time in the server
instead of letting other transactions do their jobs as soon
as possible. The second method was implemented in my
patch. Are there any drawbacks with this?

> The highest level issue is that the system only has so much physical
> resources. If we are unable to focus our resources onto the things that
> matter most then we end up wasting resources. Mixing async and sync
> transactions on the same server allows a single application to carefully
> balance performance and durability. Exactly as we do with
> synchronous_commit.
>   

I don't think this is the same situation. With synchronous_commit,
you have an auxiliary process that's handed the job of doing
the syncing. But there's nowhere to hand out the waiting for
sync ack from the standby.

> By now, people are beginning to see that synchronous replication is
> important but has poor performance. Fine grained control is essential to
> using it effectively in areas that matter most.
>   


-- 
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de    http://www.postgresql.at/