使用过使用过Oracle MySQL 5.5及之前版本的产品大概都对MySQL在sync_binlog和innodb_flush_log_at_trx_commit全部设置为1 的场景下性能急剧下降感到记忆犹新，这两个参数的主要作用是确保事务提交时，该事务对应的binary log和redo log都已经刷新到硬盘上。一方面可以确保已经提交的事务在数据库recover时不会丢失，但同时sync的代价也使得数据库事务提交的速度受到极大的约束。

int ha_commit_trans(THD *thd, bool all){
   …..
   for (; ha_info && !error; ha_info= ha_info->next()){
        if ((err= ht->prepare(ht, thd, all)))
        {
          .....
        }
    }
   ......
   cookie= tc_log->log_xid(thd, xid))
  ……
   error=ha_commit_one_phase(thd, all) ? (cookie ? 2 : 1) : 0;
......
}

从代码上看，MySQL上层首先逐个调用下层引擎的prepare方法，所有引擎prepare后，调用log_xid方法，在log.cc中，我们了解到log_xid的主要作用就是将各个线程的binlog cache刷新到binary log 文件中。最后对各个底层引擎在ha_commit_one_phase中调用commit方法。从代码中可以得知，MySQL实际上将binary log作为一个协调者，以记录binary log为准，binary log记录的事务更新，表示所有引擎层都已完成prepare，在崩溃恢复时，应该进行提交，相反，如果binary log中没有记载，表示MySQL上层并没有确认所有的引擎都已完成prepare，在崩溃恢复时，应该进行回滚。在不允许已经提交的事务丢失的业务场景下，由于binary log也是缓冲写，所以MySQL需要每次事务提交都fsync binary log，这与innodb redo log的瓶颈类似，严重影响了性能，但是这还远不是最可怕的。

我们查阅Innodb的代码，我们在Ha_innodb.cc中找到了InnoDB的prepare实现函数innobase_xa_prepare，在实现中，我们发现了下面这段代码：

［MySQL 5.5］

/* For ibbackup to work the order of transactions in binlog
  and InnoDB must be the same. Consider the situation

    thread1> prepare; write to binlog; ...
     
    thread2> prepare; write to binlog; commit
    thread1>	     ... commit

  To ensure this will not happen we're taking the mutex on
  prepare, and releasing it on commit.

  Note: only do it for normal commits, done via ha_commit_trans.
  If 2pc protocol is executed by external transaction
  coordinator, it will be just a regular MySQL client
  executing XA PREPARE and XA COMMIT commands.
  In this case we cannot know how many minutes or hours
  will be between XA PREPARE and XA COMMIT, and we don't want
  to block for undefined period of time. */
  
mysql_mutex_lock(&prepare_commit_mutex);

为了保证binary log中记载的事务更新与底层引擎层提交的是事务顺序的一致性，innodb在prepare方法中获取了prepare_commit_mutex锁，并且要在commit结束后才释放，这意味着从prepare， 写binlog，到commit整个事务提交三个过程所有线程都是串行执行的，这也意味着innodb redo group commit 完全成了一个摆设，这就是著名的Group Commit Bug。

int ha_commit_trans(THD *thd, bool all){
   .....
   for (; ha_info && !error; ha_info= ha_info->next()){
        if ((err= ht->prepare(ht, thd, all)))
        {
          .....
        }
    }
   ......
   tc_log->log_and_order(thd, xid, all, need_commit_ordered);
  ......
   error = commit_one_phase_low(thd, all, trans, is_real_trans) ? 2 : 0;
......
}

bool MYSQL_BIN_LOG::write_transaction_to_binlog_events(group_commit_entry *entry)
{
   ......
   is_leader= queue_for_group_commit(entry, wfc);
   ......
   /*
    The first in the queue handle group commit for all; the others just wait
    to be signalled when group commit is done.
  */
  if(is_leader)
    trx_group_commit_leader(entry);
 else if (!entry->queued_by_other)
    entry->thd->wait_for_wakeup_ready();
  else
  {
        /*
          If we were queued by another prior commit, then we are woken up
          only when the leader has already completed the commit for us.
          So nothing to do here then.
        */
  }
 ......
}

从逻辑上可以看出，进入commit阶段的线程，首先会判断是不是Leader，第一个进入空队列的线程即为队列Leader，所有的工作都由Leader线程完成，其他的线程等待Leader唤醒，不做任何事情。所以我们的重点是分析trx_group_commit_leader方法：

void MYSQL_BIN_LOG::trx_group_commit_leader(group_commit_entry *leader){
    //队列锁
    mysql_mutex_lock(&LOCK_group_commit_queue);
     ....
    //提供外部参数控制，队列等待提交，提高Group效率
    if (opt_binlog_commit_wait_count)
      wait_for_sufficient_commits();
    ......
    mysql_mutex_unlock(&LOCK_group_commit_queue);
    // 遍历队列中的每个线程，将各个线程队列的binlog cache刷新到binary log文件中
    for(current = queue; current != NULL; current = current->next)
    {
        current->error = write_transaction(current, commit_id)  
    }

    ......
   // 进行一次fsync
    flush_and_sync(&synced)
    ......
    mysql_mutex_lock(&LOCK_commit_ordered);
    ......
   //对队列中每个线程调用引擎层的order_commit方法，主要负责在引擎层写入redo log
    while(current != NULL)
    {
      group_commit_entry *next;
      run_commit_ordered(current->thd, current->all);
      next = current->next;
      current = next;
    }
    ......
    mysql_mutex_unlock(&LOCK_commit_ordered);
    ......
}

在ha_innodb.cc中我们查看order_commit的方法定义：

/*****************************************************************//**
Perform the first, fast part of InnoDB commit.
Doing it in this call ensures that we get the same commit order here
as in binlog and any other participating transactional storage engines.
Note that we want to do as little as really needed here, as we run
under a global mutex. The expensive fsync() is done later, in
innobase_commit(), without a lock so group commit can take place.
Note also that this method can be called from a different thread than
the one handling the rest of the transaction. */
static
void
innobase_commit_ordered(
/*====================*/
 handlerton *hton, /*!< in: Innodb handlerton */
 THD*	thd,	/*!< in: MySQL thread handle of the user for whom
   the transaction should be committed */
 bool	all)

网易云新用户大礼包：https://www.163yun.com/gift