Both are running 5.5.28.

For the incremental backup purpose, I need to enable the log-slave-updates option. But Seconds_Behind_Master keeps increasing immediately after restarting MySQL. A simple statement get... 2 minutes to updated on the slave at spare time. At the busy time, it seems that the Slave cannot catch up with the Master, so I have to comment it out and restart MySQL.

Is there any way to improve this?

  • It's very strange, because your slave is stringer then your master, can you provide the my.ini from both master and slave ? and also, are you tables mostly innoDB or MyISAM ? May 1, 2013 at 19:15
  • Almost of tables are InnoDB. Updated my question to provide my.cnf.
    – quanta
    May 1, 2013 at 20:08
  • To clarify... you are saying that enabling log-slave-updates or not is the single factor in your environment that makes the difference between whether your slave can keep up or not.. correct? May 2, 2013 at 22:59
  • @Michael-sqlbot: yes. With my current settings: innodb_max_dirty_pages_pct = 0, sync_binlog = 1, the slave can keep up normally. But if I enable the log-slave-updates, the slave may lag behind the master for a long time. Looks like the sync_binlog is the main culprit: mysqlperformanceblog.com/2009/01/21/…
    – quanta
    May 3, 2013 at 3:14

2 Answers 2


As your main concern is to have an incremental backup solution, you can change the follow variables:


innodb_max_dirty_pages_pct - set it to 75, then innoDB will cache some changes and flush it to disk at once.

innodb_doublewrite - Disable the innodb double write

sync_binlog - Disable syn binlog

long_query_time - increase the long query time or disable it (at the moment you are logging every single query)

Why slave delays?

Other think is, it looks to be a busy master, on master you are able to handle 768 connections at the same time, it means that 768 thread can be writing data to MySQL, and the server (if not having looking issues) will execute all these queries at same time. In the other end(slave), you have a single replication thread to execute all queries received from master, and if a query take 10 seconds to execute on slave, the only thread available to execute changes from master will be busy, in this case, some delay are expected. In your case you also use the slave as a read only copy of the master, then the replication thread may also need to wait some other thread release lock on some rows to apply the changes.


You can try to comment out these two variables on your slave to use default values - they may cause high disk I/O:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.