pt-slave-delay is more delay than set value

Slave Server is delayed more than 12 hours even though I use pt-slave-delay with --delay=12h option.
The slave usually is delayed for about 12 hours but slave is delays for more thant 20 hours
after cold backup which stops MySQL. pt-slave-delay have been stopped for about ten hours.

I reproducted this problem on test environment and test scripts.

  1. Building 1 Master and 1 Slave replicatioin environment

  2. stop MySQL on slave
    slave> /etc/init.d/mysql stop

  3. generate test data on master

sh create_data.sh

  1. execute update MySQL data script on master after generating test data

update_data.sh

  1. start MySQL and pt-slave-delay on slave

/etc/init.d/mysql start; /usr/bin/pt-slave-delay --delay=12h --interval=30s localhost

6 waiting for a while…

I attached pt-slave-delay log. pt-slave-delay wrote the follwing message

2013-08-01T17:27:35 STOP SLAVE until 2013-08-01T19:50:07 at master position mysql-bin.000005/4757615

However pt-slave-delay didn’t start at around 19:50. It started at 4:30:06 on the next day.
It is 18 hour behind!

2013-08-02T04:30:06 START SLAVE until master 2013-08-01T16:30:05 mysql-bin.000005/2045535

Why is pt-slave-delay more delayed than set values(12h)?

The delay will always be approximate, however your variation sounds like a bigger variance than expected.

If you are starting pt-slave-delay on the slave while the slave is already behind the master (slave lag), then you may be hitting this bug:

https://bugs.launchpad.net/percona-toolkit/+bug/1095476

You may also try having the tool connect to the master to calculate the delay (i.e. the --user-master option). I’ve never used that personally, but worth a try to see if it makes any difference for you.

http://www.percona.com/doc/percona-t…ave-delay.html

Hi scott.nemes

Thank you for giving me good advice.

I tries to use --use-master option but nothing has worked.
The problem still unsloved.

I applied the following patched and rechecked the behaviour.
https://bugs.launchpad.net/percona-toolkit/+bug/962330/+attachment/3463314/+files/20121217_r2_pt-slave-delay.diff

The patch sloved the problem but other problem it generated the new problem.
Replication delay gradually become shorter and become 0 delay for the last time.