List:Replication« Previous MessageNext Message »
From:Joaquín Ayuso de Paúl Date:January 16 2008 4:37pm
Subject:Re: InnoDB high concurrency - innodb_support_xa
View as plain text  
I see,

innodb_flush_log_at_trx_commit=1


Shall we consider to reactivate the XA protocol? In order of taking the 
step of deactivating it we decided to do not use an "auto_increment" 
index in the table so consistency in data is not lost if writes to 
binlog is wrongly ordered.

Anyway, we are considering to partition the table in order to make this 
specific cluster scalable. And probably will come back to use the XA 
protocol... what do you think?

Joax.

Mark Callaghan wrote:
> For the systems that I use, this is not a performance bottleneck as long as
> InnoDB has been configured to not sync the transaction log on every commit
> and you are not syncing the binlog on every commit. Have you set
> innodb_flush_log_at_trx_commit=2?
>
> Group commit for InnoDB was broken in MySQL5.0. What we got in return was
> useful, the XA like protocol used between storage engines and the binlog
> means that the two can be made consistent during crash recovery. Prior to
> this there was a window where a crash between the separate commits (InnoDB
> transaction log, binlog) would leave them inconsistent after crash recovery.
>
> On Jan 16, 2008 5:03 AM, Joaquín Ayuso de Paúl
> <joaquin.ayuso@stripped>
> wrote:
>
>   
>> Hi all,
>>
>> I need some help on a Master-Slave replication model. The architecture
>> is a innoDB engine table replicated to the slaves.
>>
>> We found a bottleneck on the master server due to the concurrency over
>> the table, not able to handle more than 200 inserts / deletes
>> simultáneusly with the standard configuration and sync-binlog
> deactivated.
>>
>> This limit is primarily -after researching on the server logs, stats and
>> over the internet - the IO wait. We found that every transaction made to
>> the table needs a check/write on the binlog. This is done in order to
>> maintain the order consistency of the writes / deletes on the table,
>> queuing the queries incoming to maintain the order in the binary log.
>>
>> there is a variable in order to make InnoDB not check the binary logs
>> and just "write as it comes" into the binlog. This variable is
>> *innodb_support_xa*.
>>
>> My question is: setting innodb_support_xa to 0 will break replication?
>> We found it really usefull, but on the documentation they warn really
>> hard on the point that the integrity of the binlog might be broken with
>> this set. Is that correct?
>>
>> Thanks in advance.
>>
>> Joax.
>>
>> --
>> Joaquín Ayuso de Paúl
>> ---------------------
>> Tecnología y Producto
>> C/Barquillo 23, 1º Iz
>> Madrid, España
>> +34 663 016 273
>> joaquin.ayuso@stripped
>> www.tuenti.com
>>
>>
>> --
>> MySQL Replication Mailing List
>> For list archives: http://lists.mysql.com/replication
>> To unsubscribe:
>> http://lists.mysql.com/replication?unsub=1
>>
>>
>>     
>
>
>   

-- 
Joaquín Ayuso de Paúl
---------------------
Tecnología y Producto
C/Barquillo 23, 1º Iz
Madrid, España
+34 663 016 273
joaquin.ayuso@stripped
www.tuenti.com

Thread
InnoDB high concurrency - innodb_support_xaJoaquín Ayuso de Paúl16 Jan
  • RE: InnoDB high concurrency - innodb_support_xaPeter Benjamin Volk.external16 Jan
  • Re: InnoDB high concurrency - innodb_support_xaMark Callaghan16 Jan
    • Re: InnoDB high concurrency - innodb_support_xaJoaquín Ayuso de Paúl16 Jan
      • Re: InnoDB high concurrency - innodb_support_xaMark Callaghan16 Jan