List:Replication« Previous MessageNext Message »
From:Mark Callaghan Date:January 16 2008 9:25pm
Subject:Re: InnoDB high concurrency - innodb_support_xa
View as plain text  
Many systems use innodb_flush_log_at_trx_commit=2 with HW RAID and a battery
backed RAID cache. But you have to decide whether you can tolerate the risk.
However, there is also a lot of risk in running with the XA binlog code
disabled.

On Jan 16, 2008 8:37 AM, Joaquín Ayuso de Paúl
<joaquin.ayuso@stripped>
wrote:

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


-- 
Mark Callaghan
mcallaghan@stripped

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