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 '13 at 19:15
  • Almost of tables are InnoDB. Updated my question to provide my.cnf.
    – quanta
    May 1 '13 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 '13 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 '13 at 3:14

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, privacy policy and cookie policy

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