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
Tags: bfsm_2007_10_25, bfsm_2007_11_01, bfsm_2007_12_06, qc
Triage: Triaged: D5 (Feature request) / R1 (None/Negligible) / E1 (None/Negligible)

[17 Oct 2007 14:04] David Shrewsbury
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
[17 Oct 2007 14:25] Guilhem Bichot
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).
[26 Oct 2007 14:26] Mark Leith
Patch to add sync_relay_log variable

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

[26 Oct 2007 14:30] Mark Leith
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
[25 Jun 2008 13:35] Lars Thalmann
See also BUG#35542.
[22 Jul 2008 8:44] Zhenxing He
pushed to mysql-6.0-rpl
[22 Sep 2008 13:39] Bugs System
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.
[15 Oct 2008 10:46] Bugs System
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
[27 Oct 2008 4:14] Bugs System
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.
[27 Oct 2008 15:10] Bugs System
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.
[29 Oct 2008 17:52] Bugs System
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.
[7 Nov 2008 19:22] Bugs System
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.
[11 Nov 2008 11:27] Bugs System
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.
[30 Jan 2009 13:31] Bugs System
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)
[4 Feb 2009 15:21] Jon Stephens
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.
[17 Feb 2009 9:59] Lars Thalmann
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.
[17 Feb 2009 13:32] Jon Stephens
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
[29 Sep 2009 14:28] Bugs System
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
[27 Oct 2009 9:50] Bugs System
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)
[27 Oct 2009 18:59] Jon Stephens
Already documented for 6.0.10.

Closed.
[12 Nov 2009 8:17] Bugs System
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)
[12 Nov 2009 15:04] Jon Stephens
Also documented bugfix in the 5.5.0 changelog; closed.