← Back to team overview

maria-developers team mailing list archive

Re: commit performance when the binlog is enabled



On Dec 25, MARK CALLAGHAN wrote:
> InnoDB fixed group commit in the InnoDB plugin. This performs as
> expected when the binlog is disabled. This does not perform as I
> expect when the binlog is enabled.
> The problems for InnoDB are:
> 1) commit is serialized on the binlog write/fsync
> 2) row locks are not released until the commit step of XA prepare/commit
> 3) per-table auto inc locks not released until the commit step of XA
> I think that 2) and 3) can be fixed without significant changes.

It's not that easy, I think.

What InnoDB needs locks for ?
Not for protecting uncommitted changes - it uses versioning for it.
For serializability (when innodb_locks_unsafe_for_binlog=true or on
SERIALIZABLE level) and for explicit SELECT ... IN SHARE MORE or FOR
UPDATE. Explicit locks are typically used when one reads the data and
later modifies them in the same transaction based on the read values,
right ?

After xa_prepare no data can be modified anymore, it's safe to release
these explicit locks.

If InnoDB locks would be protecting uncommitted data from beeing seen by
another transaction, they would have to stay until commit - but InnoDB
doesn't use locks for this. Safe too.

But locks that help to maintain serializability still have to be
released on commit, I'm afraid. Otherwise you'll have

   trn1> start transaction; insert t1 select * from t2;
   trn1> commit;
   trn1>> ... xa_prepare() ...

   trn2> start transaction; insert t2 values (1); commit;
   trn2>> xa_prepare(); binlog.write(); xa_commit();

   trn1> ... binlog.write(); xa_commit();

and you have incorrect transaction order in binlog.

To summarize - you can release InnoDB locks on prepare only if
innodb_locks_unsafe_for_binlog=false or RBR, and not SERIALIZABLE.

Which could be the only case you care about anyway :)

Regards / Mit vielen Grüßen,

   __  ___     ___ ____  __
  /  |/  /_ __/ __/ __ \/ /   Sergei Golubchik <serg@xxxxxxx>
 / /|_/ / // /\ \/ /_/ / /__  Principal Software Engineer/Server Architect
/_/  /_/\_, /___/\___\_\___/  Sun Microsystems GmbH, HRB München 161028
       <___/                  Sonnenallee 1, 85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Häring

Follow ups