mysql sync_binlog与组提交的关系

最新推荐文章于 2024-10-13 09:05:32 发布

aoerqileng

最新推荐文章于 2024-10-13 09:05:32 发布

阅读量1.3k

点赞数

分类专栏： MYSQL

本文链接：https://blog.csdn.net/aoerqileng/article/details/106092148

版权

本文探讨了MySQL的sync_binlog参数与组提交之间的关系，以及如何通过binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count控制事务提交行为。当sync_binlog设置为大于1的值时，会导致单个事务提交时间增加，而组提交的阶段包括flush、sync和commit。在sync阶段，有两个参数决定了一组事务的数量。设置sync_binlog为1可以确保数据安全性，但不设置为1也能利用XA事务协调保证数据一致性，然而在某些情况下仍可能出现数据丢失问题。

摘要由CSDN通过智能技术生成

组提交参数：binlog_group_commit_sync_delay

Controls how many microseconds the binary log commit waits before synchronizing the binary log file to disk. By default binlog_group_commit_sync_delay is set to 0, meaning that there is no delay. S
binlog_group_commit_sync_no_delay_count
he maximum number of transactions to wait for before aborting the current delay as specified by binlog_group_commit_sync_delay

默认都是0

When sync_binlog=0 or sync_binlog=1 is set, the delay specified by binlog_group_commit_sync_delay is applied for every binary log commit group before synchronization (or in the case of sync_binlog=0, before proceeding). When sync_binlog is set to a value n greater than 1, the delay is applied after every n binary log commit groups.

看到sync_binlog是影响上面延时参数对多少组有影响。默认就是每一个组都会马上进行刷binlog，没有延时。那这个sync_binlog设置了大于1的数值，是不是针对单个的事务，是增加了提交的时间的。

那么在组提交的3个阶段中，每个阶段要处理多少组？每组有多少事务？有哪些参数控制？

看下5，7源码，这部分函数是处理flush的函数，看到是fush了total_bytes_var 字节的数据，函数里面有记录一个组需要flush多少次。

/**
  Execute the flush stage.

  @param total_bytes_var Pointer to variable that will be set to total
  number of bytes flushed, or NULL.

  @param rotate_var Pointer to variable that will be set to true if
  binlog rotation should be performed after releasing locks. If rotate
  is not necessary, the variable will not be touched.

  @return Error code on error, zero on success
 */

int
MYSQL_BIN_LOG::process_flush_stage_queue(my_off_t *total_bytes_var,
                                         bool *rotate_var,
                                         THD **out_queue_var)
{
   
  DBUG_ENTER("MYSQL_BIN_LOG::process_flush_stage_queue");
  #ifndef DBUG_OFF
  // number of flushes per group.
  int no_flushes= 0;
  #endif
  DBUG_ASSERT(total_bytes_var && rotate_var && out_queue_var);
  my_off_t total_bytes= 0;
  int flush_error= 1;
  mysql_mutex_assert_owner(&LOCK_log);

  /*
    Fetch the entire flush queue and empty it, so that the next batch
    has a leader. We must do this before invoking ha_flush_logs(...)
    for guaranteeing to flush prepared records of transactions before
    flushing them to binary log, which is required by crash recovery.
  */
  THD *first_seen= stage_manager.fetch_queue_for(Stage_manager::FLUSH_STAGE);
  DBUG_ASSERT(first_seen != NULL);
  /*
    We flush prepared records of transactions to the log of storage
    engine (for example, InnoDB redo log) in a group right before
    flushing them to binary log.
  */
  ha_flush_logs(NULL, true