MySQL binlog

• 今天想到binlog的一个问题,binlog是何时生成的呢?
  正常情况下应该是每次执行更改数据的SQL都会生成对应的binlog event,
  但是如果平时注意的话,就会意识到binlog里不会记录rollback的事务,只有提交的事务,
  那就推测binlog是在提交时生成的.

• 但是用户提交时再生成binlog的话,信息在哪保存的呢?
  redo里只有物理页的修改,不可能是根据redo生成
  想到binlog有个参数
  binlog_cache_size
  官方文档上说: The size of the cache to hold changes to the binary log during a transaction.
  也就是缓存一个事务过程中的数据变化
  而且这个是每个session都会分配那么大的缓存

• 那如果一个大事务,修改的数据超过了缓存大小呢?

mysql> show status like 'binlog%';
+----------------------------+------------------+
| Variable_name              | Value            |
+----------------------------+------------------+
| Binlog_snapshot_file       | mysql-bin.000011 |
| Binlog_snapshot_position   | 23554531         |
| Binlog_cache_disk_use      | 0                |
| Binlog_cache_use           | 1013712          |
| Binlog_stmt_cache_disk_use | 0                |
| Binlog_stmt_cache_use      | 15               |
+----------------------------+------------------+

查看官网
Binlog_cache_disk_use
The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.
可见超过binlog_cache_size的数据,就直接缓存到临时文件中了

• 那到底何时落盘呢?
  查看参数
  sync_binlog
  sync_binlog=N
  where N is a value other than 0 or 1: The binary log is synchronized to disk after N binary log commit groups have been collected.
  可见binlog刷盘是按照事务来做的

由此可见,binlog在提交前都是先缓存,提交后才会落盘

查看源码确认

MySQL Version: 8.0.16

• 查看binlog相关函数 commit (之前版本为 binlog_commit)

/*
 提交事务后处理当前session的binlog以及存储引擎层的提交
主要有三部分工作:
1. 完善binlog cache,补充必要的cache
2. 执行 execute an ordered flush and commit
3. 判断处理异常错误
*/
TC_LOG::enum_result MYSQL_BIN_LOG::commit(THD *thd, bool all) {
  .........
    // 重点关注提交部分
    if (ordered_commit(thd, all, skip_commit)) DBUG_RETURN(RESULT_INCONSISTENT);
  .........
}

• ordered_commit 函数

/*
处理组提交部分逻辑
流程如下:
  1. Queue ourselves for flushing.
  2. Grab the log lock, which might result is blocking if the mutex is
     already held by another thread.
  3. If we were not committed while waiting for the lock
     1. Fetch the queue
     2. For each thread in the queue:
        a. Attach to it
        b. Flush the caches, saving any error code
     3. Flush and sync (depending on the value of sync_binlog).
     4. Signal that the binary log was updated
  4. Release the log lock
  5. Grab the commit lock
     1. For each thread in the queue:
        a. If there were no error when flushing and the transaction shall be
  committed:
           - Commit the transaction, saving the result of executing the commit.
  6. Release the commit lock
  7. Call purge, if any of the committed thread requested a purge.
  8. Return with the saved error code
*/

int MYSQL_BIN_LOG::ordered_commit(THD *thd, bool all, bool skip_commit) {
//     Stage #1: flushing transactions to binary log
  if (has_commit_order_manager(thd)) {
    Slave_worker *worker = dynamic_cast<Slave_worker *>(thd->rli_slave);
    Commit_order_manager *mngr = worker->get_commit_order_manager();

    if (mngr->wait_for_its_turn(worker, all)) {
      thd->commit_error = THD::CE_COMMIT_ERROR;
      DBUG_RETURN(thd->commit_error);
    }

    if (change_stage(thd, Stage_manager::FLUSH_STAGE, thd, NULL, &LOCK_log))
      DBUG_RETURN(finish_commit(thd));
  } else if (change_stage(thd, Stage_manager::FLUSH_STAGE, thd, NULL,
                          &LOCK_log)) {
    DBUG_PRINT("return", ("Thread ID: %u, commit_error: %d", thd->thread_id(),
                          thd->commit_error));
    DBUG_RETURN(finish_commit(thd));
  }
.......
  /*
    Stage #2: Syncing binary log file to disk
  */

  if (change_stage(thd, Stage_manager::SYNC_STAGE, wait_queue, &LOCK_log,
                   &LOCK_sync)) {
    DBUG_PRINT("return", ("Thread ID: %u, commit_error: %d", thd->thread_id(),
                          thd->commit_error));
    DBUG_RETURN(finish_commit(thd));
  }

  /*
    Shall introduce a delay only if it is going to do sync
    in this ongoing SYNC stage. The "+1" used below in the
    if condition is to count the ongoing sync stage.
    When sync_binlog=0 (where we never do sync in BGC group),
    it is considered as a special case and delay will be executed
    for every group just like how it is done when sync_binlog= 1.
  */
  if (!flush_error && (sync_counter + 1 >= get_sync_period()))
    stage_manager.wait_count_or_timeout(
        opt_binlog_group_commit_sync_no_delay_count,
        opt_binlog_group_commit_sync_delay, Stage_manager::SYNC_STAGE);

  final_queue = stage_manager.fetch_queue_for(Stage_manager::SYNC_STAGE);

  if (flush_error == 0 && total_bytes > 0) {
    DEBUG_SYNC(thd, "before_sync_binlog_file");
   // !!!! 调用系统调用 Call fsync() to sync the file to disk.
    std::pair<bool, bool> result = sync_binlog_file(false);
    sync_error = result.first;
  }

//     Stage #3: Commit all transactions in order.

}

证实确实是操作缓存到cache,提交时再做罗盘