MySQL Bugs: #31665: sync_binlog should cause relay logs to be synchronized

Bug #31665	sync_binlog should cause relay logs to be synchronized
Submitted:	17 Oct 2007 14:04	Modified:	12 Nov 2009 15:04
Reporter:	David Shrewsbury	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:	6.0	OS:	Any
Assigned to:	Zhenxing He	CPU Architecture:	Any
Tags:	bfsm_2007_10_25, bfsm_2007_11_01, bfsm_2007_12_06, qc

Description:
According to the Reference Manual, sync_binlog forces only the binary logs to flush to disk. This option should be changed to have the same effect on the relay logs, too.

How to repeat:
n/a

Hi. I coded this option. It was designed to sync only the binlog, not relay logs.
On a system which has binlog and relay log, syncing the relay log doubles the performance hit, and it does not make sense to sync the relay log. Binlog is very important, if master crashes, the user may not want to lose any committed statement, hence --sync-binlog. Without this option, there may be committed transactions on the master which can never reach the slave.
On the other hand, relay log is often unimportant: if slave crashes, it can be repointed to the master. And even if relay log was synced, it would not solve problems of slave crashes, as master.info, relay-log.info and the relay log would still be inconsistent with each other. See
http://forge.mysql.com/worklog/task.php?id=3970
So, I would like to warn everybody against the temptation to make sync-binlog apply to the relay log. Better add a new option --sync-relay-log (but again, I think it's useless given the other problems listed).

Patch to add sync_relay_log variable

Attachment: sync_relay_log.patch (application/octet-stream, text), 5.46 KiB.

Patch attached to add sync_relay_log option. It works just like sync_binlog:

mysql> show variables like 'sync_relay_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| sync_relay_log | 1     | 
+----------------+-------+
1 row in set (0.00 sec)

mysql> set global sync_relay_log = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'sync_relay_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| sync_relay_log | 2     | 
+----------------+-------+
1 row in set (0.00 sec)

mysql> set session sync_relay_log = 3;
ERROR 1229 (HY000): Variable 'sync_relay_log' is a GLOBAL variable and should be set with SET GLOBAL

[markleith@medusa:~/mysql] $ ./mysql-5.0.52-syncrelay/libexec/mysqld --verbose --help | grep -A 1 sync-relay
  --sync-relay-log=#  Synchronously flush relay log to disk after every #th
                      event. Use 0 (default) to disable synchronous flushing.
--
sync-relay-log                    0

See also BUG#35542.

pushed to mysql-6.0-rpl

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54453

2636 He Zhenxing	2008-09-22
      BUG#35542 Add option to sync master and relay log to disk after every event
      BUG#31665 sync_binlog should cause relay logs to be synchronized
      
      This patch also sync relay log info if --sync-relay-log is set to a
      value other than zero.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/56245

2637 He Zhenxing	2008-10-15
      Fixes to improve performance of patch for BUG#31665

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57081

2706 Alfranio Correia	2008-10-27
      Merged patch that was proposed by Sinisa and modified by He Zhenxing to
      fix bugs: BUG#35542 and BUG#31665. This is an update to BUG#40337.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57114

2704 Alfranio Correia	2008-10-27
      Merged patch that was proposed by Sinisa and modified by He Zhenxing to fix bugs: BUG#35542 and BUG#31665. This is an update to BUG#40337.
      And introduced a new recovery process that disregards old relay-log.bin(s) and starts
      re-fetching from the master based on the information in relay-log.info which may be flushed and synced after each transaction commit.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/57346

2704 Alfranio Correia	2008-10-29
      Added option to sync master.info (--sync-master-info, integer) and relay log (--sync-relay-log, integer) to disk after every event and the relay info (--sync-relay-log-info, boolean) per transaction. This supersedes the patch proposed by Sinisa and modified by He Zhenxing 
      to fix bugs: BUG#35542 and BUG#31665. This is an update to BUG#40337.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58228

2704 Alfranio Correia	2008-11-07
      Added option to sync master.info (--sync-master-info, integer) 
      and relay log (--sync-relay-log, integer) to disk after 
      every event and the relay info (--sync-relay-log-info, boolean) per transaction. 
      This supersedes the patch proposed 
      by Sinisa and modified by He Zhenxing to fix bugs: BUG#35542 and BUG#31665. This is an update to BUG#40337.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/58440

2704 Alfranio Correia	2008-11-11
      Added option to sync master.info (--sync-master-info, integer) and relay log (--sync-relay-log, integer) to disk after
      every event and the relay info (--sync-relay-log-info, boolean) per transaction.
      This supersedes the patch proposed by Sinisa and modified by He Zhenxing to fix bugs: BUG#35542 and BUG#31665. 
      This is an update to BUG#40337.

Pushed into 6.0.10-alpha (revid:luis.soares@sun.com-20090129165607-wiskabxm948yx463) (version source revid:luis.soares@sun.com-20090129163120-e2ntks4wgpqde6zt) (merge vers: 6.0.10-alpha) (pib:6)

Documented --sync-relay-log option as follows:

1. In 6.0.10 changelog:

        A new global server variable sync_relay_log is introduced
        for use on replication slaves. Setting this variable to a
        nonzero integer value N causes the slave to synchonize the 
        relay log to disk after every N events. It is analogous to 
        how the sync_binlog variable works for binary logs on a 
        replication master.

        This variable can also be set in my.cnf using the server 
        option --sync-relay-log.

        For more information, see "Replication Slave Options and Variables" (http://dev.mysql.com/doc/refman/6.0/en/replication-options-slave.html).

Set status to NDI pending additional patches pushed to 6.0 tree (per discussion with Lars
and Alfranio).

See also Bug #35542, Bug #40337.

This is fixed and pushed to 6.0 although there are other
improvements (BUG#40337) in progress.  Setting back to
"documenting" state so that this bug can be closed.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/85039

3118 Alfranio Correia	2009-09-29
      BUG#35542 Add option to sync master and relay log to disk after every event
      BUG#31665 sync_binlog should cause relay logs to be synchronized
      
      NOTE: Backporting the patch to next-mr.
            
      Add sync_relay_log option to server, this option works for relay log 
      the same as option sync_binlog for binlog. This option also synchronize
      master info to disk when set to non-zero value.
                  
      Original patches from Sinisa and Mark, with some modifications

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091027094604-9p7kplu1vd2cvcju) (version source revid:zhenxing.he@sun.com-20091026140226-uhnqejkyqx1aeilc) (merge vers: 6.0.14-alpha) (pib:13)

Already documented for 6.0.10.

Closed.

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:alik@sun.com-20091027095744-rf45u3x3q5d1f5y0) (merge vers: 5.5.0-beta) (pib:13)

Also documented bugfix in the 5.5.0 changelog; closed.