MySQL Bugs: #28760: Simulating a replication lag

Bug #28760	Simulating a replication lag
Submitted:	30 May 2007 3:31	Modified:	25 Nov 2008 16:05
Reporter:	Kay Röpke
Status:	In progress
Category:	Server: Replication	Severity:	S4 (Feature request)
Version:	4.1.22, 5.0.41, 5.1.18-beta	OS:	Any
Assigned to:	Sven Sandberg	Target Version:
Tags:	Contribution, replication, Delay
Triage:	Triaged: D5 (Feature request)

Description:
While tracking down a bug in our software that was only happening when a slave lagged
behind the master in replication, I was looking for a way to delay replication.

The most reliable way was to add support for specifying a replication delay time in the
server.

This has proven very useful as a debugging aid.

How to repeat:
Not applicable.

Suggested fix:
It's in the form of a new global variable 'slave_replication_delay' that's settable via
SET GLOBAL on a slave.
When 'slave_replication_delay' is non-zero it would case the SQL thread to suspend
executing relay log events until at least the amount of seconds specified by
'slave_replication_delay' have elapsed.

I have written and tested a patch for 4.1.22 and also made a patch for 5.0.41 (though
that's not tested yet). Support for 5.1.18-beta, or for that matter any MySQL version that
supports replication, shouldn't be a problem either because the patch is rather small and
confined.

The basis of the delay is the 'when' field of the event and it takes into consideration
any externally imposed replication delay, so that the SQL thread effectively treats the
seconds specified by 'slave_replication_delay' as a lower limit of replication lag.

See our public Worklog for more implementation suggestions
http://forge.mysql.com/worklog/task.php?id=344

Thank you for a reasonable feature request.

I'll go ahead and change my patch to support the CHANGE MASTER enhancement outlined in the
worklog entry mentioned by Lenz.

This bug report may actually be a duplicate of BUG#21639 and BUG#22072 (it seems to be a
very popular request)

Patch was proposed on the internals mailing list: http://lists.mysql.com/internals/34705

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/34023

ChangeSet@1.2563, 2007-09-10 18:29:14-04:00, cmiller@zippy.cornsilk.net +9 -0
  Bug#28760 / WL#344: Delayed replication
  
  Contributed by Kay Roepke, CLA#50.

Couple of notes regarding to the recent patch.
1. i think having a line in SHOW SLAVE STATUS' report about
   the value of the delayed option is necessary as
   there is no other way to get this info except to read it

Note regarding to the recent patch (reviewers are
welcome to evaluate my opinion as well):

1. i think having a line in SHOW SLAVE STATUS' report about
   the value of the delayed option is necessary as
   there is no other way to get this info except to read it
   from master.info.
2. It'd be nice to report some warning in slave's error log
   in case (now - ev->when) < 0.

I will address Andrei's last comments as well as the checking of the return status of
safe_sleep() (as discussed in Heidelberg) to the next patch.

We notice that it’s because of MySQL table locking.

Lasitha,

this patch is meant to introduce an intentional delay into replication, not to discover
the reason for a specific delay.

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/44830

ChangeSet@1.2553, 2008-04-03 16:08:37+07:00, sven@riska.(none) +62 -0
  WL#344: Time-delayed replication
  BUG#28760: Simulating a replication lag
  Implemented a new "CHANGE MASTER TO MASTER_DELAY = X" option, which causes
  the replication slave to be X seconds behind the master.

I _really_ want to use the MASTER_DELAY feature to find some lag race condition bugs. 
(And I'm thinking of using it for intentional 1h delay to rescue us from ourselves in case
of a haywire update horkage).  I'm not particular about how I do that - even willing to
move to other debian repos (dev, test, experimental, etc.)  Would be awesome to simply
find a binary that contains this - and I vote for it to be in the default releases -
extraordinarily useful!  But I'm coming to terms with acceptance that I have to build it
from source...

I have successfully built mysql-5.0.32 from source on debian (etch).  I would now like to
patch the source with the MASTER_DELAY patch referenced here.  In my source tree I have
debian/patches/ with many security patches in the "dpatch" format - I'm fairly confident
that if I could find this patch in that format I could cause it to be rolled into the
buildeb.  

Has anyone successfully applied this patch on debian and if so, what steps did you take? 
RTFM links welcomed.

Marc, we're looking at this in OurDelta (http://ourdelta.org)
On initial review, Sven's 5.1 patch looked doable and not too intrusive, so even a 5.0
backport seems viable but we haven't tried it yet.

It's being tracked at https://bugs.launchpad.net/ourdelta/+bug/288898 please feel free to
add our interest there (launchpad allows you to click on a bug to say "affects me too")
and participate/comment in other ways.

I would really like to see a 5.0 backport of this patch. This provides some great value as
an offsite (or onsite) backup strategy of a mysql database.